SQL&GG02: Geocachingový deník

Vítejte u dalšího pokračování obročenky o SQL a GeoGetu. Od posledního dílu uteklo hodně vody v potoce. Já změnil zaměstnání, pořídil si náhradního georobotka a začal požívat nového SQLite klienta. Dlouho jsem hledal téma k další lekci SQL, až Čtyřhranka vydala na svém báječném blogu článek, kde se zmínila o deníku z výletů. A mě napadlo vytáhnout si z GG databáze moje FI-like logy a sestavit si tak vlastně GC deník. Téma na první pohled celkem jednoduché, ale nebojte, trošku se nám to zesložití.

 

Před každým "hraním" s databází doporučuju provést jeji zálohu a nedoporučuju mít nad jednou databází spuštěné dva klienty najednou (např. GG a SQLiteStudio)

 

Od posledně jsem začal používat jiného klienta SQLite db. Sháněl jsem nějaký, co se nemusí instalovat. A našel jsem SQLiteStudio. Je v základu portable, umí to co SQLite Expert a navíc v něm jdou definovat uživatelské funkce. Ty jsou použitelné sice pouze v něm, ale to zase až tak nevadí. Trochu odlišným způsobem se v něm připojují uživatelské knihovny. Toto bude popsáno dále.
Tak se do toho pusťme. Pokud budeme chtít vypsat náš GC deník, tak budeme potřebovat data ze dvou tabulek

• geolog - obsahuje KOMPRIMOVANÝ a tedy nečitelný text ve sloupci logtext a datum logu ve sloupci dt
• geocache - v této tabulce nás bude zajímat alespoň jméno keše a pak asi i dtfound a dtfoundtime. Ty dva časové sloupce se nám budou hodit pro řazení celého deníku.

Jako základ budeme brát data z geolog tabulky. Z té nás budou zajít hlavně FoundIt-like logy. Found it se používá u většiny keší, ale třeba pro eventy a webcamy jsou typy logů jiné. Proto je nutné použít výčet typů logů. A nesmíme zapomenout i na to, že hledáme svoje vlastní logy - podmínka na sloupec finder.

SELECT *
  FROM geolog
 WHERE finder = 'tarmara' AND 
       type IN ('Found it',
       'Webcam Photo Taken',
       'Attended');

Pokud si v GG ukládáte u nalezených keší datum a čas nálezu, tak doporučuji si zkontrolovat, zda nemáte diskrepanci mezi geolog.dt a geocache.dtfound. Nevím z jakého důvodu, ale u mě se pár takových keší našlo. Abychom zjistili, o jaké keše se jedná, tak si spojíme obě tabulky přes sloupec ID a použijeme podmínku pro nerovnost hodnot v obou sloupcích. Pro jednotlivé tabulky už použijeme alias (l pro geolog a c pro geocache), aby databáze mohla jednoznačně určit sloupce, na které odkazujeme. Pokud tak neučiníme, tak databáze například u sloupce id nebude vědět sloupec z jaké tabulky máme na mysli. Pokud ve vaší db najdete nálezy s touto diskrepancí, tak ji prosím opravte ručně např. v GG. Důvody proč k ní dochází jsou mimo námět tohoto článku, většinou jde ale o problém na seat-keyboard rozhraní.

SELECT l.type,
       l.dt geolog_date,
       c.dtfound geocache_date,
       c.dtfoundtime,
       c.name
  FROM geolog l
       LEFT JOIN
       geocache c ON c.id = l.id
 WHERE l.finder = 'tarmara' AND 
       type IN ('Found it',
       'Webcam Photo Taken',
       'Attended') AND 
       l.dt <> c.dtfound;

Pokud máme "vyčištěno", tak se můžeme vrhnout na samotné vypsání logů. Zde ale narazíme na problém s komprimací přímo v databázi. Sice to ušetří místo na disku, ale bez dekomprimace si log nepřečteme. GG má vlastní funkce jak dekompresit logy, ale ty nejsou mimo GG dostupné. SQLite umožňuje přilinkovat knihovnu s funkcemi, které nejsou v db běžně dostupné. V minulém díle to byly goniometrické funkce použité pro výpočet vzdálenosti a azimutů. Teď jsem potřeboval sehnat (de)kompresní funkce. Nakonec se zadařilo na ve fóru na gc.cz se ke stažení potřebná knihovna (alternativní stažení). V SQLiteStudiu se knihovny linkují jinak než v prvním díle. Je nutné je uložit někam na disk a pak zavolat speciální funkci na přilinkování.

