STDumpPoints: A Function that Dumps the Vertices/Points in a geometry

I often find I need to manipulate the points of a geometry/geography object directly in SQL. Oracle has the neat TABLE function mdsys.sdo_util.GetVertices( sdo_geometry ) for this. Since SQL Server 2008 does not have such a function I found I had to write it myself. I prefer to program in TSQL using the OGC geometry/geography API rather than in a CLR language like C# so I did it entirely in TSQL.

Note that the function is written for only the geometry data type. See my article on the conversion functions toGeography/toGeometry , and the tests below, to see how the function can be used for the geography data type.

 USE [GISDB]  -- Change this to your database
 GO
 .
 CREATE FUNCTION [dbo].[STDumpPoints]( @p_geom geometry )
   RETURNS @Points TABLE
    (x FLOAT,
     y FLOAT,
     z FLOAT,
     m FLOAT )
 AS
 BEGIN
   DECLARE
     @v_GeometryType VARCHAR(1000),
     @ringn          INT,
     @geomn          INT,
     @pointn         INT,
     @geom           geometry;
   BEGIN
     IF ( @p_geom IS NULL )
       RETURN;
 .
    SET @v_GeometryType = @p_geom.STGeometryType(); 
 .
     IF ( @v_GeometryType = 'Point' )
     BEGIN
       INSERT INTO @Points ( [x],[y],[z],[m] )
            VALUES ( @p_geom.STX,
                      @p_geom.STY,
                      @p_geom.Z,
                      @p_geom.M );
       RETURN;
     END;
 .
     IF ( @v_GeometryType = 'MultiPoint' ) 
     BEGIN
       SET @geomn  = 1;
       WHILE ( @geomn <= @p_geom.STNumGeometries() )
       BEGIN
         INSERT INTO @Points ( [x],[y],[z],[m] )
              VALUES ( @p_geom.STGeometryN(@geomn).STX,
                        @p_geom.STGeometryN(@geomn).STY,
                        @p_geom.STGeometryN(@geomn).Z,
                        @p_geom.STGeometryN(@geomn).M );
         SET @geomn = @geomn + 1;
       END;
       RETURN;
     END;
 .
     IF ( @v_GeometryType = 'LineString' )
     BEGIN
       SET @pointn = 1;
       WHILE ( @pointn <= @p_geom.STNumPoints() )
       BEGIN
         INSERT INTO @Points ( [x],[y],[z],[m] )
            VALUES ( @p_geom.STPointN(@pointn).STX,
                      @p_geom.STPointN(@pointn).STY,
                      @p_geom.STPointN(@pointn).Z,
                      @p_geom.STPointN(@pointn).M );
         SET @pointn = @pointn + 1;
       END;
       RETURN;
     END;
 .
     IF ( @v_GeometryType = 'MultiLineString' )
     BEGIN
       SET @geomn  = 1;
       WHILE ( @geomn <= @p_geom.STNumGeometries() )
       BEGIN
         SET @pointn = 1;
         WHILE ( @pointn <= @p_geom.STGeometryN(@geomn).STNumPoints() )
         BEGIN
           INSERT INTO @Points ( [x],[y],[z],[m] )
                VALUES ( @p_geom.STGeometryN(@geomn).STPointN(@pointn).STX,
                          @p_geom.STGeometryN(@geomn).STPointN(@pointn).STY,
                          @p_geom.STGeometryN(@geomn).STPointN(@pointn).Z,
                          @p_geom.STGeometryN(@geomn).STPointN(@pointn).M );
           SET @pointn = @pointn + 1;
         END;
         SET @geomn = @geomn + 1;
       END;
       RETURN;
     END;
 .
     IF ( @v_GeometryType = 'Polygon' )
     BEGIN
       SET @ringn  = 0;
       WHILE ( @ringn < ( 1 + @p_geom.STNumInteriorRing() ) )
       BEGIN
         IF ( @ringn = 0 )
           SET @geom = @p_geom.STExteriorRing()
         ELSE
           SET @geom = @p_geom.STInteriorRingN(@ringn);
         SET @pointn = 1;
         WHILE ( @pointn <= @geom.STNumPoints() )
         BEGIN
           INSERT INTO @Points ( [x],[y],[z],[m] )
                VALUES ( @geom.STPointN(@pointn).STX,
                          @geom.STPointN(@pointn).STY,
                          @geom.STPointN(@pointn).Z,
                          @geom.STPointN(@pointn).M);
           SET @pointn = @pointn + 1;
         END;
         SET @ringn = @ringn + 1;
       END;
       RETURN;
     END;
 .
     IF ( @v_GeometryType = 'MultiPolygon' )
     BEGIN
       SET @geomn  = 1;
       WHILE ( @geomn <= @p_geom.STNumGeometries() )
       BEGIN
         SET @ringn  = 0;
         WHILE ( @ringn < ( 1 + @p_geom.STGeometryN(@geomn).STNumInteriorRing() ) )
         BEGIN
           IF ( @ringn = 0 )
             SET @geom = @p_geom.STGeometryN(@geomn).STExteriorRing()
           ELSE
             SET @geom = @p_geom.STGeometryN(@geomn).STInteriorRingN(@ringn);
           SET @pointn = 1;
           WHILE ( @pointn <= @geom.STNumPoints() )
           BEGIN
             INSERT INTO @Points ( [x],[y],[z],[m] )
                  VALUES ( @geom.STPointN(@pointn).STX,
                            @geom.STPointN(@pointn).STY,
                            @geom.STPointN(@pointn).Z,
                            @geom.STPointN(@pointn).M );
             SET @pointn = @pointn + 1;
           END;
           SET @ringn = @ringn + 1;
         END;
         SET @geomn = @geomn + 1;
       END;
       RETURN;
     END;
 .
     IF ( @v_GeometryType = 'GeometryCollection' )
     BEGIN
       SET @geomn  = 1;
       WHILE ( @geomn <= @p_geom.STNumGeometries() )
       BEGIN
          INSERT INTO @Points ( [x],[y],[z],[m] )
               SELECT [x],[y],[z],[m]
                 FROM [dbo].[STDumpPoints](@p_geom.STGeometryN(@geomn));
         SET @geomn = @geomn + 1;
       END;
       RETURN;
     END;
   END;
   RETURN;
 END
 GO

