MYSQL query bring results in order of distance using coordinates

Asked

Viewed 695 times

1

I have a table that has the coordinates (latitude and longitude) of the users.

How do I make a query to bring who is closest to the location.

Ex:

ID |     Lat    | Long
1  | -22.874428 | -43.372334
2  | -20.292799 | -40.301991

Bring these two where id 2 would be closest (search coordinates: -20.292799 | -40.301991)

I’d like the consult to bring something like

ID | Distancia
1  | 2km
2  | 20km
  • 2km and 20km would be calculated within the sql query
mysql

  • Do you have the reference coordinate data? And you know how to calculate the distance using coordinates?

  • I have the data yes, for example: -20.292799 | -40.301991. I can’t calculate the distance.. :/

  • This one only brings the specific distance, I need to bring the query a column with distance, so you can make the ordering, I will edit the question to get better, thank you.

  • What is the value of the origin? Well, how will you calculate the distance if you do not know where it starts? Would this distance be in a straight line right? And not using a street system to check the distance, right?

1 answer

1

You can calculate the distance using an arithmetic expression, example:

SELECT
    l.id,
    l.lat,
    l.lng,
    ( 3959 * acos( cos( radians(c.lat) ) 
              * cos( radians( l.lat ) ) 
              * cos( radians( l.lng ) - radians(c.lng) ) 
              + sin( radians(c.lat) ) 
              * sin( radians( l.lat ) ) ) ) AS distancia 
FROM
    locais AS l
    JOIN (
      SELECT
        -20.282957 AS lat,
        -40.401991 AS lng
    ) AS c
ORDER BY
    distancia

Fiddle running the example above: https://www.db-fiddle.com/f/pFEVDYaXQyj81g2kh6VLBw/2

Browser other questions tagged mysql

You are not signed in. Login or sign up in order to post.