[postgis-users] [postgis-devel] St_Intersection on same table

[Mike Toews]

>
> From: postgis-devel-bounces at postgis.refractions.net
> [mailto:postgis-devel-bounces at postgis.refractions.net] On Behalf Of Rafal
> Foltynski
> Sent: Friday, April 23, 2010 11:32 AM
> To: postgis-devel at postgis.refractions.net
> Subject: [postgis-devel] St_Intersection on same table
>
> Hi,
>
> I have a table with many overlying polygons.  I want to create a new
> polygon
> relation from the intersection of these polygons that has two fields 1.
> geometry of small polygons - the result of St_Intersection() and 2. count
> of
> the number of overlying polygons that each small polygon was created from.
> How do I create a query to run St_Intersect on the same geometry field in
> one table?
>
> For example: if I have a table with 3 polygons, 2 of them share a small
> area
> and the third polygon is disjoint, the resulting polygon relation would
> only
> have one small polygon area (shared area between two polygons) and count of
> 2.
>
> Rafal
>

My query for this would look something like:

select count(distinct g1.gid), count(distinct g2.gid), array_accum(distinct
g1.gid), array_accum(distinct g2.gid), st_collect(distinct g1.geometry) as
st_collect1, st_collect(distinct g2.geometry) as st_collect2,
st_intersection(g1.geometry, g2.geometry),
st_area(st_intersection(g1.geometry, g2.geometry))
from geom_table g1, geom_table g2
where g1.gid < g2.gid and st_intersects(g1.geometry, g2.geometry) and
st_isvalid(g1.geometry) and st_isvalid(g2.geometry)
group by st_intersection(g1.geometry, g2.geometry)
order by count(distinct g1.gid), st_area(st_intersection(g1.geometry,
g2.geometry))

A few notes:

   - I use a custom array_accum function described here:
   http://www.postgresql.org/docs/current/static/xaggr.html
   - My primary key is gid serial, which is used in the array_accum
   aggregate function for my information
   - There might be a few cases where count(distinct g1.gid)
   <> count(distinct g2.gid), so you might want both counts shown. This seems
   to be the case if you have multiple exact geometries with different PKs.

Hope this helps.

-Mike
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20100423/e9e243a5/attachment.html>