MySQL GPS distance calculation is failing

Question

MySQL 5.6 here. I'm storing GPS coordinates as MySQL Point types in my DB and am trying to build a query that takes a single GPS coordinate (consisting of latitude and longitude components) and searches for any other table records that are within a given distance (in km) from that point.

I'm using this excellent StackOverflow answer as a model to start with.

Here's my table's description:

mysql> describe locations;
+-----------------+---------------------+------+-----+---------+----------------+
| Field           | Type                | Null | Key | Default | Extra          |
+-----------------+---------------------+------+-----+---------+----------------+
| location_id     | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
| location_ref_id | varchar(36)         | NO   | UNI | NULL    |                |
| location_gps    | point               | NO   | UNI | NULL    |                |
+-----------------+---------------------+------+-----+---------+----------------+

And here is the result of SELECT * from locations;:

mysql> select location_id, location_ref_id, AsText(location_gps) as location_gps from locations;
+-------------+--------------------------------------+----------------------------+
| location_id | location_ref_id                      | location_gps               |
+-------------+--------------------------------------+----------------------------+
|           1 | 76b3352e-b5a4-42a8-95cf-21686d331bec | POINT(18.469692 -63.93212) |
|           2 | cf85de5b-f91d-4975-88b7-79af478ab297 | POINT(28.469692 -73.93212) |
|           3 | 337bfea2-6e56-45f3-b6f5-43f6d59fe0b4 | POINT(38.469692 -83.93212) |
+-------------+--------------------------------------+----------------------------+
3 rows in set, 1 warning (0.00 sec)

And here's my query when I hardcode a specific latitude/longitude point (28.469692, -73.93212) as well as a specific search radius (1km):

SELECT
  location_ref_id, location_line_1, AsText(location_gps) as location_gps
FROM
  locations
WHERE
  MBRContains (
    LineString (
      Point (
        28.469692 + 1 / ( 111.1 / COS(RADIANS(-73.93212))),
        -73.93212 + 1 / 111.1
      ),
      Point (
        28.469692 - 1 / ( 111.1 / COS(RADIANS(-73.93212))),
        -73.93212 - 1 / 111.1
      )
    ),
    POINT(28.469692, -73.93212)
  )
;

And the results from that query:

+-------------+--------------------------------------+----------------------------+
| location_id | location_ref_id                      | location_gps               |
+-------------+--------------------------------------+----------------------------+
|           1 | 76b3352e-b5a4-42a8-95cf-21686d331bec | POINT(18.469692 -63.93212) |
|           2 | cf85de5b-f91d-4975-88b7-79af478ab297 | POINT(28.469692 -73.93212) |
|           3 | 337bfea2-6e56-45f3-b6f5-43f6d59fe0b4 | POINT(38.469692 -83.93212) |
+-------------+--------------------------------------+----------------------------+

Clearly these 3 locations are not all within 1km of each other, so something is wrong. Can anyone spot what it is?!

Answer 1

You need to use the column name, not an hard coded value, when you build the 2nd point (or when you build the search box, it shouldn't matter).

As it is, you are asking for all records if the MBRContains() returns true. Since it evaluates if a static point is within +- 1km of itself, it is always true and so you are getting all points.

Try using this query instead:

SELECT
  location_ref_id, location_line_1, AsText(location_gps) as location_gps
FROM
  locations
WHERE
  MBRContains (
    LineString (
      Point (
        28.469692 + 1 / ( 111.1 / COS(RADIANS(-73.93212))),
        -73.93212 + 1 / 111.1
      ),
      Point (
        28.469692 - 1 / ( 111.1 / COS(RADIANS(-73.93212))),
        -73.93212 - 1 / 111.1
      )
    ),
    location_gps
  )
;