Fastest Way to Find Distance Between Two Lat/Long Points

Question

I currently have just under a million locations in a mysql database all with longitude and latitude information.

I am trying to find the distance between one point and many other points via a query. It's not as fast as I want it to be especially with 100+ hits a second.

Is there a faster query or possibly a faster system other than mysql for this? I'm using this query:

SELECT 
  name, 
   ( 3959 * acos( cos( radians(42.290763) ) * cos( radians( locations.lat ) ) 
   * cos( radians(locations.lng) - radians(-71.35368)) + sin(radians(42.290763)) 
   * sin( radians(locations.lat)))) AS distance 
FROM locations 
WHERE active = 1 
HAVING distance < 10 
ORDER BY distance;

Note: The provided distance is in Miles. If you need Kilometers, use 6371 instead of 3959.

Basilius

The formula you give seems to have a lot of elements that are constant. Is it possible to pre-compute data and store those values as well in your DB? For example 3959 * acos( cos( radians(42.290763) ) is a constant but has 4 major computations in it. Instead could you just store 6696.7837? – Calbert

Or at least pre-compute constants outside of the query? That will cut down on the work that has to be done. – Calbert

@Peter M It seems likely that any decent SQL database would optimize so that was computed only once. – Fleisig

For those wondering, 42.290763 is the latitude and -71.35368 is the longitude of the point from which to compute the distances. – Deedeeann

Another way is to use a UDF, a couple of years ago I hade the same problem and wrote this lib_mysqludf_haversine...maybe could be useful to someone else. – Financial

@LuS, I just starred your library on Github. Works great. Put that as an answer here and I'll upvote it. A pre-reminder that stackoverflow doesn't like "just a link" answers, so explain it a little bit. – Jabin

@TylerCollier glad to read that! and thanks for your kindness, I've post ad answer. All the best, Luca – Financial

Just for info ,Distance caluclated by this formula is in miles ,not in kilometers.Please Replace 3959 to 6371 to get results in kilometers – Americaamerican

Your formula is one of the most compact geo-distance formulas I've ever seen. Can you elaborate a little on how it works? Specifically, what's the 3959 constant? – Nyctophobia

5 ways to solve the Question: mysql.rjweb.org/doc.php/find_nearest_in_mysql Computing distance is not the real problem; avoiding doing it a million times is the real answer. Two of the ways do a good job of avoiding a million computations. – Antoineantoinetta

Answer 1

• Create your points using Point values of Geometry data types in MyISAM table. As of Mysql 5.7.5, InnoDB tables now also support SPATIAL indices.

• Create a SPATIAL index on these points

• Use MBRContains() to find the values:

    SELECT  *
    FROM    table
    WHERE   MBRContains(LineFromText(CONCAT(
            '('
            , @lon + 10 / ( 111.1 / cos(RADIANS(@lat)))
            , ' '
            , @lat + 10 / 111.1
            , ','
            , @lon - 10 / ( 111.1 / cos(RADIANS(@lat)))
            , ' '
            , @lat - 10 / 111.1 
            , ')' )
            ,mypoint)
  

, or, in MySQL 5.1 and above:

    SELECT  *
    FROM    table
    WHERE   MBRContains
                    (
                    LineString
                            (
                            Point (
                                    @lon + 10 / ( 111.1 / COS(RADIANS(@lat))),
                                    @lat + 10 / 111.1
                                  ),
                            Point (
                                    @lon - 10 / ( 111.1 / COS(RADIANS(@lat))),
                                    @lat - 10 / 111.1
                                  ) 
                            ),
                    mypoint
                    )

This will select all points approximately within the box (@lat +/- 10 km, @lon +/- 10km).

This actually is not a box, but a spherical rectangle: latitude and longitude bound segment of the sphere. This may differ from a plain rectangle on the Franz Joseph Land, but quite close to it on most inhabited places.

• Apply additional filtering to select everything inside the circle (not the square)

• Possibly apply additional fine filtering to account for the big circle distance (for large distances)

Furcate

How good is the Spatial in mySQL, I remember using it a while ago and it didn't seem that great. Have they improved it in newer versions? – Basilius

@Ryan Detzel: In 5.0, this query will be a matter of milliseconds for 1,000,000 rows. Just run EXPLAIN on the query and make sure the SPATIAL index is used for coarse filtering. – Furcate

@Quassnoi: A couple corrections: You'll probably want to switch the order of the coordinates to lat, long. Also, longitudinal distances are proportional the cosine of the latitude, not longitude. And you'll want to change it from multiplication to division, so your first coordinate would be corrected as @lon - 10 / ( 111.1 / cos(@lat)) (and be the second in the pair once everything was correct. – Barbiebarbieri

WARNING : The body of the answer has NOT been edited to accord with the very valid comment made by @M. Dave Auayan. Further notes: This method goes pearshaped if the circle of interest (a) includes a pole or (b) is intersected by the +/-180 degree meridian of longitude. Also using cos(lon) is accurate only for smallish distances. See janmatuschek.de/LatitudeLongitudeBoundingCoordinates – Wring

Is there any way that we could get some insight into what the constants (10, 111.11, @lat, @lon, mypoint) represent? I assume that the 10 is for kilometers distance, @lat and @lon represent the provided lattitue and longitude, but what do 111.11 and mypoint represent in the example? – Oblivion

@ashays: there are roughly 111.(1) km in a degree of latitude. mypoint is the field in the table which stores the coordinates. – Furcate

Another error correction - you are missing a closing ) on the second to last line – Sawfish

Has anyone checked the query in practice (or is this all in theory) ... I can't seem to get it to even return exact lat lng points #9378460 – Sawfish

@Eddie: MBR in MBRContains stands for Minimal Bounding Rectangle. For a diagonal LineString it has a positive area. You can use a Polygon too, of course, but it just introduces unneeded complexity. – Furcate

MyISAM is not requeired any more (dev.mysql.com/doc/refman/5.0/en/spatial-extensions.html): Before MySQL 5.0.16, these features are available for MyISAM tables only. As of MySQL 5.0.16, InnoDB, NDB, BDB, and ARCHIVE also support spatial features. – Biogen

@R_User: InnoDB does not support spatial indexes even as of 5.7 dev.mysql.com/doc/refman/5.7/en/… MyISAM supports both SPATIAL and non-SPATIAL indexes. Other storage engines support non-SPATIAL indexes, as described in Section 13.1.11, “CREATE INDEX Syntax”. You can create a spatial type in InnoDB, but you can't index it. – Furcate

@Furcate You can create a spatial type in InnoDB, and you CAN index it, but only by B-tree index, not by SPATIAL index with R-Tree with quadratic splitting. Last one accessible only for MyISAM table engine. Read here: It is also possible to create normal indexes on spatial columns. In a non-SPATIAL index, you must declare a prefix for any spatial column except for POINT columns. – Pecker

@HAL9000: yes, that's why I explicitly mentioned spatial indexes in my comment above. A B-Tree index on a spatial type won't solve the op's pborlem. – Furcate

See en.wikipedia.org/wiki/Latitude#Length_of_a_degree_of_latitude for the constants – Plumbago

Actually, this answer is wrong. When you have ( 111.1 / COS(RADIANS(@lat))), you have to remove the parentheses around this expression. Otherwise, you get the wrong arithmetic operator before the COS(). The results will be off by about 2x for Paris or Berlin, for example. See this link for the correct conversion and distance formula (can be trivially rewritten to SQL): github.com/delight-im/Android-SimpleLocation/blob/… – Plumbago

@Furcate I'm not sure I agree - I think MySQL fully supports spatial indices in InnoDB in 5.7 and I think they're full R-Tree indices too. Manual seems to back up what I'm saying as far as I can see. – Chloechloette

@carlosp: what's exactly you don't agree with? – Furcate

@Furcate You commented that MySQL 5.7 doesn't support spatial indices in the InnoDB engine; but it does. I think you also implied in a later comment that they're not R-tree indices, but I think they are. I wanted to get this clear so future persons reading don't get misinformation about this. In general I've found the performance of spatial indices on a POINT column in MySQL 5.7 to be excellent. – Chloechloette

@CarlosP: how could I possibly comment in 2013 on something that was implemented in 2014? – Furcate

@Quassanoi - Ah I see, great - so this entire comment thread is out-of-date and I hadn't noticed. I'm surprised you didn't realise that I had made that mistake. Anyway, great, further readers can now be up-to-date on the latest developments! – Chloechloette

@carlosp: no problem. Btw did you know you had enough reputation to edit outdated answers? – Furcate

@CarlosP I've implemented above but getting Error Code: 3037. Invalid GIS data provided to function st_geometryfromtext. need help. – Entranceway

Answer 2

Not a MySql specific answer, but it'll improve the performance of your sql statement.

What you're effectively doing is calculating the distance to every point in the table, to see if it's within 10 units of a given point.

What you can do before you run this sql, is create four points that draw a box 20 units on a side, with your point in the center i.e.. (x1,y1 ) . . . (x4, y4), where (x1,y1) is (givenlong + 10 units, givenLat + 10units) . . . (givenLong - 10units, givenLat -10 units). Actually, you only need two points, top left and bottom right call them (X1, Y1) and (X2, Y2)

Now your SQL statement use these points to exclude rows that definitely are more than 10u from your given point, it can use indexes on the latitudes & longitudes, so will be orders of magnitude faster than what you currently have.

e.g.

select . . . 
where locations.lat between X1 and X2 
and   locations.Long between y1 and y2;

The box approach can return false positives (you can pick up points in the corners of the box that are > 10u from the given point), so you still need to calculate the distance of each point. However this again will be much faster because you have drastically limited the number of points to test to the points within the box.

I call this technique "Thinking inside the box" :)

EDIT: Can this be put into one SQL statement?

I have no idea what mySql or Php is capable of, sorry. I don't know where the best place is to build the four points, or how they could be passed to a mySql query in Php. However, once you have the four points, there's nothing stopping you combining your own SQL statement with mine.

select name, 
       ( 3959 * acos( cos( radians(42.290763) ) 
              * cos( radians( locations.lat ) ) 
              * cos( radians( locations.lng ) - radians(-71.35368) ) 
              + sin( radians(42.290763) ) 
              * sin( radians( locations.lat ) ) ) ) AS distance 
from locations 
where active = 1 
and locations.lat between X1 and X2 
and locations.Long between y1 and y2
having distance < 10 ORDER BY distance;

I know with MS SQL I can build a SQL statement that declares four floats (X1, Y1, X2, Y2) and calculates them before the "main" select statement, like I said, I've no idea if this can be done with MySql. However I'd still be inclined to build the four points in C# and pass them as parameters to the SQL query.

Sorry I can't be more help, if anyone can answer the MySQL & Php specific portions of this, feel free to edit this answer to do so.

Snook

P.S. I did this years ago for a parcel delivery sub system, and it worked a treat. Apologies if latitudes should be Y and Longitudes should be X :) – Snook

