Using multipolygon or polygon features for large intersect operations?

Question

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)

Answer 1

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);

Answer 2

From your description, a query of landuse='forest' that intersects your municipality will return the one record in your landuse table. And that should be pretty efficient. But it seems like you're really looking for which of the pieces of the forest multipolygon intersect with your municipality polygon(s), so you need to break that into distinct pieces. No need to create a new table, though - use ST_Dump() in your query to do this on the fly, and it should be pretty fast. Like:

SELECT * FROM municipalities m, 
  (SELECT gid, ST_Dump(geom).geom as the_geom FROM landuse WHERE class = 'forest') as foo
WHERE ST_Intersects(m.the_geom, foo.the_geom)
AND m.name = 'Denver';