GeoGet

Complete geocaching solutions

User Tools

Site Tools


user:databaze

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

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í!

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

Upozornění: v nedaleké budoucnosti jsou plánovány velké změny ve struktuře databáze. 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 databaze (PRAGMA user_version).

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 Geocaching.cz.

Struktura databáze GeoGetu

Struktura databáze odpovída 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) 3)
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 2)
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 kodnoty 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 1)
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) 1)
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) 1)
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 cas aktualizace tagu 1))
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 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 0 - ručně zadaný, 1 - importovaný, 2 - z importovaných korigovaných souřadnic 2.6.5
dtupdate datum aktualizace waypointu 2)
dtupdate2 plné datum a čas aktualizace waypointu 1)
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

1) 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);  

2) 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.

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

SQLite APPLICATION_ID

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)

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 Geocaching
OC keš ze serveru Opencaching (existuji i narodní servery)
WM Waymark
GS objekt ze serveru GeoSpy
MG Magnetky puzzle http://www.sestavsisvujsvet.cz (data získaná exportem ze serveru K8) plugin
MU Munzee (databáze vytvářená pluginem Munzee)
LO ŘOPík (databáze objektů lehkého opevnění poskytnutá HaLuMou)
TA turistická známka - česko (data získaná exportem ze serveru K8) plugin
TV turistická vizitka (data získaná importem ze serveru http://www.turisticky-denik.cz přes jejich API)
TN turistická nálepka (data získaná exportem ze serveru K8) 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í knihovny zlib, aby se uštetř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.

Spolupráce Geogetí databáze s jinou databází

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/

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 ini ručně změnit pomocí položky dblimit. (v jednotkách MiB)

Příklady SQL dotazů

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

Seznam velikostí keší v databázi

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.

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.

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

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.

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

Vyhledání keší určitého autora s určitým tagem

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'

Seznam nalezených keší bez Found logu

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')
);

Seznam keší, kde má uživatel DNF log

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

Seznam keší, na kterých má uživatel GeoGetu vícenásobný nález

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;

Nastavení typu waypointu

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

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

Statistika logů na keši po posledním Found it logu

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;

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.

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)

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.

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  

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.

-- 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)

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

 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
  )

Goniometrické funkce nad databází

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.

Výpis nalezených keší s rozdílným datem nálezu a logu

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í

user/databaze.txt · Last modified: 2017/10/28 10:20 by tarmara