STVertices: Wrapper over STDumpPoints

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 STDumpPoints.

 USE [GISDB]
 GO
 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER ON
 GO
 CREATE FUNCTION [dbo].[STVertices]( @p_geom geometry )
   RETURNS @Points TABLE
    (x FLOAT,
     y FLOAT,
     z FLOAT,
     m FLOAT )
 AS
 BEGIN
    INSERT INTO @Points ( [x],[y],[z],[m] )
    SELECT [x],[y],[z],[m]
      FROM dbo.DumpPoints(@p_geom);
    RETURN;
 END
 GO

Testing it….

 SELECT e.[x], e.[y]
   FROM dbo.STVertices(
          geometry::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))',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.