MySQL Great Circle Distance Haversine formula

Question

I ve got a working PHP script that gets Longitude and Latitude values and then inputs them into a MySQL query  I d like to make it solely MySQL  Here s my current PHP Code   if   distance     Any   amp  amp   customer zip            get the great circle distance        get the origin zip code info      zip sql    SELECT   FROM zip code WHERE zip code     customer zip         result   mysql query  zip sql        row   mysql fetch array  result        origin lat    row  lat         origin lon    row  lon           get the range      lat range    distance 69 172       lon range   abs  distance  cos  details 0     69 172         min lat   number format  origin lat -  lat range   4                  max lat   number format  origin lat    lat range   4                  min lon   number format  origin lon -  lon range   4                  max lon   number format  origin lon    lon range   4                  sql     lat BETWEEN   min lat  AND   max lat  AND lon BETWEEN   min lon  AND   max lon  AND            Does anyone know how to make this entirely MySQL  I ve browsed the Internet a bit but most of the literature on it is pretty confusing

User · Answer

I can t comment on the above answer  but be careful with  Pavel Chuchuva s answer  That formula will not return a result if both coordinates are the same  In that case  distance is null  and so that row won t be returned with that formula as is   I m not a MySQL expert  but this seems to be working for me   SELECT id    3959   acos  cos  radians 37      cos  radians  lat       cos  radians  lng   - radians -122      sin  radians 37      sin  radians  lat         AS distance  FROM markers HAVING distance  lt  25 OR distance IS NULL ORDER BY distance LIMIT 0   20

User · Answer

From Google Code FAQ - Creating a Store Locator with PHP  MySQL  amp  Google Maps      Here s the SQL statement that will find the closest 20 locations that are within a radius of 25 miles to the 37  -122 coordinate  It calculates the distance based on the latitude longitude of that row and the target latitude longitude  and then asks for only rows where the distance value is less than 25  orders the whole query by distance  and limits it to 20 results  To search by kilometers instead of miles  replace 3959 with 6371     SELECT id    3959   acos  cos  radians 37      cos  radians  lat        cos  radians  lng   - radians -122      sin  radians 37      sin radians lat       AS distance  FROM markers  HAVING distance  lt  25  ORDER BY distance  LIMIT 0   20

User · Answer

calculate distance in Mysql    SELECT  6371   acos cos radians lat2     cos radians lat1      cos radians long1  -radians long2     sin radians lat2     sin radians lat1     AS distance   thus distance value will be calculated and anyone can apply as required

User · Answer

I have written a procedure that can calculate the same  but you have to enter the latitude and longitude in the respective table   drop procedure if exists select lattitude longitude   delimiter     create procedure select lattitude longitude In CityName1 varchar 20    In CityName2 varchar 20    begin      declare origin lat float 10 2       declare origin long float 10 2        declare dest lat float 10 2       declare dest long float 10 2        if CityName1  Not In  select Name from City lat lon  OR CityName2  Not In  select Name from City lat lon  then           select  The Name Not Exist or Not Valid Please Check the Names given by you  as Message       else          select lattitude into  origin lat from City lat lon where Name CityName1           select longitude into  origin long  from City lat lon where Name CityName1           select lattitude into  dest lat from City lat lon where Name CityName2           select longitude into  dest long  from City lat lon where Name CityName2           select origin lat as CityName1 lattitude                 origin long as CityName1 longitude                 dest lat as CityName2 lattitude                 dest long as CityName2 longitude           SELECT 3956   2   ASIN SQRT  POWER SIN  origin lat - dest lat    pi   180   2   2    COS origin lat   pi   180    COS dest lat   pi   180    POWER SIN  origin long-dest long    pi   180   2   2       1 609344 as Distance In Kms        end if   end        delimiter

User · Answer

SELECT            6371   acos cos radians search lat     cos radians lat         cos radians lng  - radians search lng     sin radians search lat             sin radians lat        AS distance   FROM table   WHERE lat    search lat AND lng    search lng AND distance  lt  25    ORDER BY distance   FETCH 10 ONLY    for distance of 25 km

User · Answer

greatCircleDistance   acos  cos  latitude0    cos  latitude1    cos  longitude0 -  longitude1    sin  latitude0    sin  latitude1     with latitude and longitude in radian   so  SELECT    acos         cos radians   latitude0          cos radians   latitude1          cos radians   longitude0   - radians   longitude1          sin radians   latitude0           sin radians   latitude1        AS greatCircleDistance   FROM yourTable    is your SQL query  to get your results in Km or miles  multiply the result with the mean radius of Earth  3959 miles 6371 Km or 3440 nautical miles   The thing you are calculating in your example is a bounding box  If you put your coordinate data in a spatial enabled MySQL column  you can use MySQL s build in functionality to query the data   SELECT    id FROM spatialEnabledTable WHERE    MBRWithin ogc point  GeomFromText  Polygon  0 0 0 3 3 3 3 0 0 0

User · Answer

I thought my javascript implementation would be a good reference to         Check to see if the second coord is within the precision   meters      of the first coord and return accordingly     function checkWithinBound coord one  coord two  precision        var distance   3959000   Math acos           Math cos  degree to radian  coord two lat                Math cos  degree to radian  coord one lat                Math cos               degree to radian  coord one lng   - degree to radian  coord two lng                        Math sin  degree to radian  coord two lat                Math sin  degree to radian  coord one lat                 return distance  lt   precision            Get radian from given degree     function degree to radian degree        return degree    Math PI   180

User · Answer

