Autor Zpráva
karlos1
Profil *
#1 · Zasláno: 17. 8. 2014, 17:38:08
Odpovědět Citovat
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
#2 · Zasláno: 17. 8. 2014, 18:07:05
Odpovědět Citovat
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 *
#3 · Zasláno: 17. 8. 2014, 18:38:28
Odpovědět Citovat
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
#4 · Zasláno: 17. 8. 2014, 18:41:29
Odpovědět Citovat
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 *
#5 · Zasláno: 17. 8. 2014, 18:48:38 · Upravil/a: karlos1
Odpovědět Citovat
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
#6 · Zasláno: 17. 8. 2014, 19:58:56
Odpovědět Citovat
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 *
#7 · Zasláno: 18. 8. 2014, 13:05:31
Odpovědět Citovat
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
#8 · Zasláno: 18. 8. 2014, 13:09:30
Odpovědět Citovat
karlos1:
CASE končí jen ENDem. Nikoli END CASE.
A proč to vůbec řešíš tak nešikovně? 
CEILING(distance/5)*5
karlos1
Profil *
#9 · Zasláno: 18. 8. 2014, 15:17:07
Odpovědět Citovat
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
#10 · Zasláno: 18. 8. 2014, 15:22:09 · Upravil/a: juriad 19. 8. 2014, 02:05:06
Odpovědět Citovat
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.
Časová prodleva: 10 let

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:

0