SELECT load_extension('.\zlib_ext.dll');

Tento příkaz přilinkuje knihovnu umístěnou přímo v adresáři SQLiteStudia (tam kde je umístěný SQLiteStudio.exe)

 

Tak teď můžeme dekompresit text logu, tak se pusťme do složení dotazu do databáze. Použijeme dotaz, kterým jsem hledali diskrepanci v datumech. Odstraníme z něj podmínku na nerovnost datumů, přidáme funkci na dekompresi textu logu a nakonec přidáme řazení výsledků podle data a času nálezu.

SELECT c.name,
       zdecompress(l.logtext),
       c.dtfound,
       c.dtfoundtime,
       l.type
  FROM geolog l
       LEFT JOIN
       geocache c ON c.id = l.id
 WHERE l.finder = 'tarmara' AND 
       type IN ('Found it',
       'Webcam Photo Taken',
       'Attended') 
 ORDER BY c.dtfound,
          c.dtfoundtime;

To je krásných vzpomínek na jednom místě, co? A taky občas na sériích píšete jeden log pro všechny keše? Nebo hnusoflusným igelitkám a zaokapovkám bez nápadu píšete do logů jen TFTC? A máte tedy deník zaplevelený i podobnými duplicitami a logy, které vám žádnou vzpomínku neevokují? Co s tím? Řešením je buď promazání exportovaných výsledků třeba v Excelu. Nebo elegantnější s použitím tagů GG. Sice to bude chtít trochu úsilí při prvotním zadávání tagů, ale pak už se vám vždycky vyberou jen ty "hodnotné" logy. Předpokládejme tedy, že v GG máme tag DENIK a budou nás zajímat jen keše s nálezy a hodnotou DENIK=1. Vy si můžete tag pojmenovat jak chcete, i hodnoty můžete používat jaké chcete. Ale v příkladu je použitý právě výběr pro tag DENIK=1.
Databáze Geogetu obsahuje tři tabulky, které se používají pro systém tagů. Je tou už trošku složitější konstrukce než vazby mezi dosud používanými tabulkami:

• geotagcategory - kategorie tagu
• geotagvalue - hodnota tagu
• geotag - vazební tabulka mezi třemi tabulkami - geocache, geotagcategory, geotagvalue - tato tabulka obsahuje pouze identifikátory a pro "čitelnost" je nutné použít hodnoty z uvedených tří tabulek

Následující příklad vrátí seznam keší s nejvíce FP ve vaší databázi, seřazený podle počtu FP sestupně a ještě s podmínkou že daná keš musí mít více jak 100 FP. Funkce absolutní hodnota ABS() je použita z důvodu uložení hodnoty tagu jako textu. Text je řazen jiným způsobem než číslo a proto je nutné ho převést na číslo. A u FP nám při převedení textu na absolutní hodnotu nehrozí ztráta informace o znaménku. V případě tagu Elevation už by muselo dojít k přenásobení funkcí SIGN(), aby nedošlo k vytažení keší u Mrtvého moře na kopec.

SELECT c.id,
       i.name,
       k.value,
       v.value
  FROM geocache c
       LEFT JOIN
       geotag t ON c.id = t.id
       LEFT JOIN
       geotagcategory k ON t.ptrkat = k.[key]
       LEFT JOIN
       geotagvalue v ON t.ptrvalue = v.[key]
 WHERE k.value = 'favorites' AND 
       abs(v.value) > 100
 ORDER BY abs(v.value) DESC;

Pokud se tedy vrátíme k našemu deníku, tak přidáme do GG tag DENIK, k vybraným keším přiřadíme tomuto tagu hodnotu 1. A následně můžeme použít následující dotaz.

