GeoGet

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== Databáze a SQL ======
Zde naleznete popis struktury databáze programu GeoGet. Znalost databázové struktury je důležitá pro samostatné použití SQL dotazů.

<WRAP round important>SQL dotazy jsou příkazy přímo pro //databázový stroj// mimo kontrolu GeoGetu. Čtení z databáze (příkazy SELECT) je vcelku bez nebezpečí, ale jakékoli změny je třeba provádět obezřetně a opravdu jen v případě, že víte, co děláte. A i v tom případě databázi zálohujte.\\
\\
SQL dotazy patří do rukou jen lidem, kteří vědí, co dělají!</WRAP>

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

  * [[user:sql_konzole|SQL konzole]]
  * Některý z pluginů:
    * [[user:skript:combine|Combine]]
    * [[user:skript:smartfilter|SmartFilter]] (jen SELECT, nejlépe vracející ID)
    * modul [[user:skript:stator:modules:stator-customtables|SQLTable]] generátoru statistik [[user:skript:stator|Stator]]
    * [[user:skript:geojarry|GeoJarry]] (jen SELECT, nejlépe vracející ID)
  * Pokud používáte prohlížeč Mozilla Firefox, tak lze doporučit doplněk [[https://addons.mozilla.org/en-US/firefox/addon/sqlite-manager-webext/|SQLite Manager]]
  * Použití některé aplikace pro správu SQLite databází.
    * Mezi opravdu jednoduché patří
      * [[http://www.singular.gr/sqlite/|Sqlite3Explorer]]
      * [[https://sqlitebrowser.org/|SQLite Database Browser]]
    * Pro sofistikovanější práci lze použít např. 
      * [[http://www.sqliteexpert.com/index.html|SQLite Expert Personal]]
      * [[https://www.yunqa.de/delphi/products/sqlitespy/index|SQLiteSpy]]
      * [[http://sqliteadmin.orbmu2k.de/|SQLite Administrator]]
      * [[https://sqlitestudio.pl/|SQLiteStudio]] (k němu napsal [[https://www.geocaching.cz/blog/53/entry-458-sqlgg02-geocachingov%C3%BD-den%C3%ADk/|blogový příspěvek]] Tarmara).
      * [[https://www.mitec.cz/sqliteq.html|SQLite Query]] (v roce 2020 jsem objevil i program od českého autora)
    * Dokonce existuje i nějaký doplněk pro TotalCommander/UnrealCommander [[https://totalcmd.net/plugring/sqliteviewer.html|SQLite Viewer]].

<WRAP round info>
Upozornění: V některých případech může být potřeba kontrolovat, zda databáze je opravdu GeoGetí databází. Aplikace, které je databáze určena, je nastavena ve vlastnostech databáze. SQL ''PRAGMA application_ID'' by měl vrátit hodnotu ''1845946112''.
</WRAP>

<WRAP round info>
Upozornění: je vhodné kontrolovat si verzi databáze, zda plugin nebo aplikace pracuje se správnou databázovou strukturou. Může tedy dojít k tomu, že dříve připravený SQL dotaz nebude pracovat správně v některé z příštích verzí databáze. Verze databáze je uvedena ve vlastnostech databáze ''PRAGMA user_version''. Aktuální verze databáze je 3 (pro verzi GeoGetu 2.11).
</WRAP>

//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 =====
Diskuze o se nachází na stránkách [[http://www.geocaching.cz/forum/viewthread.php?forum_id=20&thread_id=18571|Geocaching.cz]].

===== Struktura databáze GeoGetu =====
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.

^ Tabulka ^ Sloupec ^ Popis dat |
| ''coord_index'' | virtuální tabulka s prostorovým indexem, GG ji vidí jako jednu tabulku (**coord_index**), fyzicky vznikají tyto čtyři||
| ''coord_index_node'' | ::: ||
| ''coord_index_parent'' | ::: ||
| ''coord_index_rowid'' | ::: ||
| ''geocache'' | ''key'' ||
| ::: | ''id'' | ID bodu (GCxxxx) <wrap important> <sup>3)</sup></wrap> |
| ::: | ''guid'' | GUID bodu |
| ::: | ''x'' | zem. délka |
| ::: | ''y'' | zem. šířka |
| ::: | ''name'' | název bodu |
| ::: | ''author'' | autor bodu |
| ::: | ''cachetype'' | typ bodu |
| ::: | ''cachesize'' | velikost krabičky |
| ::: | ''difficulty'' | obtížnost |
| ::: | ''terrain'' | terén |
| ::: | ''inventory'' | nepoužitý sloupec, potenciálně pro budoucí využití |
| ::: | ''cachestatus'' | stav bodu (0 = ok, 1 = disabled, 2 = archived, 3 = unknown) |
| ::: | ''userstatus'' | nepoužitý sloupec, potenciálně pro budoucí využití |
| ::: | ''dthidden'' | datum ukrytí bodu |
| ::: | ''dtlast'' | datum posledního nálezu bodu |
| ::: | ''dtupdate'' | datum poslední aktualizace bodu <wrap important> <sup>2)</sup></wrap> |
| ::: | ''comment'' | poznámka |
| ::: | ''dtfoundtime'' | čas nalezení bodu |
| ::: | ''dtfound'' | datum nalezení bodu |
| ::: | ''country'' | země |
| ::: | ''state'' | území |
| ::: | ''gs_cacheid'' | groundspeak ID keše (hodnota odpovídá GC kódu a lze ji z GC kódu vypočítat, naopak z této hodnoty je možné vypočítat GC kód) |
| ::: | ''gs_stateid'' | groundspeak ID státu |
| ::: | ''gs_ownerid'' | grounspeak ID autora |
| ::: | ''dtupdate2'' | plné datum a čas poslední aktualizace bodu <wrap important> <sup>1)</sup></wrap> |
| ''geolist'' | ''key'' ||
| ::: | ''id'' | ID bodu (GCxxxx) |
| ::: | ''shortdesc'' | krátký popis (komprimován zlib) |
| ::: | ''shortdescflag'' | formát krátkého popisu (prostý text = 0, HTML = 1) |
| ::: | ''longdesc'' | dlouhý popis (komprimován zlib) |
| ::: | ''longdescflag'' | formát dlouhého popisu (prostý text = 0, HTML = 1) |
| ::: | ''hint'' | nápověda (dekódovaná) |
| ::: | ''hash'' | kontrolní součet textu listingu využitý pro kontrolu jeho změny |
| ::: | ''dtupdate2'' | plné datum a čas poslední aktualizace listingu (jen když dojde ke změně hash) <wrap important> <sup>1)</sup></wrap> |
| ''geolog'' | ''key'' ||
| ::: | ''id'' | ID bodu (GCxxxx) |
| ::: | ''dt'' | datum logu |
| ::: | ''type'' | typ logu |
| ::: | ''finder'' | nálezce |
| ::: | ''logtext'' | text logu (komprimován zlib) |
| ::: | ''gs_logid'' | groundspeak ID logu |
| ::: | ''gs_finderid'' | groundspeak ID nálezce |
| ::: | ''hash'' | kontrolní součet textu logu využitý pro kontrolu jeho změny |
| ::: | ''dtupdate2'' | plné datum a čas poslední aktualizace logu (jen když dojde ke změně hash) <wrap important> <sup>1)</sup></wrap> |
| ''geotag'' | ''key'' ||
| ::: | ''id'' | ID bodu (GCxxxx) |
| ::: | ''flag'' | rezervováno pro budoucí potřebu označování druhu tagu (0, od verze 2.8 (asi) obsahuje datum a čas aktualizace tagu <wrap important> <sup>1)</sup></wrap>)|
| ::: | ''ptrkat'' | kategorie (klíč, ukazatel na sloupec ''geotagcategory.key'') |
| ::: | ''ptrvalue'' | hodnota (klíč, ukazatel na sloupec ''geotagvalue.key'') |
| ''geotagcategory'' | ''key'' | klíč, ukazatel na sloupec ''geotag.ptrkat'' |
| ::: | ''value'' | název kategorie tagů |
| ::: | ''flag'' | rezervováno pro budoucí potřebu označování druhu tagu |
| ''geotagvalue'' | ''key'' | klíč, ukazatel na sloupec ''geotag.ptrvalue'' |
| ::: | ''value'' | hodnota tagu |
| ::: | ''flag'' | rezervováno pro budoucí potřebu označování druhu tagu |
| ''metadata''<wrap important> <sup>4)</sup></wrap> | ''key'' ||
| ::: | ''name'' | jméno uložené hodnoty |
| ::: | ''value'' | vlastní hodnota |
| ''waypoint'' | ''key'' ||
| ::: | ''id'' | ID waypointu |
| ::: | ''guid'' | GUID waypointu |
| ::: | ''x'' | zem. šířka waypointu |
| ::: | ''y'' | zem. délka waypointu |
| ::: | ''name'' | název waypointu |
| ::: | ''prefixid'' | prefix waypointu |
| ::: | ''lookup'' | Lookup code waypointu (kód pro zobrazení WP na serveru) - hodnota bývala získávaná při importu listingu z HTML stránky, což už teď nejde, takže je to relikt, nepoužívá se |
| ::: | ''wpttype'' | typ waypointu |
| ::: | ''cmt'' | popis waypointu z gc.com |
| ::: | ''comment'' | poznámka k waypointu |
| ::: | ''flag'' | **bit 0:** ''1'' - importovaný, ''0'' - ručně zadaný (to zároveň znamená "Nepřepisovat při importu")|
| ::: | ::: | **bit 1:** ''1'' (hodnota=2) - z importovaných korigovaných souřadnic <sup>2.6.5</sup> |
| ::: | ::: | **bit 2:** ''1'' (hodnota=4) - navštívený waypoint <sup>2.9.13</sup> |
| ::: | ''dtupdate'' | datum aktualizace waypointu <wrap important> <sup>2)</sup></wrap> |
| ::: | ''dtupdate2'' | plné datum a čas aktualizace waypointu <wrap important> <sup>1)</sup></wrap> |
| ''wpt_coord_index'' | virtuální tabulka s prostorovým indexem, GG ji vidí jako jednu tabulku (**wpt_coord_index**), fyzicky vznikají tyto čtyři||
| ''wpt_coord_index_node'' | ::: ||
| ''wpt_coord_index_parent'' | ::: ||
| ''wpt_coord_index_rowid'' | ::: ||

===  ===

<wrap important> <sup>1)</sup></wrap> 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:
<code delphi>
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);  
</code>

<wrap important> <sup>2)</sup></wrap> 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. 

<wrap important> <sup>3)</sup></wrap> Zvláštní důležitost mají první 2 znaky, tzv. [[#prefix|Prefix]].

<wrap important> <sup>4)</sup></wrap> 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)

|create_application|aplikace a její verze, která databázi vytvořila|
|create_level|verze databáze, na jaké verzi byla databáze vytvořena. Aktuální verze databáze je dostupná přes funkce databáze, viz. SQL příkaz ''PRAGMA user_version;'' a poznámka na začátku této stránky|
|create_dt|datum a čas vytvoření databáze v numerickém tvaru|
|create_datetime|datum a čas vytvoření databáze ve tvaru YYYY-MM-DD hh:mm:ss.ssss|
|upgrade_application|aplikace a její verze, které databázi aktualizovala|
|upgrade_dt|datum a čas posledního povýšení databáze (číslo)|
|upgrade_datetime|datum a čas posledního povýšení databáze (string)|
|MyFindsTS|datum a čas posledního načítání logů přes API|



===== SQLite APPLICATION_ID =====
Databáze jsou podepsány pomocí APPLICATION_ID <sup>2.9.10</sup>, 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.

<code sql>PRAGMA schema.application_id; 
PRAGMA schema.application_id = integer;</code>

**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'') \\

===== Prefix =====
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:

^ Prefix ^ Význam |
| GC | keš ze serveru [[http://geocaching.com|Geocaching]] |
| OC | keš ze serveru [[http://opencaching.com|Opencaching]] (existuji i národní servery) |
| WM | [[http://waymarking.com|Waymark]]  |
| GS | objekt ze serveru [[http://geospy.com|GeoSpy]] |
| MG | Magnetky puzzle http://www.sestavsisvujsvet.cz (data získaná exportem ze [[http://k8.kreteni.eu/tz/tz.php|serveru K8]]) [[http://www.geoget.cz/doku.php/user:skript:magnetky|plugin]] |
| MU | Munzee (databáze vytvářená pluginem [[user:skript:munzees|Munzee]])|
| LO | ŘOPík ([[user:skript:rop|databáze objektů lehkého opevnění]] poskytnutá HaLuMou) |
| TA | turistická známka - Česko (data získaná exportem ze [[http://k8.kreteni.eu/tz/tz.php|serveru K8]]) [[http://www.geoget.cz/doku.php/user:skript:tznamky|plugin]] |
| TV | turistická vizitka (data získaná importem ze serveru http://www.turisticky-denik.cz [[http://www.geoget.cz/doku.php/user:skript:wanderbook|přes jejich API]]) |
| TN | turistická nálepka (data získaná exportem ze [[http://k8.kreteni.eu/tz/tz.php|serveru K8]]) [[http://www.geoget.cz/doku.php/user:skript:tnalepky|plugin]] |

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

===== Přístup ke komprimovaným sloupcům =====
Databáze obsahuje v některých sloupcích data, která jsou komprimovaná pomocí [[http://zlib.net/|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:

  - existují příslušné [[user:skripty:functions|funkce v API]] (''zCompress()'' a ''zDecompress()'')
  - 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 [[http://www.geocaching.cz/topic/18571-geoget-sql/?p=512297|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.

===== Spolupráce GeoGetí databáze s jinou databází =====
<wrap tip>Pomocí SQL příkazu ATTACH</wrap> je možné propojit GeoGetí databázi s jinou a obě používat najednou.

<wrap tip>Při použití SQLite Studia</wrap> 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. 
<code sql>select * from nazev_db.tabulka1 join nazev_jine_db.tabulka2</code>
Více se píše zde: http://www.geocaching.cz/topic/30860-vlastn%C3%AD-tabulky-v-ggdb/

===== Big Data =====
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 [[user:geogetini|ini]] ručně změnit pomocí položky ''dblimit''. (v jednotkách MiB)

===== Přístup  TAGům =====
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

<code SQL>
  SELECT id, ptrkat, ptrvalue FROM geotag
  WHERE id='GC29NEN'
</code>

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**.

<code SQL>
  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'
</code>

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:

<code SQL>
  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
</code>

===== Příklady SQL dotazů =====
Zde je uvedeno několik zajímavých možností využití SQL.

==== Seznam velikostí keší v databázi ====
<code sql>SELECT DISTINCT cachesize FROM geocache</code>
<wrap info>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.</wrap>

==== Výpis keší které mají v obtížnosti nebo terénu čárku ====
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.
<code sql>SELECT id FROM geocache WHERE (difficulty LIKE '%,%') OR (terrain LIKE '%,%')</code>

==== Výpis keší s více finálovými waypointy ====
Vypíše seznam keší, které obsahují vice než jeden finálový waypoint s nenulovými souřadnicemi.
<code sql>SELECT id FROM waypoint WHERE wpttype='Final Location' AND (x<>0 OR y<>0) GROUP BY id HAVING count(*) > 1</code>

==== Vyhledání keší určitého autora s určitým tagem ====
Tento dotaz vypíše keše uživatele //romantic29// s [[user:navody:tagy|tagem]] //Brdy// s hodnotou //ANO//.
<code sql>
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'
</code>

==== Seznam nalezených keší bez Found logu ====
Tento dotaz používá plugin [[user:skript:autostat|AutoStat]].
<code sql>
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')
);
</code>

==== Seznam keší, kde má uživatel DNF log ====
<code sql>
SELECT id FROM geolog WHERE finder="%GG_OWNER%" and type like "Didn't find it"
</code>

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:
<code sql>
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
</code>

==== Seznam keší, na kterých má uživatel GeoGetu vícenásobný nález ====
Tento dotaz je součástí pluginu [[user:skript:geojarry|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í.
<code sql>
SELECT id
FROM geolog
WHERE finder = '%GEOGET_OWNER%'
AND type IN ('Found it','Webcam Photo Taken','Attended')
GROUP BY id
HAVING count(type) > 1;
</code>

==== Nastavení typu waypointu ====
Nastaví waypoint s prefixem FI jako **rucne** pridany Final (''+'' na ''='')
<code sql>UPDATE waypoint SET flag=0 WHERE prefixid='FI' AND flag=1</code>

==== Statistika logů na keši po posledním Found it logu ====
Podobná metoda je součástí maker [[user:skript:poigarmin|POI Garmin]] a [[user:skript:gpxgarmin|GPX Garmin]].

Výstup vypadá např. takto: <code>3x Didn't find it, 1x Temporarily Disable Listing, 2x Write Note</code>

Samotná metoda včetně následného zpracování dotazu:
<code delphi>
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;
</code>

[[http://www.geocaching.cz/topic/18571-geoget-sql/?p=533721|Tarmara]] totéž udělal v čistém SQL:

<code 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;
</code>

==== Seznam keší bez logů ====
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.

<code SQL>
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)
</code>

==== Kombinace a počty keší, které je třeba odlovit pro Nté vyplnění matrixu ====
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.

<code SQL>
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  
</code>

==== Seznam keší s dosud nenalezenou kombinací D/T ====
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.

<code SQL>
-- 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  
)
</code>

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

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

==== Seznam okresů, v kterých ještě nemám nalezenou žádnou kešku ====
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).

<code SQL>
 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
  )
</code>

==== Goniometrické funkce nad databází ====
[[http://www.geocaching.com/profile/?u=tarmara|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 [[http://www.geocaching.cz/blog/53/entry-267-sqlgg01-challenge-360-degrees-of-the-czech-republic/|zde]].

==== Výpis nalezených keší s rozdílným datem nálezu a logu ====
Jak zde píše [[http://www.geocaching.cz/blog/53/entry-458-sqlgg02-geocachingov%C3%BD-den%C3%ADk/|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.//
<code sql>
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;
</code>

----

[[http://www.geocaching.cz/blog/53-tarmar%C5%AFv-technick%C3%BD-z%C3%A1pisn%C3%ADk/|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í

{{tag>sql}}