Distance between two points using Geography datatype in sqlserver 2008?
12,992
If you have your Latitude and Longitude in form of degrees (just like in your table), then you may use the following function:
CREATE FUNCTION dbo.DictanceKM(@lat1 FLOAT, @lat2 FLOAT, @lon1 FLOAT, @lon2 FLOAT)
RETURNS FLOAT
AS
BEGIN
RETURN ACOS(SIN(PI()*@lat1/180.0)*SIN(PI()*@lat2/180.0)+COS(PI()*@lat1/180.0)*COS(PI()*@lat2/180.0)*COS(PI()*@lon2/180.0-PI()*@lon1/180.0))*6371
END
OR if you insist on Geography type, usage is:
DECLARE @g geography;
DECLARE @h geography;
SET @g = geography::STGeomFromText('LINESTRING(-122.360 47.656, -122.343 47.656)', 4326);
SET @h = geography::STGeomFromText('POINT(-122.34900 47.65100)', 4326);
SELECT @g.STDistance(@h);
Author by
Admin
Updated on June 13, 2022Comments
-
Admin almost 2 years
Here is my code.
Create Table [dbo].[MajorCities] ( [CityID] int Identity(1,1), [CityName] varchar(60), [Latitude] float, [Longitude] float, GeoRef Geography ) INSERT INTO dbo.[MajorCities] values ('New Delhi, India', 28.6, 77.2, null), ('Paris, France', 48.86667, 2.3333, null), ('Rio de Janeiro, Brazil', -22.9, -43.23333, null), ('Sydney, Australia', -33.88306, 151.21667, null), ('New York City, USA', 40.78333, -73.96667, null) select * from [MajorCities] UPDATE [dbo].[MajorCities] SET [GeoRef] = geography::STPointFromText ('POINT (' + CAST ([Longitude] AS VARCHAR (20)) + ' ' + CAST ([Latitude] AS VARCHAR (20)) + ')', 4326)
I want to find the distance between two locations in KM.
Please help me. Thanks.
-
N8allan over 9 yearsNote that spheroid computations such as these are accurate to about 10 meters over thousands of km. If higher accuracy is needed, use Vincenty or Karney's algorithms.
-
Durai Amuthan.H over 9 years@OlegDok - I also tried to find out the algorithm implementation using sql scripts.I couldn't find anything...