Search code examples
sqlsql-serverazure-sql-databasespatialsql-server-2017

Calculate distance between two geographical locations in sql server

I am trying to calculate the distance from one point to another point(Polygon/Multipolygon) using the following query in SQL Server.

DECLARE @g geometry;  
DECLARE @h geometry;  
SET @g = geometry::STGeomFromText('POLYGON ((139.999722222222 40.0513888888889, 141.401111111111 40.0513888888889, 141.398333333333 39.3494444444444, 140.765 38.1005555555556, 140.766111111111 37.3333333333333, 140.314722222222 36.8161111111111, 140.316111111111 36.2841666666667, 139.050277777778 36.2847222222222, 139.049444444444 35.1669444444444, 138.499444444444 35.1669444444444, 138.499444444444 35.5852777777778, 136.816944444444 35.5830555555556, 136.034166666667 34.8980555555556, 135.732222222222 34.8997222222222, 135.732222222222 35.3827777777778, 136.431944444444 35.8013888888889, 137.365 36.5341666666667, 138.165555555556 36.9333333333333, 139.415 37.8152777777778, 140.285833333333 39.4497222222222, 140.285833333333 39.8652777777778, 139.915555555556 39.8652777777778, 139.915555555556 39.9669444444444, 139.999722222222 40.0513888888889))', 4326);
SET @h = geometry::STGeomFromText('POINT(-1.9335937499142 53.956085529457)', 4326);  
SELECT @g.STDistance(@h)/1609.344 as DistanceInMiles,@h.STDistance(@g) as DistanceInMeters

Following coordinates are in UK,

POINT(-1.9335937499142 53.956085529457)

Polygon's coordinates are in Japan. Somehow when I run the query the distance I am getting is incorrect.

You can check the result in the following db fiddle,

https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=d6f4e3cbfa8d836c24006f6005eefc4b

Not sure whether I am doing anything wrong?

Solution

  • You have 2 issues here. The first is not working the GEOGRAPHY datatypes and working with GEOMETRY instead, when you are actually working with GPS coordinates. Geography will consider the shape of the earth and will return more precise values for this scenario.

    The second and biggest problem is the ring orientation of your Japan polygon. The order the coordinates are placed on the hard-coded string is very important to determine the resulting polygon. Sometimes SQL Server can't properly determine which side of the edges between our points we actually mean as the polygon.

    You can fix this with a very neat trick by doing a STUnion with the GEOMETRY representation of the same polygon:

    DECLARE @g_metry GEOMETRY = GEOMETRY::STGeomFromText('POLYGON ((139.999722222222 40.0513888888889, 141.401111111111 40.0513888888889, 141.398333333333 39.3494444444444, 140.765 38.1005555555556, 140.766111111111 37.3333333333333, 140.314722222222 36.8161111111111, 140.316111111111 36.2841666666667, 139.050277777778 36.2847222222222, 139.049444444444 35.1669444444444, 138.499444444444 35.1669444444444, 138.499444444444 35.5852777777778, 136.816944444444 35.5830555555556, 136.034166666667 34.8980555555556, 135.732222222222 34.8997222222222, 135.732222222222 35.3827777777778, 136.431944444444 35.8013888888889, 137.365 36.5341666666667, 138.165555555556 36.9333333333333, 139.415 37.8152777777778, 140.285833333333 39.4497222222222, 140.285833333333 39.8652777777778, 139.915555555556 39.8652777777778, 139.915555555556 39.9669444444444, 139.999722222222 40.0513888888889))', 4326);

DECLARE @g GEOGRAPHY = @g_metry.MakeValid().STUnion(@g_metry.STStartPoint()).STAsText()

DECLARE @h GEOGRAPHY = GEOGRAPHY::STGeomFromText('POINT(-1.9335937499142 53.956085529457)', 4326); 

SELECT 
    @g.STDistance(@h)/1609.344 as DistanceInMiles,
    @h.STDistance(@g) as DistanceInMeters

    Result:

    DistanceInMiles     DistanceInMeters
5574.53292757983    8971341.11980304

    ![enter image description here

    For further reading you can read this blog post.