Mam szerokość i długość geograficzną i chcę wyciągnąć rekord z bazy danych, która ma najbliższą szerokość i długość geograficzną według odległości, jeśli ta odległość jest większa niż określona, ​​nie pobieraj jej.

Struktura stołu:

id
latitude
longitude
place name
city
country
state
zip
sealevel

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;

gdzie [starlat] i [startlng] to pozycja, od której należy rozpocząć pomiar odległości.

Rozwiązanie Google:

Tworzenie tabeli

Tworząc tabelę MySQL, chcesz zwrócić szczególną uwagę na atrybuty lat i lng. Przy obecnych możliwościach powiększania w Mapach Google powinieneś potrzebować tylko 6 cyfr dokładności po przecinku. Aby zachować minimalną ilość miejsca wymaganego dla tabeli, możesz określić, że atrybuty lat i lng są zmiennoprzecinkowymi o rozmiarze (10,6). Pozwoli to na przechowywanie w polach 6 cyfr po przecinku plus maksymalnie 4 cyfry przed przecinkiem, np. -123,456789 stopni. Twoja tabela powinna mieć również atrybut id, który będzie służył jako klucz podstawowy.

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 ;

Wypełnianie tabeli

Po utworzeniu tabeli czas wypełnić ją danymi. Przykładowe dane podane poniżej dotyczą około 180 pizzerii rozsianych po całych Stanach Zjednoczonych. W phpMyAdmin możesz użyć zakładki IMPORT, aby importować różne formaty plików, w tym CSV (wartości rozdzielane przecinkami). Microsoft Excel i Google Spreadsheets eksportują do formatu CSV, dzięki czemu można łatwo przenosić dane z arkuszy kalkulacyjnych do tabel MySQL poprzez eksportowanie / importowanie plików 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');

Znajdowanie lokalizacji za pomocą MySQL

Aby znaleźć lokalizacje w tabeli znaczników, które znajdują się w określonym promieniu w określonej szerokości / długości geograficznej, możesz użyć instrukcji SELECT opartej na formule Haversine. Wzór Haversine'a jest używany ogólnie do obliczania odległości po ortodromie między dwiema parami współrzędnych na kuli. Szczegółowe wyjaśnienie matematyczne jest podane w Wikipedii, a dobre omówienie wzoru w odniesieniu do programowania znajduje się na stronie Movable Type.

Oto instrukcja SQL, która znajdzie najbliższe 20 lokalizacji w promieniu 25 mil od współrzędnej 37, -122. Oblicza odległość na podstawie szerokości / długości geograficznej tego wiersza i docelowej szerokości / długości geograficznej, a następnie pyta tylko o wiersze, w których wartość odległości jest mniejsza niż 25, porządkuje całe zapytanie według odległości i ogranicza je do 20 wyników. Aby wyszukiwać według kilometrów zamiast mil, zamień 3959 na 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 < 28 
ORDER BY distance LIMIT 0, 20;

Ten ma znaleźć szerokości i długości geograficzne w odległości mniejszej niż 28 mil.

Innym jest znalezienie ich w odległości od 28 do 29 mil:

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 < 29 and distance > 28 
ORDER BY distance LIMIT 0, 20;

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

Oto moje pełne rozwiązanie zaimplementowane w PHP.

To rozwiązanie wykorzystuje formułę Haversine przedstawioną w http://www.scribd.com/doc/2569355/Geo-Distance-Search-with-MySQL .

Należy zauważyć, że formuła Haversine ma słabości wokół biegunów. Ta odpowiedź pokazuje, jak zaimplementować wzór Vincenty Great Circle Distance, aby to obejść, jednak zdecydowałem się po prostu użyć Haversine, ponieważ jest wystarczająco dobry do moich celów.

Zapisuję szerokość geograficzną jako DECIMAL (10,8), a długość geograficzną jako DECIMAL (11,8). Mam nadzieję, że to pomoże!

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']."<BR>";
}
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"
);
?>

Może być możliwe zwiększenie wydajności poprzez użycie procedury składowanej MySQL, zgodnie z sugestią zawartą w artykule „Geo-odległość-wyszukiwanie-z-MySQL” zamieszczonym powyżej.

