st_geometry_dump ( geometry [, options ] )

The following table describes the results returned by the st_geometry_dump procedure:

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 type-specific methods such as ST_GeometryN or ST_PointN.

The st_geometry_dump system procedure can be used to correct invalid geometries.

None

None

• ST_IsValid method for type ST_Geometry
• CREATE SPATIAL REFERENCE SYSTEM statement
• st_geometry_on_invalid option
• STORAGE FORMAT clause, CREATE SPATIAL REFERENCE SYSTEM statement

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' );

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 dbo.st_geometry_dump( @geo, 'SetGeom=No' );

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:

SELECT ST_LineString::ST_LineStringAggr( 
    new ST_Point( IF xmax = 1200 then 120.0 ELSE xmax ENDIF, 
                  ymax, 4326 )  ORDER BY id )
FROM  dbo.st_geometry_dump( @geo )
WHERE geom_type='ST_Point';