SQL&GG01: Challenge - 360 degrees of the Czech Republic

Bez dlouhých teoretických úvodů se pokusím poskytnout velmi jednoúčelové řešení, které vzniklo pro lepší plánování odlovu jedné jediné challnge keše GC4PCQB. Měl jsem problém s tím ověřovačem na project-cg.com, který sice umí zobrazit všechny keše v sektorech, ale jinak je uživatelsky velmi nepřívětivý. Seznam nelze nijak exportovat a pro mě jako uživatele GG je to skoro k ničemu. Databáze GG ale neumí jednoduše spustit SQL dotaz s goniometrickými funkcemi, takže bylo nutné vyřešit zabít dvě mouchy jednou ranou:

• sepsat SQL dotaz, který bude umět vypsat keše v jednotlivých sektorech
• výstup z tohoto dotazu nějak dostat do GG

První bod nebyl zase takový problém, znalosti SQL mám pro tehle případ dostatečné, do GG databáze se dostanu pohodlně pomocí http://www.sqliteexpert.com/ (stačí verze Personal) a níže uvedené knihovny s goniometrickými funkcemi.
Druhý bod se dal řešit exportem ID keší do ggt souboru a jeho otevřením v GG. Což ale není uživatelsky příjemné a neIT uživatel tím bude maličko trpět.
Takže následovalo přemýšlení, bádání, testování a objevování Ameriky. Nakonec se ale podařilo. U pluginu SmartFilter jsem maličko pozměnil jeho zdrojový kód, takže si uměl přilinkovat knihovnu a pak už jen stačilo uložit SQL dotaz do správného souboru a řešení je na světě. Snad se bude někomu hodit a třeba i někomu ukáže cestu k netušeným možnostem GG databáze.
Postup je následující:

• nainstalovat do GG plugin SmartFilter (SF) - http://geoget.ararat...ipt:smartfilter
• stáhnout zip soubor s knihovnou pro SQLite s goniometrickými funkcemi - http://cran.r-projec...tfuns_0.0.1.zip - informace o projektu na http://cran.r-projec...funs/index.html
• otevřit si instalačni adresář SF - býva to <%datovy_adresar_GG%>\script\SmartFilter\ - pokud adresář nemužete najít, zkuste najít soubor SmartFilter.ggp.pas a naleznete i adresář
• do tohoto adresáře nakopírujte celý adresář RSQLite.extfuns z výše staženého zip souboru
• upravte zdrojový kód SF (soubor SmartFilter.ggp.pas) přidáním řádky obsahující nalinkování knihovny s goniometrickými funkcemi. Bez tohoto nebude SQLite schopen spustit níže uvedený SQL dotaz, protože SQLite v základu neobsahuje funkce jako sinus a cosinus. Toto provedete tak, že otevrete soubor SmartFilter.ggp.pas v nejakém textovém editoru (notepad, PSPad; nedoporučuji Word!) a najdete řádek obsahují následující príkaz:

  tabCache:=Geoget_DB.GetTable(Query, false);

  Měl by se nacházet někde kolem řádku 770. Přímo NAD! tento řádek přidejte následující příkaz:

• tabCache:=Geoget_DB.GetTable('SELECT load_extension(''.\script\SmartFilter\RSQLite.extfuns\libs\i386\RSQLite.extfuns.dll'');', false);

  Nic nemažte, nic nepřepisujte, jen a pouze nakopírujte ten nový príkaz NAD ten stávající. A soubor uložte. Celá tahle operace umožní SQLite používat goniometrické funkce při spracování SQL dotazu.
