SpatialDB Advisor

Vectorising geometry objects in SQL Server 2008

I demonstrated in this article the usefulness of having a vectorising function in one’s spatial database kitbag (there are GetVector functions in my CENTROID and GEOM packages – in particular the GetVector function is vital to the implementation of the algorithm in my CENTROID.SDO_CENTROID function).

So, I have also added one to my SQL Server 2008 kitbag.

This function, as against the PostGIS and Oracle implementation, does not use any object types due to limitations in the SQL Server 2008 TSQL system (my implementation deliberately does not use the CLR system).

USE [GISDB]  -- Change this to your database
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[GetVector]( @p_geom geometry )
  RETURNS @Vectors TABLE
   (sx float,
    sy float,
    sz float,
    sm float,
    ex float,
    ey float,
    ez float,
    em float)  
AS
Begin
  Declare
    @v_GeometryType varchar(1000),
    @ringn          int,
    @geomn          int,
    @first          int,
    @second         int,
    @geom           geometry,
    @start_geom     geometry,
    @end_geom       geometry;
  Begin
    If ( @p_geom is NULL ) 
      return;

    SET @v_GeometryType = @p_geom.STGeometryType();

    If ( @v_GeometryType in ('Point','MultiPoint') ) 
      return;

    IF ( @v_GeometryType = 'LineString' )
    BEGIN
      SET @first  = 1;
      SET @second = 2;
      WHILE ( @second <= @p_geom.STNumPoints() )
      BEGIN
        SET @start_geom = @p_geom.STPointN(@first);
        SET @first      = @first  + 1;
        SET @end_geom   = @p_geom.STPointN(@second);
        SET @second     = @second + 1;
        INSERT INTO @Vectors ( [sx],[sy],[sz],[sm],[ex],[ey],[ez],[em] ) 
               VALUES ( @start_geom.STX, @start_geom.STY, @start_geom.Z, @start_geom.M,
                          @end_geom.STX,  @end_geom.STY, @end_geom.Z,   @end_geom.M );
      END;  
      RETURN;
    END;
    IF ( @v_GeometryType = 'MultiLineString' ) 
    BEGIN
      SET @geomn  = 1;
      WHILE ( @geomn <= @p_geom.STNumGeometries() )
      BEGIN
        SET @geom   = @p_geom.STGeometryN(@geomn);
        SET @first  = 1;
        SET @second = 2;
        WHILE ( @second <= @geom.STNumPoints() )
        BEGIN
          SET @start_geom = @geom.STPointN(@first);
          SET @first      = @first  + 1;
          SET @end_geom   = @geom.STPointN(@second);
          SET @second     = @second + 1;
          INSERT INTO @Vectors ( [sx],[sy],[sz],[sm],[ex],[ey],[ez],[em] ) 
               VALUES ( @start_geom.STX, @start_geom.STY, @start_geom.Z, @start_geom.M,
                          @end_geom.STX,  @end_geom.STY, @end_geom.Z,   @end_geom.M );
        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 @first  = 1;
        SET @second = 2;
        WHILE ( @second <= @geom.STNumPoints() )
        BEGIN
          SET @start_geom = @geom.STPointN(@first);
          SET @first      = @first  + 1;
          SET @end_geom   = @geom.STPointN(@second);
          SET @second     = @second + 1;
          INSERT INTO @Vectors ( [sx],[sy],[sz],[sm],[ex],[ey],[ez],[em] ) 
               VALUES ( @start_geom.STX, @start_geom.STY, @start_geom.Z, @start_geom.M,
                          @end_geom.STX,  @end_geom.STY,  @end_geom.Z,  @end_geom.M );
        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 @first  = 1;
          SET @second = 2;
          WHILE ( @second <= @geom.STNumPoints() )
          BEGIN
            SET @start_geom = @geom.STPointN(@first);
            SET @first      = @first  + 1;
            SET @end_geom   = @geom.STPointN(@second);
            SET @second     = @second + 1;
            INSERT INTO @Vectors ( [sx],[sy],[sz],[sm],[ex],[ey],[ez],[em] ) 
                 VALUES ( @start_geom.STX, @start_geom.STY, @start_geom.Z, @start_geom.M,
                            @end_geom.STX,  @end_geom.STY,  @end_geom.Z,  @end_geom.M );
          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
        If Not ( @p_geom.STGeometryN(@geomn).STGeometryType() in ('Point','MultiPoint') ) 
        BEGIN
           INSERT INTO @Vectors ( [sx],[sy],[sz],[sm],[ex],[ey],[ez],[em] )
                SELECT [sx],[sy],[sz],[sm],[ex],[ey],[ez],[em]
                  FROM dbo.GetVector(@p_geom.STGeometryN(@geomn));
        END;
        SET @geomn = @geomn + 1;
      END;
      RETURN;
    END;
  End;
  RETURN;
End
GO

Testing

Here’s a bunch of tests.

select v.*
  from dbo.GetVector(geometry::STGeomFromText(
'LINESTRING(0 0 10 10, 1 1 10 20, 2 2 10 30, 3 3 10 40)',0)) as v;

select v.*
  from dbo.GetVector(geometry::STGeomFromText(
'MULTILINESTRING((0 0,1 1,1 2),(2 3,3 2,5 4))',0)) as v;

-- Ordinary polygon
--
select v.*
  from dbo.GetVector(geometry::STGeomFromText(
'POLYGON((326000.0 5455000.0,327000.0 5455000.0,326500.0 5456000.0,326000.0 5455000.0))',0)) as v;

-- Polygon with a hole
--
select v.*
  from dbo.GetVector(geometry::STGeomFromText(
'POLYGON((326000.0 5455000.0,327000.0 5455000.0,326500.0 5456000.0,326000.0 5455000.0),
(326500.0 5455500.0,326550.0 5455200.0,326450.0 5455200.0,326500.0 5455500.0))',0)) as v;

-- MultiPolygon With a hole
--
select v.*
  from dbo.GetVector(geometry::STGeomFromText(
'MULTIPOLYGON(((326000.0 5455000.0,327000.0 5455000.0,326500.0 5456000.0,326000.0 5455000.0),
(326500.0 5455500.0,326550.0 5455200.0,326450.0 5455200.0,326500.0 5455500.0)),
((321000.0 5450000.0,322000.0 5450000.0,321500.0 5451000.0,321000.0 5450000.0)))',0))  as v;

But what if you want the start and end of the vector to be geometry points?

-- Get as points
--
select geometry::Point(v.sx,v.sy,0).STAsText() as startPoint,
       geometry::Point(v.ex,v.ey,0).STAsText() as endPoint
  from dbo.GetVector(geometry::STGeomFromText(
'LINESTRING(0 0 10 10, 1 1 10 20, 2 2 10 30, 3 3 10 40)',0)) as v;

Finally, what if you want each vector to be represented as a single linestring and not a individual ordinates (sx,sy, …. ,ez,em)?

-- Get as linestring
--
select geometry::STGeomFromText(
'LINESTRING(' + 
CAST(v.sx as varchar(10)) + ' ' + CAST(v.sy as varchar(10)) + ',' + 
CAST(v.ex as varchar(10)) +' ' + CAST(v.ey as varchar(10)) + ')',0).STAsText() as vLine
  from dbo.GetVector(geometry::STGeomFromText(
'LINESTRING(0 0 10 10, 1 1 10 20, 2 2 10 30, 3 3 10 40)',0)) as v;

I hope this is useful to someone.