SELECT c.name,
       zdecompress(l.logtext) logtext,
       c.dtfound,
       c.dtfoundtime,
       l.type,
       k.value tag_kat,
       v.value tag_value
  FROM geolog l
       LEFT JOIN
       geocache c ON c.id = l.id
       LEFT JOIN
       geotag t ON c.id = t.id
       LEFT JOIN
       geotagcategory k ON t.ptrkat = k.[key]
       LEFT JOIN
       geotagvalue v ON t.ptrvalue = v.[key]
 WHERE l.finder = 'tarmara' AND 
       type IN ('Found it',
       'Webcam Photo Taken',
       'Attended') AND 
       k.value = 'DENIK' AND 
       abs(v.value) = 1
 ORDER BY c.dtfound,
          c.dtfoundtime;

A máme vybrány jen ty srdeční nálezy, dotaz běhá svižně. Tak ještě poslední krok. Co třeba ke každému nálezu vyhledat vzdálenost od poslední keše a azimut z poslední keše? Nebudeme teď už brát ohled na tag DENIK. A nebudeme už ani potřebovat text samotného logu. Využijeme i vlastnost SQLite Studia - ukládání vlastních funkcí. Ty je možné psát buď ve dvou skriptovacích jazycích QtScript a Tcl. Jejich syntaxe je mi ale natolik vzdálená, že zatím oželím jejich používání a jejich výhody (případní školitelé pro moje rychlé ochytření jsou vítáni). Zbývá tedy poslední možnost a tou jsou funkce založené na SQL. Je to jednoduché. Do funkce přes parametry pošlete hodnoty a SQL dotaz vrátí výsledek. Nemusíte tedy v dotazu opakovat stále stejné dlouhé vzorce. Drobné nevýhody jsou v tom, že nelze používat cykly a že SQL funkce jsou použitelné jen v rámci SQLiteStudia. Následující obrázek ukáže jak vytvořit funkci Distance, která z parametrů dopočítá vzdálenost dvou bodů. Používá Great-Circle metodu, protože pro přesnější Vincentyho formuli už jsou třeba cykly a iterace. Druhou novinkou je použití operátoru WITH, který nám umožní definovat pod-dotaz, na který se poté můžeme v dotazu odkazovat. Toto zjednodušuje psaní dotazu, pokud byste museli stále opakovat jeden a ten samý pod-dotaz. Zdrojové pro "SQL funkce" distance a bearing jsou na konci příspěvku.
-


-
My klauzuli WITH použijeme pro definování subdotazu který pojmenujeme found. Ten bude vracet kešky s nálezem a navíc dokalkulovávat znakový řetězec, který bude obsahovat datum a čas nálezu (sloupec ft). Pak použijeme konstrukci dotazu, která nám pro každou keš nalezne keš nalezenou před ní. Uděláme to tak, že vybereme pro každou nalezenou keš takovou "předchůdkyni", která byla nalezena před ní (podmínka ss1.ft < t.ft) a zároveň má nejvyšší čas nálezu (agregace max(ft)). Důležitou podmínkou pro správné fungování tohoto dotazu je unikátnost data a času nálezu u každé keše v použité databázi. Bez toho totiž není možné určit jedinečné pořadí nalezení keší. Samotný datum bude fungovat jen a pouze v případě, kdy by na každý den připadla jen jedna nalezená keš. I když asi i tací se mezi námi najdou. Tento dotaz už je pro databázi výkonově mnohem náročnější, protože pro každou nalezenou keš musí projít všechny nalezené keše a vybrat z nich tu s nejvyšším datem a časem nálezu. Mě osobně v databázi s 66000 kešemi (1530 nalezenými) běží dotaz cca 70 sekund. A pozor na to, že pro jeho běh už musíte mít přilinkovanou i knihovnu RSQLite.extfuns.dll z prvního dílu seriálu o SQL

WITH found AS (
   SELECT id,
          name,
          x,
          y,
          dtfound * 10000 + dtfoundtime ft,
          dtfound
     FROM geocache
    WHERE dtfound <> 0
)
SELECT round(distance(t.x, t.y, f.x, f.y), 3) dist,
       round(bearing(t.x, t.y, f.x, f.y), 0) bear,
       t.id,
       t.name,
       f.id,
       f.name
  FROM found t
       LEFT JOIN
       found f ON f.ft = (
                            SELECT max(ft) 
                              FROM found ss1
                             WHERE ss1.ft < t.ft
                         )
 ORDER BY t.ft DESC;

