Search code examples
sql-serverdistancegeography

How to calculate distance of two points along line in TSQL

I have added line in the MSSQL database which represent a route in map.As,

update route
  set locationArray=geography::STGeomFromText('LINESTRING(7.068419558716859 80.140939950943,7.070868430303301 80.14106869697571,7.072976578922316 80.14136910438538,7.074339417352194 80.14203429222107,7.07631978477513 80.14278531074524,7.078491790888004 80.14435172080994,7.078300143701023 80.14649748802185,7.078449202631107 80.14877200126648,7.076617903866453 80.15068173408508,7.074978246481405 80.15248417854309,7.074105179783337 80.15490889549255,7.0723803358444135 80.15643239021301)', 4326)
  where routeID=45;

Now I chose two points in the line and I wand to calculate distance between those two lines along the line (Not the direct distance or displacement) similar to the image . enter image description here

I have tried Both STLength() function and STDistance() functions. But both of them are providing direct distance. Can someone please help me to calculate the distance along the line with using TSQL?

Solution

  • STLength (geometry Data Type)

    Returns the total length of the elements in a geometry instance.

    STLength (geography Data Type)

    Returns the total length of the elements in a geography instance or the geography instances within a GeometryCollection.

    I calculated the sum of simple lengths of each segment SQRT(X*X + Y*Y) in Excel and compared it to SQL Server. Excel gave the same result as STLength for geometry type. STLength does not calculate distance from start to end point, it calculates lengths of each segment and then sums them up, as expected.

    DECLARE @G1 geometry  = geometry::STGeomFromText ('LINESTRING(7.068419558716859 80.140939950943,7.070868430303301 80.14106869697571,7.072976578922316 80.14136910438538,7.074339417352194 80.14203429222107,7.07631978477513 80.14278531074524,7.078491790888004 80.14435172080994,7.078300143701023 80.14649748802185,7.078449202631107 80.14877200126648,7.076617903866453 80.15068173408508,7.074978246481405 80.15248417854309,7.074105179783337 80.15490889549255,7.0723803358444135 80.15643239021301)', 4326);
DECLARE @G2 geography = geography::STGeomFromText('LINESTRING(7.068419558716859 80.140939950943,7.070868430303301 80.14106869697571,7.072976578922316 80.14136910438538,7.074339417352194 80.14203429222107,7.07631978477513 80.14278531074524,7.078491790888004 80.14435172080994,7.078300143701023 80.14649748802185,7.078449202631107 80.14877200126648,7.076617903866453 80.15068173408508,7.074978246481405 80.15248417854309,7.074105179783337 80.15490889549255,7.0723803358444135 80.15643239021301)', 4326);

SELECT @G1.STLength() AS LGeometry, @G2.STLength() AS LGeography;

    Result

    LGeometry             LGeography
0.0252888043080671    1809.85271737151

    So, STLength works correctly, check your code. Check that you are using correct type (geometry vs geography).