Multipolygon vs Polygon Features for Large Intersect Operations in PostGIS

extentsgeoprocessingintersectionpostgis

I have two large layers that I want to do intersect operations. One is municipalities and the other is landuse. Right now, the landuse layer is of MultiPolygon type that is, for the entire state, I have only one entry in the table with the class "Forest", but composed of several small polygons.

If I wanted to find all the forest areas in my city, what would be more efficient in PostGIS:

  1. Do an intersect between my municipality and the FOREST multipolygon that takes up the entire state or,
  2. Convert the Forest multipolygon into a new layer with several polygons and then do the Intersect

My gut feeling is that if I break the multipolygon feature into single polygons, the intersect might be faster because it would only occur in the polygons that have a bounding box that overlaps my municipality. If I have multipolygon features, the intersect would have to go through the entire state to identify only the parts that fall inside the municipality.

This is more or less what I'm trying yo get. I want the light green polygons.

Does it matter it the forest polygons (F) are stored as multipolygons or singlepolygons?

I'm going to be processing a very large dataset (Amazon)

enter image description here

Best Answer

You are right that dumping the multipolygons to polygons makes the index more efficient, for the reason you mention.

So if the size of the dataset makes performance an issue you should:

  1. dump the multipolygons into a new table.
  2. create a spatial index on the polygons
  3. get the intersecting part and restrict the query to polygons theat intersect.

Something like:

/*Create polygon table from multipolygons*/
Create table forest_polygons as select (st_dump(geom)).geom geom from forest_table;

/*create the spatial index*/
create index idx_forest_geom on forest_polygons using gist(geom);

/*tell the planner about the index*/
analyze forest_polygons;

/*Make a selection of forest in the municipality, or more correct:
Asign municipalitiy id to the forests*/
Select st_intersection(gem), municipalities_id as forest_in_municipality from
forest_polygons inner join municipalities 
      on st_intersects(forest_polygons.geom, municipalities.geom);
Related Question