Zde naleznete popis struktury databáze programu GeoGet. Znalost databázové struktury je důležitá pro samostatné použití SQL dotazů.

Pro provedení SQL příkazů existuje několik cest:

• SQL konzole
• Některý z pluginů:
  • Combine
  • SmartFilter (jen SELECT, nejlépe vracející ID)
  • modul SQLTable generátoru statistik Stator
  • GeoJarry (jen SELECT, nejlépe vracející ID)
• Pokud používáte prohlížeč Mozilla Firefox, tak lze doporučit doplněk SQLite Manager
• Použití některé aplikace pro správu SQLite databází.
  • Mezi opravdu jednoduché patří
    • Sqlite3Explorer
    • SQLite Database Browser
  • Pro sofistikovanější práci lze použít např.
    • SQLite Expert Personal
    • SQLiteSpy
    • SQLite Administrator
    • SQLiteStudio (k němu napsal blogový příspěvek Tarmara).
    • SQLite Query (v roce 2020 jsem objevil i program od českého autora)
  • Dokonce existuje i nějaký doplněk pro TotalCommander/UnrealCommander SQLite Viewer.

Pro úplnost je třeba zmínit, že GeoGet vlastně používá databází více, ale uživatel pracuje přímo jen s databází bodů, která je popsaná níže. Ostatní databáze jsou používány interně a jejich plnění daty uživatel téměř nemá jak ovlivnit. Jde o soubory (místo geoget použijte jméno souboru s použitou databází bodů):

• *.imc (obrázky k bodům)
• *.map (náhledové mapky k bodům)
• *.cache (mezipaměť obrázků, pluginů, polygonů, …)

Diskuze o se nachází na stránkách Geocaching.cz.

Struktura databáze odpovídá verzi, která je uvedena v PRAGMA user_version, v současnosti (GeoGet od verze 2.6) je platná hodnota 2.

¹⁾ Ačkoli jde o hodnotu s datem a časem uloženou v databázi, nejedná se o databázovou hodnotu funkce Date('now'), ale o real hodnotu získanou z Delphi funkce Now(). Hodnota udává lokální čas. Příklad použití pro získání seznamu keší, které nebyly aktualizovány za posledních 24 hodin a nejsou archivované, může vypadat nějak takto:

var dt:TDateTime;
    aList2:TStringList;
 
  ...
  aList2:=TStringList.Create();
  dt:=Now()-1;                   //od aktualniho casu odecteme 24 hodin
  GEOGET_DB.ParameterClear();
  GEOGET_DB.AddParameterFloat(':vcera', dt);
  GEOGET_DB.GetTableStrings('SELECT id FROM geocache WHERE cachestatus < 2 AND dtupdate2 < :vcera', aList);  

²⁾ Položka dtupdate je zastaralá, v aplikaci se od verze 2.8 nevyužívá a je ponechána jen kvůli kompatibilitě. Do budoucna je její existence značně nejistá, proto se doporučuje ji nepoužívat ani ve scriptech. Jedná se o text data ve tvaru RRRRMMDD, hodnota udává lokální čas, resp. datum.

³⁾ Zvláštní důležitost mají první 2 znaky, tzv. Prefix.

⁴⁾ tabulka metadata je obsahuje různé hodnoty jedinečné pro databázi. Je možné doplňovat i vlastní hodnoty, ale v tom případě je důležité věnovat zvláštní pozornost jménu proměnné. Doporučené jméno je složené ve tvaru plugin.proměnná, např. stator.gcczId. GeoGet používá následující proměnné (platné pro verzi 2.11)

Databáze jsou podepsány pomocí APPLICATION_ID ^2.9.10, pomocí něhož lze poznat konkrétní databázový soubor GeoGetu od obyčejné SQLite tabulky. Toto lze číst a nastavovat přes PRAGMA SQL.

PRAGMA schema.application_id; 
PRAGMA schema.application_id = INTEGER;

Zvolená čísla:
6E06E700 - hlavní databáze (*.db3)
6E06E701 - mezipaměť obrázků (*.imc, *.map)
6E06E702 - mezipaměť polygonů (polygon.cache)
6E06E703 - mezipaměť mapových dlaždic pro mapgen (md-*.sqlite)
6E06E704 - mezipaměť skriptů (ggc.cache a ggp.cache)

Prefixem bodu rozumíme první dva znaky identifikátoru hlavního (rodičovského) bodu v databázi. Podle prefixu mohou pluginy vybírat body, s kterými pracují.

Prefix mají i waypointy, ale jejich prefix není pevně daný a nelze je podle něj vybírat. Při zakládání waypointu je třeba zvolit jiný prefix než mají hlavní body a než mají ostatní waypointy patřící k aktuálnímu rodičovskému bodu.

Dosud jsou pro rodičovské body používány následující prefixy:

