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

[Paragon Corporation]

What about ST_Dwithin(q.the_geom, l.gda_geom,0.00001)

&& and ST_Distance actually doesn't have as much short-circuiting as
ST_Dwithin since it has to compute the distance for && matches  --
ST_Dwithin in addition to having && built in kicks out once it has concluded
the within distance criteria has been achieved. 

-----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