How can I return a substring of a LINESTRING in SQL Server 2008 Spatial?

Question

Say I have a LINESTRING defined as

LINESTRING(-122.360 47.656, -122.343 47.656, -122.310 47.690, -122.310 47.670, -122.300 47.630)

And I want to get a substring from

POINT(-122.360 47.656) to POINT(-122.310 47.690)

How can I return a substring of a LINESTRING in SQL Server 2008 Spatial?

Answer 1

How about

DECLARE @g geometry;
SET @g = geometry::STGeomFromText('LINESTRING(-122.360 47.656, -122.343 47.656, -122.310 47.690, -122.310 47.670, -122.300 47.630)', 0);
SELECT @g.STPointN(1).ToString();
SELECT @g.STPointN(3).ToString();

DECLARE @h geometry;
SET @h = geometry::STGeomFromText('POINT(-122.360 47.656)', 0);
SELECT @h.STTouches(@g);

Have a look at

STPointN (geometry Data Type)

And

STStartPoint (geometry Data Type)

Maybe also try

STTouches (geometry Data Type)

Answer 2

Well, I managed to do it in a CLR user defined function.

It is not quite a "substring" method per se, but I needed to subdivide a LINESTRING into multiple LINESTRINGs each of either X units in length or as short as the subpoints forming the LINESTRING segment were (it is not very clear, I know!)

Below is a snippet of what I did, I hope it could be of use to somebody else:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using Microsoft.SqlServer.Types;
using System.Collections;

public partial class UserDefinedFunctions
{
    /// <summary>
    /// Take a LINESTRING and return a sub LINESTRING from it given the
    /// starting point and the distance to move
    /// </summary>
    /// <param name="inputLine"></param>
    /// <param name="divideEveryDistance"></param>
    /// <returns></returns>
    #region DivideLineString
    [Microsoft.SqlServer.Server.SqlFunction(FillRowMethodName = "FillDivideLineStringRow", TableDefinition = "segment geography")]
    public static IEnumerable DivideLineString(Microsoft.SqlServer.Types.SqlGeography inputLine, double divideEveryDistance)
    {
        // ArrayList to hold the resulting rows
        ArrayList resultCollection = new ArrayList();

        // Check that the input geography is a LINESTRING
        if (!inputLine.InstanceOf("LINESTRING"))
            throw new ArgumentException("This operation may only be executed on LineString instances.");

        // If the input distance is less than or equal zero
        // just return the original linestring
        if (divideEveryDistance <= 0)
        {
            resultCollection.Add(inputLine);
            return resultCollection;
        }

        // Builder to hold the aggregated LINESTRING
        SqlGeographyBuilder subLinestringBuilder;

        // Initialize the starting point to the start point of the input LINESTRING
        SqlGeography startPoint = inputLine.STStartPoint();
        SqlGeography currentPoint = null;

        // Initialize the starting index to the first point on the input LINESTRING
        int currentPointIndex = 1;

        // Loop on all the points on the input LINESTRING
        while (currentPointIndex < inputLine.STNumPoints())
        {
            // Initialize the builder
            subLinestringBuilder = new SqlGeographyBuilder();
            subLinestringBuilder.SetSrid(4326);
            subLinestringBuilder.BeginGeography(OpenGisGeographyType.LineString);

            // Start with the starting point of the line
            subLinestringBuilder.BeginFigure((double)startPoint.Lat, (double)startPoint.Long);

            // Distance traversed accumulator
            double currentDistance = 0;

            // While we didn't cover the required divide distance and we're still within the boundaries of the input LINESTRING
            while (currentDistance < divideEveryDistance && currentPointIndex < inputLine.STNumPoints())
            {
                // Calculate the distance between the startPoint and the nth point
                currentPoint = inputLine.STPointN(currentPointIndex);
                currentDistance = (double)startPoint.STDistance(currentPoint);

                // Add the currentPoint to the subLineString
                subLinestringBuilder.AddLine((double)currentPoint.Lat, (double)currentPoint.Long);

                // Visit the next point
                currentPointIndex++;
            }

            // We covered the required divide distance,
            // Move on to the next segment of the line
            if (currentPoint != null)
                // Set the startpoint of the next segment to be the last point we visited
                startPoint = SqlGeography.Point((double)currentPoint.Lat, (double)currentPoint.Long, 4326);

            // If we reached the end of the LINESTRING, create a segment between the last point
            // we visited and the end point of the LINESTRING
            if (currentPointIndex >= inputLine.STNumPoints())
            {
                // Add the endpoint of the original linestring
                subLinestringBuilder.AddLine((double)inputLine.STEndPoint().Lat, (double)inputLine.STEndPoint().Long);
            }

            // End the current line segment
            subLinestringBuilder.EndFigure();
            subLinestringBuilder.EndGeography();

            // Add the row to the result collection
            resultCollection.Add(subLinestringBuilder.ConstructedGeography);
        }

        // We're done, return the table
        return resultCollection;
    } 
    #endregion

    /// <summary>
    /// Method required to fill the table-valued function
    /// </summary>
    /// <param name="obj"></param>
    /// <param name="geography"></param>
    #region FillDivideLineStringRow
    private static void FillDivideLineStringRow(Object obj, out SqlGeography geography)
    {
        geography = (SqlGeography)obj;
    } 
    #endregion
};