Clicky

Skocz do zawartości


Zdjęcie
- - - - -

MySQL - Wyszukiwanie w odległości/promieniu

10 odpowiedzi w tym temacie
mysql

  • Zaloguj się, aby dodać odpowiedź

#1 kevin

kevin

    Młodszy Mastah

  • Użytkownik
  • PipPip
  • 207 postów

Napisano 04 czerwiec 2013 - 18:45

Cześć.

W swojej tabeli ogłoszeń trzymam współrzędne (latitude, longitude) danej lokalizacji i chciałbym na podstawie tych danych zbudować w swojej wyszukiwarce opcje "Szukaj w promieniu/odległości" - Opcja bardzo popularna wśród serwisów ogłoszeniowych. Problem polega na tym, że zwracane są zupełnie dziwne wyniki (odległości się nie zgadzają).

Załóżmy, że mam ogłoszenie, którego adres kieruje na dane: Polska, Warszawa, ul. Plac Jana Henryka Dąbrowskiego - wg. Google maps współrzędne to: 52.23755320, 21.0095850. Druga lokalizacja to: Polska, Lublin, ul. Dolna Panny Marii - wg. Google maps współrzędne to: 51.24348220, 22.56104220

(wg. Google maps dystans pomiędzy tymi dwoma miastami to: ~ 170 km). Przedstawiam więc schemat tabeli (testowy, bez indeksów, itdp. - dla testu po prostu):


create table if not exists `ogloszenia`

(

`id` int(10) unsigned not null auto_increment primary key,

`title` varchar(64) not null,

`description` text not null,



`form_location_latitude` varchar(11) not null,

`form_location_longitude` varchar(11) not null

) engine = innodb default charset = utf8;



insert into `ogloszenia` values (1, 'Sprzedam rower z 2011 roku', 'Krotki opis.. - Z warszawy', '52.23755320', '21.0095850'),

(2, 'Sprzedam uzywane meble z Lublina', 'Lublin ogloszenie..', '51.24348220', '22.56104220');

I teraz szukam wg. mojej lokalizacji (załóżmy: Mińsk Mazowiecki, współrzędne: 52.19113140, 21.55392710.
Wg. google masz do Warszawy mam ~ 42 km natomiast do Lublina już 144 km. Więc ustawiam odpowiednio zakres (podany w km) i nie dostaje żadnego rekordu.
Wykonuje następujące zapytanie SQL:


set @latitude = 52.19113140;

set @longitude = 21.55392710;

set @distance = 150;



select p.title, ((ACOS(SIN(@latitude * PI() / 180) * SIN(`form_location_latitude` * PI() / 180) + COS(@latitude * PI() / 180) * COS(`form_location_latitude` * PI() / 180) * COS((@longitude - `form_location_longitude`) * PI() / 180)) * 180 / PI()) * 60 * 1.1515) AS distance

from ogloszenia p

having distance < @distance;

Wyszukuje w odległości 150 km od mojego miejsca (tj: Mińsk Mazowiecki) i zwraca mi następujące wyniki:
36159428024454965240.jpg

("Sprzedam rower" --> 23.26521057393872)
("Sprzedam meble" ---> 78.38883582177581)


Dlaczego zwraca mi takie dziwne dystanse? Nie powinno chociaż pobierać zbliżonych odległości od mojej lokalizacji?

#2 Zepco

Zepco

    Senior Mastah

  • Moderator
  • 1583 postów
  • Skąd:Kielce

Napisano 04 czerwiec 2013 - 18:48

Widziałeś ten temat http://forum.kohanap...8.msg17356.html ?

OŚWIADCZENIE: Ja, niżej podpisany, świadomy wszystkich konsekwencji tego posta postanawiam go dopuścić do użytku publicznego, albowiem bo gdyż aczkolwiek uważam, że nie wyrządzi on (znaczy: post) krzywdy nikomu innemu niźli mnie samemu (czyli autorowi posta).
-- Zepco --


#3 kevin

kevin

    Młodszy Mastah

  • Użytkownik
  • PipPip
  • 207 postów

Napisano 04 czerwiec 2013 - 19:13

Rozwiązanie lorak110786 pomogło - gubi kilometry (w przypadku MM -> Lublin: ~ 4km zgubiło) natomiast MM -> Warszawa +/- 20 km wcięło. Tylko, który fragment z tego SELECT/WHERE mogę odpowiednio wyregulować, aby te zgubione kilometry uregulować?

PS. Dzięki za link.

#4 Zepco

Zepco

    Senior Mastah

  • Moderator
  • 1583 postów
  • Skąd:Kielce

Napisano 04 czerwiec 2013 - 20:02

Gdzie google pokazuje odległość w linii prostej, bo nie mogę doszukać się takiej opcji? Ja takie rzeczy sprawdzam natomiast na http://mapa.szukacz.pl/

OŚWIADCZENIE: Ja, niżej podpisany, świadomy wszystkich konsekwencji tego posta postanawiam go dopuścić do użytku publicznego, albowiem bo gdyż aczkolwiek uważam, że nie wyrządzi on (znaczy: post) krzywdy nikomu innemu niźli mnie samemu (czyli autorowi posta).
-- Zepco --


#5 mck

mck

    Jestę Blogerę

  • Admin
  • 1544 postów

Napisano 04 czerwiec 2013 - 21:07

^^ W google earth jest linijka, którą można sprawdzić odległość w lini prostej.

@kevin: Twoje zapytanie jest dobre, ale pomnóż sobie wynik *1.609344, bo aktualnie masz w milach :)

