Disassembles a geometry into its lowest level component geometries.
st_geometry_dump( geometry [, "options" ] )
geometry The ST_Geometry value of the geometry to be disassembled.
"options" A optional VARCHAR(255) string of parameters and values, separated by semicolons, you can use to configure the output of the procedure. The default is NULL.
The following table lists the parameters that can be specified:
Parameter  Default value  Allowed values  Description 

Format  Original  Original, Internal, or Mixed  The format to return the geometry in. Specifying Original returns the geometry in its original format. Specifying Internal returns the geometry in its normalized format. Specifying Mixed returns whatever stored formats are available, one row per format. 
ExpandPoints  Yes  Yes, No  By default, when disassembling a geometry containing points (such as ST_LineString or ST_MultiPoint), the st_geometry_dump system procedure outputs the constituent points to separate rows. Set ExpandPoints to No if you do not want these extra rows to be generated. 
MaxDepth  1  1, any number greater or equal to zero  By default, st_geometry_dump system procedure continues to disassembles an object hierarchy until it reaches the leaf objects. The MaxDepth parameter can be set to limit the number of levels in the hierarchy the geometry is disassembled. With a value of 0, only the root geometry is returned. With a value of 1, the geometry and its immediate children are returned, and so on. 
SetGeom  Yes  Yes, No  The st_geometry_dump system procedure returns a column that is the ST_Geometry associated with an object in the original type hierarchy. If this column is not needed, the parameter SetGeom can be set to No to reduce the running time and output size of the procedure. 
Validate  Basic  None, Basic, Full  By default, the st_geometry_dump system procedure applies the validation rules that the database server uses when loading geometries, and sets the Valid column of the result set to 1 if the object in the row matches these rules. The Validate parameter can be set to None to disable this checking, or it can be set to Full to also apply the additional checks performed by the ST_IsValid method. Full checking takes longer to perform. 
The following table describes the results returned by the st_geometry_dump procedure:
Column  Data type  Description 

id  UNSIGNED BIGINT  A unique ID for this row in the results. 
parent_id  UNSIGNED BIGINT  The ID of the immediate parent of this object. 
depth  INTEGER  The depth from the root object to the object associated with this row. 
format  VARCHAR(128)  Whether the geometry is the original representation (Original) or the normalized representation (Internal). 
valid  BIT  Whether the geometry is valid (1) according to the checking level specified by the Validate option. 
geom_type  VARCHAR(128)  The geometry type, as returned by the ST_GeometryType. 
geom  ST_Geometry  The geometry specification. If SetGeom parameter is set to No, the geometry specification is not returned in the result set. 
xmin  DOUBLE  The minimum x value for the geometry. 
xmax  DOUBLE  The maximum x value for the geometry. 
ymin  DOUBLE  The minimum y value for the geometry. 
ymax  DOUBLE  The maximum y value for the geometry. 
zmin  DOUBLE  The minimum z value for the geometry. 
zmax  DOUBLE  The maximum z value for the geometry. 
mmin  DOUBLE  The minimum m value for the geometry. 
mmax  DOUBLE  The maximum m value for the geometry. 
details  LONG VARCHAR  Any extra details about the geometry, including additional information about why the object is not valid. 
The st_geometry_dump system procedure disassembles a geometry hierarchy with one row for each of the objects in the hierarchy (including the root object). Each geometry in the hierarchy can be validated to find out if it is valid, and if not, why.
Some of the functionality of the st_geometry_dump system procedure can be matched by using typespecific methods such as ST_GeometryN or ST_PointN.
The st_geometry_dump system procedure can be used to correct invalid geometries.
None
None
The following example disassembles the polygon, 'Polygon ((0 0, 3 0, 3 3, 0 3, 0 0))'
, into its component geometries:
SELECT * FROM st_geometry_dump( 'Polygon ((0 0, 3 0, 3 3, 0 3, 0 0))', 'SetGeom=No' ); 
id  parent_id  depth  format  valid  geom_type  geom  xmin  xmax  ymin  ymax  ... 

1  1  0  Internal  1  ST_Polygon  Polygon ((0 0, 3 0, 3 3, 0 3, 0 0)) 
0  3  0  3  ... 
2  1  1  Internal  1  ST_LineString  LineString (0 0, 3 0, 3 3, 0 3, 0 0) 
0  3  0  3  ... 
3  2  2  Internal  1  ST_Point  Point (0 0) 
0  0  0  0  ... 
4  2  2  Internal  1  ST_Point  Point (3 0) 
3  3  0  0  ... 
5  2  2  Internal  1  ST_Point  Point (3 3) 
3  3  3  3  ... 
6  2  2  Internal  1  ST_Point  Point (0 3) 
0  0  3  3  ... 
7  2  2  Internal  1  ST_Point  Point (0 0) 
0  0  0  0  ... 
The following example shows how the st_geometry_dump system procedure can be used to find the invalid points within a geometry. In this example, the linestring contains a point with longitude 1200. Because of this, the point and the linestring are both reported as invalid (valid=0) in the results.
SET TEMPORARY OPTION st_geometry_on_invalid='Ignore'; CREATE OR REPLACE VARIABLE @geo ST_Geometry; SET @geo = new ST_LineString( 'LineString(1200 2, 80 10)', 4326 ); SELECT * FROM st_geometry_dump( @geo, 'SetGeom=No' ); 
id  parent_id  depth  format  valid  geom_type  geom  xmin  xmax  ymin  ymax  ...  details 

1  1  0  Original  0  ST_LineString  (NULL)  80  1,200  2  10  ...  Value 1200.000000 out of range for coordinate longitude (SRS allows 180.000000 to 180.000000). 
2  1  1  Original  0  ST_LineString  (NULL)  1,200  1,200  2  2  ...  Value 1200.000000 out of range for coordinate longitude (SRS allows 180.000000 to 180.000000). 
3  1  1  Original  1  ST_Point  (NULL)  80  80  10  10  ... 
Once invalid data has been identified, the st_geometry_dump system procedure can be used with other spatial methods to correct the invalid elements to assemble a valid geometry. The following example shows how an invalid point with longitude 1200 can be corrected to have longitude 120.0:
SET TEMPORARY OPTION st_geometry_on_invalid='Ignore'; CREATE OR REPLACE VARIABLE @geo ST_Geometry; SET @geo = new ST_LineString( 'LineString(1200 2, 80 10)', 4326 ); SELECT ST_LineString::ST_LineStringAggr( new ST_Point( IF xmax = 1200 then 120.0 ELSE xmax ENDIF, ymax, 4326 ) ORDER BY id ) FROM st_geometry_dump( @geo ) WHERE geom_type='ST_Point'; 
Discuss this page in DocCommentXchange.

Copyright © 2014, SAP AG or an SAP affiliate company.  SAP Sybase SQL Anywhere 16.0 