There is an equation used in navigation that provides the distance from two points, that is, from latitude and longitude it is possible to calculate the distance from a certain point.
The name of this technique is the Haversine formula.
Example:
1- Creating the table
CREATE TABLE enderecos (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
nome VARCHAR (60) NOT NULL,
endereco VARCHAR (80) NOT NULL,
lat FLOAT (10, 6) NOT NULL,
lng FLOAT (10, 6) NOT NULL
);
2 - Inserting the data
INSERT INTO 'cake'.'enderecos' ('id', 'nome', 'endereco', 'lat', 'lng') VALUES
(NULL, 'Shopping Iguatemi Porto Alegre', 'Av. João Wallig, 1800 - Passo da Areia, Porto Alegre - RS', '-30.027668', '-51.163269'),
(NULL, 'Bourbon Shopping', 'Av. Assis Brasil, 164 - São João, Porto Alegre - RS', '-30.007913', '-51.184273'),
(NULL, 'Praia De Belas Shopping', 'Av. Praia de Belas, 1181 - Praia de Belas, Porto Alegre - RS', '-30.049527', '-51.228753'),
(NULL, 'Barra Shopping Sul', 'Av. Diário de Notícias, 300, Porto Alegre - RS', '-30.084494', '-51.245297'),
(NULL, 'Shopping TOTAL', 'Av. Cristóvão Colombo, 545 - Floresta, Porto Alegre - RS', '-30.025511', '-51.212344')
3 - SQL query
SELECT id,
(6371 * acos(
cos( radians(-30.053831) )
* cos( radians( lat ) )
* cos( radians( lng ) - radians(-51.191810) )
+ sin( radians(-30.053831) )
* sin( radians( lat ) )
)
) AS distancia
FROM enderecos
HAVING distancia < 25
ORDER BY distancia ASC
LIMIT 4;
Since the formula is applied on a sphere, it is necessary to enter the radius. The number 6371 refers to the approximate radius of the planet Earth, in kilometers. If you want to perform the calculation in miles, use 3959. The query is sorted by increasing distance, that is, nearest first.
4 - Result
The returned results are working perfectly. Do not forget that these distances are marked in a straight line, that is, if you are going to establish a car route, it is very likely that the distance increases due to the route that has to be done.
id distance
3 3.5876619973975385
5 3.7180529211314073
1 4.001380483066799
2 5.15708294670291
In this way, we were able to collect the addresses closest to the referenced point. All addresses are close, so a listing without limitation would bring the 5 addresses as they are within the 25km radius.
Search source: link
link
