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

[Paragon Corporation]

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-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at 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 at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users