#6 phpion

phpion

    Senior Mastah

  • Użytkownik
  • PipPipPip
  • 774 postów
  • Skąd:Sosnowiec, Dąbrowa Górnicza

Napisano 05 czerwiec 2013 - 07:14

Swoją drogą: czy aby na pewno dla długości i szerokości geograficznej odpowiednim typem danych jest VARCHAR? Wątpię :)
Notifero - Technologie Informatyczne | Warsztat: Kohana 3.x/2.x + PostgreSQL/MySQL | Programista Kohana

#7 kevin

kevin

    Młodszy Mastah

  • Użytkownik
  • PipPip
  • 207 postów

Napisano 05 czerwiec 2013 - 07:40

Dzięki za informacje mck, spróbuje tego i zobaczymy jak wyniki będą prezentować się.
Odnośnie typu danych: na początku miałem float(10, 6), ale ucinał mi współrzędne to na szybko zmieniłem na varchar.
Zastanawiam się właśnie czy jaki typ dla kolumny nadać - float(10, 6) (z innym zakresem) czy może double? Wiem, że w MySQL jest jakiś typ specjalny pod takie zadania, ale jakbym miał się uprzeć to co lepszym wyjściem było? float vs double?

#8 mck

mck

    Jestę Blogerę

  • Admin
  • 1544 postów

Napisano 05 czerwiec 2013 - 08:03

Decimal.
Dokładność 10,6 jest bez sensu - maksymalna długość geograficzna to 180 stopni, więc wystarczą 3 znaki przed przecinkiem. Dla tych danych, które podałeś wystarczy w zupełności 11,8.

#9 kevin

kevin

    Młodszy Mastah

  • Użytkownik
  • PipPip
  • 207 postów

Napisano 05 czerwiec 2013 - 18:43

Ponownie dzięki za informacje/wskazówki. Zmieniłem typ kolumny, więc wróciłem do problemu z gubiącymi się km. Zamieniłem mile na km, ale w tej chwili wynik wychodzi za duży. Oryginalnie zapytanie zwraca: 37.4 - zakładając, że to faktycznie wynik jest w milach to 37.4 mil to ~ 60.2 km. Mając zapytanie, w którym mnożę o 1.609344 (1mila = 1km) otrzymuje właśnie wynik: 60.2

Zapytanie prezentuje się następująco:
select title, ROUND(6368 * ACOS(SIN(RADIANS(form_location_latitude)) * SIN(RADIANS(52.19113140)) + COS(RADIANS(form_location_latitude)) * COS(RADIANS(52.19113140)) * COS(RADIANS(21.55392710) - RADIANS(form_location_longitude))) * 1.609344, 1) AS distance
from ogloszenia
where 6368 * ACOS(SIN(RADIANS(form_location_latitude)) * SIN(RADIANS(52.19113140)) + COS(RADIANS(form_location_latitude)) * COS(RADIANS(52.19113140)) * COS(RADIANS(21.55392710) - RADIANS(form_location_longitude))) * 1.609344 <= 80;

Gdzie powinienem szukać przyczyny tej rozbieżności? W zapytaniu czy w "trefnych" współrzędnych.

#10 mck

mck

    Jestę Blogerę

  • Admin
  • 1544 postów

Napisano 05 czerwiec 2013 - 19:33

Musisz się zdecydować, którego zapytania chcesz używać. To z pierwszego postu zwraca wynik w milach, a to z ostatniego w kilometrach. Oba wyniki są niemal identyczne (pierwsze zapytanie jest dokładniejsze) i pokrywają się ze wskazaniami google earth.

#11 PrzemB

PrzemB

    Początkujący

  • Użytkownik
  • Pip
  • 5 postów

Napisano 18 listopad 2015 - 08:55

SELECT *, (6371*acos(cos(radians(:lat))*cos(radians(`lat`))*cos(radians(`lng`)-radians(:lng))+sin(radians(:lat))*sin(radians(`lat`)))) AS distance FROM `ogloszenia` HAVING distance < 150 ORDER BY distance LIMIT 0 , 20;

Wybiera 20 najbliższych rekordów posortowanych względem podanych :lat, :lng w [km], zaś wg. tego zapytania w tabeli znajdują się kolumny lat, lng typu float(10, 6).

 

Tu masz funkcję w PHP dzięki, której możesz sprawdzić odległość między dwoma punktami.

function distance($lat1, $lon1, $lat2, $lon2, $unit) {
	$theta = $lon1 - $lon2;
	$dist = sin(deg2rad($lat1)) * sin(deg2rad($lat2)) +  cos(deg2rad($lat1)) * cos(deg2rad($lat2)) * cos(deg2rad($theta));
	$dist = acos($dist);
	$dist = rad2deg($dist);
	$miles = $dist * 60 * 1.1515;
	$unit = strtoupper($unit);

	if ($unit == "K") {
		return ($miles * 1.609344);
	} else if ($unit == "N") {
		return ($miles * 0.8684);
	} else {
		return $miles;
	}
}

Powyższą funkcję (przetestowana) znalazłem na http://www.geodataso.../developers/php







Również z jednym lub większą ilością słów kluczowych: mysql

Użytkownicy przeglądający ten temat: 0

0 użytkowników, 0 gości, 0 anonimowych