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

[Stephen Woodbridge]

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