I'm using Postgres 9.6 and PostGIS 2.3.2. I want to find the intersection of two tables, then the area of the intersection for each of the attributes from the first table. My first table is called
category | numeric | geom | geometry(MultiPolygon,5070) |
And my second table is called
geom | geometry(MultiPolygon,5070) |
I want to calculate the area of the intersection that is in each
The tables are large, so I am keen to do this efficiently. This is my first attempt but it is VERY slow (been running for many hours):
SELECT g.category AS category, ST_Area(ST_Transform(g.geom, 4326)::geography) AS area, ST_Area(ST_Transform(ST_Intersection(g.geom, r.st_union), 4326)::geography) as overlap FROM (SELECT category, ST_Union(geom) AS geom FROM usgovt GROUP BY category) g, forest f WHERE ST_Intersects(g.geom, f.geom);
I think I'm doing it wrong.
What is the most efficient way to calculate the intersection once, THEN look at the area of the intersection in each category?