Testing it we get:

 SELECT e.[x], e.[y], e.[z], e.[m]
   FROM [dbo].[STDumpPoints](geometry::STGeomFromText('POINT(0 1 2 3)',0)) AS e;

Result

 SELECT e.[x], e.[y], e.[z], e.[m]
   FROM [dbo].[STDumpPoints](geometry::STGeomFromText('LINESTRING(2 3 4,3 4 5)',0)) AS e;

Result

 SELECT e.[x], e.[y], e.[z], e.[m]
   FROM [dbo].[STDumpPoints](geometry::STGeomFromText('POLYGON ((0 0, 100 0, 100 100, 0 100, 0 0))',0)) AS e;

Result

 SELECT e.[x], e.[y], e.[z], e.[m]
   FROM [dbo].[STDumpPoints](geometry::STGeomFromText('MULTILINESTRING((2 3 4,3 4 5),(1 1,2 2))',0)) AS e;

Result

 SELECT e.[x], e.[y], e.[z], e.[m]
   FROM [dbo].[STDumpPoints](geometry::STGeomFromText('MULTIPOINT((1 1),(2 2),(3 3))',0)) AS e;

 SELECT e.[x], e.[y], e.[z], e.[m]
   FROM [dbo].[STDumpPoints](geometry::STGeomFromText('MULTIPOLYGON (((200 200, 400 200, 400 400, 200 400, 200 200)), ((0 0, 100 0, 100 100, 0 100, 0 0)))',0)) AS e;

 SELECT e.[x], e.[y], e.[z], e.[m]
   FROM [dbo].[STDumpPoints](geometry::STGeomFromText('GEOMETRYCOLLECTION(POLYGON ((0 0, 100 0, 100 100, 0 100, 0 0)),POINT(2 3 4),MULTIPOINT((1 1),(2 2),(3 3)),LINESTRING(2 3 4,3 4 5),MULTILINESTRING((2 3 4,3 4 5),(1 1,2 2)),POINT(4 5),MULTIPOINT((1 1),(2 2)),POLYGON((326000.0 5455000.0,327000.0 5455000.0,326500.0 5456000.0,326000.0 5455000.0)),MULTIPOLYGON (((200 200, 400 200, 400 400, 200 400, 200 200)), ((0 0, 100 0, 100 100, 0 100, 0 0))))',0)) AS e;

 SELECT e.[x], e.[y], e.[z], e.[m]
   FROM [dbo].[STDumpPoints](
          dbo.toGeometry(geography::STGeomFromText('POLYGON ((148.0 -44.0, 148.0 -43.0, 147.0 -43.0, 147.0 -44.0, 148.0 -44.0), (147.4 -43.6, 147.2 -43.6, 147.2 -43.2, 147.4 -43.2, 147.4 -43.6))',4326),
                         0)) AS e;

Result

I hope this function is useful to someone.