PostGIS Spatial Database Engine UMN Mapserver Boston Geographic Information Systems    Checkout our PostGIS in Action book.  First chapter is a free download   PostGreSQL Object Relational Database Management System
GIS Books  Home   Consulting Services  About Boston GIS   Boston GIS Blog  Postgres OnLine Journal
PostGIS in Action is out in hard-copy,
download the first chapter
and SQL Primer for free. Tips and Tricks for PostGIS
  GIS Article comments Comments Rss
PostGIS ST_Dump, Dump

What is ST_Dump, Dump

ST_Dump is a function that takes a geometry and returns a set of Postgis geometry_dump structure. Geometry_dump is composed of 2 properties. geom and path. The geom is a geometry and path is a multi-dimensional integer array that has the exact location of the geom in the MULTI, Geometry Collection. For MULTI geometries, the path is one dimensional looking like {1}. For single geometries, the path is an empty array. For GeometryCollection it has multiple array elements depending on the complexity of the Geometry Collection.

ST_Dump comes in very handy for expanding a MULTI geometry into single geometries. Below is an example. That expands a set of MULTIPOLYGONs into single POLYGONs

SELECT somefield1, somefield2, (ST_Dump(the_geom)).geom As the_geom FROM sometable

It comes in very handy in conjunction with ST_Collect. ST_Collect will return a MULTI geom if you give it single geoms, but will return a Geometry collection if you try to feed it MULTI geometries. In general ST_Collect is faster than ST_Union. To take advantage of the speed and simplicity of ST_Collect without generation of GeometryCollections, you can expand your MULTIPOLYGONS, MULTILINESTRINGS, MULTIPOINTS into single geoms and then recollect them according to the grouping you desire. If you want to regroup a set of MULTI.. Into a new grouping, you can do something like the below.

SELECT stusps, ST_Multi(ST_Collect(f.the_geom)) as singlegeom FROM (SELECT stusps, (ST_Dump(the_geom)).geom As the_geom FROM somestatetable ) As f GROUP BY stusps

Post Comments About PostGIS ST_Dump, Dump

This Document is available under the GNU Free Documentation License 1.2 & for download at the BostonGIS site

Boston GIS      Copyright 2017      Paragon Corporation