PostGIS - convert multipolygon to single polygon

Question

Is it possible to import a shape file containing multipolygons into single polygon in PostGIS? Whenever I try importing a shape file of a polygon, it is stored as a multipolygon (as opposed to a single polygon) in a geom column. Thus, I am unable to extract it as a single polygon value from the multipolygon.

All helpful suggestions much appreciated

Interrogate

Answer 1

You can use ST_GeometryN together with ST_NumGeometries and the generate_series function to obtain what you need.

Let's assume you have the table from Jakub's example:

CREATE TABLE multi AS(
SELECT 1 as id, 2 as test, ST_GeomFromText('MULTIPOLYGON(((0 0,4 0,4 4,0 4,0 0)),((1 1,2 1,2 2,1 2,1 1)), ((-1 -1,-1 -2,-2 -2,-2 -1,-1 -1)))') AS geom
);

This one contains a multipolygon, an id and another column.

To get each single polygon from the table including all other attributes try something like:

SELECT id, test, ST_GeometryN(geom, generate_series(1, ST_NumGeometries(geom))) AS geom 
FROM multi

"id" and "test" are the values for each row in the original table. generate_series creates a series of numbers from 1 to the number of geometries in each row.

Therefore you will split each multi geometry in its separate single geometry parts and the values in the other columns remain the same.

Just replace the columns and table in the example with the columns from your exported shapefile and you will get the table with the single polygons.

Hope this answers your question.

Halland

Answer 2

I used ST_DUMP to convert a table of multipolygon geometries in PostgreSQL to a new table with polygon geometries and other columns of data.

CREATE TABLE poly AS                       --poly will be the new polygon table
WITH dump AS (
    SELECT id, test,                       --columns from your multipolygon table 
      (ST_DUMP(geometry)).geom AS geometry 
    FROM multi                             --the name of your multipolygon table
) 
SELECT id, test, 
  geometry::geometry(Polygon,4326)         --type cast using SRID from multipolygon
FROM dump;

Update: I think this could be accomplished much easier with this query.

CREATE TABLE polygon_table AS 
    SELECT id, example_column, (ST_DUMP(geom)).geom::geometry(Polygon,4326) AS geom FROM multipolygon_table

Abeyant

this is the superior answer – Propagable

What is the .geom::geometry(Polygon,4326) for? – Kapp

@Richard, since they're creating the table, they need to tell postgres/postgis the SRID of the geometry column – Merciless

Answer 3

Import into a staging table and then use ST_DUMP to brake the multigeom into individual pieces and use that to populate the destination table.

UPDATE

You import all the data you need into a staging table (let's call it multi) and then use ST_DUMP to break the mutligeometry into single geometries:

WITH multi AS(
SELECT 1 as id, 2 as test, ST_GeomFromText('MULTIPOLYGON(((0 0,4 0,4 4,0 4,0 0)),((1 1,2 1,2 2,1 2,1 1)), ((-1 -1,-1 -2,-2 -2,-2 -1,-1 -1)))') as poli
)
,dump AS(
SELECT  id
    ,test
    ,ST_DUMP(poli) as d
FROM    multi)
SELECT  id
    ,test
    ,(dump.d).path
    ,ST_AsTEXT((dump.d).geom)
FROM dump 

Castera

Can you explain? We have a shape file and exporting into postgesql using postgis 2.0 shape loader. – Interrogate