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'?


The geometry_dump data type is a simple composite type with two fields: path[] (a 1-d array holding the position of the geometry within the dumped object), and geom, 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;
 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.

  • Thanks, this is just what I needed. I have never used or come across SQL syntax with something like ".geom". Cheers. – Roman Trubka May 4 '12 at 1:28
  • The extra brackets are important so: Correct: "SELECT (ST_Dump(geom)).geom FROM table" Incorrect: "SELECT ST_Dump(geom).geom FROM table" – HeikkiVesanto Nov 29 '16 at 12:02

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.