ST_DumpPoints

" /> A Dump Points Function for SQL Server 2008 Spatial
Go to content Go to navigation and search

Home

Current SQL Server Blog Articles


Search

Browse

RSS / Atom

Email me

textpattern

Creative Commons License
All Blog Articles, Data Models and Free Source Code by Simon Greener, The SpatialDB Advisor is licensed under a Creative Commons Attribution-ShareAlike 3.0 Unported License.

A Dump Points Function for SQL Server 2008 Spatial

Monday September 28 2009 at 06:03

KeywordsDumpPoints ST_DumpPoints dumping points get vertices
Summary

A function that dumps (or extracts) the vertices / points of a geometry/geography object is very useful for the database spatial practitioner. Here is one whose name is drawn from PostGIS’s ST_DumpPoints

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.

  1. USE [GISDB]  -- Change this to your database
  2. GO
  3. .
  4. SET ANSI_NULLS ON
  5. GO
  6. .
  7. SET QUOTED_IDENTIFIER ON
  8. GO
  9. .
  10. CREATE FUNCTION [dbo].[DumpPoints]( @p_geom geometry )
  11.   RETURNS @Points TABLE
  12.    (x FLOAT,
  13.     y FLOAT,
  14.     z FLOAT,
  15.     m FLOAT )
  16. AS
  17. BEGIN
  18.   DECLARE
  19.     @v_GeometryType VARCHAR(1000),
  20.     @ringn          INT,
  21.     @geomn          INT,
  22.     @pointn         INT,
  23.     @geom           geometry;
  24.   BEGIN
  25.     IF ( @p_geom IS NULL )
  26.       RETURN;
  27. .
  28.     SET @v_GeometryType = @p_geom.STGeometryType();
  29. .
  30.     IF ( @v_GeometryType = 'Point' )
  31.     BEGIN
  32.       INSERT INTO @Points ( [x],[y],[z],[m] )
  33.            VALUES ( @p_geom.STX,
  34.                     @p_geom.STY,
  35.                     @p_geom.Z,
  36.                     @p_geom.M );
  37.       RETURN;
  38.     END;
  39. .
  40.     IF ( @v_GeometryType = 'MultiPoint' )
  41.     BEGIN
  42.       SET @geomn  = 1;
  43.       WHILE ( @geomn <= @p_geom.STNumGeometries() )
  44.       BEGIN
  45.         INSERT INTO @Points ( [x],[y],[z],[m] )
  46.              VALUES ( @p_geom.STGeometryN(@geomn).STX,
  47.                       @p_geom.STGeometryN(@geomn).STY,
  48.                       @p_geom.STGeometryN(@geomn).Z,
  49.                       @p_geom.STGeometryN(@geomn).M );
  50.         SET @geomn = @geomn + 1;
  51.       END;
  52.       RETURN;
  53.     END;
  54. .
  55.     IF ( @v_GeometryType = 'LineString' )
  56.     BEGIN
  57.       SET @pointn = 1;
  58.       WHILE ( @pointn <= @p_geom.STNumPoints() )
  59.       BEGIN
  60.         INSERT INTO @Points ( [x],[y],[z],[m] )
  61.            VALUES ( @p_geom.STPointN(@pointn).STX,
  62.                     @p_geom.STPointN(@pointn).STY,
  63.                     @p_geom.STPointN(@pointn).Z,
  64.                     @p_geom.STPointN(@pointn).M );
  65.         SET @pointn = @pointn + 1;
  66.       END;  
  67.       RETURN;
  68.     END;
  69. .
  70.     IF ( @v_GeometryType = 'MultiLineString' )
  71.     BEGIN
  72.       SET @geomn  = 1;
  73.       WHILE ( @geomn <= @p_geom.STNumGeometries() )
  74.       BEGIN
  75.         SET @pointn = 1;
  76.         WHILE ( @pointn <= @p_geom.STGeometryN(@geomn).STNumPoints() )
  77.         BEGIN
  78.           INSERT INTO @Points ( [x],[y],[z],[m] )
  79.                VALUES ( @p_geom.STGeometryN(@geomn).STPointN(@pointn).STX,
  80.                         @p_geom.STGeometryN(@geomn).STPointN(@pointn).STY,
  81.                         @p_geom.STGeometryN(@geomn).STPointN(@pointn).Z,
  82.                         @p_geom.STGeometryN(@geomn).STPointN(@pointn).M );
  83.           SET @pointn = @pointn + 1;
  84.         END;  
  85.         SET @geomn = @geomn + 1;
  86.       END;
  87.       RETURN;
  88.     END;
  89. .
  90.     IF ( @v_GeometryType = 'Polygon' )
  91.     BEGIN
  92.       SET @ringn  = 0;
  93.       WHILE ( @ringn < ( 1 + @p_geom.STNumInteriorRing() ) )
  94.       BEGIN
  95.         IF ( @ringn = 0 )
  96.           SET @geom = @p_geom.STExteriorRing()
  97.         ELSE
  98.           SET @geom = @p_geom.STInteriorRingN(@ringn);
  99.         SET @pointn = 1;
  100.         WHILE ( @pointn <= @geom.STNumPoints() )
  101.         BEGIN
  102.           INSERT INTO @Points ( [x],[y],[z],[m] )
  103.                VALUES ( @geom.STPointN(@pointn).STX,
  104.                         @geom.STPointN(@pointn).STY,
  105.                         @geom.STPointN(@pointn).Z,
  106.                         @geom.STPointN(@pointn).M);
  107.           SET @pointn = @pointn + 1;
  108.         END;
  109.         SET @ringn = @ringn + 1;
  110.       END;
  111.       RETURN;
  112.     END;
  113. .
  114.     IF ( @v_GeometryType = 'MultiPolygon' )
  115.     BEGIN
  116.       SET @geomn  = 1;
  117.       WHILE ( @geomn <= @p_geom.STNumGeometries() )
  118.       BEGIN
  119.         SET @ringn  = 0;
  120.         WHILE ( @ringn < ( 1 + @p_geom.STGeometryN(@geomn).STNumInteriorRing() ) )
  121.         BEGIN
  122.           IF ( @ringn = 0 )
  123.             SET @geom = @p_geom.STGeometryN(@geomn).STExteriorRing()
  124.           ELSE
  125.             SET @geom = @p_geom.STGeometryN(@geomn).STInteriorRingN(@ringn);
  126.           SET @pointn = 1;
  127.           WHILE ( @pointn <= @geom.STNumPoints() )
  128.           BEGIN
  129.             INSERT INTO @Points ( [x],[y],[z],[m] )
  130.                  VALUES ( @geom.STPointN(@pointn).STX,
  131.                           @geom.STPointN(@pointn).STY,
  132.                           @geom.STPointN(@pointn).Z,
  133.                           @geom.STPointN(@pointn).M );
  134.             SET @pointn = @pointn + 1;
  135.           END;
  136.           SET @ringn = @ringn + 1;
  137.         END;
  138.         SET @geomn = @geomn + 1;
  139.       END;
  140.       RETURN;
  141.     END;
  142. .
  143.     IF ( @v_GeometryType = 'GeometryCollection' )
  144.     BEGIN
  145.       SET @geomn  = 1;
  146.       WHILE ( @geomn <= @p_geom.STNumGeometries() )
  147.       BEGIN
  148.          INSERT INTO @Points ( [x],[y],[z],[m] )
  149.               SELECT [x],[y],[z],[m]
  150.                 FROM dbo.DumpPoints(@p_geom.STGeometryN(@geomn));
  151.         SET @geomn = @geomn + 1;
  152.       END;
  153.       RETURN;
  154.     END;
  155.   END;
  156.   RETURN;
  157. END
  158. GO

Testing it we get:

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

Result

x y z m
0 1 2 3
  1. SELECT e.[x], e.[y], e.[z], e.[m]
  2.   FROM dbo.DumpPoints(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
  1. SELECT e.[x], e.[y], e.[z], e.[m]
  2.   FROM dbo.DumpPoints(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
  1. SELECT e.[x], e.[y], e.[z], e.[m]
  2.   FROM dbo.DumpPoints(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
  1. SELECT e.[x], e.[y], e.[z], e.[m]
  2.   FROM dbo.DumpPoints(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
  1. SELECT e.[x], e.[y], e.[z], e.[m]
  2.   FROM dbo.DumpPoints(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
  1. SELECT e.[x], e.[y], e.[z], e.[m]
  2.   FROM dbo.DumpPoints(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:

  1. SELECT e.[x], e.[y], e.[z], e.[m]
  2.   FROM dbo.DumpPoints(
  3.          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),
  4.                         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.

Creative Commons License

post this at del.icio.uspost this at Diggpost this at Technoratipost this at Redditpost this at Farkpost this at Yahoo! my webpost this at Windows Livepost this at Google Bookmarkspost this to Twitter

Comment