Znajdź odległość między dwoma punktami za pomocą szerokości i długości geograficznej w mysql
Witam mam następującą tabelę
--------------------------------------------
| id | city | Latitude | Longitude |
--------------------------------------------
| 1 | 3 | 34.44444 | 84.3434 |
--------------------------------------------
| 2 | 4 | 42.4666667 | 1.4666667 |
--------------------------------------------
| 3 | 5 | 32.534167 | 66.078056 |
--------------------------------------------
| 4 | 6 | 36.948889 | 66.328611 |
--------------------------------------------
| 5 | 7 | 35.088056 | 69.046389 |
--------------------------------------------
| 6 | 8 | 36.083056 | 69.0525 |
--------------------------------------------
| 7 | 9 | 31.015833 | 61.860278 |
--------------------------------------------
Teraz chcę uzyskać dystans między dwoma punktami. Powiedzmy, że użytkownik ma Miasto 3, a użytkownik ma Miasto 7. Mój scenariusz to jeden użytkownik mający miasto i szerokość geograficzną i długi czas szuka innych użytkowników w odległości od swojego miasta. Na przykład użytkownik mający miasto 3 szuka. Chce uzyskać odległość użytkownika innego miasta powiedzieć, że jest 7. I have searched and found following query
SELECT `locations`.`city`, ( 3959 * acos ( cos ( radians(31.589167) ) * cos( radians( Latitude ) ) * cos( radians( Longitude ) - radians(64.363333) ) + sin ( radians(31.589167) ) * sin( radians( Latitude ) ) ) ) AS `distance` FROM `locations` HAVING (distance < 50)
Z tego co wiem to zapytanie znajduje odległość od jednego punktu do wszystkich innych punktów. Teraz chcę uzyskać dystans z jednego punktu do drugiego punktu.
Każda linia przewodnia będzie bardzo mile widziana.
7 answers
Myślę, że twoje pytanie mówi, że masz city
wartości dla dwóch miast, między którymi chcesz obliczyć odległość.
To zapytanie wykona zadanie za Ciebie, podając odległość W km. Stosuje się w nim sferyczne prawo cosinusa.
Zauważ, że dołączasz tabelę do siebie, dzięki czemu możesz pobrać dwie pary współrzędnych do obliczeń.
SELECT a.city AS from_city, b.city AS to_city,
111.111 *
DEGREES(ACOS(LEAST(1.0, COS(RADIANS(a.Latitude))
* COS(RADIANS(b.Latitude))
* COS(RADIANS(a.Longitude - b.Longitude))
+ SIN(RADIANS(a.Latitude))
* SIN(RADIANS(b.Latitude))))) AS distance_in_km
FROM city AS a
JOIN city AS b ON a.id <> b.id
WHERE a.city = 3 AND b.city = 7
Zauważ, że stała 111.1111
jest liczbą kilometrów na stopień szerokości geograficznej, w oparciu o stare napoleońskie definicja metra jako jednej dziesiątej tysięcznej odległości od równika do bieguna. Ta definicja jest wystarczająco zbliżona do wyszukiwania lokalizacji.
Jeśli chcesz zamiast kilometrów użyć 69.0
.
Http://sqlfiddle.com/#! 9 / 21e06/412/0
Jeśli szukasz pobliskich punktów, możesz pokusić się o użycie klauzuli czegoś takiego:
HAVING distance_in_km < 10.0 /* slow ! */
ORDER BY distance_in_km DESC
To jest (jak mówimy w pobliżu Boston MA USA) wicked slow.
W takim przypadku musisz użyć obliczeń obwiedni. Zobacz ten writeup o tym, jak to zrobić. http://www.plumislandmedia.net/mysql/haversine-mysql-nearest-loc/
Formuła zawiera funkcję LEAST()
. Dlaczego? Ponieważ funkcja ACOS()
wyświetla błąd, jeśli jej argument jest nawet nieco większy niż 1. Gdy dwa punkty są bardzo blisko siebie, wyrażenie z obliczeniami COS()
i SIN()
może czasami dać wartość nieco większą niż 1 ze względu na zmiennoprzecinkowy epsilon(niedokładność). LEAST(1.0, dirty-great-expression)
telefon radzi sobie z tym problemem.
Jest lepszy sposób, wzór Przez Tadeusza Wincentego. Używa ATAN2()
zamiast ACOS()
, więc jest mniej podatny na problemy z epsilonem.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2020-03-24 19:42:33
Heres to zapytanie i funkcja MySQL, które służą do uzyskania odległości między dwiema szerokościami geograficznymi i długościami geograficznymi, a odległość powróci w KM.
Zapytanie Mysql:-
SELECT (6371 * acos(
cos( radians(lat2) )
* cos( radians( lat1 ) )
* cos( radians( lng1 ) - radians(lng2) )
+ sin( radians(lat2) )
* sin( radians( lat1 ) )
) ) as distance from your_table
Funkcja Mysql: -
DELIMITER $$
CREATE FUNCTION `getDistance`(`lat1` VARCHAR(200), `lng1` VARCHAR(200), `lat2` VARCHAR(200), `lng2` VARCHAR(200)) RETURNS varchar(10) CHARSET utf8
begin
declare distance varchar(10);
set distance = (select (6371 * acos(
cos( radians(lat2) )
* cos( radians( lat1 ) )
* cos( radians( lng1 ) - radians(lng2) )
+ sin( radians(lat2) )
* sin( radians( lat1 ) )
) ) as distance);
if(distance is null)
then
return '';
else
return distance;
end if;
end$$
DELIMITER ;
Jak używać w swoim kodzie PHP
SELECT getDistance($lat1,$lng1,$lat2,$lng2) as distance
FROM your_table.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2020-12-06 17:52:32
Możesz użyć wbudowanej funkcji St_distance_sphere MySql. Efektywniej oblicza odległość w metrach.
Obsługiwane od wersji MySql 5.7 i nowszej.
select ST_Distance_Sphere(
point(-87.6770458, 41.9631174),
point(-73.9898293, 40.7628267))
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2020-03-27 23:51:33
Oto funkcja MySQL, która zajmie dwie pary szerokości / długości geograficznej i poda odległość w stopniach między dwoma punktami. Używa wzoru Haversine ' a do obliczenia odległości. Ponieważ ziemia nie jest idealną kulą, istnieje pewien błąd w pobliżu biegunów i równika.
- aby przeliczyć na mile, pomnóż przez 3961.
- aby przeliczyć na kilometry, należy pomnożyć przez 6373.
- aby przeliczyć na liczniki, należy pomnożyć przez 6373000.
- aby przekształcić w stopy, należy pomnożyć przez (3961 * 5280) 20914080.
DELIMITER $$
CREATE FUNCTION \`haversine\`(
lat1 FLOAT, lon1 FLOAT,
lat2 FLOAT, lon2 FLOAT
) RETURNS float
NO SQL
DETERMINISTIC
COMMENT 'Returns the distance in degrees on the Earth between two known points of latitude and longitude. To get miles, multiply by 3961, and km by 6373'
BEGIN
RETURN DEGREES(ACOS(
COS(RADIANS(lat1)) *
COS(RADIANS(lat2)) *
COS(RADIANS(lon2) - RADIANS(lon1)) +
SIN(RADIANS(lat1)) * SIN(RADIANS(lat2))
));
END;
DELIMITER;
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2018-10-25 11:59:03
Nie wiem, jak przebiega Obliczanie odległości, ale musisz sporządzić self join
swoją tabelę i odpowiednio wykonać obliczenia. Coś takiego prawdopodobnie
select t1.id as userfrom,
t2.id as userto,
( 3959 * acos ( cos ( radians(31.589167) ) * cos( radians( t1.Latitude ) ) *
cos( radians( t1.Longitude ) - radians(64.363333) ) + sin ( radians(31.589167) ) *
sin( radians( t2.Latitude ) ) ) ) AS `distance`
from table1 t1
inner join table1 t2 on t2.city > t1.city
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2014-06-23 16:55:57
Oto wzór, który przekonwertowałem z https://www.geodatasource.com/developers/javascript
To ładna, czysta funkcja, która oblicza odległość w KM
DELIMITER $$
CREATE DEFINER=`root`@`localhost` FUNCTION `FN_GET_DISTANCE`(
lat1 DOUBLE, lng1 DOUBLE, lat2 DOUBLE, lng2 DOUBLE
) RETURNS double
BEGIN
DECLARE radlat1 DOUBLE;
DECLARE radlat2 DOUBLE;
DECLARE theta DOUBLE;
DECLARE radtheta DOUBLE;
DECLARE dist DOUBLE;
SET radlat1 = PI() * lat1 / 180;
SET radlat2 = PI() * lat2 / 180;
SET theta = lng1 - lng2;
SET radtheta = PI() * theta / 180;
SET dist = sin(radlat1) * sin(radlat2) + cos(radlat1) * cos(radlat2) * cos(radtheta);
SET dist = acos(dist);
SET dist = dist * 180 / PI();
SET dist = dist * 60 * 1.1515;
SET dist = dist * 1.609344;
RETURN dist;
END$$
DELIMITER ;
Znajdziesz również tę samą funkcję w różnych językach na stronie;
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2018-05-13 04:26:34
Ważne! Każdy, kto używa lub kopiuje te obliczenia, upewnij się, że używasz least(1.0, (...))
podczas przekazywania obliczeń do funkcji acos()
. Funkcja acos()
nie przyjmie wartości powyżej 1 i odkryłem porównując wartości lat/lng, które są identyczne są czasy, gdy obliczenia wychodzą na coś w rodzaju 1.000002
. Spowoduje to odległość NULL
zamiast 0
i może nie zwrócić wyników, których szukasz, w zależności od struktury zapytania!
To jest Poprawny:
select round(
( 3959 * acos( least(1.0,
cos( radians(28.4597) )
* cos( radians(lat) )
* cos( radians(lng) - radians(77.0282) )
+ sin( radians(28.4597) )
* sin( radians(lat)
) ) )
), 1) as distance
from locations having distance <= 60 order by distance
To jest złe:
select round(
( 3959 * acos(
cos( radians(28.4597) )
* cos( radians(lat) )
* cos( radians(lng) - radians(77.0282) )
+ sin( radians(28.4597) )
* sin( radians(lat)
) )
), 1) as distance
from locations having distance <= 60 order by distance
Najwyżej oceniona odpowiedź również mówi o tym, ale chciałem się upewnić, że jest to bardzo jasne, ponieważ właśnie znalazłem długotrwały błąd w moim zapytaniu.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2020-02-07 22:19:48