Witam.

Mam problem z zaprojektowaniem tabeli sql z miejscowościami i ich współrzędnymi. Mogę je zapisywać do bazy danych w dowolnym formacie, ale nie mam pojęcia jak ułożyć te tabele, żeby nie zabić mysql'a.

Problem wygląda tak: posiadam współrzędne punktu (używam Google Maps Api, ale to bez znaczenia) w postaci $x i $y. Teraz chciałbym stworzyć systemik, który podawałby najbliższą miejscowość od tych współrzędnych z tabeli mysql. Powiedzmy szkic tabel wyglądałby tak:


id_dzielnicy | id_miasta | nazwa_dzielnicy | współrzędne x | współrzędne y

id_miasta | nazwa_miasta | dodatkowe opcje np. co do markera (ta tabela mniej istotna

W php mam wartości współrzędnych punktu. ($x i $y)


I teraz problem bo nie wiem, czy tak jest dobrze i nie mam pojęcia jak w sql zabrać się za pytanie, żeby znajdował te najbliższe punkty...

Proszę o pomoc. Jeśli coś niejasne - wyjaśnię.

Teoretycznie powinno się to liczyć według promienia

[SQL] pobierz, plaintext 
SELECT * FROM wspolrzedne ORDER BY SQRT(POW(db_x - punkt_x, 2) + POW(db_y - punkt_y, 2)) LIMIT 1
[SQL] pobierz, plaintext 

Chociaż ciut szybciej działa według wartości bezwzględnej różnicy współrzędnych:

[SQL] pobierz, plaintext 
SELECT * FROM wspolrzedne ORDER BY ABS(db_x - punkt_x) + ABS(db_y - punkt_y)
[SQL] pobierz, plaintext 

Dzięki serdeczne za szybką i profesjonalną odpowiedź! Przetestuję to.

Jesli nie pobieramy informacji o roznicy odleglosci pomiedzy dwoma punktami (tylko uzywamy jej do sortowania) to mozna pominac pierwiastkowanie dzieki czemu znacznie przyspieszymy obliczenia, czyli zamiast:

[SQL] pobierz, plaintext 
SELECT * FROM wspolrzedne ORDER BY SQRT(POW(db_x - punkt_x, 2) + POW(db_y - punkt_y, 2)) LIMIT 1
[SQL] pobierz, plaintext 

uzyc:

[SQL] pobierz, plaintext 
SELECT * FROM wspolrzedne ORDER BY POW(db_x - punkt_x, 2) + POW(db_y - punkt_y, 2) LIMIT 1
[SQL] pobierz, plaintext 

Należy pamiętać, że Ziemia jest kulą, więc dwa punkty, które są bardzo blisko siebie w okolicach bieguna liczone tą metodą będą od siebie oddalone tak samo jak np. dwa punkty po przeciwnej stronie kuli ziemskiej na równiku. Zapytanie, jakie należy zadać do SQLa jest z tego powodu nieco bardziej skomplikowane:

Obliczając odległość pomiędzy dwoma punktami leżącymi na powierzchni Ziemi nie możemy traktować jej jako układu współrzędnych XY, gdyż Ziemia jest kulista. Poczytaj o Ortodromie. Polecam również Calculate distance, bearing and more between two Latitude/Longitude points

Przykładowe zapytanie o znacznie zmniejszonym błędzie pomiaru:

[SQL] pobierz, plaintext 
SELECT latitude, longitude, acos((sin(pi()*?/180)*sin(pi()*latitude/180))+(cos(pi()*?/180)*cos(pi()*latitude/180)*cos(pi()*longitude/180-pi()*?/180)))*6378137 AS "Dystans [m]"
FROM wspolrzedne ORDER BY acos((sin(pi()*?/180)*sin(pi()*latitude/180))+(cos(pi()*?/180)*cos(pi()*latitude/180)*cos(pi()*longitude/180-pi()*?/180)))*6378137
LIMIT 1
[SQL] pobierz, plaintext 

W miejsce znaków zapytania w zapytaniu wstawić należy współrzędne geograficzne punktu wokół którego szukamy punktów odpowiednio:
szerokość, szerokość, długość, szerokość, szerokość, długość.
Zapytanie wyświetla najbliższy punkt do podanego i odległość między nimi w metrach (6378137 to promień Ziemi w metrach). Obliczanie odległości jako ortodromy w tym przypadku znacznie zmniejszy błąd pomiaru. Są jeszcze dokładniejsze metody, ale ta powinna w zupełności wystarczyć. Błąd w tej metodzie może wynieść max. 30 metrów.

Uzywalem tej metody i byly dosc duze bledy, moze nie jest przystosowana do naszej szerokosci

W takim razie pozostaje Haversine formula

Tutaj bardziej po ludzku http://www.movable-type.co.uk/scripts/gis-faq-5.1.html

Do ilu miejsc po przecinku określałeś współrzędne geograficzne ?

Tutaj brakuje Wam odwzorowania kartograficznego albo przyjęcia jednego z układów współrzędnych. Co do: mosqi83, tak nie można mówić dwóch współrzędnych, ale te co Ty podałaś też są złe Tak więc musimy mówić tutaj o współrzędnych graficznych geograficznych B L i H, później je należy przekonwertować na układ kartezjański i to w zależności od układu odniesienia, więc o ile się nie mylę w Polsce jeszcze jest stosowany układ współrzędnych 1992, ale już nie długo czyli od 2012 albo 2010 ma wejść w życie układ współrzędnych 2000. No i teraz by przeliczyć współrzędne B, L i H na X, Y i Z to albo się zaopatrzycie w programy geodezyjne, albo znajdziecie gdzieś skan książek geodezyjnych I co Wam powiem więcej to wcale nie jest takie proste. Odwzorowania kartograficzne to jest niezły kosmos Nie ma sensu bym się tutaj nad tym rozwodził bo jestem za cienki z geodezji by Wam to przedstawić, ale mogę służyć pomocą.
Pozdrawiam.

Jeśli by brać pod uwagę wysokość no to rzeczywiście byłoby sporo liczenia, ale sądziłem, że wysokość jest pomijana. Zresztą nie podano jaka to ma być odległość - po powierzchni Ziemi czy lotnicza.

A jeśli bez wysokości, to z tego co podaje źródło dot. http://www.movable-type.co.uk/scripts/latlong.html#ellipsoid to wzory nie są zbytnio skomplikowane, a dokładność jest najczęściej na poziomie +-3m / 1km więc nie jest tragicznie. Zależy jaka dokładność jest potrzebna i czy opłacalne jest jej osiągnięcie.

Zwróćcie uwagę na dokładność Vincenty formula