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.
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);
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With