Using SQLite to do your own analysis

From Geoqo
Revision as of 20:14, 2 April 2007 by Yamar (Talk | contribs)
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to: navigation, search

If you know sql, you can actually use sqlite to open your own database and look through it in extra ways that geoqo may not be able to handle as easily. An example is that brdad wanted to know how many different people used "produced by" names different than their own caching name. I showed him he could do this using sqlite on the database:

First, how many caches were in my DB (geoqo -d stats will tell you this too):

 sqlite ~/.geoqo/dbs/geoqo
 SQLite version 2.8.17
 Enter ".help" for instructions
 sqlite> select count(ident) from geocaches;
 14488

Then how many had a mismatch:

 sqlite> select count(ident) from geocaches where groundspeak_owner_name <> groundspeak_placed_by;
 2948

And finally, show the list counting who had the most mismatches:

 sqlite> select groundspeak_owner_name, count(ident) from geocaches where groundspeak_owner_name <> groundspeak_placed_by group by groundspeak_owner_name order by count(ident) desc limit 10;
 GBESGeocachers|89
 Marky|81
 SnoWake|47
 47Dad47|31
 Churm|28
 Rocket Girl|28
 DadWrap|26
 n2books2|23
 rivercity|21
 Salmon Falls Widow|21

Then you can acually use geoqo itself to break down it further into what names some used:

 > geoqo -s 'cache:owner_name==Marky' -d top10:groundspeak_placed_by
 Top 10 'groundspeak_placed_by's
  Num    % Value
 ----------------------------------------------------------------------
   74   82 Marky & Joani
    9   10 Marky
    2    2 Marky & Joani & Woof!
    2    2 Marky & Fizzy
    1    1 Punsmann (Adopted by Marky)
    1    1 Friends of LAH (motogrrl)
    1    1 Shane & Pepper (Maintained by Marky & Joani)


And for snowwake:

 > geoqo -s 'cache:owner_name==SnoWake' -d top10:groundspeak_placed_by
 Top 10 'groundspeak_placed_by's
  Num    % Value
 ----------------------------------------------------------------------
   35   74 SnoWake -n- Dingo
    2    4 SnoWake -n- Rexy, and Dingo too!
    2    4 SnoWake, Dingo and Panza
    1    2 SnoWake -n- Dingo, and Panza too!
    1    2 SnoWake, DIngo -n- Panza
    1    2 SnoWake (sans Dingo)
    1    2 Dingo (with a little help from SnoWake -n- Rexy)
    1    2 andleis' minions
    1    2 SnoWake, Free Heeler, Dingo -n- Panza
    1    2 SnoWake, Dingo, Panza and an Alien Bunny
    1    2 SnoWake -n- Free Heeler, Panza -n- Dingo

Tips and Tricks

Personal tools