Do přehledu nejsou z pochopitelných důvodů zahrnuty prefixy různých soukromých databází uživatelů.

Databáze obsahuje v některých sloupcích data, která jsou komprimovaná pomocí knihovny zlib, aby se ušetřilo nějaké to místo na disku.

Protože knihovna je integrovaná do GeoGetu, je její použití ve skriptech snadné. Cesty jsou 2:

1. existují příslušné funkce v API (zCompress() a zDecompress())
2. volání knihovny lze využít přímo v SQL dotazu, např. “SELECT unzlib(logtext) FROM geolog WHERE…”

Problém může nastat při použití jiné aplikace než je GeoGet. V tom případě je nutné si nějak poradit sám. Ideální asi je vytvořit si vlastní UDF funkci a tu zaregistrovat (UDF funkce jsou v SQLite asociovány s aplikací, ne s databází). Jednu takovou knihovnu nabízí Pe_Bo. Jinou možností je načíst komprimované hodnoty jako blobtext a pak si je vlastní funkcí v programu rozbalit.

Pomocí SQL příkazu ATTACH je možné propojit GeoGetí databázi s jinou a obě používat najednou.

Při použití SQLite Studia není třeba používat explicitně ATTACH příkazu, ale prefixem se lze odkazovat na připojené databáze přímo v SQLite studiu. Asi se tam implicitně ten ATTACH používá na pozadí, protože pak fungují příkazy napříč databázemi.

SELECT * FROM nazev_db.tabulka1 JOIN nazev_jine_db.tabulka2

Více se píše zde: http://www.geocaching.cz/topic/30860-vlastn%C3%AD-tabulky-v-ggdb/

Od verze 2.9.9 se u souborů databáze nad 2GB

• neprovádí se zálohování na pozadí a je signalizována chyba
• nezjišťují se tak podrobné statistiky do statusbaru, pouze celkový počet keší. (Nezjišťuje se celkový počet waypointů a logů)

Limit 2GB se dá v ini ručně změnit pomocí položky dblimit. (v jednotkách MiB)

Protože se předpokládá, že většina hodnot tagů má mnohonásobné použití, není u keše použita (uložena) přímo hodnota (=dlouhý text), ale jen číslo, které ukazuje na ten konkrétní dlouhý text. Místo opakujícího se dlouhého textu se tedy opakuje jen číslo. Úspora místa je ale vyvážena trochu komplikovanějším přístupem k hodnotám TAGů. Protože text není uložen přímo u keše, je potřeba “spolupráce” tří tabulek:

• geotagcategory - seznam kategorií (value=text kategorie) a jejich číselných hodnot (key)
• geotagvalue - seznam hodnot tagů (value=text hodnoty), jejich číselných hodnot (key)
• geotag - přidělená kategorie (ptrkat, obsahuje key kategorie) a hodnota tagu (ptvalue, obsahuje key hodnoty) k jednotlivé keši (id, obsahuje GC kód keše)

Jak bylo zmíněno, hodnoty jsou vždy uloženy jako text. Pokud tedy bude nutné chápat text jako číselnou hodnotu (např. pro porovnávaní nadmořských výšek), bude nutné text převést na číslo. To lze udělat dvěma způsoby:

• k hodnotě přičíst nebo odečíst 0, tím dojde k automatickému převodu
• použitím fráze CAST (text as decimal)

SQL s vysvětlivkami

Nejjednodušší SQL příkaz

  SELECT id, ptrkat, ptrvalue FROM geotag
  WHERE id='GC29NEN'

bohužel nedává očekávaný výsledek. Jak bylo řečeno výše, výsledkem bude pouze výpis ukazatelů na texty kategorie a hodnoty tagu, nikoli vlastní texty. Navíc bude výpis obsahovat všechny tagy všech kategorií, které jsou keši přiřazeny.

Abychom se dostali k textům a mohli podle nich případně i vybírat, je potřeba spojit id, ptrkat a ptrvalue a vypsat value z tabulky geotagvalue.

  SELECT gc.id, gtv.value FROM geocache gc
    LEFT JOIN geotag gt ON gc.id=gt.id
    LEFT JOIN geotagcategory gtc ON gt.ptrkat=gtc.key
    LEFT JOIN geotagvalue gtv ON gt.ptrvalue=gtv.key
  WHERE gc.id='GC29NEN' AND gtc.value='elevation'

