Autor Zpráva
karlos1
Profil *
Ahoj,

mám databázi všech obcí a měst ČR (vč. některých větších čtvrtí, městysů, atd.), která obsahuje cca 8.000 záznamů. U každého záznamu mám GPS souřadnice. A nyní nad touto tabulkou potřebuji vyhledávat města v okruhu X km od mnou zadaného.

Napadlo mě následující:
1. zadám do formulářového pole "Liberec"
2. SELECTem z databáze si vytáhnu GPS Liberce (1. SQL dotaz)
3. SELECTem načtu všechny záznamy z DB a uložím do pole
4. projdu pole, porovnám okruh vzdálenosti a vyhovující záznamy uložím do pole pro další zpracování, zbytek zahodím.

1. a 2. jsou asi správně, dalšími dvěma body si jistý nejsem a přijde mi to značně neefektivní. Dá se nějak porovnávat už nad DB? Nebo je možné už v DB vyselektovat jen nějaké lokace, kterým pak budu počítat vzdálenost?

S pomocí googlu jsem dospěl k následujícímu výpočtu vzdálenosti (PHP):
$distance = 6378.135 * acos(cos(deg2rad(90 - $lat2)) * cos(deg2rad(90 - $lat1)) + sin(deg2rad(90 - $lat2)) * sin(deg2rad(90 - $lat1)) * cos(deg2rad($lon2 - $lon1)));

Okruhy jsou malé, v řádu kilometrů (10-50km), výpočet může být +- kilometr, jen orientačně. Dá se v tomto malém rozptylu, kde můžeme zanedbat zakřivení země, použít nějaký zjednodušený převod, že "XX stupňů/minut je YY km"? Hovoříme o vzdušných vzdálenostech, nikoliv silničních.

Děkuji.
Alphard
Profil
Ten vzorec, co jste si našel, lze samozřejmě zapsat i v SQL. Alernativně jsem sem na diskusi sám několikrát dával funkci gps_distance, která dělá totéž.
Pak lze na úrovni databáze vybrat přesně požadovaný výsledek jednoduchým where gps_distance(lat, lng, X, Y) < 50.
karlos1
Profil *
Alphard:
díky. Na základě tvé reakce jsem našel GPS souradnice - mista od urciteho bodu, myslel jsi toto?

Jen se k tomu zeptám, jelikož funkce v MySQL jsem nikdy nepoužíval. Pracuji jen se SQL příkazy, jako query, fetch_assoc, atp., jak funkci zavolám z PHP kódu? klasicky v query, tedy
$mysqli->query("DROP FUNCTION ...");
?

Děkuji.
Alphard
Profil
První se ta funkce musí v MySQL definovat, tj. spustit celý ten kód (teď se dívám, že na začátku chybí delimiter ;;) někde v Admineru nebo v jiném správci. Pak už se volá jako jakákoliv jiná funkce. www.linuxsoft.cz/article.php?id_article=1034
karlos1
Profil *
Alphard:
Jestli tomu dobře rozumím, v PHPMyAdminu vložím ten kód a pak už si bude daná databáze pamatovat navždy, že ta funkce je vytvořena? Takže, v PHP s ní vůbec nepracuji.

Snažil jsem se o funkcích v SQL trochu něco najít, ale nedaří se mi. Měl bys k doporučení nějaký ověřený zdroj? Klidně v AJ (předpokládám, že bude v AJ, není to překážkou).


Pardon, přehlédl jsem odkaz na článek na linuxsoftu.
Alphard
Profil
karlos1:
v PHPMyAdminu vložím ten kód a pak už si bude daná databáze pamatovat navždy, že ta funkce je vytvořena?
Asi tak.
K těm funkcím není na základní úrovni moc co dodat, chovají se jako všude jinde. V manuálu bych se podíval na syntaxi, pročetl první odstavce a to by mělo stačit.

Použití té funkce v dibi pak vypadá třeba takto
$query->select('gps_distance(h.gps_lat, h.gps_lng, %f, %f) distance', $lat, $lng)
karlos1
Profil *
Alphard:
Děkuji moc, odkaz na linuxsoftu mi dost pomohl.

Vyhledávání funguje už skvěle, ještě bych ho chtěl trochu vylepšit, a to tak, že bych řadil výsledky dle vzdálenosti od hledaného místa, a to podle okruhů 5km a dalších kritérií (např. věku).

Příklad nalezených (a už seřazených) dat:
vzdalenost | vek
// řazení dle věku ASC, okruh 0-5km
     3     |  20 
   1.5     |  23
   3.7     |  24
   3.8     |  25
   0.1     |  28
   4.9     |  33
   1.5     |  35
// konec okruhu 0-5 km, zacina opet razeni dle veku, tentokrat okruh 5-10km
   7.3     |  22
   9.4     |  27
   7.5     |  30
// konec okruhu 5-10 km, zacina opet razeni dle veku, tentokrat okruh 10-15km
  12.4     |  22
  ...

Zkoušel jsem to na úrovni SQL takto:
SELECT t1.sloupce,
       gps_distance(t2.lat, t2.lng, X, Y) AS distance, -- tu chci vypisovat
       CASE 
              WHEN MOD(distance/5) = 0 THEN distance
              ELSE ROUND((distance + 2.5) / 5) * 5
       END CASE AS d, -- zde pro zazeni
       dalsi_sloupce
...
ale tento dotaz skončil chybou na řádku s END CASE, konkrétně nesprávná syntaxe near 'CASE AS d --. Místo distance jsem zkoušel i absolutní čísla, i volání gps_distance, ale na SQL chybu to nemělo vliv. Bez té podmínky na vzdálenost, tedy celý CASE, dotaz šlape bez problémů (resp. vrací očekávané výsledky).

Co dělám zde špatně?
Děkuji.
juriad
Profil
karlos1:
CASE končí jen ENDem. Nikoli END CASE.
A proč to vůbec řešíš tak nešikovně?
CEILING(distance/5)*5
karlos1
Profil *
juriad:
ceiling neznám. Neznal jsem, díky za tip.

A to s tím CASE. Tento zápis jsem našel v manuálu, http://dev.mysql.com/doc/refman/5.0/en/case.html.
juriad
Profil
karlos1:
Tebe ale zajímá jiný CASE.
http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html#operator_case

V tom odkázaném manuálu je na začátku poznámka. Ten s tím END CASE slouží ke skriptování - definici funkcí a procedur.

Vaše odpověď

Mohlo by se hodit


Prosím používejte diakritiku a interpunkci.

Ochrana proti spamu. Napište prosím číslo dvě-sta čtyřicet-sedm: