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

Dung Do Tien Jul 04 2021 283

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.

Related Q&A May You Like

  1. MySql throw no database selected, select the default DB to be used by double-clicking its name
  2. How to generate whole sql database script in MySql Workbench
  3. Error querySrv ENODATA _mongodb._tcp.456d in MongoDB Compass
  4. What is best way to convert datetime to date in Sql Server?
  5. How to compare date without time with now() in PostgreSQL?
  6. MySQL Error Code: 1175. safe update mode update a table without a WHERE
  7. ERROR 1819 (HY000): Your password does not satisfy the current policy requirements in MySQL
  8. ERROR: column must appear in the GROUP BY clause or be used in an aggregate function in PostgreSQL
  9. MySQL Error: 'Access denied for user 'root'@'localhost'
  10. Error: /opt/lampp/bin/mysql.server: 264: kill: No such process
  11. How to get the total of current connection in PostgreSQL?
  12. How to backup and restore MSSQL Server database in Ubuntu
  13. ERROR: OFFSET must not be negative in PostgreSQL
  14. How to update top N records for a table in PostgreSQL?
  15. PostgreSQL Error: Terminating connection due to administrator command
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.

Most Quizs Popular

Start Test

Javascript - Functions

Start Test

Asp.Net Core - Exam test 01

Start Test

Asp.Net Core - Exam test 02

Start Test

C# - Level Beginner

Popular Tips

This site uses cookies so that we can remember you and understand how you interact with our website. This allows us to improve and customize your browsing experience. See our Cookies Policy.
X Close