Search code examples
sql-serversqlgeometry

STDistance() return weird result


I am finding the distance between two points of geometries and following is my query

DECLARE @g geometry;   
SET @g = geometry::STPolyFromText('POLYGON ((5.177074447274207  60.32819571126778,
5.177074447274207   60.32533671620816,
5.172660537064075   60.32533671620816,
5.172660537064075   60.32819571126778,
5.177074447274207   60.32819571126778)
                            )', 4326).MakeValid();  


DECLARE @h geometry;
SET @h = geometry::Point(5.1752474, 60.3290297, 4326)
Select @g.STDistance(@h)

and the following is the result I get

0.000833988732217961

But when I find the distance between points on Google Map and Bing Map, I get 100mtr.

I have also checked the documentation related to SRID and as I use SRID 4326 it uses the meter as the measurement. So, if I consider the return result as the meters then there is a vastly different result.

So is there any issue with the function STDistance or should I consider this as Km instead of meters or something?


Solution

  • First: Since Earth is not flat, use geography types instead:

    --Closest point from polygon
    DECLARE @g geography = geography::Point(60.32819571126778, 5.1752474, 4326)
    --Reference point
    DECLARE @h geography = geography::Point(60.3290297, 5.1752474, 4326)
    
    SELECT @g.STDistance(@h)
    

    It returns 92,9212347595042 [meters] which seems to be correct.


    Second: To use polygon you must take care of orientation. There is distinction between 'inside polygon' and 'outside polygon' in geography type. If polygon covers half of Earth - which half should be selected? This is determined by orientation. I switched points #2 and #4. See following example:

    DECLARE @g geography = geography::STPolyFromText('POLYGON ((
    5.177074447274207   60.32819571126778,
    5.172660537064075   60.32819571126778,
    5.172660537064075   60.32533671620816,
    5.177074447274207   60.32533671620816,
    5.177074447274207   60.32819571126778))', 4326);  
    
    DECLARE @h geography = geography::Point(60.3290297, 5.1752474, 4326)
    SELECT @g.STDistance(@h)
    

    It returns 92,9192581745513 [meters] which seems to be correct.


    Third: Make sure your latitude and longitude coordinates are not switched: see Point vs STPolyFromText.