> > From: postgis-devel-boun...@postgis.refractions.net > [mailto:postgis-devel-boun...@postgis.refractions.net] On Behalf Of Rafal > Foltynski > Sent: Friday, April 23, 2010 11:32 AM > To: postgis-de...@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
_______________________________________________ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users