3

I'm trying to calculate the percent of intersection between two polygons using PostGIS.

I was able to do so successfully for 90% of my polygons, but about 1500 have very unexpected values. I extracted one of these cases to examine the behavior.

The orange polygon resides w/in the purple polygons so the proportion should be near 100%.

the two polygons

However, after running:

UPDATE orange 
SET pct_undevelopable = ST_Area(ST_Intersection(orange.geom, purple.geom)) / ST_Area(orange.geom)
FROM purple WHERE ST_Intersects(orange.geom, purple.geom);

I get the value for percent of area of intersection as: 0.000828452095134945

So I inspected the intersection part of the query and found that I got 3 rows as geometry outputs.

I exported all three as individual shapefiles: one simply doesn't work... I tried to open it in QGIS and ArcGIS and I also tried in CSV format for QGIS, no luck.

And two polygons.

Polygon 1 is what I imagine PostGIS is calculating percent of intersection from: enter image description here

Polygon 2 is what I imagine I would like PostGIS to calculate it from: enter image description here

I would love it if anyone can 1) explain what is going on (especially with the weird non functioning intersection geometry) and

2) how to fix it.

2

You're getting strange results because you have more than one row in purple that joins to orange on the condition ST_Intersects(purple.geom, orange.geom). In PostgreSQL, this produces undefined behaviour. From the PostgreSQL docs on UPDATE:

When a FROM clause is present, what essentially happens is that the target table is joined to the tables mentioned in the from_list, and each output row of the join represents an update operation for the target table. When using FROM you should ensure that the join produces at most one output row for each row to be modified. In other words, a target row shouldn't join to more than one row from the other table(s). If it does, then only one of the join rows will be used to update the target row, but which one will be used is not readily predictable.

One way around this issue is to use a subquery to aggregate the sum of the overlap areas:

UPDATE orange 
SET pct_undevelopable = 
    (SELECT sum(ST_Area(ST_Intersection(orange.geom, purple.geom))) 
     FROM purple WHERE ST_Intersects(orange.geom, purple.geom))
     / ST_Area(orange.geom);
|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.