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

[Ben Madin]

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))/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;

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-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
> 
> 
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users