[GIS] Should I split MultiPolygons into Polygons

performancepolygonpostgis

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.

Best Answer

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.

Related Solutions

[GIS] Cleaning geometries in PostGIS

You can try ST_CollectionExtract to extract [Multi]Polygons from GeometryCollections. Use ST_Multi to force them as MuliPolygons.

UPDATE public.valid_lcmsouthshapefile
  SET geom=ST_Multi(ST_CollectionExtract(ST_MakeValid(geom), 3))
  WHERE NOT ST_IsValid(geom);

After your done, use a CHECK constraint to ensure they stay valid. See details here.

[GIS] Importing MultiPolygon into PostGIS

Your MultiPolygon is invalid. You can test it with ogrinfo:

ogrinfo -ro -dialect sqlite -sql "select IsValid(geometry) from
OGRGeoJSON" multipolygon.json
INFO: Open of `multipolygon.json'
      using driver `GeoJSON' successful.
GEOS warning: Self-intersection at or near point -115.907104 39.162109000000001

Layer name: SELECT
Geometry: None
Feature Count: 1
Layer SRS WKT:
(unknown)
isvalid(geometry): Integer (0.0)
OGRFeature(SELECT):0
  isvalid(geometry) (Integer) = 0

You can try to correct it in PostGIS with ST_MakeValid http://postgis.net/docs/ST_MakeValid.html. However, it is not simple

Step 1 Correct the faulty geometry:

create table valid as select st_makevalid(geometry) as geometry from multipolygon;

Step 2 Test:

select * from valid where ST_Intersects(ST_PointFromText('POINT( -116.024551 38.485773 )',4326), geometry);

Result: error

ERROR:  Relate Operation called with a LWGEOMCOLLECTION type.  This is unsupported.
HINT:  Change argument 2: 'GEOMETRYCOLLECTION(POLYGON((-115.000793 38.677307,-115.000793 38.499878,-115....'

Pity that ST_Intersects does not handle geometrycollections. So you must get rid of the collection. First part of the collection is the polygon so you can take just that.

Step 3 Keep just the polygon part

update valid set geometry=ST_GeometryN(geometry,1);

Step 4 Test again

select * from valid where ST_Intersects(ST_PointFromText('POINT( -116.024551 38.485773 )',4326), geometry);

Result: Success!

Best Answer

Related Solutions

[GIS] Cleaning geometries in PostGIS

[GIS] Importing MultiPolygon into PostGIS

Related Question