If you add helper fields to the coordinates table  you can improve response time of the query   Like this   CREATE TABLE  Coordinates     id  INT 10  UNSIGNED NOT NULL COMMENT  id for the object    type  TINYINT 4  UNSIGNED NOT NULL DEFAULT  0  COMMENT  type    sin lat  FLOAT NOT NULL COMMENT  sin lat  in radians    cos cos  FLOAT NOT NULL COMMENT  cos lat  cos lon  in radians    cos sin  FLOAT NOT NULL COMMENT  cos lat  sin lon  in radians    lat  FLOAT NOT NULL COMMENT  latitude in degrees    lon  FLOAT NOT NULL COMMENT  longitude in degrees   INDEX  lat lon idx    lat    lon           If you re using TokuDB  you ll get even better performance if you add clustering indexes on either of the predicates  for example  like this   alter table Coordinates add clustering index c lat lat   alter table Coordinates add clustering index c lon lon     You ll need the basic lat and lon in degrees as well as sin lat  in radians  cos lat  cos lon  in radians and cos lat  sin lon  in radians for each point  Then you create a mysql function  smth like this   CREATE FUNCTION  geodistance   sin lat1  FLOAT                                 cos cos1  FLOAT   cos sin1  FLOAT                                 sin lat2  FLOAT                                 cos cos2  FLOAT   cos sin2  FLOAT      RETURNS float     LANGUAGE SQL     DETERMINISTIC     CONTAINS SQL     SQL SECURITY INVOKER    BEGIN    RETURN acos sin lat1 sin lat2   cos cos1 cos cos2   cos sin1 cos sin2      END   This gives you the distance   Don t forget to add an index on lat lon so the bounding boxing can help the search instead of slowing it down  the index is already added in the CREATE TABLE query above    INDEX  lat lon idx    lat    lon     Given an old table with only lat lon coordinates  you can set up a script to update it like this   php using meekrodb    users   DB  query  SELECT id lat lon FROM Old Coordinates     foreach   users as  user       lat rad   deg2rad  user  lat        lon rad   deg2rad  user  lon        DB  replace  Coordinates   array       object id    gt   user  id         object type    gt  0       sin lat    gt  sin  lat rad        cos cos    gt  cos  lat rad  cos  lon rad        cos sin    gt  cos  lat rad  sin  lon rad        lat    gt   user  lat         lon    gt   user  lon             Then you optimize the actual query to only do the distance calculation when really needed  for example by bounding the circle  well  oval  from inside and outside  For that  you ll need to precalculate several metrics for the query itself      assuming the search center coordinates are  lat and  lon in degrees    and radius in km is given in  distance  lat rad   deg2rad  lat    lon rad   deg2rad  lon    R   6371     earth s radius  km  distance rad    distance  R   distance rad plus    distance rad   1 06     ovality error for outer bounding box  dist deg lat   rad2deg  distance rad plus     outer bounding box  dist deg lon   rad2deg  distance rad plus cos deg2rad  lat      dist deg lat small   rad2deg  distance rad sqrt 2      inner bounding box  dist deg lon small   rad2deg  distance rad cos deg2rad  lat   sqrt 2      Given those preparations  the query goes something like this  php     neighbors   DB  query  SELECT id  type  lat  lon         geodistance sin lat cos cos cos sin  d  d  d  as distance        FROM Coordinates WHERE        lat BETWEEN  d AND  d AND lon BETWEEN  d AND  d        HAVING  lat BETWEEN  d AND  d AND lon BETWEEN  d AND  d  OR distance  lt    d        center radian values  sin lat  cos cos  cos sin        sin  lat rad  cos  lat rad  cos  lon rad  cos  lat rad  sin  lon rad        min lat  max lat  min lon  max lon for the outside box         lat- dist deg lat  lat  dist deg lat          lon- dist deg lon  lon  dist deg lon       min lat  max lat  min lon  max lon for the inside box         lat- dist deg lat small  lat  dist deg lat small          lon- dist deg lon small  lon  dist deg lon small       distance in radians         distance rad     EXPLAIN on the above query might say that it s not using index unless there s enough results to trigger such  The index will be used when there s enough data in the coordinates table  You can add      FORCE INDEX  lat lon idx  to the SELECT to make it use the index with no regards to the table size  so you can verify with EXPLAIN that it is working correctly   With the above code samples you should have a working and scalable implementation of object search by distance with minimal error

User · Answer

I have had to work this out in some detail  so I ll share my result   This uses a zip table with latitude and longitude tables  It doesn t depend on Google Maps  rather you can adapt it to any table containing lat long   SELECT zip  primary city          latitude  longitude  distance in mi   FROM   SELECT zip  primary city  latitude  longitude r          3963 17   ACOS COS RADIANS latpoint                       COS RADIANS latitude                       COS RADIANS longpoint  - RADIANS longitude                       SIN RADIANS latpoint                       SIN RADIANS latitude     AS distance in mi  FROM zip  JOIN           SELECT  42 81  AS latpoint   -70 81 AS longpoint  50 0 AS r      AS p   WHERE latitude     BETWEEN latpoint  -  r   69         AND latpoint     r   69     AND longitude    BETWEEN longpoint -  r    69   COS RADIANS latpoint           AND longpoint    r    69   COS RADIANS latpoint         d  WHERE distance in mi  lt   r  ORDER BY distance in mi  LIMIT 30   Look at this line in the middle of that query       SELECT  42 81  AS latpoint   -70 81 AS longpoint  50 0 AS r   This searches for the 30 nearest entries in the zip table within 50 0 miles of the lat long point 42 81 -70 81   When you build this into an app  that s where you put your own point and search radius   If you want to work in kilometers rather than miles  change 69 to 111 045 and change 3963 17 to 6378 10 in the query   Here s a detailed writeup   I hope it helps somebody   http   www plumislandmedia net mysql haversine-mysql-nearest-loc

[php] MySQL Great Circle Distance (Haversine formula)

Examples related to php

Examples related to mysql

Examples related to great-circle