latitude / longitude encontrar latitude / longitude mais próxima – sql complexo ou cálculo complexo

Eu tenho latitude e longitude e quero extrair o registro do database, que tem a latitude e a longitude mais próximas pela distância, se essa distância ficar maior do que a especificada, e não recuperá-la.

Estrutura da tabela:

id latitude longitude place name city country state zip sealevel 

O que você precisa é converter a distância em graus de longitude e latitude, filtrar com base naqueles para vincular as inputs que estão mais ou menos na checkbox delimitadora e, em seguida, fazer um filtro de distância mais preciso. Aqui está um ótimo trabalho que explica como fazer tudo isso:

http://www.scribd.com/doc/2569355/Geo-Distance-Search-with-MySQL

 SELECT latitude, longitude, SQRT( POW(69.1 * (latitude - [startlat]), 2) + POW(69.1 * ([startlng] - longitude) * COS(latitude / 57.3), 2)) AS distance FROM TableName HAVING distance < 25 ORDER BY distance; 

onde [starlat] e [startlng] é a posição onde começar a medir a distância.

Solução do Google:

Criando a tabela

Quando você cria a tabela MySQL, você quer prestar uma atenção especial aos atributos lat e lng. Com os resources de zoom atuais do Google Maps, você só precisa de 6 dígitos de precisão após o decimal. Para manter o espaço de armazenamento necessário para sua tabela no mínimo, você pode especificar que os atributos lat e lng sejam flutuantes de tamanho (10,6). Isso permitirá que os campos armazenem 6 dígitos após o decimal, mais até 4 dígitos antes do decimal, por exemplo, -123.456789 graus. Sua tabela também deve ter um atributo id para servir como chave primária.

 CREATE TABLE `markers` ( `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY , `name` VARCHAR( 60 ) NOT NULL , `address` VARCHAR( 80 ) NOT NULL , `lat` FLOAT( 10, 6 ) NOT NULL , `lng` FLOAT( 10, 6 ) NOT NULL ) ENGINE = MYISAM ; 

Povoando a mesa

Depois de criar a tabela, é hora de preenchê-la com dados. Os dados de amostra fornecidos abaixo são de cerca de 180 pizzarias espalhadas pelos Estados Unidos. No phpMyAdmin, você pode usar a guia IMPORT para importar vários formatos de arquivo, incluindo CSV (valores separados por vírgula). O Microsoft Excel e o Google Spreadsheets exportam para o formato CSV, para que você possa transferir facilmente dados de planilhas para tabelas do MySQL, exportando / importando arquivos CSV.

 INSERT INTO `markers` (`name`, `address`, `lat`, `lng`) VALUES ('Frankie Johnnie & Luigo Too','939 W El Camino Real, Mountain View, CA','37.386339','-122.085823'); INSERT INTO `markers` (`name`, `address`, `lat`, `lng`) VALUES ('Amici\'s East Coast Pizzeria','790 Castro St, Mountain View, CA','37.38714','-122.083235'); INSERT INTO `markers` (`name`, `address`, `lat`, `lng`) VALUES ('Kapp\'s Pizza Bar & Grill','191 Castro St, Mountain View, CA','37.393885','-122.078916'); INSERT INTO `markers` (`name`, `address`, `lat`, `lng`) VALUES ('Round Table Pizza: Mountain View','570 N Shoreline Blvd, Mountain View, CA','37.402653','-122.079354'); INSERT INTO `markers` (`name`, `address`, `lat`, `lng`) VALUES ('Tony & Alba\'s Pizza & Pasta','619 Escuela Ave, Mountain View, CA','37.394011','-122.095528'); INSERT INTO `markers` (`name`, `address`, `lat`, `lng`) VALUES ('Oregano\'s Wood-Fired Pizza','4546 El Camino Real, Los Altos, CA','37.401724','-122.114646'); 

Encontrando locais com o MySQL

Para encontrar locais na sua tabela de marcadores que estejam dentro de uma certa distância de raio de uma determinada latitude / longitude, você pode usar uma instrução SELECT baseada na fórmula de Haversine. A fórmula de Haversine é usada geralmente para calcular distâncias de grandes círculos entre dois pares de coordenadas em uma esfera. Uma explicação matemática detalhada é dada pela Wikipédia e uma boa discussão da fórmula relacionada à programação está no site do Movable Type.

Aqui está a instrução SQL que encontrará os 20 locais mais próximos que estão dentro de um raio de 25 milhas para a coordenada 37, -122. Ele calcula a distância com base na latitude / longitude dessa linha e na latitude / longitude de destino e, em seguida, solicita apenas linhas em que o valor da distância é menor que 25, ordena toda a consulta por distância e limita a 20 resultados. Para pesquisar por quilômetros em vez de milhas, substitua 3959 por 6371.

 SELECT id, ( 3959 * acos(cos(radians(37)) * cos(radians(lat)) * cos(radians(lng) - radians(-122)) + sin(radians(37)) * sin(radians(lat ))) ) AS distance FROM markers HAVING distance < 25 ORDER BY distance LIMIT 0, 20; 

https://developers.google.com/maps/articles/phpsqlsearch_v3#creating-the-map

Aqui está minha solução completa implementada em PHP.

Essa solução usa a fórmula de Haversine como apresentada em http://www.scribd.com/doc/2569355/Geo-Distance-Search-with-MySQL .

Deve-se notar que a fórmula de Haversine experimenta fraquezas em torno dos pólos. Esta resposta mostra como implementar a fórmula do Círculo de Distância Grande para contornar isso, no entanto, eu escolhi usar apenas Haversine porque é bom o suficiente para os meus propósitos.

Estou armazenando latitude como DECIMAL (10,8) e longitude como DECIMAL (11,8). Espero que isso ajude!

showClosest.php

 < ?PHP /** * Use the Haversine Formula to display the 100 closest matches to $origLat, $origLon * Only search the MySQL table $tableName for matches within a 10 mile ($dist) radius. */ include("./assets/db/db.php"); // Include database connection function $db = new database(); // Initiate a new MySQL connection $tableName = "db.table"; $origLat = 42.1365; $origLon = -71.7559; $dist = 10; // This is the maximum distance (in miles) away from $origLat, $origLon in which to search $query = "SELECT name, latitude, longitude, 3956 * 2 * ASIN(SQRT( POWER(SIN(($origLat - latitude)*pi()/180/2),2) +COS($origLat*pi()/180 )*COS(latitude*pi()/180) *POWER(SIN(($origLon-longitude)*pi()/180/2),2))) as distance FROM $tableName WHERE longitude between ($origLon-$dist/cos(radians($origLat))*69) and ($origLon+$dist/cos(radians($origLat))*69) and latitude between ($origLat-($dist/69)) and ($origLat+($dist/69)) having distance < $dist ORDER BY distance limit 100"; $result = mysql_query($query) or die(mysql_error()); while($row = mysql_fetch_assoc($result)) { echo $row['name']." > ".$row['distance']."
"; } mysql_close($db); ?>

./assets/db/db.php

 < ?PHP /** * Class to initiate a new MySQL connection based on $dbInfo settings found in dbSettings.php * * @example $db = new database(); // Initiate a new database connection * @example mysql_close($db); // close the connection */ class database{ protected $databaseLink; function __construct(){ include "dbSettings.php"; $this->database = $dbInfo['host']; $this->mysql_user = $dbInfo['user']; $this->mysql_pass = $dbInfo['pass']; $this->openConnection(); return $this->get_link(); } function openConnection(){ $this->databaseLink = mysql_connect($this->database, $this->mysql_user, $this->mysql_pass); } function get_link(){ return $this->databaseLink; } } ?> 

./assets/db/dbSettings.php

 < ?php $dbInfo = array( 'host' => "localhost", 'user' => "root", 'pass' => "password" ); ?> 

Pode ser possível aumentar o desempenho usando um procedimento armazenado do MySQL, conforme sugerido pelo artigo “Geo-Distance-Search-with-MySQL” publicado acima.

Eu tenho um database de ~ 17.000 lugares e o tempo de execução da consulta é de 0,054 segundos.

Apenas no caso de você ser preguiçoso como eu, aqui está uma solução amalgamada desta e de outras respostas sobre SO.

 set @orig_lat=37.46; set @orig_long=-122.25; set @bounding_distance=1; SELECT * ,((ACOS(SIN(@orig_lat * PI() / 180) * SIN(`lat` * PI() / 180) + COS(@orig_lat * PI() / 180) * COS(`lat` * PI() / 180) * COS((@orig_long - `long`) * PI() / 180)) * 180 / PI()) * 60 * 1.1515) AS `distance` FROM `cities` WHERE ( `lat` BETWEEN (@orig_lat - @bounding_distance) AND (@orig_lat + @bounding_distance) AND `long` BETWEEN (@orig_long - @bounding_distance) AND (@orig_long + @bounding_distance) ) ORDER BY `distance` ASC limit 25; 

Facil 😉

 SELECT * FROM `WAYPOINTS` W ORDER BY ABS(ABS(W.`LATITUDE`-53.63) + ABS(W.`LONGITUDE`-9.9)) ASC LIMIT 30; 

Apenas substitua as coordenadas pelas suas necessárias. Os valores devem ser armazenados como duplos. Este é um exemplo funcional do MySQL 5.x.

Felicidades

Você está procurando por coisas como a fórmula de haversine . Veja aqui também.

Há outros, mas este é o mais comumente citado.

Se você está procurando por algo ainda mais robusto, você pode querer olhar para os seus resources GIS de bancos de dados. Eles são capazes de algumas coisas legais, como dizer se um ponto (Cidade) aparece dentro de um determinado polígono (Região, País, Continente).

Verifique este código com base no artigo Geo-Distance-Search-with-MySQL :

Exemplo: encontre os 10 hotéis mais próximos da minha localização atual em um raio de 10 milhas:

 #Please notice that (lat,lng) values mustn't be negatives to perform all calculations set @my_lat=34.6087674878572; set @my_lng=58.3783670308302; set @dist=10; #10 miles radius SELECT dest.id, dest.lat, dest.lng, 3956 * 2 * ASIN(SQRT(POWER(SIN((@my_lat -abs(dest.lat)) * pi()/180 / 2),2) + COS(@my_lat * pi()/180 ) * COS(abs(dest.lat) * pi()/180) * POWER(SIN((@my_lng - abs(dest.lng)) * pi()/180 / 2), 2)) ) as distance FROM hotel as dest having distance < @dist ORDER BY distance limit 10; #Also notice that distance are expressed in terms of radius. 

Tente isto, mostre os pontos mais próximos das coordenadas fornecidas (dentro de 50 km). Funciona perfeitamente:

 SELECT m.name, m.lat, m.lon, p.distance_unit * DEGREES(ACOS(COS(RADIANS(p.latpoint)) * COS(RADIANS(m.lat)) * COS(RADIANS(p.longpoint) - RADIANS(m.lon)) + SIN(RADIANS(p.latpoint)) * SIN(RADIANS(m.lat)))) AS distance_in_km FROM  AS m JOIN ( SELECT  AS latpoint,  AS longpoint, 50.0 AS radius, 111.045 AS distance_unit ) AS p ON 1=1 WHERE m.lat BETWEEN p.latpoint - (p.radius / p.distance_unit) AND p.latpoint + (p.radius / p.distance_unit) AND m.lon BETWEEN p.longpoint - (p.radius / (p.distance_unit * COS(RADIANS(p.latpoint)))) AND p.longpoint + (p.radius / (p.distance_unit * COS(RADIANS(p.latpoint)))) ORDER BY distance_in_km 

Apenas mude . e

Você pode ler mais sobre esta solução aqui: http://www.plumislandmedia.net/mysql/haversine-mysql-nearest-loc/

 simpledb.execSQL("CREATE TABLE IF NOT EXISTS " + tablename + "(id INTEGER PRIMARY KEY AUTOINCREMENT,lat double,lng double,address varchar)"); simpledb.execSQL("insert into '" + tablename + "'(lat,lng,address)values('22.2891001','70.780154','craftbox');"); simpledb.execSQL("insert into '" + tablename + "'(lat,lng,address)values('22.2901396','70.7782428','kotecha');");//22.2904718 //70.7783906 simpledb.execSQL("insert into '" + tablename + "'(lat,lng,address)values('22.2863155','70.772108','kkv Hall');"); simpledb.execSQL("insert into '" + tablename + "'(lat,lng,address)values('22.275993','70.778076','nana mava');"); simpledb.execSQL("insert into '" + tablename + "'(lat,lng,address)values('22.2667148','70.7609386','Govani boys hostal');"); double curentlat=22.2667258; //22.2677258 double curentlong=70.76096826;//70.76096826 double curentlat1=curentlat+0.0010000; double curentlat2=curentlat-0.0010000; double curentlong1=curentlong+0.0010000; double curentlong2=curentlong-0.0010000; try{ Cursor c=simpledb.rawQuery("select * from '"+tablename+"' where (lat BETWEEN '"+curentlat2+"' and '"+curentlat1+"') or (lng BETWEEN '"+curentlong2+"' and '"+curentlong1+"')",null); Log.d("SQL ", c.toString()); if(c.getCount()>0) { while (c.moveToNext()) { double d=c.getDouble(1); double d1=c.getDouble(2); } } } catch (Exception e) { e.printStackTrace(); } 

Parece que você quer fazer uma pesquisa de vizinho mais próxima com algum limite à distância. O SQL não suporta nada parecido com isso, e eu preciso usar uma estrutura de dados alternativa, como uma R-tree ou uma kd-tree .

Encontre os Usuários mais próximos ao meu:

Distância em metros

Baseado na fórmula de Vincenty

Eu tenho a tabela do usuário:

 +----+-----------------------+---------+--------------+---------------+ | id | email | name | location_lat | location_long | +----+-----------------------+---------+--------------+---------------+ | 13 | xxxxxx@xxxxxxxxxx.com | Isaac | 17.2675625 | -97.6802361 | | 14 | xxxx@xxxxxxx.com.mx | Monse | 19.392702 | -99.172596 | +----+-----------------------+---------+--------------+---------------+ 

sql:

 -- my location: lat 19.391124 -99.165660 SELECT (ATAN( SQRT( POW(COS(RADIANS(users.location_lat)) * SIN(RADIANS(users.location_long) - RADIANS(-99.165660)), 2) + POW(COS(RADIANS(19.391124)) * SIN(RADIANS(users.location_lat)) - SIN(RADIANS(19.391124)) * cos(RADIANS(users.location_lat)) * cos(RADIANS(users.location_long) - RADIANS(-99.165660)), 2) ) , SIN(RADIANS(19.391124)) * SIN(RADIANS(users.location_lat)) + COS(RADIANS(19.391124)) * COS(RADIANS(users.location_lat)) * COS(RADIANS(users.location_long) - RADIANS(-99.165660)) ) * 6371000) as distance, users.id FROM users ORDER BY distance ASC 

raio da terra: 6371000 (em metros)

Parece que você deve usar apenas PostGIS, SpatialLite, SQLServer2008 ou Oracle Spatial. Todos eles podem responder a essa pergunta para você com SQL espacial.

Em casos extremos, essa abordagem falha, mas, para o desempenho, pulei a trigonometria e calculei simplesmente a diagonal ao quadrado.

MS SQL Edition aqui:

  DECLARE @SLAT AS FLOAT DECLARE @SLON AS FLOAT SET @SLAT = 38.150785 SET @SLON = 27.360249 SELECT TOP 10 [LATITUDE], [LONGITUDE], SQRT( POWER(69.1 * ([LATITUDE] - @SLAT), 2) + POWER(69.1 * (@SLON - [LONGITUDE]) * COS([LATITUDE] / 57.3), 2)) AS distance FROM [TABLE] ORDER BY 3 

Esse problema não é muito difícil, mas fica mais complicado se você precisar otimizá-lo.

O que quero dizer é que você tem 100 locais em seu database ou 100 milhões? Isso faz uma grande diferença.

Se o número de locais for pequeno, tire-os do SQL e faça o código apenas fazendo ->

 Select * from Location 

Depois de colocá-los no código, calcule a distância entre cada lat / lon e seu original com a fórmula de Haversine e classifique-a.