Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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

Cheers,

Roman

share|improve this question
add comment

1 Answer 1

up vote 6 down vote accepted

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;
                 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.

share|improve this answer
    
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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.