Here is the list of my qualifying finds for the Forgotten Cache Challenge. I qualify twice over—that is, I have at least two finds for each of the "lonely months". I have two 10-month finds, and more on all the rest of the 1-12 lonely months. I have picked two finds to list for months 1-9 and have listed all my finds over 9 months, including my 13 "bonus" finds. The 50-month find is a bit soft: the cache wasn't there, but the owner was there and told me to log a find. But my 31-month find is absolutely solid.
A description of how I gathered this information is below the table.
|Cache Name||Lonely Months||Date I found||Date previously found|
|Solar System Tour||50||01/21/2011||11/10/2006|
|Gnarly, Kidney Buster Cache||23||10/26/2008||11/12/2006|
|TWO THE TOP, 2||21||11/30/2008||02/24/2007|
|Top Of The Mountain||18||01/23/2011||07/18/2009|
|Hidden Pond Trail||14||10/17/2007||08/26/2006|
|Night to Remember and Then Some||12||01/21/2008||01/25/2007|
|4 islands view||12||03/15/2008||03/06/2007|
|The animal box||11||06/29/2008||07/01/2007|
|Santa Monica Mtns History Adventure||11||07/25/2009||08/18/2008|
|Red Rock Roost||11||02/18/2008||03/10/2007|
|Roadside Oak Rest||10||04/02/2008||06/24/2007|
|Gordon's Power Work Out Cache||9||06/11/2008||09/07/2007|
|Mabry Park East||5||09/09/2011||04/10/2011|
|Diamond in the Rough Returns||5||02/27/2008||09/08/2007|
|PALISADES HIGH POINT||4||03/30/2008||11/17/2007|
|Sweet Smell Spot||3||01/13/2011||10/29/2010|
|tozainamboku's Pacific View||2||11/29/2009||09/05/2009|
|Which way the Fault Goes?||1||05/25/2009||04/11/2009|
Creating the table, that is. If you're reading this, then you probably know how I found the caches.
The easy way is to use my Lonely Finds macro in GSAK. Please provide feedback in the GSAK Macro Support forum. (You still have to do steps 1-3 of The Original Way. You need to read the notes in the macro source, which explain this.)
In early March 2012, GroundSpeak reduced the speed at which logs can be retrieved. (This was due to abuse by an unrelated macro.) As a result, it now takes roughly nine times as long as before to retrieve all your logs. Those with many finds could be looking at a couple of weeks, so it's advisable to figure out a way to break it up. As of April 1 (no fool), GSAK and GroundSpeak are investigating how to provide the "get logs" functionality with less load on the GroundSpeak web servers, so I'm not going to try to code a work-around until things settle down.
The keys are GSAK version 8 with direct geocaching.com access, and SQLite Spy to access the GSAK databases directly instead of using macros. I could have done this with GSAK v7 by saving the complete pages of each cache I'd found and then analyzing the saved HTML with a macro, or by accessing the pages directly but counter to gc.com terms and conditions. I could have done it even in GSAK v6 by writing a macro to walk the caches and logs. But direct access and SQL make it so much easier.
This is somewhat technical. If you are comfortable working with GSAK, you should have no trouble following the procedure. This could be made into a fully functional GSAK macro with a lot less user action needed, but since this is only for occasional use, I doubt it's worth the time. But if I hear that a lot of people are using this, it might be worth the time.
Feel free to with questions or comments! I do want to know how (and whether) people are using this.
Step 1: install GSAK version 8 and SQLiteSpy. Note that SQLiteSpy does not install in the normal sense. You just download a zip file, and either run the program from inside the zip file or save the .exe file wherever you want to run it from. (I've heard from one person that my link for SQLiteSpy didn't work for them, even though it worked for me. You can find it other places, such as CNET. Just make sure you get it from a reliable site and that you have version 1.9.1 or higher. Earlier versions don't work with GSAK v8.)
Step 2: If you don't already have an up to date GSAK database of your finds, make one. Create a new GSAK database if necessary. Run the My Finds PQ and load it into that DB. (If you are using GSAK direct access to PQ results, note that the My Finds PQ cannot be downloaded that way as of October 2011. It just doesn't show up. You must download it from the web page or get it by email.)
Step 3: In GSAK, with your My Finds database open, do "geocaching.com access -> Get Logs". If this is your first time using direct access, you'll need to log on via GSAK. Pick "All in current filter" and set "Max logs per cache" to 99999. Click OK. If you've found more than a few dozen caches, go for coffee. Getting logs does not count against your direct access limits, but the rate is throttled and amounts to about 500 logs per minute, so if you've found thousands of caches, it will take a while—one user with 7200 finds reports that it took 20 hours. (If you are doing an update run, it's worth setting a filter to include just the caches you've found since the last time you did this "get all logs". After all, logs made to caches you've already analyzed are not relevant to the challenge unless they are "late logs", log date changes, log deletions, or other unusual events which invalidate your "lonely months" claim.)
Step 4: Run SQLiteSpy and open your My Finds database. Specifically, do File->Open Database (not ctrl-O, which does an Open SQL), navigate to the GSAK database folder, open the "my finds" folder. In that folder is a file called sqlite.db3. That's the database. Open it. (The GSAK database folder location is configured in GSAK in Tools->Options->General, Database Folder.)
Note: if you allowed GSAK to install files in the default location, then the database folder is probably inside AppData. This is normally a hidden folder, causing problems navigating to it normally. You may be able to get around this by copying the Database Folder location from the configuration in GSAK, pasting it into the File Name box of the Open Database dialog, hitting enter, and navigating from there. Or in Windows Explorer, do Tools->Folder Options, View, "Show hidden files and folders". Or find a young kid to do it for you.
Step 5: Copy and paste the following SQL code in the upper right window:
select myc.name 'Cache name',myl.lparent 'GC code',myl.ldate 'Date found',
max(other.ldate) 'Date previously found',
from (caches myc join logs myl on myc.code = myl.lparent)
join logs other on myl.lparent=other.lparent and myl.ldate>other.ldate
where myl.ltype='Found it' and myl.lby like 'paleolith' /* <<<<<<<<<<<<<<<<< change the ID */
and (other.ltype='Found it' or other.ltype='Publish Listing' or other.ltype='Webcam Photo Taken')
group by code,myl.ldate
order by LonelyMonths desc, name;
Change "paleolith" to your gc.com ID. Make sure to enter it exactly, including spaces and punctuation, though it's not case-sensitive. Then press F9. This produces a table with the information you see in my table, plus the GC number. This table includes all your finds, even the zero-lonely-month finds, with some exceptions noted below under "limitations".
Step 6: You can simply use that table directly. If you want to take it further, click anywhere in the table, then Select All, then Copy. (It looks like only one column is selected, but in fact this copies the entire table.) You can paste into various programs. To generate a table like mine, open a new spreadsheet in Excel and Paste. This should fill columns A through E, with headings in row 1.
Note: It appears that versions of Excel and/or Windows vary in whether they paste the column headings. I'm using Excel 2002 under Windows Vista, and the column headings are pasted, with formatting. Alan reports that Excel 2010 under Windows 7 does not paste the column headings. If you find no column headings after pasting into Excel, insert a line if needed so that the first cache is on row 2. This is only necessary if you plan to use the formula below; otherwise you can choose to operate without column headings if you prefer. If you want to add column headings, the labels I use are Cache name,GC code,Date found,Date previously found,LonelyMonths.
In cell F2 (the top of the first empty column, below the heading line), paste the following formula:
Fill this down the column through the last row. You can now copy the cells in this column and paste them into a web page as the rows of a table, as I did for the table above. You'll have to build the web page structure and the table element (though you are welcome to copy that outline from this page if you want).
And if you're good with Excel, you can transform the data in other ways.
Step 7: If you want to create a bookmark list of your qualifying finds, first create the bookmark list using the gc.com web site. Then go back to GSAK, check the User Flag on the ones you want in the list, set the User Flag filter, and do Geocaching.com access -> Add to bookmark list. Select the list, check All in current filter, and OK.
The limitations are minor, but some of them did confuse me, so I'll list them here.
select myc.name 'Cache name',myl.lparent 'GC code',myl.ldate 'log date'
from (caches myc join logs myl on myc.code = myl.lparent)
left join logs other on myl.lparent=other.lparent and myl.ldate>other.ldate
where myl.ltype='Found it' and myl.ldate<'2005-08-01' and myl.lby like 'paleolith'
and other.ltype is null
order by name;