question

moondaddy-8531 avatar image
0 Votes"
moondaddy-8531 asked paytonbaldridge-0736 published

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

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?

sql-server-transact-sql
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Pretty sure you need to put the longitude before the latitude when creating a geography point in sql. When testing on my system it worked this way.

  DECLARE @source geography = 'POINT(-95.46684547613926 30.152422380193446)'
0 Votes 0 ·

1 Answer

moondaddy-8531 avatar image
0 Votes"
moondaddy-8531 answered

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
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.