• Do stejného adresáře jako SmartFilter.ggp.pas uložte i nový textový soubor Challenge360.sql, do kterého v textovém editoru vložíte následující SQL dotaz:

  --TENTO SQL DOTAZ VRATI KESE ZE VSECH (POKUD SE NEUPRAVI, VIZ NIZE) SEKTORU, KTERE JESTE NEMATE ULOVENE
  --UVODNI SOURADNICE (X_BASE A Y_BASE) JSOU Z CHALLENGE KESE PRO KTEROU BYL TENTO DOTAZ PRIMARNE NAPSAN - GC4PCQB
  SELECT ID --, NAME, FLOOR(BEARING) SEKTOR, ROUND(BEARING, 5) BEARING
    FROM (SELECT ID,
                 NAME,
                 ANGLE / PI() * 180 A,
                 CASE
                   WHEN (ANGLE / PI() * 180) < 0 THEN
                    360 + (ANGLE / PI() * 180)
                   ELSE
                    ANGLE / PI() * 180
                 END BEARING
            FROM (SELECT ID,
                         NAME,
                         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 (
                          --NOT FOUND AND NOT ARCHIVED
                          SELECT DISTINCT ID,
                                           NAME,
                                           X,
                                           Y,
                                           49.74375 X_BASE,
                                           15.33863 Y_BASE
                            FROM GEOCACHE
                           WHERE DTFOUND = 0 AND
                                 COUNTRY = 'Czech Republic' AND
                                 CACHESTATUS <> 2)))
   WHERE
  --FLOOR(BEARING) IN (90,5,132) AND --POKUD CHCETE JEN URCITE SEKTORY, ODKOMENTOVAT RADEK (SMAZTE PRVNÍ DVE POMLCKY NA ZACÁTKU) A ZAPSAT SEM CISLA SEKTORU ODDELOVAT CARKOU A NEZALEZI NA JEJICH PORADI  
   FLOOR(BEARING) NOT IN
   ( --NEZOBRAZI KESE V SEKTORECH VE KTERYCH UZ JE ZAZNAMENANY NALEZ UZIVATELEM
    --NALEZENE SEKTORY START---------------------------------- 
    SELECT DISTINCT FLOOR(BEARING)
    --ID,NAME
      FROM (SELECT ID,
                    NAME,
                    ANGLE / PI() * 180 A,
                    CASE
                      WHEN (ANGLE / PI() * 180) < 0 THEN
                       360 + (ANGLE / PI() * 180)
                      ELSE
                       ANGLE / PI() * 180
                    END BEARING
               FROM (SELECT ID,
                            NAME,
                            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 (
                             --FOUND
                             SELECT DISTINCT ID,
                                              NAME,
                                              X,
                                              Y,
                                              49.74375 X_BASE,
                                              15.33863 Y_BASE
                               FROM GEOCACHE
                              WHERE DTFOUND <> 0 AND
                                    COUNTRY = 'Czech Republic')))
    --NALEZENE SEKTORY END-----------------------------------
    )
  --ORDER BY 3 DESC

• Pak už stačí jen spustit GG, v nem SF a v dialogovém okně vlevo dole vybrat ze seznamu Vlastní SQL to správné Challenge360 a filtr aplikovat (Načíst, Přidat...)
• Challenge360.sql lze pak bez problému editovat dle libosti, treba vybírat jen určité sektory, dle instrukcí v komentářích. Po každé změně je ale nutné znovu spustit SF s vybraným Vlastním SQL.

autor SF o téhle editaci ví, souhlasí s ní. Neplánuje žádny dalši vývoj, ale kdyby přeci jen, tak se po aktualizaci bude muset znovu upravit zdroják SF přidáním té řádky kódu. A velké díky patří testovací veverce - kolombovi.

Pokud by byl v kačerské komunitě zájem o seriál na téma SQL a jeho použití v GeoGetu, tak zkusím sepsat nějaké základy. Pokud by měl někdo námět na podobný jednoúčelový návod jako je v tomto příspěvku, tak se také ozvěte. Z témat které mám v zásobě: dohledání chybějících pro GC4HZQ8; chybějící položky v D/T81 matrixu - oboje vztažené na již nalezené keše aktuálního uživatele GG.

Doplnění po tom co jsem zjistil, že v Praze vznikla podobná keš s omezením na vybrané okresy. Pro http://coord.info/GC56R49 použijte následující dotaz (omezení na CZ Okres tag je uděláno takto humpolácky, protože SF měl nějaký problém s kódováním, ale na funkci to nemá vliv):

