Calculating distance between two positions(lat, long) in SQL Server
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.
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.08428078259
I hope it helpful for you!!
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 @Location
I hope it helpful for you.