Interesting, so, is there a way to tie this into one function for a SQL query or would it be faster to just pull out the locations in the box and then use what ever language I'm using to calculate the actual distances? – Basilius

Depends on the volume of data to be honest. Consider that area of the box will be approx 27% larger than the circle your constrained to, so potentially you're going to bring back 27% more data than you need. If is is an extra 27 rows, I wouldn't sweat it, if it's going to be 27,000 then I'd have SQL weed them out. – Snook

Ryan: What do you mean by "is there a way to tie this into one function for a SQL query"? – Snook

@Binary Worrier: I mean, one select statement. – Basilius

You can find a mysql procedure for this approach in this presentation: scribd.com/doc/2569355/Geo-Distance-Search-with-MySQL – Muggins

@Muggins ...but apparently the distances that calculates are inaccurate see the comments here forge.mysql.com/tools/tool.php?id=41 and pabloj.blogspot.com/2006/01/distance-function-for-mysql.html (I don't know for sure myself) – Neilson

To search by kilometers instead of miles, replace 3959 with 6371. – Peroxy

+1, great option; adding the box reduced my query from 4s to 0.03s avg. – Mather

Altough it seems so logic, you reserve an award for this solution! On a 2 milion record database the query went from 16 seconds to 0.06 seconds. Note: It is even faster (for large tables) if you cut the distance calculation out of the query and do the calculation for the distance in your program code! – Thagard

@Binary Worrier : So the X1, X2 and Y1, Y2 will be Longitude Min and Max and Latitude Min and Max as per the example given here: blog.fedecarg.com/2009/02/08/… please advise. – Shamble

@Prabhat: Dude I have no idea, I don't have time to read that blog and get to grips with the concepts involved, I don't even have time right now to reload all the information in this question back into my head. Best of luck finding your solution! – Snook

You can combine something like this in java github.com/vahidhedayati/latlongcalc to work out the min max values that you have not defined in your formulae above (in the java/groovy world) – Grammer

Answer 3

I needed to solve similar problem (filtering rows by distance from single point) and by combining original question with answers and comments, I came up with solution which perfectly works for me on both MySQL 5.6 and 5.7.

SELECT 
    *,
    (6371 * ACOS(COS(RADIANS(56.946285)) * COS(RADIANS(Y(coordinates))) 
    * COS(RADIANS(X(coordinates)) - RADIANS(24.105078)) + SIN(RADIANS(56.946285))
    * SIN(RADIANS(Y(coordinates))))) AS distance
FROM places
WHERE MBRContains
    (
    LineString
        (
        Point (
            24.105078 + 15 / (111.320 * COS(RADIANS(56.946285))),
            56.946285 + 15 / 111.133
        ),
        Point (
            24.105078 - 15 / (111.320 * COS(RADIANS(56.946285))),
            56.946285 - 15 / 111.133
        )
    ),
    coordinates
    )
HAVING distance < 15
ORDER By distance

coordinates is field with type POINT and has SPATIAL index
6371 is for calculating distance in kilometres
56.946285 is latitude for central point
24.105078 is longitude for central point
15 is maximum distance in kilometers

In my tests, MySQL uses SPATIAL index on coordinates field to quickly select all rows which are within rectangle and then calculates actual distance for all filtered places to exclude places from rectangles corners and leave only places inside circle.

This is visualisation of my result:

Gray stars visualise all points on map, yellow stars are ones returned by MySQL query. Gray stars inside corners of rectangle (but outside circle) were selected by MBRContains() and then deselected by HAVING clause.

Byars

Can't upvote this enough. Searching through a table with approx 5 million records and a spatial index with this method the search time is is 0.005 seconds on a old A8 processor. I know that 6371 can be replaced with 3959 to get results in miles but do the values of 111.133 and 111.320 need to be adjusted or are they universally constant? – Magog

Great solution. – Pikeperch

How to create Point is it POINT(lat, lng) or POINT(lng,lat) – Entranceway

@Entranceway It's POINT(lng,lat) – Icing

The X() and Y() function should be ST_Y and ST_X nowadays. – Bathesda

111.133 and 111.320 can be replaced with 69.06 and 69.17 in miles. – Panter

The values 111.133 etc help obtain the number of degrees per km etc. Answer can be found here anycodings.com/1questions/4834519/… – Alkyne

Answer 4

The following MySQL function was posted on this blog post. I haven't tested it much, but from what I gathered from the post, if your latitude and longitude fields are indexed, this may work well for you:

DELIMITER $$

DROP FUNCTION IF EXISTS `get_distance_in_miles_between_geo_locations` $$
CREATE FUNCTION get_distance_in_miles_between_geo_locations(
  geo1_latitude decimal(10,6), geo1_longitude decimal(10,6), 
  geo2_latitude decimal(10,6), geo2_longitude decimal(10,6)) 
returns decimal(10,3) DETERMINISTIC
BEGIN
  return ((ACOS(SIN(geo1_latitude * PI() / 180) * SIN(geo2_latitude * PI() / 180) 
    + COS(geo1_latitude * PI() / 180) * COS(geo2_latitude * PI() / 180) 
    * COS((geo1_longitude - geo2_longitude) * PI() / 180)) * 180 / PI()) 
    * 60 * 1.1515);
END $$

DELIMITER ;

Sample usage:

Assuming a table called places with fields latitude & longitude:

SELECT get_distance_in_miles_between_geo_locations(-34.017330, 22.809500,
latitude, longitude) AS distance_from_input FROM places;

Peake

I've tried this and it works perfectly, but somehow it does'nt allow me to put in a WHERE statement based on distance_from_input. Any idea why not? – Rearward

you could do it as a sub select: select * from (...) as t where distance_from_input > 5; – Peake

or just go straight with: select * from places where get_distance_in_miles_between_geo_locations(-34.017330, 22.809500, latitude, longitude) > 5000; – Peake

return Meters : SELECT ROUND(((ACOS(SIN(lat1 * PI() / 180) * SIN(lat2 * PI() / 180) + COS(lat1 * PI() / 180) * COS(lat2 * PI() / 180) * COS((lnt1 - lnt2) * PI() / 180)) * 180 / PI()) * 60 * 1.1515) * 1.609344 * 1000) AS distance – Acrobatic

Answer 5

if you are using MySQL 5.7.*, then you can use st_distance_sphere(POINT, POINT).

Select st_distance_sphere(POINT(-2.997065, 53.404146 ), POINT(58.615349, 23.56676 ))/1000  as distcance

Croydon

this is a very good and easy to read alternative. keep in mind, parameter order to POINT() is (lng,lat) otherwise you could will end up with "close" but still very different results to the other methods here. see: #35940353 – Zion

Answer 6

set @latitude=53.754842;
set @longitude=-2.708077;
set @radius=20;

set @lng_min = @longitude - @radius/abs(cos(radians(@latitude))*69);
set @lng_max = @longitude + @radius/abs(cos(radians(@latitude))*69);
set @lat_min = @latitude - (@radius/69);
set @lat_max = @latitude + (@radius/69);

SELECT * FROM postcode
WHERE (longitude BETWEEN @lng_min AND @lng_max)
AND (latitude BETWEEN @lat_min and @lat_max);

source

Mod

Please cite your sources. This is from: blog.fedecarg.com/2009/02/08/… – Yeanling

What is 69 in this case ? How to do in case if we have the earth radius ? – Warlock

Kilometer in 1 Latittude is 111 KM. Mile in 1 Latittude is 69 miles. and 69 Miles = 111 KM. That's why we have used the parameters in the conversions. – Warlock

I had been looking for this forever. Didn't know it can be that simple. Thank you so much. – Sendai

Wouldn't this be incorrect as lng_min / lng_max would need to use lat_min and lat_max in the radius math? – Newbold

Answer 7

SELECT * FROM (SELECT *,(((acos(sin((43.6980168*pi()/180)) * 
sin((latitude*pi()/180))+cos((43.6980168*pi()/180)) * 
cos((latitude*pi()/180)) * cos(((7.266903899999988- longitude)* 
pi()/180))))*180/pi())*60*1.1515 ) as distance 
FROM wp_users WHERE 1 GROUP BY ID limit 0,10) as X 
ORDER BY ID DESC

This is the distance calculation query between to points in MySQL, I have used it in a long database, it it working perfect! Note: do the changes (database name, table name, column etc) as per your requirements.

Evans

What does the value 1.1515 represent? I've seen a similar formula before, but it used 1.75 instead of 1.1515. – Whirl

In reply to my own question, I think the answer might lie here https://mcmap.net/q/119185/-geospatial-coordinates-and-distance-in-kilometers – Whirl

Answer 8

   select
   (((acos(sin(('$latitude'*pi()/180)) * sin((`lat`*pi()/180))+cos(('$latitude'*pi()/180)) 
    * cos((`lat`*pi()/180)) * cos((('$longitude'- `lng`)*pi()/180))))*180/pi())*60*1.1515) 
    AS distance
    from table having distance<22;

Ipomoea

Answer 9

A MySQL function which returns the number of metres between the two coordinates:

CREATE FUNCTION DISTANCE_BETWEEN (lat1 DOUBLE, lon1 DOUBLE, lat2 DOUBLE, lon2 DOUBLE)
RETURNS DOUBLE DETERMINISTIC
RETURN ACOS( SIN(lat1*PI()/180)*SIN(lat2*PI()/180) + COS(lat1*PI()/180)*COS(lat2*PI()/180)*COS(lon2*PI()/180-lon1*PI()/180) ) * 6371000

To return the value in a different format, replace the 6371000 in the function with the radius of Earth in your choice of unit. For example, kilometres would be 6371 and miles would be 3959.

To use the function, just call it as you would any other function in MySQL. For example, if you had a table city, you could find the distance between every city to every other city:

SELECT
    `city1`.`name`,
    `city2`.`name`,
    ROUND(DISTANCE_BETWEEN(`city1`.`latitude`, `city1`.`longitude`, `city2`.`latitude`, `city2`.`longitude`)) AS `distance`
FROM
    `city` AS `city1`
JOIN
    `city` AS `city2`

Noahnoak

Answer 10

The full code with details about how to install as MySQL plugin are here: https://github.com/lucasepe/lib_mysqludf_haversine

I posted this last year as comment. Since kindly @TylerCollier suggested me to post as answer, here it is.

Another way is to write a custom UDF function that returns the haversine distance from two points. This function can take in input:

lat1 (real), lng1 (real), lat2 (real), lng2 (real), type (string - optinal - 'km', 'ft', 'mi')

So we can write something like this:

SELECT id, name FROM MY_PLACES WHERE haversine_distance(lat1, lng1, lat2, lng2) < 40;

to fetch all records with a distance less then 40 kilometers. Or:

SELECT id, name FROM MY_PLACES WHERE haversine_distance(lat1, lng1, lat2, lng2, 'ft') < 25;

to fetch all records with a distance less then 25 feet.

The core function is:

double
haversine_distance( UDF_INIT* initid, UDF_ARGS* args, char* is_null, char *error ) {
    double result = *(double*) initid->ptr;
    /*Earth Radius in Kilometers.*/ 
    double R = 6372.797560856;
    double DEG_TO_RAD = M_PI/180.0;
    double RAD_TO_DEG = 180.0/M_PI;
    double lat1 = *(double*) args->args[0];
    double lon1 = *(double*) args->args[1];
    double lat2 = *(double*) args->args[2];
    double lon2 = *(double*) args->args[3];
    double dlon = (lon2 - lon1) * DEG_TO_RAD;
    double dlat = (lat2 - lat1) * DEG_TO_RAD;
    double a = pow(sin(dlat * 0.5),2) + 
        cos(lat1*DEG_TO_RAD) * cos(lat2*DEG_TO_RAD) * pow(sin(dlon * 0.5),2);
    double c = 2.0 * atan2(sqrt(a), sqrt(1-a));
    result = ( R * c );
    /*
     * If we have a 5th distance type argument...
     */
    if (args->arg_count == 5) {
        str_to_lowercase(args->args[4]);
        if (strcmp(args->args[4], "ft") == 0) result *= 3280.8399;
        if (strcmp(args->args[4], "mi") == 0) result *= 0.621371192;
    }

    return result;
}

Financial

Answer 11

A fast, simple and accurate (for smaller distances) approximation can be done with a spherical projection. At least in my routing algorithm I get a 20% boost compared to the correct calculation. In Java code it looks like:

public double approxDistKm(double fromLat, double fromLon, double toLat, double toLon) {
    double dLat = Math.toRadians(toLat - fromLat);
    double dLon = Math.toRadians(toLon - fromLon);
    double tmp = Math.cos(Math.toRadians((fromLat + toLat) / 2)) * dLon;
    double d = dLat * dLat + tmp * tmp;
    return R * Math.sqrt(d);
}

Not sure about MySQL (sorry!).

Be sure you know about the limitation (the third param of assertEquals means the accuracy in kilometers):

    float lat = 24.235f;
    float lon = 47.234f;
    CalcDistance dist = new CalcDistance();
    double res = 15.051;
    assertEquals(res, dist.calcDistKm(lat, lon, lat - 0.1, lon + 0.1), 1e-3);
    assertEquals(res, dist.approxDistKm(lat, lon, lat - 0.1, lon + 0.1), 1e-3);

    res = 150.748;
    assertEquals(res, dist.calcDistKm(lat, lon, lat - 1, lon + 1), 1e-3);
    assertEquals(res, dist.approxDistKm(lat, lon, lat - 1, lon + 1), 1e-2);

    res = 1527.919;
    assertEquals(res, dist.calcDistKm(lat, lon, lat - 10, lon + 10), 1e-3);
    assertEquals(res, dist.approxDistKm(lat, lon, lat - 10, lon + 10), 10);

Perplexed

Answer 12

Here is a very detailed description of Geo Distance Search with MySQL a solution based on implementation of Haversine Formula to mysql. The complete solution description with theory, implementation and further performance optimization. Although the spatial optimization part didn't work correct in my case. http://www.scribd.com/doc/2569355/Geo-Distance-Search-with-MySQL

Postpaid

Answer 13

Have a read of Geo Distance Search with MySQL, a solution based on implementation of Haversine Formula to MySQL. This is a complete solution description with theory, implementation and further performance optimization. Although the spatial optimization part didn't work correctly in my case.

I noticed two mistakes in this:

1. the use of abs in the select statement on p8. I just omitted abs and it worked.

2. the spatial search distance function on p27 does not convert to radians or multiply longitude by cos(latitude), unless his spatial data is loaded with this in consideration (cannot tell from context of article), but his example on p26 indicates that his spatial data POINT is not loaded with radians or degrees.

Freemasonry

Answer 14

$objectQuery = "SELECT table_master.*, ((acos(sin((" . $latitude . "*pi()/180)) * sin((`latitude`*pi()/180))+cos((" . $latitude . "*pi()/180)) * cos((`latitude`*pi()/180)) * cos(((" . $longitude . "- `longtude`)* pi()/180))))*180/pi())*60*1.1515  as distance FROM `table_post_broadcasts` JOIN table_master ON table_post_broadcasts.master_id = table_master.id WHERE table_master.type_of_post ='type' HAVING distance <='" . $Radius . "' ORDER BY distance asc";

Lymphangial

Answer 15

Using mysql

SET @orig_lon = 1.027125;
SET @dest_lon = 1.027125;

SET @orig_lat = 2.398441;
SET @dest_lat = 2.398441;

SET @kmormiles = 6371;-- for distance in miles set to : 3956

SELECT @kmormiles * ACOS(LEAST(COS(RADIANS(@orig_lat)) * 
 COS(RADIANS(@dest_lat)) * COS(RADIANS(@orig_lon - @dest_lon)) + 
 SIN(RADIANS(@orig_lat)) * SIN(RADIANS(@dest_lat)),1.0)) as distance;

See: https://andrew.hedges.name/experiments/haversine/

See: https://mcmap.net/q/119186/-find-distance-between-two-points-using-latitude-and-longitude-in-mysql

See: http://www.plumislandmedia.net/mysql/haversine-mysql-nearest-loc/

NOTE: LEAST is used to avoid null values as a comment suggested on https://mcmap.net/q/119186/-find-distance-between-two-points-using-latitude-and-longitude-in-mysql

Fibrinolysis

Answer 16

I really liked @Māris Kiseļovs solution, but I like many others may have the Lat and lng's POINTS reversed from his example. In generalising it I though I would share it. In my case I need to find all the start_points that are within a certain radius of an end_point.

I hope this helps someone.

SELECT @LAT := ST_X(end_point), @LNG := ST_Y(end_point) FROM routes  WHERE route_ID = 280;
SELECT 
  *,
  (6371e3 * ACOS(COS(RADIANS(@LAT)) * COS(RADIANS(ST_X(start_point))) 
  * COS(RADIANS(ST_Y(start_point)) - RADIANS(@LNG)) + SIN(RADIANS(@LAT))
  * SIN(RADIANS(ST_X(start_point))))) AS distance 
FROM routes
WHERE MBRContains
 (
  LineString
    (
    Point (
            @LNG + 15 / (111.320 * COS(RADIANS(@LAT))),
            @LAT + 15 / 111.133
    ),
    Point (
    @LNG - 15 / (111.320 * COS(RADIANS(@LAT))),
        @LAT - 15 / 111.133
    )
 ),
 POINT(ST_Y(end_point),ST_X(end_point))
)
HAVING distance < 100
ORDER By distance;

Alkyne