--TENTO SQL DOTAZ VRATI KESE ZE VSECH (POKUD SE NEUPRAVI, VIZ NIZE) SEKTORU, KTERE JESTE NEMATE ULOVENE
--UVODNI SOURADNICE (X_BASE A Y_BASE) JSOU Z CHALLENGE KESE PRO KTEROU BYL TENTO DOTAZ PRIMARNE NAPSAN - GC56R49
SELECT ID, NAME, FLOOR(BEARING) SEKTOR, ROUND(BEARING, 5) BEARING, OKRES
  FROM (SELECT ID,
               NAME,
               ANGLE / PI() * 180 A,
               CASE
                 WHEN (ANGLE / PI() * 180) < 0 THEN
                  360 + (ANGLE / PI() * 180)
                 ELSE
                  ANGLE / PI() * 180
               END BEARING,
               OKRES
          FROM (SELECT ID,
                       NAME,
                       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,
                       OKRES
                  FROM (
                        --NOT FOUND AND NOT ARCHIVED
                        SELECT DISTINCT C.ID,
                                         C.NAME,
                                         C.X,
                                         C.Y,
                                         50.07977 X_BASE,
                                         14.42973 Y_BASE,
                                         T.ID,
                                         V.VALUE  OKRES
                          FROM GEOCACHE C
                          LEFT JOIN (SELECT *
                                       FROM GEOTAG
                                      WHERE PTRKAT = 11 /*key CZ Okres*/
                                     ) T
                            ON C.ID = T.ID
                          LEFT JOIN GEOTAGVALUE V
                            ON T.PTRVALUE = V.KEY
                         WHERE DTFOUND = 0 AND
                               COUNTRY = 'Czech Republic' AND
                               CACHESTATUS <> 2)))
 WHERE OKRES LIKE '%Praha%' AND
      --FLOOR(BEARING) IN (90,5,132) AND --POKUD CHCETE JEN URCITE SEKTORY, ODKOMENTOVAT RADEK (SMAZTE PRVNÍ DVE POMLCKY NA ZACÁTKU) A ZAPSAT SEM CISLA SEKTORU ODDELOVAT CARKOU A NEZALEZI NA JEJICH PORADI  
       FLOOR(BEARING) NOT IN
       ( --NEZOBRAZI KESE V SEKTORECH VE KTERYCH UZ JE ZAZNAMENANY NALEZ UZIVATELEM
        --NALEZENE SEKTORY START---------------------------------- 
        SELECT DISTINCT FLOOR(BEARING)
        --ID,NAME
          FROM (SELECT ID,
                        NAME,
                        ANGLE / PI() * 180 A,
                        CASE
                          WHEN (ANGLE / PI() * 180) < 0 THEN
                           360 + (ANGLE / PI() * 180)
                          ELSE
                           ANGLE / PI() * 180
                        END BEARING
                   FROM (SELECT ID,
                                NAME,
                                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 (
                                 --FOUND
                                 SELECT DISTINCT ID,
                                                  NAME,
                                                  X,
                                                  Y,
                                                  50.07977 X_BASE,
                                                  14.42973 Y_BASE
                                   FROM GEOCACHE
                                  WHERE DTFOUND <> 0 AND
                                        COUNTRY = 'Czech Republic')))
        --NALEZENE SEKTORY END-----------------------------------
        )
--ORDER BY 3 DESC
;

Protože někteří kačeři měli s tímto řešením počáteční problémy doplňuji i nějaký ten troubleshooting (budu průběžně doplňovat):

• Project-gc.com chekery a moje řešení používá k výpočtu úhlů metodu zvanou Great Circle. Je to medoda výpočetně nenáročná, vyjádřitelná jedním vzorcem. Geoget a nad ním postavený Stator (a možná i jiné pluginy) používají Vincentyho metodu. Ta je výpočetně mnohem náročnější, protože je iterační a nedá se tak zapsat jedním vzorcem. Je však mnohem přesnější, protože místo aproximace Země koulí používá WGS84 geoid. Rozdíly ve vysledcích jsou pozorovatelné už i na České republice. Funkce pro Vincentyho metodu pro SQLite jsem zatím neobjevil a ani ji nevytvořil....ale přijde den...
• Pokud se Vám objeví následující chyba

  [Script Runtime Error]: SmartFilter.ggp.pas
  Exception: Could not retrieve data
  SQLITE_ERROR [1]: The specified module could not be found.
  SELECT load_extension('.\script\SmartFilter\RSQLite.extfuns\libs\i386\RSQLite.extfuns.dll');
  Routine: PLUGINSTART
  

  tak máte špatně umístěnou RSQLite knihovnu. Zkontrolujte zda máte správně nakopírovaný adresář z bodu 4, případně upravte cestu ke knihovně v bodu 5. Tohle je hodně obecná věc, ale nejsem schopen postihnout všechny možné varianty jak máte nakonfigurovaný GG.
• Objevila se i chyba

  SmartFilter.ggp.pas,
  Exception: index seznamu je mimo hranice (-1),
  Routine: Pluginstart.
  

  zde uživateli pomohlo přepnout v SF volbu "Keše" (ne defaultní "Keše a waypointy"). Nevím proč a neříkám, že to na tuhle chybu pomůže vždy, protože mě to například funguje i s "Keše a waypointy".