MySQL 5.7 find rows that are close to each other by distance

Question

I have a table of cities, example:

+----------------------------+-------------------+--------------+
| city_name                  | latitude          | longitude    |
+----------------------------+-------------------+--------------+
| Barrow                     |          71.29058 |   -156.78872 |
| Wainwright                 |            70.638 |    -159.9713 |
| Atqasuk                    |            70.481 |    -157.3835 |
| Clyde River                |          70.46916 |    -68.59143 |
| Prudhoe Bay                |          70.25528 |   -148.33722 |
| Nuiqsut                    |           70.2175 |   -150.97639 |

How can find all rows that are within a mile of another row?

What have you tried, and where did you get stuck? What are the results that you expect for this sample data? — GMB, Jan 14 '20 at 22:08
I have drawn a blank to be honest. I tried this query: https://stackoverflow.com/questions/43667187/mysql-select-records-with-near-distance-from-each-other but it didn't work out as I hoped. — Jafo, Jan 14 '20 at 22:09
Please see [Why should I provide an MCRE for what seems to me to be a very simple SQL query](http://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query) — Strawberry, Jan 14 '20 at 23:52
This doesn't seem to me to be a very simple query, so that doesn't apply. — Jafo, Jan 15 '20 at 00:02

score 3 · Answer 1 · answered Jan 14 '20 at 22:15

3

You could self join the table, use spatial function st_distance() to compute the distance between the two houses, and filter:

select t1.*, t2.*
from mytable t1
inner join mytable t2
    on st_distance(
        point(t1.latitude, t1.longitude), 
        point(t2.latitude, t2.longitude)
    ) < 1609.34

1609.34 is a rough estimation of how many meters a mile contains.

answered Jan 14 '20 at 22:15

GMB

216,147
25
84
135

1

Nobody expecting it will be fast with many records in the table =) – fifonik Jan 14 '20 at 22:25
I should mention that I am using MySQL 5.7. This doesn't appear to work for that version. (edited title of question) – Jafo Jan 14 '20 at 23:02
@Jafo: `st-distance()` was added in MySQL 5.6 (so your version has it). What do you mean by * doesn't appear to work*? – GMB Jan 15 '20 at 00:10
@Jafo: can you please provide a [db fiddle](https://dbfiddle.uk/) where the problem can be seen? – GMB Jan 15 '20 at 00:50
Sure, here you go: https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=05ec782b839be0f016b9eef09b61a7ec – Jafo Jan 15 '20 at 00:58

score 0 · Answer 2 · answered Jan 19 '20 at 08:40

When you construct a POINT using POINT(x, y) - you get cartesian point without SRS, meaning the MySQL computes everything on a plane, using whatever units you passed as x and y.

So for example

select st_distance(
        point(-90, 33),
        point(-85, 33));

returns exactly 5. You can think of it as distance in degrees (which is useless - because 5 degrees along meridian is different from 5 degrees along parallel). Or you can think of it as the input was in meters on some plane, and the output is also in meters.

What you need (in MySQL 5.7 - in 8.0 you can use real SRS) is st_distance_sphere function, e.g. for the example above this returns 466234 meters.

select st_distance_sphere(
        point(-90, 33),
        point(-85, 33));

MySQL 5.7 find rows that are close to each other by distance

2 Answers2