Mam bazę danych ~ 17 000 miejsc, a czas wykonania zapytania to 0,054 sekundy.

Na wypadek, gdybyś był tak leniwy jak ja, oto rozwiązanie połączone z tą i innymi odpowiedziami na 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;

Łatwy ;)

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

Po prostu zastąp współrzędne wymaganymi. Wartości muszą być przechowywane jako podwójne. To jest działający przykład MySQL 5.x.

Twoje zdrowie

Spróbuj tego, pokaże najbliższe punkty do podanych współrzędnych (w promieniu 50 km). Działa idealnie:

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 <table_name> AS m
JOIN (
      SELECT <userLat> AS latpoint, <userLon> 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

Po prostu zmień <table_name>. <userLat>i<userLon>

Więcej o tym rozwiązaniu możesz przeczytać tutaj: http://www.plumislandmedia.net/mysql/haversine-mysql-nearest-loc/

Oryginalne odpowiedzi na to pytanie są dobre, ale nowsze wersje mysql (MySQL 5.7.6 on) obsługują zapytania geograficzne, więc możesz teraz korzystać z wbudowanych funkcji zamiast wykonywać złożone zapytania.

Możesz teraz zrobić coś takiego:

select *, ST_Distance_Sphere( point ('input_longitude', 'input_latitude'), 
                              point(longitude, latitude)) * .000621371192 
          as `distance_in_miles` 
  from `TableName`
having `distance_in_miles` <= 'input_max_distance'
 order by `distance_in_miles` asc

Wyniki są zwracane w formacie meters. Więc jeśli chcesz, KMpo prostu użyj .001zamiast .000621371192(co jest dla mil).

Dokumenty MySql są tutaj

Szukasz czegoś takiego jak formuła haversine . Zobacz także tutaj .

Są inne, ale jest to najczęściej cytowane.

Jeśli szukasz czegoś jeszcze bardziej niezawodnego, możesz przyjrzeć się możliwościom GIS baz danych. Są w stanie zrobić kilka fajnych rzeczy, na przykład powiedzieć ci, czy punkt (miasto) pojawia się w danym wielokącie (region, kraj, kontynent).

Sprawdź ten kod na podstawie artykułu Geo-Distance-Search-with-MySQL :

Przykład: znajdź 10 hoteli położonych najbliżej mojej obecnej lokalizacji w promieniu 10 mil:

#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.

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();
    }

Wygląda na to, że chcesz przeprowadzić wyszukiwanie najbliższego sąsiada z pewnym ograniczeniem odległości. O ile mi wiadomo, SQL nie obsługuje niczego takiego i musiałbyś użyć alternatywnej struktury danych, takiej jak R-drzewo lub kd-drzewo .

Znajdź najbliższych użytkowników do mojego:

Odległość w metrach

Oparty na formule Vincenty'ego

mam tabelę użytkowników:

+----+-----------------------+---------+--------------+---------------+
| 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

promień ziemi: 6371000 (w metrach)

Wersja MS SQL tutaj:

        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

Wygląda na to, że powinieneś po prostu użyć PostGIS, SpatialLite, SQLServer2008 lub Oracle Spatial. Wszyscy mogą odpowiedzieć na to pytanie za pomocą przestrzennego SQL.

W skrajnych przypadkach to podejście zawodzi, ale dla wydajności pominąłem trygonometrię i po prostu obliczyłem przekątną do kwadratu.

Ten problem nie jest wcale trudny, ale staje się bardziej skomplikowany, jeśli trzeba go zoptymalizować.

Chodzi mi o to, czy masz 100 lokalizacji w swojej bazie danych, czy 100 milionów? To robi dużą różnicę.

Jeśli liczba lokalizacji jest niewielka, usuń je z SQL i umieść w kodzie, wykonując po prostu ->

Select * from Location

Gdy już wprowadzisz je do kodu, oblicz odległość między każdym szerokości / długości a oryginałem za pomocą formuły Haversine i posortuj go.