Calculating distance between two positions(lat, long) in SQL Server
Dung Do Tien Jul 04 2021 245
Hello,
I have a database with an Mssql server. I have a Product table, this table has some columns as below:
Id integer
Title varchar(500)
ShortContent varchar(1000)
LongContent varchar(2000)
Longitude float,
Latitude float,
IsPublish bit,
CreatedDate Datetime
..........And I want to when displaying some products in the browser, It only displays product that nearly visitor.
I got the latitude and longitude of visitors base on the browser, now how can I calculate the distance between them with product items in the SQL server?
Thanks for any suggestions.
Have 2 answer(s) found.
- M1
MSIIIXI Jul 04 2021
In Mssql server provided STDistance() method to help calculate distance between 2 positions base on LAT & LONG.
I usually calculate as below:
DECLARE @fromLat FLOAT = 21.0393657684326, @fromLong FLOAT = 105.811706542969, @toLat FLOAT = 21.01762609595832, @toLong FLOAT = 105.78410516850488; SELECT Geography::Point(@fromLat, @fromLong, 4326).STDistance(Geography::Point(@toLat, @toLong, 4326)) --=> Return : 3745.08428078259I hope it helpful for you!!
- f0
fahmi zaki Jul 05 2021
To find the distance between two latitudes and longitudes in SQL Server, we can use mentioned query. This query calculates the distance in miles.
DECLARE @sourceLatitude FLOAT = 28.58; DECLARE @sourceLongitude FLOAT = 77.329; DECLARE @destinationLatitude FLOAT = 27.05; DECLARE @destinationLongitude FLOAT = 78.001; DECLARE @Location FLOAT SET @Location = SQRT(POWER(69.1 * ( @destinationLatitude - @sourceLatitude), 2) + POWER(69.1 * ( @sourceLongitude - @destinationLongitude ) * COS(@destinationLatitude / 57.3), 2)) PRINT @LocationI hope it helpful for you.
* Type maximum 2000 characters.
* All comments have to wait approved before display.
* Please polite comment and respect questions and answers of others.