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.