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

[Stephen Woodbridge]

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