如何在SQL Server 2008 Spatial中返回LINESTRING的子字符串？

假设我有一个LINESTRING定义为

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

我想从

中获取一个子字符串

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

如何在SQL Server 2008 Spatial中返回LINESTRING的子字符串？

---

解决方案

我成功地在CLR用户定义函数中完成了这项工作。

它本身不是一个"子字符串"方法，但我需要将一个LINESTRING细分为多个LINESTRING，每个LINESTRING的长度或与构成LINESTRING段的子点一样短(这不是很清楚，我知道！)

以下是我所做工作的一小段，希望能对其他人有所帮助：

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
};