PostGIS Polygons – How to Get Intersection of Polygons in the Same Table

postgispostgresqlpostgresql-9.6spatial-analystsql

I have a single table of over 1000 polygons in postgreSQL (with PostGIS), and I have a field "evaluation_type" that only receives two values, either 'good'(green) or 'bad'(red), as you can see in the picture below.

Once some 'bad' and 'good' polygons overlap, I'd like to use a PostGIS function where I can get the following:

And in the attribute table I'd have
– 'good'
– 'bad'
– 'fair': For the intersections

I'd like the result layer (the one containing the intersection) in a "2D" layer, in other words, I want no overlap between the intersects. It's because, in the end, the thing I'm looking for is to count, for each small little polygon, how many 'good' and 'bad' polygons it represents, see below the hypothetical attribute table I want to have.

id| evaluation | count_good | count_bad

1 |  good    |   223    |   0 
2 |  fair    |   108    |   288 
3 |  bad     |   0      |   278 
4 |  fair    |   193    |   132 
5 |  fair    |   183    |   198
6 |  ...

If the small polygon only represents a intersection of 'good' polygons it will receive an value of 'good' and will have 0 in the field 'count_bad'
If the small polygon only represents a intersection of 'bad' polygons it will receive an value of 'bad' and will have 0 in the field 'count_good'
If the small polygon represents a intersection of 'bad' and 'good' polygons it will receive an value of 'fair' and will have 0 counts > 0 in both fields 'count_good' and 'count_bad'

As far as I remember in ArcGIS Union tool, it would break the intersections. I realized that ST_Union behaves a bit different. I used the following code and it returned a dissolved polygon.

    CREATE TABLE out_table AS  
    SELECT ST_Union(geom) as geom  
    FROM areas_demo;

Do you have a hint, how could I get such result I described?

Best Answer

you can use ST_INTERSECTION using the source table twice. Make sure to avoid comparing the a polygon to itself. Also avoid comparing a pair of polygon twice.

SELECT ST_INTERSECTION(a.geom, b.geom), 'fair'
FROM mytable a, mytable b
WHERE a.ID < b.ID
AND ST_INTERSECTS(a.geom, b.geom);

Related Solutions

PostGIS Polygons – How to Separate Polygons Based on Intersection

Since you said you get a group of intersecting polygons for each polygon you're interested in, you may want to create what is referred to as a "polygon overlay".

This isn't exactly what Adam's solution is doing. To see the difference, take a look at this picture of an ABC intersection:

ABC intersection

I believe Adam's solution will create an "AB" polygon that covers both the area of "AB!C" and "ABC", as well as an "AC" polygon that covers "AC!B" and "ABC", and a "BC" polygon that is "BC!A" and "ABC". So the "AB", "AC", and "BC" output polygons would all overlap the "ABC" area.

A polygon overlay produces non-overlapping polygons, so AB!C would be one polygon and ABC would be one polygon.

Creating a polygon overlay in PostGIS is actually pretty straightforward.

There are basically three steps.

Step 1 is extract the linework [Note that I'm using the exterior ring of the polygon, it does get a little more complicated if you want to correctly handle holes]:

SELECT ST_ExteriorRing(polygon_col) AS the_geom FROM my_table) AS lines

Step 2 is to "node" the linework (produce a node at every intersection). Some libraries like JTS have "Noder" classes you can use to do this, but in PostGIS the ST_Union function does it for you:

SELECT ST_Union(the_geom) AS the_geom FROM (...your lines...) AS noded_lines

Step 3 is to create all the possible non-overlapping polygons that can come from all those lines, done by the ST_Polygonize function:

SELECT ST_Polygonize(the_geom) AS the_geom FROM (...your noded lines...)

You could save the output of each of those steps into a temp table, or you can combine them all into a single statement:

CREATE TABLE my_poly_overlay AS
SELECT geom FROM ST_Dump((
    SELECT ST_Polygonize(the_geom) AS the_geom FROM (
        SELECT ST_Union(the_geom) AS the_geom FROM (
            SELECT ST_ExteriorRing(polygon_col) AS the_geom FROM my_table) AS lines
        ) AS noded_lines
    )
)

I'm using ST_Dump because the output of ST_Polygonize is a geometry collection, and it is (usually) more convenient to have a table where each row is one of the polygons that makes up the polygon overlay.

PostGIS ST_Intersection – Performing Intersection of All Geometries in a Table

As I understand that you would like to intersect the geometries across rows in one table, so you need to do something like that:

  select (ST_intersection(a.geom, b.geom)) from t1 a, t1 b where ST_Intersects
    (a.geom,b.geom) AND a.id<b.id And a.foo='bar';

Tested (working)

id| evaluation | count_good | count_bad

Best Answer

Related Solutions

PostGIS Polygons – How to Separate Polygons Based on Intersection

PostGIS ST_Intersection – Performing Intersection of All Geometries in a Table

Related Question