I have a large number (1 million+) multi point polygons (red lines, table_a), in which are around 20 million other multi point polygons (black blocks table_b). They are in separate tables. What i want to do is measure the sum of the area of the black blocks within the red line.
My workflow is to first cut the black blocks along the red line then use
st_intersection to select them and sum using
UPDATE bldg_footprint = st_area (cut_buildings)
WHERE ST_Intersects( admin_bound table_a , buildings table_b )
I can see from this answer here. That I can either the blocks directly using the polygon or first extract the lines and then cut using lines.
My question is wether there's a function in PostGIS that is more efficient than that approach?
I'd be open to a QGIS solution but had assumed that given the size of data PostGIS will be faster.