8

In the system I'm implementing I have a table T1 with an associated geometry. Most of the geometries are a set of n polygons, with 10 < n < 100. At the moment in the table T1 I have a geometry column of type MultiPolygon, having a GiST index.

As the table T1 is going to be large, I'm afraid that it would be better to create a one-to-many relation with a second table T2, having a column of type Polygon, and split each MultiPolygon in several Polygons.

But I have to implement searches on the table T1, therefore with the second approach the drawback is that I have an additional join to handle. Furthermore it would be more complicated to insert a geometry for a single record of the table T1.

I am wondering if anyone has any experience with such an issue and if that person could shed some light.

Improve this question

1 Answer 1

Reset to default
1

I'd be inclined to say not to worry about the size of your T1 table; indexes (and triggers of course) are what affect performance for the most part.

In addition to having one fewer joins, the translation of WKB to the internal PostGIS/GEOS representation I would imagine would be slightly more optimal than doing it for several polygons individually.

The only need for having a separate geometry table would be if one polygon was associated to more than one record. Multipolygons should be considered a single entity for the purposes of database design, and only if the relationship is more complex should you consider a separate table. And there would be a slight physical space saving with fewer tables.

Try it with both setups, and running the query in pgadmin which gives you the timings for each part of the query.

Improve this answer
9
  • 4
    @MerseyViking. The index will make a far better job if the multipolygons is splitted into polygons. In the multipolygon case the index is built on the total extent bbox of the multipolygon. The smaller bboxes in the index the more effective index.
    – Nicklas Avén
    May 27, 2011 at 11:38
  • @Nicklas agree BBOX will be larger with multi-polygons. +1
    – Mapperz ♦
    May 27, 2011 at 14:32
  • Ok, thanks. But what I can't understand is when to use the MultiPolygon type if an index alway works better for Polygon.
    – user3125
    May 27, 2011 at 14:40
  • 2
    The decision of polygon vs multi polygon is more complex than that. There is more things to consider. Multi polygons can give a "better" database design. Depending on how the polygons and multi polygons is spatially related and what processing, analyse you want to do, the gain in splitting the multi polygons can be big or nothing. Multi polygons is also a common geometry type as result of processing. If you for instance use ST_Difference on a polygon and a crossing road you will get back a multi polygon.
    – Nicklas Avén
    May 27, 2011 at 15:16
  • 1
    @MerseyViking, I see your point but since he has between 10 and 100 polygons in each multipolygon there is a great chance that splitting them to polygons will give a great boost. And a join on indexed integer fields will not cost that much.
    – Nicklas Avén
    May 27, 2011 at 15:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.