Calculating distance between two positions(lat, long) in SQL Server

Dung Do Tien Jul 04 2021 257

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.
  • M

    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!!

  • f

    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.

Leave An Answer
* NOTE: You need Login before leave an answer

* Type maximum 2000 characters.

* All comments have to wait approved before display.

* Please polite comment and respect questions and answers of others.

Popular Tips

X Close