Latitude and Longitude Calculation - Approximation

Question

You guys, I’m in a bit of a pickle...

Currently I have the data Latitude and Longitude of business registers in my database, with this I need to bring data next to me, in case would have latitude and longitude of my position, for example, bring companies that are 10 meters away from me, doubt is "How to do"?

I found on the internet some Selects, but I was not successful even limiting to "10 meters" it returns data there from the nail box. This for several selects.

I would like to see the community if someone has already messed with it and if there is something working that could share.. D

Thank you for your attention and help in advance!

Answer 1

There is an equation used in navigation that provides distance from two points, that is, from latitude and longitude it is possible to calculate the distance from a given point.

The name of this technique is the formula of Haversine.

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 - Entering 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;

As the formula is applied on a sphere, it is necessary to inform the radius. The number 6371 refers to the approximate radius of planet Earth, in kilometers. If you want to do the calculation in miles, use 3959. The query is ordered by increasing distance, ie closer 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 route by car, it is quite likely that the distance increases due to the path that must 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 nearby, so an unrestricted listing would bring the 5 addresses, as they are within the 25km radius.

Research source: http://www.phpit.com.br/artigos/como-procurar-locais-proximos-usando-sql.phpit https://en.wikipedia.org/wiki/Haversine_formula

Answer 2

I got some latitude and longitude here on Google Maps.

-23.575546 -46.665508

Mount the URL with this data:

http://maps.googleapis.com/maps/api/geocode/json?latlng=-23.57546,-46.66508&sensor=true

And you will have the exact JSON address