3

Here

Distance between two lat,lon points

I found a very simple and nice formula:

d = sqrt(pow(lat2-lat1, 2) + cos(lat1)*pow(lon2-lon1, 2))

which I am using like this:

SELECT name, SQRT(POW(lat-45, 2) + COS(45)*POW(lon-8, 2)) AS distance
FROM shop
ORDER BY distance

and everything worked fine, because I only needed to get an approximate RELATIVE distance.

But then I needed an ABSOLUTE distance, even if approximate... And I started to realize that maybe "d" cannot be converted in meters/miles because e.g. there are 180 degrees of latitude but 360 of longitude, so this almost-Pythagorean theorem is "stretching" the diagonal line in a way that messes up a possible absolute calculation... After a while, I gave up and... Here I am.

How can I convert "d" to an absolute distance in meters/miles?

PS: Yes, I will precalculate all the constants in the SQL query in the final version...

Community
  • 1
  • 1
ZioBit
  • 905
  • 10
  • 29
  • Which DBMS are you using? Postgres? Oracle? –  Oct 26 '15 at 15:41
  • Ops, sorry, no no... Just mySQL – ZioBit Oct 26 '15 at 16:36
  • For Postgres you could have used the earthdistance module: http://www.postgresql.org/docs/current/static/earthdistance.html –  Oct 26 '15 at 16:54
  • 1
    Possible duplicate of [MySQL Great Circle Distance (Haversine formula)](http://stackoverflow.com/questions/574691/mysql-great-circle-distance-haversine-formula) – Matt Gibson Nov 03 '15 at 14:13

2 Answers2

0

In SQL for get the distance in metters check this query, also you can convert it to a function

declare @lat1 FLOAT
declare @lat2 FLOAT
declare @lon1 FLOAT
declare @lon2 FLOAT

SET @lat1 = 18.4768047
SET @lon1 = -69.9516518
SET @lat2 = 18.4624464
SET @lon2 = -69.9652686


select (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)*1000

This is an example

UPDATE!!!

I create this procedure for MySQL for get KM, Miles, Metters and yards

DELIMITER //
CREATE PROCEDURE sp_Distance(lat1 FLOAT,lon1 FLOAT,lat2 FLOAT,lon2 FLOAT,unit INT) 
BEGIN  

declare KM INT;
declare MILE INT;
declare METTER INT;
declare YARD INT;

SET KM = 6371;
SET MILE = 3959;
SET METTER = KM*1000;
SET YARD = MILE*1760;


SELECT ((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)))*
(SELECT CASE
WHEN unit = 1 THEN KM
WHEN unit = 2 THEN MILE
WHEN unit = 3 THEN METTER
WHEN unit = 4 THEN YARD END as unit_value)
) as Distance;
END;//

DELIMITER ;

Call with:

call sp_Distance(18.4768047,-69.9516518,18.4624464,-69.9652686,1)

And for SQL

CREATE PROCEDURE sp_Distance(@lat1 FLOAT,@lon1 FLOAT,@lat2 FLOAT,@lon2 FLOAT,@unit INT) 
AS
BEGIN 

declare @KM INT
declare @MILE INT
declare @METTER INT
declare @YARD INT

SET @KM = 6371
SET @MILE = 3959
SET @METTER = @KM*1000
SET @YARD = @MILE*1760

SELECT ((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)))*
(SELECT CASE
WHEN @unit = 1 THEN @KM
WHEN @unit = 2 THEN @MILE
WHEN @unit = 3 THEN @METTER
WHEN @unit = 4 THEN @YARD END as unit_value)
) as Distance
END

Call with:

exec sp_Distance 18.4768047,-69.9516518,18.4624464,-69.9652686,1
Santiago
  • 1,744
  • 15
  • 23
  • Thank you, I will try it, but I won't mark it as accepted because I was asking if I could get the meters from "d", not to have another query ;) But I will test it neverthless! – ZioBit Oct 26 '15 at 16:36
  • UPDATE: @RickJames use this for get miles select 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))*3959 – Santiago Nov 03 '15 at 12:46
0

this worked for me:

$sql = "SELECT *,(((acos(sin((".$latitude."*pi()/180)) * sin((Latitude*pi()/180))+cos((".$latitude."*pi()/180)) * cos((Latitude*pi()/180)) * cos(((".$longitude."- Longitude)*pi()/180))))*180/pi())*60*1.1515*1.609344) as distance FROM shops WHERE distance >= ".$distance; //distance is in Km

also take a look google maps functions: https://developers.google.com/maps/articles/phpsqlsearch_v3?csw=1#findnearsql

mr_gian55
  • 43
  • 8