[postgis-users] Selecting Unique polygons with st_intersects()

[Paragon Corporation]

Ben,

Forgot to ask how slow it is without the intersection.  Probably
intersection is eating up a good chunk of time.

We sometimes get better performance by simplifying before running the
intersection.  In your case since its just a rule of thumb check, a high
simplification would probably work fine.  The simplification would then make
the intersection faster.

So something like

ST_Intersection(ST_SimplifyPreserveTopology(q.the_geom, 800),
ST_SimplifyPreserveTopology(l.gda_geom, 800))

Well the 800 you can play with.  I'm not sure how large your geometries are.


Simplification also works for speeding up ST_Dwtihin and intersect checks as
we described here

http://www.postgis.us/downloads/oscon2009_PostGISTips.pdf


Hope that helps,
Regina and Leo
http://www.postgis.us

  

-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Ben
Madin
Sent: Tuesday, June 07, 2011 12:17 AM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] Selecting Unique polygons with st_intersects()

Steve, 

for fun... why not!

It actually took longer. I'm very bad at understanding query plans, but it
looks to me like the planner thought it would be quicker on less rows, but
it actually took much longer...

any thoughts would be appreciated - I've attached the queries and output
below. FWIW there are indexes on both gid fields (primary keys) and both
geometry fields (GIST)

Ben





On 07/06/2011, at 11:12 AM, Stephen Woodbridge wrote:

> I know the newer functions are supposed to be index aware, but for 
> grins, try changing the ON clause to
> 
> ON q.the_geom && l.gda_geom and st_distance(q.the_geom, 
> l.gda_geom)=0.0 AND l.gid ...
> 
> Objects that intersect have to have a distance of 0.0 and the distance
function may have some faster algorithms than intersect.
> 
> -Steve


EXPLAIN ANALYZE SELECT DISTINCT ON (pic) 'QLD'::text as state, lga_name09 as
shire, propname as name, pic, round((st_area(st_transform(q.the_geom,
32754))/10000)::numeric,2) as ha FROM lga l JOIN qldproperties q ON
st_intersects(q.the_geom, l.gda_geom) AND l.gid IN
(245,247,252,254,258,259,275,279,289,297)
ORDER BY pic, st_area(st_intersection(q.the_geom, l.gda_geom)) DESC;

 
QUERY PLAN

----------------------------------------------------------------------------
----------------------------------------------------------------------------
-------------
 Unique  (cost=312868.52..312871.53 rows=602 width=405051) (actual
time=19483.631..19484.592 rows=2673 loops=1)
   ->  Sort  (cost=312868.52..312870.03 rows=602 width=405051) (actual
time=19483.629..19483.828 rows=2861 loops=1)
         Sort Key: q.pic, (st_area(st_intersection(q.the_geom, l.gda_geom)))
         Sort Method:  quicksort  Memory: 432kB
         ->  Nested Loop  (cost=0.00..276.73 rows=602 width=405051) (actual
time=9.762..19446.773 rows=2861 loops=1)
               Join Filter: _st_intersects(q.the_geom, l.gda_geom)
               ->  Seq Scan on lga l  (cost=0.00..31.76 rows=10
width=400338) (actual time=0.124..0.262 rows=10 loops=1)
                     Filter: (gid = ANY
('{245,247,252,254,258,259,275,279,289,297}'::integer[]))
               ->  Index Scan using qldproperties_the_geom_gist on
qldproperties q  (cost=0.00..8.28 rows=1 width=4713) (actual
time=0.036..1.680 rows=359 loops=10)
                     Index Cond: (q.the_geom && l.gda_geom)  Total runtime:
19489.877 ms



EXPLAIN ANALYZE SELECT DISTINCT ON (pic) 'QLD'::text as state, lga_name09 as
shire, propname as name, pic, round((st_area(st_transform(q.the_geom,
32754))/10000)::numeric,2) as ha FROM lga l JOIN qldproperties q ON
q.the_geom && l.gda_geom and st_distance(q.the_geom, l.gda_geom)=0.0 AND
l.gid IN (245,247,252,254,258,259,275,279,289,297)
ORDER BY pic, st_area(st_intersection(q.the_geom, l.gda_geom)) DESC;

 
QUERY PLAN

----------------------------------------------------------------------------
----------------------------------------------------------------------------
-------------
 Unique  (cost=1680.75..1680.80 rows=9 width=405051) (actual
time=56247.410..56248.288 rows=2673 loops=1)
   ->  Sort  (cost=1680.75..1680.77 rows=9 width=405051) (actual
time=56247.408..56247.586 rows=2861 loops=1)
         Sort Key: q.pic, (st_area(st_intersection(q.the_geom, l.gda_geom)))
         Sort Method:  quicksort  Memory: 432kB
         ->  Nested Loop  (cost=0.00..119.61 rows=9 width=405051) (actual
time=33.922..56211.975 rows=2861 loops=1)
               Join Filter: (st_distance(q.the_geom, l.gda_geom) = 0::double
precision)
               ->  Seq Scan on lga l  (cost=0.00..31.76 rows=10
width=400338) (actual time=0.050..0.212 rows=10 loops=1)
                     Filter: (gid = ANY
('{245,247,252,254,258,259,275,279,289,297}'::integer[]))
               ->  Index Scan using qldproperties_the_geom_gist on
qldproperties q  (cost=0.00..8.28 rows=1 width=4713) (actual
time=0.034..1.639 rows=359 loops=10)
                     Index Cond: (q.the_geom && l.gda_geom)  Total runtime:
56248.683 ms
(11 rows)

_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users