Query MYSQL bring results by order of distance using coordinates [duplicate]

Question

Query MYSQL bring results by order of distance using coordinates [duplicate]

Navigation

#1 by (1 votes)

0

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

How do I make an appointment to bring people closer 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 would like the query to bring something like

ID | Distancia
1  | 2km
2  | 20km

the 2km and 20km would be calculated within the sql query

mysql

asked by anonymous 31.08.2017 / 13:16

1 answer

How to pass the Model to the controller via JQuery problem with a function run when clicked enter

score 1 · Answer 1

You can calculate the distance using an arithmetic expression, for 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 above example: link