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))/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: [email protected]
[mailto:[email protected]] 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
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
