SQL Linestring with multiple coordinates - can I use each location for distance calculations?

Question

I am trying to figure out if I can use a LineString with multiple coordinates, and calculate distances between each of the items in linestring, and another table with coordinates:

I have a few locations that I need to visit for e.g.

   A1 40.7120879 -73.9113197
   A2 40.7828647 -73.9653551
   A3 40.740777  -73.841136

I want to find out distance between these linestring locations and the other locations I will stop at

B1 40.7029334,-73.6208314
B2 40.7037142,-73.6086649
B3 40.7088981,-73.6137852
B4 40.7133267,-73.6056991
B5 40.7185586,-73.6007205

e.g.

A1 B1 - xxa meters
A1 B2 - xxb meters
A1 B3 - xxc meters
A1 B4 - xxd meters
A1 B5 - xxe meters 

A2 B1 - xya meters
A2 B2 - xyb meters
A3 B3 - xyc meters
A4 B4 - xyd meters
A5 B5 - xye meters

etc

CREATE TABLE SpatialTable 
    ( id int IDENTITY (1,1),
    GeogCol1 geography
);
GO

INSERT INTO SpatialTable (GeogCol1)
VALUES (geography::STGeomFromText('LINESTRING(40.7120879 -73.9113197, 40.7828647 -73.9653551, 40.740777, -73.841136)', 4326))


CREATE TABLE Route
    ( id int IDENTITY (1,1),
    Latitude float,
    Longitude float 
);
GO


Insert into Route(Latitude,Longitude)
values
(40.7029334,-73.6208314),
(40.7037142,-73.6086649),
(40.7088981,-73.6137852),
(40.7133267,-73.6056991),
(40.7185586,-73.6007205)

Answer 1

There is no inbuilt function for this (that I am aware of at least), but something like this should give you the results you are looking for.

--Index of point on linestring
DECLARE  @i INT = 1

--Output
DECLARE @ResultTable TABLE (LinePointNumber INT, PointID INT, Distance FLOAT)

--Identifier for the individual points
DECLARE @id INT
DECLARE @g GEOGRAPHY

--Linestring
DECLARE @geogcol GEOGRAPHY = (SELECT TOP 1 GeogCol1 FROM SpatialTable)

--loop through each point on the linestring
WHILE @i <= @geogcol.STNumPoints()
BEGIN
  --turn points into geography and loop through the collection
  --if you just cast a float to a varchar it will truncate, so cast to a decimal first to prevent that
  DECLARE pointcursor CURSOR FOR SELECT id, GEOGRAPHY::STPointFromText('POINT(' + CAST(CAST(Longitude AS DECIMAL(15,10)) AS VARCHAR(15)) + ' ' + CAST(CAST(Latitude AS DECIMAL(15,10)) AS VARCHAR(15)) + ')', 4326)
  OPEN pointcursor
  FETCH NEXT FROM pointcursor INTO @id, @g
  WHILE @@FETCH_STATUS = 0
  BEGIN
    --STPointN returns that point, the the distance is STDistance
    INSERT INTO @ResultTable (LinePointNumber, PointID, Distance) VALUES (@i, @id, @g.STDistance(@geogcol.STPointN(@i)))

    FETCH NEXT FROM pointcursor INTO @id, @g
  END
  CLOSE pointcursor
  DEALLOCATE pointcursor
  SET @i = @i + 1
END

SELECT * FROM @ResultTable

Answer 2

Hcaelxxam has just beat me to a solution - and in native SQL. Never-the-less, I've worked it up for you so here it is.

Given a helper class of

public class MyPoint
{
    public MyPoint(string title, SqlGeography location)
    {
        this.Title = title;
        this.Location = location;
    }

    public string Title { get; set; }

    public SqlGeography Location { get; set; }
}

Use the following code:

// The line, or route consisting of "waypoints"
        SqlGeography lineString = SqlGeography.STLineFromText(new System.Data.SqlTypes.SqlChars("LINESTRING(40.7120879 -73.9113197, 40.7828647 -73.9653551, 40.740777 -73.841136)"), 4326);

        // A container for all lookups
        List<MyPoint> stopLocations = new List<MyPoint>();

        // A container for all waypoints in the route
        List<MyPoint> wayPoints = new List<MyPoint>();

        // Add the stop locations
        stopLocations.Add(new MyPoint("B1", SqlGeography.Point(-73.6208314, 40.7029334, 4326)));
        stopLocations.Add(new MyPoint("B2", SqlGeography.Point(-73.6086649, 40.7037142, 4326)));
        stopLocations.Add(new MyPoint("B3", SqlGeography.Point(-73.6137852, 40.7088981, 4326)));
        stopLocations.Add(new MyPoint("B4", SqlGeography.Point(-73.6056991, 40.7133267, 4326)));
        stopLocations.Add(new MyPoint("B5", SqlGeography.Point(-73.6007205, 40.7185586, 4326)));

        // Add the waypoints
        for (int i = 1; i < (lineString.STNumPoints() + 1); i++)
        {
            wayPoints.Add(new MyPoint("A" + i, SqlGeography.Point(lineString.STPointN(i).Lat.Value, lineString.STPointN(i).Long.Value, lineString.STSrid.Value)));
        }

        // Join the data (essentially a cross-join) and calculate distances
        var joinAndCalculate = (
            from wp in wayPoints
            from sl in stopLocations
            select new
            {
                WayPointTitle = wp.Title,
                StopLocationTitle = sl.Title,
                DistanceInMetres = wp.Location.STDistance(sl.Location)
            }
            ).OrderBy(x => x.WayPointTitle).ThenBy(x => x.StopLocationTitle)
            .ToList()
                               ;
        // Print to the console, for testing
        foreach (var data in joinAndCalculate)
        {
            Console.WriteLine(data.WayPointTitle + "\t" + data.StopLocationTitle + "\t" + data.DistanceInMetres);
        }

If not you, it may help someone else.