# 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

x y z m
0 1 2 3
``` 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

x y z m
2 3 4 NULL
3 4 5 NULL
``` 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

x y z m
0 0 NULL NULL
100 0 NULL NULL
100 100 NULL NULL
0 100 NULL NULL
0 0 NULL NULL
``` 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

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

Result

x y z m
1 1 NULL NULL
2 2 NULL NULL
3 3 NULL NULL
``` 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;
```

Result

x y z m
200 200 NULL NULL
400 200 NULL NULL
400 400 NULL NULL
200 400 NULL NULL
200 200 NULL NULL
0 0 NULL NULL
100 0 NULL NULL
100 100 NULL NULL
0 100 NULL NULL
0 0 NULL NULL
``` 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;
```

Result

x y z m
0 0 NULL NULL
100 0 NULL NULL
100 100 NULL NULL
0 100 NULL NULL
0 0 NULL NULL
2 3 4 NULL
1 1 NULL NULL
2 2 NULL NULL
3 3 NULL NULL
2 3 4 NULL
3 4 5 NULL
2 3 4 NULL
3 4 5 NULL
1 1 NULL NULL
2 2 NULL NULL
4 5 NULL NULL
1 1 NULL NULL
2 2 NULL NULL
326000 5455000 NULL NULL
327000 5455000 NULL NULL
326500 5456000 NULL NULL
326000 5455000 NULL NULL
200 200 NULL NULL
400 200 NULL NULL
400 400 NULL NULL
200 400 NULL NULL
200 200 NULL NULL
0 0 NULL NULL
100 0 NULL NULL
100 100 NULL NULL
0 100 NULL NULL
0 0 NULL NULL

And to show that it will work for geography data type based data:

``` 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

x y z m
148 -44 NULL NULL
148 -43 NULL NULL
147 -43 NULL NULL
147 -44 NULL NULL
148 -44 NULL NULL
147.4 -43.6 NULL NULL
147.2 -43.6 NULL NULL
147.2 -43.2 NULL NULL
147.4 -43.2 NULL NULL
147.4 -43.6 NULL NULL

I hope this function is useful to someone.