Yamar at 20:14, 2 April 2007

2007-04-02T20:14:24Z

← Older revision		Revision as of 20:14, 2 April 2007
Line 60:		Line 60:
	1 2 SnoWake, Dingo, Panza and an Alien Bunny		1 2 SnoWake, Dingo, Panza and an Alien Bunny
	1 2 SnoWake -n- Free Heeler, Panza -n- Dingo		1 2 SnoWake -n- Free Heeler, Panza -n- Dingo
		+
		+	{{TRICKS}}

Yamar at 20:14, 2 April 2007

2007-04-02T20:14:08Z

New page

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

Using SQLite to do your own analysis - Revision history

Yamar at 20:14, 2 April 2007

Yamar at 20:14, 2 April 2007