3

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?

Dale K
  • 25,246
  • 15
  • 42
  • 71
sandip
  • 165
  • 1
  • 11

1 Answers1

3

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.

Paweł Dyl
  • 8,888
  • 1
  • 11
  • 27
  • I think you meant `geography` at the first line. – EzLo Jan 02 '19 at 10:49
  • @PawełDyl questions over here if I use Point than should I use Point(Latitude,Longitude) and for Polygon should I use Polygon(Longitude Latitude)? cause in example you switch the latitude and longitude in Polygon and Point. – sandip Jan 02 '19 at 13:17
  • 1
    Well, yes. Point is specified here: https://learn.microsoft.com/en-us/sql/t-sql/spatial-geography/point-geography-data-type?view=sql-server-2017. I left POLYGON coordinates unchanged. – Paweł Dyl Jan 02 '19 at 14:30
  • @PawełDyl and what about if I use geometry.In documentation it just mentioned POINT(x,y,SRID) but what should be the X and Y.This is my main confusion – sandip Jan 03 '19 at 08:22
  • 1
    As a simple rule: `geometry` better fits flat areas (Cartesian), `geography` better fits 'globe' calculations (geodetic). If you click link in my previous comment, you should see following declaration `Point ( Lat, Long, SRID )`. `Lat` is Latitude, `Long` is Longtitude. – Paweł Dyl Jan 03 '19 at 08:41
  • @PawełDyl sorry to bother you again. I have checked the Orientation related to geography polygon.but in my case I am getting polygon from another service. and hence, how one should decide the orientation of geography polygon? – sandip Jan 03 '19 at 09:16
  • 1
    According to [specification](https://learn.microsoft.com/en-us/sql/relational-databases/spatial/spatial-data-types-overview?view=sql-server-2017#orientation-of-spatial-data): 'The interior of the polygon in an ellipsoidal system is defined by the left-hand rule'. You can: 1. ask another service to respect rules. 2. If rule is always broken, reverse points. 3. If there is no rule - select points forming minimal area from list a) received from service, b) reversed (one set should likely cover almost whole world, switching #2 and #4 is reversing 5-element list). – Paweł Dyl Jan 03 '19 at 10:28