<div class="gmail_quote"><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex;"><div><div class="h5">From: <a href="mailto:postgis-devel-bounces@postgis.refractions.net">postgis-devel-bounces@postgis.refractions.net</a><br>


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

<div>from geom_table g1, geom_table g2</div><div>where g1.gid < g2.gid and st_intersects(g1.geometry, g2.geometry) and st_isvalid(g1.geometry) and st_isvalid(g2.geometry)</div><div>group by st_intersection(g1.geometry, g2.geometry)</div>

<div>order by count(distinct g1.gid), st_area(st_intersection(g1.geometry, g2.geometry))</div><div><br></div></div><div>A few notes:</div><div><ul><li>I use a custom array_accum function described here: <a href="http://www.postgresql.org/docs/current/static/xaggr.html" target="_blank">http://www.postgresql.org/docs/current/static/xaggr.html</a></li>

<li>My primary key is gid serial, which is used in the array_accum aggregate function for my information</li><li>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.</li>

</ul></div></div><div>Hope this helps.</div><div><br></div><div>-Mike</div></div></div></div></div>