How can I find the distance between rows using the longitude and latitude columns using MySQL?

Question

I have a database with lng and lat columns for each row. How can I make a SQL query that will return the rows in order of the distance? I will compare the lat and lng with external values. I will send the SQL query with PyMySQL using Python. If this isn't possible, I have the haversine formula written in Python, and I can calculate the distances like that, but I don't want to have to waste time looping through all the entries in the database, which is very big.

Sample Table:

   long ============ lat
============================
1. -74 ============= 84
2. -87 ============= 75

What I expect to happen. I give a long, lat coordinate and it compares it to all the rows in the database, and it returns the rows in order of the closest to the farthest to the coordinate I provide.

in order of distance from what? please show some sample code so people can help accordingly — gold_cy, Mar 13 '19 at 19:20
It would be great if you can provide us with some sample data and expected results. — Paolo, Mar 13 '19 at 19:20
Might be interesting to read: https://mysqlserverteam.com/geography-in-mysql-8-0/ — Klaus D., Mar 13 '19 at 20:07

score 0 · Answer 1 · answered Mar 13 '19 at 19:35

0

Your query needs to include a calculated field column for the distance. Your ORDER BY would use the calculated field.

answered Mar 13 '19 at 19:35

Hopper

146
6

Not necessarily. The distance can be computed on the fly. – The Impaler Mar 13 '19 at 19:49
@TheImpaler I was planning that. I will append the top 5 rows in order to a list of distances. – CompuGenuis Programs Mar 13 '19 at 20:08
Your comment means that you could do a top 5 based on the calculation. Your initial post made me think that you would want all of the calculated distances. – Hopper Mar 14 '19 at 13:53
@Hopper Both are true. I will want all the distances, yet in Python, I will append the top five to a list. The other distances I will use as well. – CompuGenuis Programs Mar 15 '19 at 00:40

score 0 · Answer 2 · answered Mar 13 '19 at 20:20

0

First, I would implement the Haversine function as a MySQL function, as in:

create function haversine_distance (
  float long1, 
  float lat1,
  float long2,
  float lat2)
returns float deterministic
return ... -- formula here

Once you have that, then the query becomes really simple. For example for longitude 10.21 / latitude -50.45, the query would look like:

select *
from my_table
order by haversine_distance(long, lat, 10.21, -50.45) asc

answered Mar 13 '19 at 20:20

The Impaler

45,731
9
39
76

I am not good at MySQL, and that it is not my intention to learn this moment. I will run the SQL query with either PyMySQL or phpmyadmin. Is there a way you might be able to explain some of the unusual code? – CompuGenuis Programs Mar 13 '19 at 20:48
1

Well, the first part is the definition of a formula (the Haversine formula you mentioned) in the MySQL engine (manual page https://dev.mysql.com/doc/refman/8.0/en/create-procedure.html). The second part is as simple SQL that retrieves the rows sorted by the distance to a specific point you use as parameters (long + lat). – The Impaler Mar 13 '19 at 20:55
If you don't have time to learn the basics of functions in MySQL, then you are better off doing it Python, or any other client language. The MySQL solution has a much higher performance, though. It could be useful in the long run. – The Impaler Mar 13 '19 at 20:56
This code doesn't run in PhpMyAmin – CompuGenuis Programs Mar 13 '19 at 20:58

How can I find the distance between rows using the longitude and latitude columns using MySQL?

2 Answers2