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%.
However, after running:
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:
Polygon 2 is what I imagine I would like PostGIS to calculate it from:
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.