How to get distance (latitude and longitude) from mysql query [PHP,MySQL]

Question

I have this table in mysql database. table

This is my PHP file:

$user_latitude = 45.11123;
$user_longitude = 20.33445;

$query = "";

I want to create a query that returns the ids (of the rows in the table) in order of distance from user position($user_latitude,$user_longitude). I want to do this by using a MySQL query.

where is the rest of the query? or did you want some pointers or write it for you? — Funk Forty Niner, Aug 02 '18 at 15:04
I don't write the rest because it's not important I'm interesting only in the query. — Jhon Doe, Aug 02 '18 at 15:06
Well then, I guess it's not important that I keep asking more questions. Some of which stand to be asked by others. Good luck with this. — Funk Forty Niner, Aug 02 '18 at 15:07
[Haversine](https://en.wikipedia.org/wiki/Haversine_formula) is what you are looking for: https://stackoverflow.com/a/574736/7799650 — Freddythunder, Aug 02 '18 at 15:07

score 1 · Answer 1 · answered Aug 02 '18 at 15:22

1

You can use the formula below to calculate the distance. For more info look here and here

$query = mysql_query("SELECT id, (6371 * acos (cos (radians($user_latitude))* cos(radians(latitude))* cos( radians($user_longitude) - radians(longitude) )+ sin (radians($user_latitude) )* sin(radians(latitude)))) AS distance FROM table_name WHERE 1 ORDER BY distance DESC");

The provided distance will be in Kilometers. If you need Miles, use 3959 instead of 6371.

answered Aug 02 '18 at 15:22

Sookie Singh

1,543
11
17

Thank you and how can I set a limit, like 10 km? – Jhon Doe Aug 02 '18 at 23:23
You can use `WHERE distance < 10` – Sookie Singh Aug 03 '18 at 06:54

How to get distance (latitude and longitude) from mysql query [PHP,MySQL]

1 Answers1