Pokud budeme chtít vybrat všechny “živé” a nenalezené keše, které mají počet favoritních bodů větší než 90, a seřadíme je podle jejich počtu:

  SELECT gc.id, gtv.value FROM geocache gc
    LEFT JOIN geotag gt ON gc.id=gt.id
    LEFT JOIN geotagcategory gtc ON gt.ptrkat=gtc.key
    LEFT JOIN geotagvalue gtv ON gt.ptrvalue=gtv.key
  WHERE gc.cachestatus=0           -- jen aktivni kese
    AND gc.dtfound<1               -- jen nenalezene
    -- sem muzeme pridat dalsi pozadovane podminky
    -- ...
    -- podminky pro tagy:
    AND gtc.value='favorites'      -- kategorie tagu FP
    AND (gtv.value+0)>90           -- hodnota (text) je jako cislo>90
  -- serazeni
  ORDER BY (gtv.value+0) DESC      -- hodnoty jako cislo seradime sestupne

Zde je uvedeno několik zajímavých možností využití SQL.

SELECT DISTINCT cachesize FROM geocache

Lze samozřejmě upravovat pro výpis všech typů waypointů v databázi SELECT DISTINCT wpttype FROM waypoint, nebo typů keší SELECT DISTINCT cachetype FROM geocache, atd.

Někdy se přihodí, že nelze keš vyfiltrovat filtry, protože má z nějakého důvodu jako desetinný oddělovač v obtížnosti a/nebo terénu čárku , místo tečky .. Tento příkaz zadaný třeba v GeoJarry vypíše postižené keše, které lze ručně opravit.

SELECT id FROM geocache WHERE (difficulty LIKE '%,%') OR (terrain LIKE '%,%')

Vypíše seznam keší, které obsahují vice než jeden finálový waypoint s nenulovými souřadnicemi.

SELECT id FROM waypoint WHERE wpttype='Final Location' AND (x<>0 OR y<>0) GROUP BY id HAVING COUNT(*) > 1

Tento dotaz vypíše keše uživatele romantic29 s tagem Brdy s hodnotou ANO.

SELECT id
FROM geocache JOIN geotag ON geocache.id = geotag.id JOIN geotagcategory ON geotag.ptrkat = geotagcategory.key JOIN geotagvalue ON geotag.ptrvalue = geotagvalue.key
WHERE geocache.author = 'romantic29' AND geotagcategory.value = 'Brdy' AND geotagvalue.value = 'ANO'

Tento dotaz používá plugin AutoStat.

SELECT id
FROM geocache
WHERE dtfound > 0 AND id NOT IN (
  SELECT id
  FROM geolog
  WHERE finder LIKE '%GEOGET_OWNER%' AND TYPE IN ('Found it','Webcam Photo Taken','Attended')
);

SELECT id FROM geolog WHERE finder="%GG_OWNER%" AND TYPE LIKE "Didn't find it"

Předchozí SQL nebere ohled na to, zda keš je opravdu dosud nenalezena. Pokud by bylo potřeba vybrat jen dosud nenalezené keše, na kterých je DNF log, mohl by SQL vypadat třeba takto:

SELECT id FROM geocache WHERE id IN (
  SELECT id FROM geolog WHERE finder="%GG_OWNER%" AND TYPE LIKE "Didn't find it"
  )
  AND dtfound<=0

Tento dotaz je součástí pluginu geojarry. Proměnná %GEOGET_OWNER% funguje pouze při použití přes GeoJarry dotaz do databáze GeoGetu, není obecnou proměnnou pro práci s databází.

SELECT id
FROM geolog
WHERE finder = '%GEOGET_OWNER%'
AND TYPE IN ('Found it','Webcam Photo Taken','Attended')
GROUP BY id
HAVING COUNT(TYPE) > 1;

Nastaví waypoint s prefixem FI jako rucne pridany Final (+ na =)

UPDATE waypoint SET flag=0 WHERE prefixid='FI' AND flag=1

Podobná metoda je součástí maker POI Garmin a GPX Garmin.

Výstup vypadá např. takto:

3x Didn't find it, 1x Temporarily Disable Listing, 2x Write Note

Samotná metoda včetně následného zpracování dotazu:

function LogStat(geo: TGeo) : String;
var
   tab :TSQliteTable;
begin
   Result := '';
     try
      Geoget_DB.AddParamText(':id',geo.ID);
      tab := Geoget_DB.GetTable('SELECT type, count(key) AS cnt FROM geolog WHERE id = :id AND dt >= (SELECT max(dt) FROM geolog WHERE id = :id AND type IN (''Found it'',''Webcam Photo Taken'',''Attended'')) AND type NOT IN (''Found it'',''Webcam Photo Taken'',''Attended'') GROUP BY type ORDER BY cnt DESC;', false);
      try
        while not tab.EOF do
        begin
          Result := Result + tab.FieldAsString(tab.FieldIndex['cnt']) + 'x ' + tab.FieldAsString(tab.FieldIndex['type']);
          if tab.next then Result := Result + ', ';
        end;
      finally
        tab.free;
      end;
     finally
     end;
end;

Tarmara totéž udělal v čistém SQL:

