Split polygon with polygon in different tables / layers

Question

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 st_area.

Something like

SET table_a UPDATE bldg_footprint = st_area (cut_buildings) FROM table_b 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.

Answer 1

This is probably not the most elegant or efficient way of solving the problem but it worked for me;

The following cuts the black blocks using the red lines and returns the measurement of the cut blocks falling within the red line;

SELECT boundaries.postcode, boundaries.pc_sectors, ST_Intersection(boundaries.pc_unit_geom, bldgs.geom) AS bldgs_split,--splits the black blocks ST_Area (st_intersection(boundaries.pc_unit_geom, bldgs.geom)) as bldg_area --returns area of the split blocks, as individual records FROM d_os_vectormap_bldgs bldgs, d_os_codepoint_poly boundaries WHERE st_intersects(boundaries.pc_unit_geom, bldgs.geom) --selects blocks within the boundary

I then passed the results of that query to another, as below, to get the sum of all the block by boundary area, you could do this by modifying the original query using group by.

SELECT SUM (bldg_area) as sum_bldg_footprint, boundary_id from buildings_clipped_to_boundaries GROUP BY boundary_id

NB Make sure you have an index on the geometry columns or this will be very slow.