20

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

PolyGeo
  • 65,136
  • 29
  • 109
  • 338
Roman Trubka
  • 403
  • 1
  • 3
  • 7

1 Answers1

38

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.

Mike T
  • 42,095
  • 10
  • 126
  • 187
  • Thanks, this is just what I needed. I have never used or come across SQL syntax with something like ".geom". Cheers. – Roman Trubka May 04 '12 at 01:28
  • 2
    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