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 GetVertices wrapper for DumpPoints in SQL Server 2008 Spatial

Thursday December 29 2011 at 00:44

KeywordsGetVertices DumpPoints vertices points extract
Summary

A GetVertices wrapper function for DumpPoints useful for Oracle Spatial people like me.

Oracle’s point dumping function, mdsys.sdo_util.getVertices is most useful. When swapping between platforms sometimes I look first for such a function in SQL Server Spatial forgetting that I named it DumpPoints

Here is a “wrapper” function for DumpPoints.

  1. USE [GISDB]
  2. GO
  3. SET ANSI_NULLS ON
  4. GO
  5. SET QUOTED_IDENTIFIER ON
  6. GO
  7. CREATE FUNCTION [dbo].[GetVertices]( @p_geom geometry )
  8.   RETURNS @Points TABLE
  9.    (x FLOAT,
  10.     y FLOAT,
  11.     z FLOAT,
  12.     m FLOAT )
  13. AS
  14. BEGIN
  15.    INSERT INTO @Points ( [x],[y],[z],[m] )
  16.    SELECT [x],[y],[z],[m]
  17.      FROM dbo.DumpPoints(@p_geom);
  18.    RETURN;
  19. END
  20. GO

Testing it….

  1. SELECT e.[x], e.[y]
  2.   FROM dbo.GetVertices(
  3.          geometry::STGeomFromText('POLYGON ((148.0 -44.0, 148.0 -43.0, 147.0 -43.0, 147.0 -44.0, 148.0 -44.0),
  4.                                            (147.4 -43.6, 147.2 -43.6, 147.2 -43.2, 147.4 -43.2, 147.4 -43.6))',0)) AS e;

Result

x y
148 -44
148 -43
147 -43
147 -44
148 -44
147.4 -43.6
147.2 -43.6
147.2 -43.2
147.4 -43.2
147.4 -43.6

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 [1]

Quite useful! Thanks for being generous and posting this online. The world needs more kind, helpful people like you. :)

— Jason · 6 July 2012, 08:18 · #