Re: [postgis-users] Selecting Unique polygons with st_intersects()
Hi Regina,
On 07/06/2011, at 1:54 PM, Paragon Corporation wrote:
> What about ST_Dwithin(q.the_geom, l.gda_geom,0.1)
>
> && 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.
Vey similar - looking at the Join filter it appears to be the same issue where
the planner is overly optimistic about the time it will take.
QUERY PLAN
-
Unique (cost=117.57..117.58 rows=1 width=405051) (actual
time=56098.368..56099.248 rows=2679 loops=1)
-> Sort (cost=117.57..117.58 rows=1 width=405051) (actual
time=56098.367..56098.540 rows=2870 loops=1)
Sort Key: q.pic, (st_area(st_intersection(q.the_geom, l.gda_geom)))
Sort Method: quicksort Memory: 433kB
-> Nested Loop (cost=0.00..117.56 rows=1 width=405051) (actual
time=38.165..56061.873 rows=2870 loops=1)
Join Filter: ((l.gda_geom && st_expand(q.the_geom, 1e-05::double
precision)) AND _st_dwithin(q.the_geom, l.gda_geom, 1e-05::double precision))
-> Seq Scan on lga l (cost=0.00..31.76 rows=10 width=400338)
(actual time=0.110..0.255 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.676
rows=359 loops=10)
Index Cond: (q.the_geom && st_expand(l.gda_geom,
1e-05::double precision))
Total runtime: 56099.663 ms
(11 rows)
cheers
Ben
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Selecting Unique polygons with st_intersects()
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-boun...@postgis.refractions.net
[mailto:postgis-users-boun...@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))/1)::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))/1)::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 usi
Re: [postgis-users] Selecting Unique polygons with st_intersects()
What about ST_Dwithin(q.the_geom, l.gda_geom,0.1)
&& 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-boun...@postgis.refractions.net
[mailto:postgis-users-boun...@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))/1)::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))/1)::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@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
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))/1)::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))/1)::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@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Selecting Unique polygons with st_intersects()
Ben, 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 On 6/6/2011 10:00 PM, Ben Madin wrote: Thanks Regina, you are right - for some unknown reason I had in my mind that the order by clause would come after the distinct giving no surety of the part of the property being returned. For Posterity, I used : 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))/1)::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; It's surprisingly slow, so I'll have to fiddle with the indexes, but it certainly does what I want. cheers Ben On 07/06/2011, at 8:56 AM, Paragon Corporation wrote: Ben, Perhaps I'm missing something -- but why don't you just put an ORDER BY in your DISTINCT ON to control which one is returned? SELECT DISTINCT ON (pic) lga_name09 as shire, propname as name, pic 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 Hope that helps, Regina http://www.postgis.us -Original Message- From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of Ben Madin Sent: Monday, June 06, 2011 5:36 PM To: PostGIS Users Discussion Subject: [postgis-users] Selecting Unique polygons with st_intersects() G'day all, I've just realised that in selecting a group of properties by the local government area they reside in, I end up with duplicates = some properties span shire boundaries. I've used st_intersects, because some properties cover boundaries, so I need those that are within and may be partially without the boundary. I'm sure I'm not the first person to have this problem, but I was wondering if anyone has any insights into the most efficient way to choose a unique listing of property and shire. I was wondering about taking the area of the property still within the local government area, and choosing the row with the biggest value... Although I have unique property identifiers, I can't be sure of not getting a ridiculous answer - an erroneously small amount of a property in a shire etc. My current query looks a bit like : SELECT DISTINCT ON (pic) lga_name09 as shire, propname as name, pic 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); but I need to do better than using SELECT DISTINCT ON. cheers Ben ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Selecting Unique polygons with st_intersects()
Thanks Regina, you are right - for some unknown reason I had in my mind that the order by clause would come after the distinct giving no surety of the part of the property being returned. For Posterity, I used : 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))/1)::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; It's surprisingly slow, so I'll have to fiddle with the indexes, but it certainly does what I want. cheers Ben On 07/06/2011, at 8:56 AM, Paragon Corporation wrote: > Ben, > > Perhaps I'm missing something -- but why don't you just put an ORDER BY in > your DISTINCT ON to control which one is returned? > > SELECT DISTINCT ON (pic) lga_name09 as shire, propname as name, pic > 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 > > Hope that helps, > Regina > http://www.postgis.us > > > -Original Message- > From: postgis-users-boun...@postgis.refractions.net > [mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of Ben > Madin > Sent: Monday, June 06, 2011 5:36 PM > To: PostGIS Users Discussion > Subject: [postgis-users] Selecting Unique polygons with st_intersects() > > G'day all, > > I've just realised that in selecting a group of properties by the local > government area they reside in, I end up with duplicates = some properties > span shire boundaries. I've used st_intersects, because some properties > cover boundaries, so I need those that are within and may be partially > without the boundary. > > I'm sure I'm not the first person to have this problem, but I was wondering > if anyone has any insights into the most efficient way to choose a unique > listing of property and shire. I was wondering about taking the area of the > property still within the local government area, and choosing the row with > the biggest value... > > Although I have unique property identifiers, I can't be sure of not getting > a ridiculous answer - an erroneously small amount of a property in a shire > etc. > > My current query looks a bit like : > > SELECT DISTINCT ON (pic) lga_name09 as shire, propname as name, pic 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); > > but I need to do better than using SELECT DISTINCT ON. > > cheers > > Ben > > > ___ > postgis-users mailing list > postgis-users@postgis.refractions.net > http://postgis.refractions.net/mailman/listinfo/postgis-users > > > ___ > postgis-users mailing list > postgis-users@postgis.refractions.net > http://postgis.refractions.net/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Selecting Unique polygons with st_intersects()
Ben, Perhaps I'm missing something -- but why don't you just put an ORDER BY in your DISTINCT ON to control which one is returned? SELECT DISTINCT ON (pic) lga_name09 as shire, propname as name, pic 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 Hope that helps, Regina http://www.postgis.us -Original Message- From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of Ben Madin Sent: Monday, June 06, 2011 5:36 PM To: PostGIS Users Discussion Subject: [postgis-users] Selecting Unique polygons with st_intersects() G'day all, I've just realised that in selecting a group of properties by the local government area they reside in, I end up with duplicates = some properties span shire boundaries. I've used st_intersects, because some properties cover boundaries, so I need those that are within and may be partially without the boundary. I'm sure I'm not the first person to have this problem, but I was wondering if anyone has any insights into the most efficient way to choose a unique listing of property and shire. I was wondering about taking the area of the property still within the local government area, and choosing the row with the biggest value... Although I have unique property identifiers, I can't be sure of not getting a ridiculous answer - an erroneously small amount of a property in a shire etc. My current query looks a bit like : SELECT DISTINCT ON (pic) lga_name09 as shire, propname as name, pic 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); but I need to do better than using SELECT DISTINCT ON. cheers Ben ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Selecting Unique polygons with st_intersects()
Hi Ben, Can you do something with this as a sub-select: select gid, st_area(st_intersection(a.the_geom, b.the_geom)) as size from tablea a, tableb b where a.the_geom && b.the_geom and size is not null order by size desc limit 1 Hopefully, st_area() can deal with collections that might include points and linestrings, or you will have to dump and filter the intersection results. I would be interested in see your query if you get something to work. -Steve On 6/6/2011 5:35 PM, Ben Madin wrote: G'day all, I've just realised that in selecting a group of properties by the local government area they reside in, I end up with duplicates = some properties span shire boundaries. I've used st_intersects, because some properties cover boundaries, so I need those that are within and may be partially without the boundary. I'm sure I'm not the first person to have this problem, but I was wondering if anyone has any insights into the most efficient way to choose a unique listing of property and shire. I was wondering about taking the area of the property still within the local government area, and choosing the row with the biggest value... Although I have unique property identifiers, I can't be sure of not getting a ridiculous answer - an erroneously small amount of a property in a shire etc. My current query looks a bit like : SELECT DISTINCT ON (pic) lga_name09 as shire, propname as name, pic 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); but I need to do better than using SELECT DISTINCT ON. cheers Ben ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] Selecting Unique polygons with st_intersects()
G'day all, I've just realised that in selecting a group of properties by the local government area they reside in, I end up with duplicates = some properties span shire boundaries. I've used st_intersects, because some properties cover boundaries, so I need those that are within and may be partially without the boundary. I'm sure I'm not the first person to have this problem, but I was wondering if anyone has any insights into the most efficient way to choose a unique listing of property and shire. I was wondering about taking the area of the property still within the local government area, and choosing the row with the biggest value... Although I have unique property identifiers, I can't be sure of not getting a ridiculous answer - an erroneously small amount of a property in a shire etc. My current query looks a bit like : SELECT DISTINCT ON (pic) lga_name09 as shire, propname as name, pic 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); but I need to do better than using SELECT DISTINCT ON. cheers Ben ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
