TSQL: Calculating distance between two points (Latitude, Longitude)

moondaddy 911 Reputation points
2020-12-05T06:24:33.627+00:00

Using SQL Server 2019 I tried this:

DECLARE @source geography = 'POINT(30.152422380193446 -95.46684547613926)'
DECLARE @target geography = 'POINT(30.31111901975078 -95.45654579441755)'

SELECT @source.STDistance(@target)

These are 2 points copied from google maps just north of Houston Texas about 10 miles apart. When I run this I get the following exception:

Msg 6522, Level 16, State 1, Line 9
A .NET Framework error occurred during execution of user-defined routine or aggregate "geography": 
System.FormatException: 24201: Latitude values must be between -90 and 90 degrees.
System.FormatException: 
   at Microsoft.SqlServer.Types.GeographyValidator.ValidatePoint(Double x, Double y, Nullable`1 z, Nullable`1 m)
   at Microsoft.SqlServer.Types.Validator.BeginFigure(Double x, Double y, Nullable`1 z, Nullable`1 m)
   at Microsoft.SqlServer.Types.ForwardingGeoDataSink.BeginFigure(Double x, Double y, Nullable`1 z, Nullable`1 m)
   at Microsoft.SqlServer.Types.CoordinateReversingGeoDataSink.BeginFigure(Double x, Double y, Nullable`1 z, Nullable`1 m)
   at Microsoft.SqlServer.Types.WellKnownTextReader.ParsePointText(Boolean parseParentheses)
   at Microsoft.SqlServer.Types.WellKnownTextReader.ParseTaggedText(OpenGisType type)
   at Microsoft.SqlServer.Types.WellKnownTextReader.Read(OpenGisType type, Int32 srid)
   at Microsoft.SqlServer.Types.SqlGeography.ParseText(OpenGisType type, SqlChars taggedText, Int32 srid)
   at Microsoft.SqlServer.Types.SqlGeography.GeographyFromText(OpenGisType type, SqlChars taggedText, Int32 srid)
   at Microsoft.SqlServer.Types.SqlGeography.Parse(SqlString s)
.
Msg 6522, Level 16, State 1, Line 10
A .NET Framework error occurred during execution of user-defined routine or aggregate "geography": 
System.FormatException: 24201: Latitude values must be between -90 and 90 degrees.
System.FormatException: 
   at Microsoft.SqlServer.Types.GeographyValidator.ValidatePoint(Double x, Double y, Nullable`1 z, Nullable`1 m)
   at Microsoft.SqlServer.Types.Validator.BeginFigure(Double x, Double y, Nullable`1 z, Nullable`1 m)
   at Microsoft.SqlServer.Types.ForwardingGeoDataSink.BeginFigure(Double x, Double y, Nullable`1 z, Nullable`1 m)
   at Microsoft.SqlServer.Types.CoordinateReversingGeoDataSink.BeginFigure(Double x, Double y, Nullable`1 z, Nullable`1 m)
   at Microsoft.SqlServer.Types.WellKnownTextReader.ParsePointText(Boolean parseParentheses)
   at Microsoft.SqlServer.Types.WellKnownTextReader.ParseTaggedText(OpenGisType type)
   at Microsoft.SqlServer.Types.WellKnownTextReader.Read(OpenGisType type, Int32 srid)
   at Microsoft.SqlServer.Types.SqlGeography.ParseText(OpenGisType type, SqlChars taggedText, Int32 srid)
   at Microsoft.SqlServer.Types.SqlGeography.GeographyFromText(OpenGisType type, SqlChars taggedText, Int32 srid)
   at Microsoft.SqlServer.Types.SqlGeography.Parse(SqlString s)

Any idea how to run this calculation?

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,547 questions
{count} votes

Accepted answer
  1. moondaddy 911 Reputation points
    2020-12-05T06:47:04.323+00:00

    I found it here:
    https://h-savran.blogspot.com/2017/09/how-to-find-distance-between-two-places.html

    and this is the code. I compared it to a straight line on google maps and it matched.

    DEClARE
    @lat decimal(18,15),
    @lng decimal(18,15)
    SET @lat = 47.639322;
    SET @lng = -122.128383;
    
    Declare @source geography = geography::Point(30.20491677226107, -95.45612258030434, 4326);
    Declare @destination geography= geography::Point(30.120021042878015, -95.44187468725596, 4326);
    
    Select @source.STDistance(@destination) as Meters
    Select @source.STDistance(@destination) / 1000 as Kilometers
    Select @source.STDistance(@destination) / 1609.344 as Miles
    
    2 people found this answer helpful.
    0 comments No comments

0 additional answers

Sort by: Most helpful