PostGIS – Converting Geometry_Dump Data Type to Geometry Type

geometrypolygonpolygon-creationpostgis

I am working with a dataset of property boundaries that has had ST_Union() run on it, thus all the polygons appear as a multi polygon in one row of a table. I would like to separate these out so I get a separate row/polygon for each shape. I have tried running ST_Dump() on this and it seems to separate out the polygons in the dataset but I can no longer view this in a GIS (I'm currently using QGIS). The data-type for this column says it's a 'geometry_dump' and I assume this is why I cannot visualize it. When I access my Postgres database from QGIS this new table is listed under the heading of datasets without a geometry.

Does anyone know if there is something I can do to convert a 'geometry_dump' to a 'geometry'?

Best Answer

The geometry_dump data type is a simple composite type with two fields:

  1. path[] — a 1-d array holding the position of the geometry within the dumped object, and
  2. geom — a simple geometry subset of the original, which is what you want.

To access geom, just use (ST_Dump(the_geom)).geom (the extra parentheses are required, and is a bit of a gotcha when accessing members from composite types). For example:

WITH data AS (SELECT 'MULTIPOLYGON (((170 190, 110 150, 200 120, 170 190)), 
  ((220 160, 230 110, 280 120, 220 160)), 
  ((220 200, 260 240, 270 190, 220 200)))'::geometry AS the_geom)

SELECT ST_AsText( (ST_Dump(the_geom)).geom )
FROM data;
                 st_astext
--------------------------------------------
 POLYGON((170 190,110 150,200 120,170 190))
 POLYGON((220 160,230 110,280 120,220 160))
 POLYGON((220 200,260 240,270 190,220 200))
(3 rows)

The PostGIS manual has other good examples for ST_Dump.

Related Question