1

I am trying to use the SQL Server 2014 functions to determine distance between two points on a geographical surface. I have three fields in a table (Lat, Long, Coordinates). [Lat] and [Long] are existing values and I store the geographical point coordinates in the [Coordinates] field using the following:

UPDATE dbo.[MyTable]
SET [Coordinates] = geography::STPointFromText('POINT(' + CAST([Lon] AS VARCHAR(20)) + ' ' + CAST([Lat] AS VARCHAR(20)) + ')', 4326) ;

So now I have a table full of records that have the geographical coordinates pre-computed in the [Coordinates] field. Now I want to determine the distance in miles between Point_A and Point_B. I used the following:

-- Compute Point_A:

DECLARE @g geography = (SELECT [Coordinates] FROM [MyTable] WHERE [Id] = 68);

-- Compute Point_B:

DECLARE @h geography = (SELECT [Coordinates] FROM [MyTable] WHERE [Id] = 1439);

-- Compute Distance:

SELECT ROUND(@g.STDistance(@h))  AS [Distance];

The actual distance is about 20 miles but this computation is giving me a number that is thousands of times greater than 20 miles.

Is .STDistance returning meters instead of miles?

On a related note: can anyone point me to an example on the web where one matches thousands of geographical points in a table with the nearest geographical points in another table containing thousand of points? I can see this computation taking a very long time if I can't find a way to shorten the process.

Tom C.
  • 73
  • 2
  • 10
  • 1
    Possible duplicate of [SQL Server 2008 GEOGRAPHY STDistance() value](http://stackoverflow.com/questions/3335773/sql-server-2008-geography-stdistance-value) – GSerg Mar 29 '16 at 18:11
  • As for the speed, I believe you want a [spatial index](https://msdn.microsoft.com/en-us/library/bb934196%28v=sql.120%29.aspx) ([restrictions may apply](https://msdn.microsoft.com/en-us/library/bb895265%28v=sql.120%29.aspx#geography)). – GSerg Mar 29 '16 at 18:14
  • I believe it's in meters – Mattias Jul 29 '16 at 07:39

3 Answers3

1

The actual value returned depends on the SRID (Spatial Reference Identifiers) of your geography column - you set this when you created the geography items. If SRID is not specified, the default value of 4326 is assumed, which corresponds to the WGS 84 datum.

As far as performance goes you can index the geography columns, heres a link to get you on the right path.

https://technet.microsoft.com/en-us/library/bb964712(v=sql.105).aspx

Vincent
  • 842
  • 7
  • 13
1

if your SRID is 4326 , unit is metre

you can use this sql check another SRID

Select * from sys.spatial_reference_systems
Guan Jyun Chen
  • 113
  • 1
  • 10
0

I was using the default SRID value of 4326.

Through trial and error, I discovered that it is returning meters vs. miles.

It would have been nice if Microsoft would have made that more clear in their documentation.

Tom C.
  • 73
  • 2
  • 10
  • Tom meters is the default return value when using PostGIS as well and we have to convert to miles. It appears that is a standard in Geographical systems. – KeyOfJ Dec 09 '16 at 14:49