Ayefon.com

iGeoCacher SQL User Guide

This guide won't make you an SQL(Structured Query Language) genius but it should go a long way towards helping you write some of your own Ad Hoc queries.

The heart of this facility in iGeoCacher is the SELECT statment. It is pretty simple. Every query you write will generally begin as follows:

select * from geocaches

This in essence says "select all columns from the geocaches table. Now at the moment that's the only table there is and for ad hoc queries you must select all columns due to the way I've written it so every select query will begin this way.

Now this will return every row in the table (every cache you've loaded.) That's probably not very interesting. The whole point is to be able to select just the caches you want. You do this with a WHERE clause. So let's say I want to show just the caches in group 2. That would look like this:

select * from geocaches where grp=2

You can use boolean operators in a where clause. To get the caches from groups 2 and 4 I could write:

select * from geocaches where grp=2 or grp=4

Well that's easy enough. But how come the caches come out in seemingly random order? I want them sorted by name.

select * from geocaches where grp=2 or grp=4 order by name

What if I want them sorted by group and then name?

select * from geocaches where grp=2 or grp=4 order by grp,name

How about that same thing in reverse name order?

select * from geocaches where grp=2 or grp=4
order by grp,name desc

The "desc" keyword after an order by column means "in descending order"

By now you are getting the pattern. We need one more thing. Wildcards. You are used to using the "*" and "?" in file names as wildcards. This corresponds to "%" and "_" in SQL. The first matches any string and the second matches any character.

So let's say I wanted to find all the caches placed by NCMountie (one of the more prolific geocachers in my area.) Knowing that the name field in all his caches contains the string of characters "by NCMountie" in it somewhere I can do this:

select * from geocaches where name like '%by NCMountie%'

Notice the change from "=" to "like" and the single quotes. In general you use "like" with wildcarded values and you put quotes around string values. In the previous examples grp is an integer so quotes aren't required. However it works if you provide them so in general you are safe to use single quotes around all values. Around character fields they are required generally.

 

Schema Info

Most of the fields in the iGeoCacher geocaches table are character fields (varchar): cacheid, lat, lon, type, ctype, name, sdesc, link, hint, container, findstatus

Integer fields: grp, loadorder, pk

text fields: ldesc, logs, notes

Column Name Description
cacheid This is the GC id. (i.e. GC120WT)
name This what you would call the title. (i.e "King of the Hill by honeychile & Bartacus")
sdesc The short description.
ldesc The long description.
type This is the cache type (Traditional, Unknown, Multi-cache, etc.)
hint You know what this is - the hint.
grp The iGeoCacher group assignment (not part of GC gpx)
container Regular, Small, Micro, etc.
findstatus Found or null (note that this is iGeoCacher found info that you have specified. GC find status isn't in GPX files.)
cachenotes notes that you have entered in the edit detail screen.
logs The downloaded logs in one large html text string.
pk Primary key. Probably not that useful for queries as it is just an automatically increasing integer for each row as it is loaded.

Example Queries

Give me all of the caches with difficulties of 1,1.5 or 2 that I have not found.

Strategy: Knowing that the difficulty appears in the cache name with a leading parenthesis and a following slash, I use the wild cards thus:

select * from geocaches
where (name like '%(1/%'
or name like '%(1.5/%'
or name like '%(2/%')
and length(findstatus) = 0

Note that to check for the absence of a character value like findstatus you check to see that the length is 0.

The same query to see just my Found caches that met all of the other criteria would be:

select * from geocaches
where (name like '%(1/%'
or name like '%(1.5/%'
or name like '%(2/%')
and findstatus = 'Found'

Case counts so "Found" is not the same as "found."

Finally, note the parentheses. Logically, AND operations are done first then OR so the parentheses are necessary to ensure that the difficulties are all ORed together before the AND is applied. Without the parentheses I would be asking "Give me all the caches with difficulties of 1 and all the caches with difficulties of 1.5. Then add to that those that have a difficulty of 2 combined with a found status of "Found."

If you have nothing but AND operations or nothing but OR operations then you won't need parentheses. It's only when you mix them that you have to be careful.

That was complex, how about something simple like "Show me just the multi-caches in my collection."

select * from geocaches where type like '%ulti%'

Wait, was that a typo? Did I leave out the "M?" No, I'm being cagey. If the type was "Multi-cache" that wouldn't be the same as 'multi-cache.' Depending on where your GPX file came from you might or might not get a captitalized first letter but the "ulti" should always be there and is sufficiently specific to give me what I want.

Learning to use a query language in the absence of hard standards can be tricky. You have to be creative. It's not unlike what you do to get the Google search you want. If you get noting, back off until you get something then refine.