SELECT id,
       ifnull(group_concat(cnt || 'x ' || TYPE), '0x Not-"Found It" logs') Not_FI_logs
  FROM (
          SELECT g.id,
                 l.type,
                 COUNT(1) cnt
            FROM (
                    SELECT *
                      FROM geocache
                     WHERE cachestatus <> 2
                 )
                 g
                 LEFT JOIN
                 geolog l ON g.id = l.id AND 
                             l.type NOT IN ('Found it', 'Webcam Photo Taken', 'Attended') AND 
                             dt >= (
                                      SELECT MAX(dt) 
                                        FROM geolog ls
                                       WHERE l.id = ls.id AND 
                                             ls.type IN ('Found it', 'Webcam Photo Taken', 'Attended') 
                                   )
           GROUP BY g.id,
                    l.type 
           ORDER BY g.id,
                    l.type
       )
 GROUP BY id;

Keše, které jsou stažené pomocí Summary přes GC.Live, neobsahují žádné logy. Zapoznámkované řádky je možné odpoznámkovat (vymazat první dva znaky - na řádku), aby byl výběr keší patřičně zúžen.

SELECT id,cachestatus FROM geocache WHERE
  --dtfound>0 AND     -- jen nalezene
  --cachestatus<>2 AND  -- nearchivovane
  --cachetype<>'Lab Cache' AND -- bez LABek
  id NOT IN (SELECT DISTINCT(id) FROM geolog)

V ukázkovém SQL je N=3. Výsledek ale není možné zobrazit přímo v GeoGetu, protože není vrácen GC kód keše, ale jen kombinace a počet. GeoGet tedy nemá co zobrazit.

SELECT dt, 3-pocet AS chybi FROM
(SELECT difficulty||'/'||terrain dt, COUNT(difficulty||'/'||terrain) pocet FROM geocache WHERE dtfound>0 GROUP BY difficulty||'/'||terrain) f
WHERE pocet<3  

SQL příkaz je napsán tak, aby jej bylo možné použít i pro druhé, třetí, čtvrté, … vyplnění tabulky D/T. Stačí pouze nastavit správné číslo na předposledním řádku. Pokud by někdo chtěl pochopit, jak příkaz pracuje, je opatřen komentářem a je třeba jej číst z prostředka, od vnitřního příkazu SELECT.

-- kese, ktere maji pozadovanou kombinaci
SELECT id,difficulty||'/'||terrain FROM geocache WHERE dtfound<1 AND cachestatus<>2 AND difficulty||'/'||terrain IN (
  -- kombinace, ktere maji mensi pocet nalezu nez X
  SELECT f.dt FROM
    -- pocet kombinaci kesi odlovenych od kazde kombinace D/T
    (SELECT difficulty||'/'||terrain dt, COUNT(difficulty||'/'||terrain) pocet FROM geocache WHERE dtfound>0 GROUP BY difficulty||'/'||terrain) f  
  WHERE f.pocet<1  
)

Pokud jde o první vyplnění D/T tabulky, tak je příkaz o dost jednodušší:

SELECT id FROM geocache WHERE cachestatus<>2 AND dtfound<1 AND difficulty||'-'||terrain
 NOT IN (SELECT DISTINCT(difficulty||'-'||terrain) FROM geocache WHERE dtfound>0)

SQL příkaz nevrací žádné ID keše, proto jej nejde použít v žádném pluginu pro zobrazení seznamu keší. Může být ale použit v databázové konzoli nebo v programech pro práci s SQLite databází (viz seznam na začátku této stránky).

 SELECT Okres FROM (SELECT DISTINCT gtv.value Okres
      FROM geocache gc, geotag gt, geotagcategory gtc, geotagvalue gtv
        WHERE country='Czech Republic'
          AND gt.id=gc.id
          AND gtc.key=gt.ptrkat AND gtc.value='CZ okres' AND gtv.key=gt.ptrvalue          
      ) t2
  WHERE t2.Okres NOT IN (  
     SELECT DISTINCT gtv.value OkresNalezen
       FROM geocache gc, geotag gt, geotagcategory gtc, geotagvalue gtv
       WHERE country='Czech Republic'
         AND gt.id=gc.id AND gc.dtfound>0
         AND gtc.key=gt.ptrkat AND gtc.value='CZ okres' AND gtv.key=gt.ptrvalue
       GROUP BY gtv.[VALUE] ORDER BY gtv.value
  )

tarmara zveřejnil jeho výsledky s goniometrickými funkcemi v SQL pro vyhledávání keší v kruhových výsečích. Více naleznete zde.

Jak zde píše tamara: 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.

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 = '%GEOGET_OWNER%' AND 
       TYPE IN ('Found it',
       'Webcam Photo Taken',
       'Attended') AND 
       l.dt <> c.dtfound;

tamarův technický zápisník - GeoGet, hlavně jeho databáze a vyhledávání v ní; SQL a ohýbání již existujících řešení