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


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);
  • Is that still true that regular polygons are more efficient? If so, why? Do indexes on multi polygon columns have some kind of shortfall? – Evan Carroll Nov 1 '16 at 6:02

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';
  • So, as far as processing speed goes, it does not matter if I store the landuse as multipolygon or singlepolygon? I added a drawing to my question to explain better what I'm trying to acheive. – Daniel Jun 13 '14 at 11:28
  • Based on your drawing, you need to use ST_Split() to clip the forest polygons using the municipal polygons. And you're forced to ST_Dump() the multipolygons to individual polygon records in order to use ST_Split() as it will not work with multipolygons. – mtn.biker Jun 13 '14 at 12:31
  • So if I store the result of ST_Dump in a new table, all my calls to ST_Split will be faster each time I need to do this? I ask this because we intend on building a system where the user will query forested areas (or other land uses) for a municipality but we don't know which. So I could either store all land uses for all municipalities in a table or calculate the area on the fly – Daniel Jun 13 '14 at 19:18
  • ST_Split is not the right function here. It will just split the polygon. Use ST_Intersection to get the interesecting part. – Nicklas Avén Jun 16 '14 at 6:56

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.