Funguje? To jsem rád. Snad vám tenhle příspěvek k něčemu bude. A nebojte, dotazy samotnými v databázi nic nezkazíte. SELECT dotaz z databáze jen a pouze čte, nic v ní nemění. Dotazy INSERT, UPDATE a DELETE (což už jsou spíš příkazy) ale v db mění samotná data a to hned po spuštění příkazu. Takže na ně bacha! A o nich snad možná někdy příště. S velkým červeným disclaimerem "NA VLASTNÍ NEBEZPEČÍ" a se schválením a kontrolou od autorů GG.
Pokud budete chtít exkurz do SQL úplně od základu, tak doporučuji letitý seriál na Interval.cz - Opět upozornění: POZOR na INSERT/UPDATE/DELETE příkazy!!! - v některých dílech se objevují. A před každým testováním doporučuji provést zálohu databáze.

• https://www.interval...k-na-dotazy-1/
• https://www.interval...k-na-dotazy-2/
• https://www.interval...zy-s-agregaci/
• https://www.interval...ladani-dotazu/
• https://www.interval...vnorene-dotazy/
• https://www.interval...krocile-dotazy/
• https://www.interval...bulkami-a-daty/
• https://www.interval...tvorba-pohledu/

Pokud budete v SQLiteStudiu (nebo třeba ve SmartFiltru) potřebovat nalinkovat vícero knihoven najednou, tak není třeba volat pro každou knihovnu zvláštní dotaz, ale stačí každé volání vložit do jednoho "sloupce". A nelekejte se, že dotazu chybí FROM klauzule s definicí tabulky. Pokud pro vykonání dotazu není tato klauzule potřeba, tak se v SQLite uvádět nemusí (na rozdíl např. od Oracle db).

SELECT load_extension('.\RSQLite.extfuns.dll'), load_extension('.\zlib_ext.dll');

Zdrojové kódy SQL funkcí pro SQLiteStudio, obě mají stejné parametry, zadávané ve stupních v desetinném čísle:

• lat - zeměpisná šířka koncového bodu
• lon - zeměpisná délka koncového bodu
• lat_base - zeměpisná šířka výchozího bodu
• lon_base - zeměpisná délka výchozího bodu

/*bearing - výchozí úhel pro orthodomu z base do bodu - great circle*/
SELECT CASE WHEN (ANGLE / PI() * 180) < 0 THEN 360 + (ANGLE / PI() * 180) ELSE ANGLE / PI() * 180 END BEARING
  FROM (
          SELECT ATAN2(SIN( (Y * PI() / 180) - (Y_BASE * PI() / 180) ) * COS(X * PI() / 180), COS(X_BASE * PI() / 180) * SIN(X * PI() / 180) - SIN(X_BASE * PI() / 180) * COS(X * PI() / 180) * COS( (Y * PI() / 180) - (Y_BASE * PI() / 180) ) ) ANGLE
            FROM (
                    SELECT @lat X,
                           @lon Y,
                           @lat_base X_BASE,
                           @lon_base Y_BASE
                 )
       );

/*distance - délka orthodomy z base do bodu - great circle*/
SELECT ROUND(6371.009 * ATAN2(SQRT(POWER(COS(X_BASE * PI() / 180) * SIN(ABS( (Y * PI() / 180) - (Y_BASE * PI() / 180) ) ), 2) + POWER(COS(X * PI() / 180) * SIN(X_BASE * PI() / 180) - SIN(X * PI() / 180) * COS(X_BASE * PI() / 180) * COS(ABS( (Y * PI() / 180) - (Y_BASE * PI() / 180) ) ), 2) ), SIN(X * PI() / 180) * SIN(X_BASE * PI() / 180) + COS(X * PI() / 180) * COS(X_BASE * PI() / 180) * COS(ABS( (Y * PI() / 180) - (Y_BASE * PI() / 180) ) ) ), 4) 
  FROM (
          SELECT @lat X,
                 @lon Y,
                 @lat_base X_BASE,
                 @lon_base Y_BASE
       );