0

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 usgovt:

 category   | numeric                     |
 geom       | geometry(MultiPolygon,5070) |

And my second table is called forest:

geom       | geometry(MultiPolygon,5070) |

I want to calculate the area of the intersection that is in each category.

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?

1

To perform queries fast you need to have indexes on both geometries that you compare using ST_Intersects. I don't know if you have an index on the forest table, but for sure your subquery doesn't have indexes on the unions. I see two options. Create a table based on your subquery and add indexes on the resulting geoms OR join your tables before aggregating them like this:

SELECT
  g.category AS category,
  ST_Area(ST_Union(g.geom)) AS area
FROM usgovt g
JOIN forest f ON ST_Intersects(g.geom, f.geom)
GROUP BY category;

(I don't think you need this: ST_Transform(g.geom, 4326)::geography when working in EPSG:5070, you are already using meters)

  • Thanks very much (and thanks for the tip about ST_Transform)! I think the query you provide will give me the area of the whole polygon, though, rather than just the intersection of the two? – Richard Apr 24 '17 at 17:45
  • I ended up creating a table from the subquery and indexing it, then calculating the intersection afterwards. – Richard Apr 24 '17 at 22:07

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.