LonelyCache

Just another quick update. I was able to figure out how to update the Leaderboard query to include not only the total points, but also the hide/find points. This project is stretching my SQL capabilities to the max. I suppose that is a good thing

Also, since I'm not interested in world domination (or am I ), I updated the DB to be LonelyCache Wide instead of WorldWide.
Again, for those into this kind of thing , here's the SQL query for the above pic, another small glimpse into the LonelyCache database schema:

Corfman Clan wrote:Again, for those into this kind of thing ,

Sorry, did I hear someone say my name? My ears are burning.

Corfman Clan wrote: here's the SQL query for the above pic, another small glimpse into the LonelyCache database schema:

Code: Select all

SELECT Row,Ranking,CacherID,Cacher,Total
      ,(select sum(OverallPoints)from GeocacherPoints
        Where CacherID=r.CacherID and job=1 and RegionID = 1) FindsTotal
      ,(select sum(OverallPoints)from GeocacherPoints
        Where CacherID=r.CacherID and job=2 and RegionID = 1) HidesTotal
 FROM
 (SELECT
   ROW_NUMBER() OVER (ORDER BY Total DESC, g.Name) AS Row
  ,RANK() OVER (ORDER BY Total DESC) AS Ranking
  ,CacherID, g.Name as Cacher, Total
  FROM
 (SELECT CacherID, SUM(g.OverallPoints) AS Total
  FROM dbo.GeocacherPoints g
  WHERE regionid = 1 AND Job IN (1,2)
  GROUP BY CacherID) s
JOIN dbo.Geocacher g ON g.ID = CacherID) r
WHERE Row between 1 and 100
ORDER BY Row

That is a nice display of SQL power, right there! Multiple queries within queries, and not all that intuitive how you had to do it. I hope you had to experiment and build it piece at a time, and weren't able to crank that out off the top of your head! I do have some questions which I will create a new topic for.

It's hard to believe that we've been working on this for about five months now. This has turned out to be quite an undertaking. Thank you for your patience and continued support!

Since the last status update, we've improved upon the leaderboard pages. We've added pages displaying a list of caches by rank for whatever the leaderboard. In addition, we've started work on showing a cacher's standings in the various leaderboards.

Right now, we're working towards completing what we feel is needed to apply for the Geocaching.com access keys for the production API. That will allow us to move off of the staging DB and start gathering up to date data.

WOOHOO!!! sounds like we are getting close...just in time for part of the summer caching season...

Thanks for all that you have done and continue to do! Five months is nothing when we keep our eyes on the end product.

Corfman Clan wrote:It's hard to believe that we've been working on this for about five months now.

Has it really been that long. Maybe it's just me that this year is going by so fast. It just seems to me that January was only a few short weeks ago.

Thanks again for all the hard work up to this point.

Corfman Clan wrote:It's hard to believe that we've been working on this for about five months now. This has turned out to be quite an undertaking. Thank you for your patience and continued support!

Wow it's June already!

I know the canines can't Thank You enough for all the hard work everybody is putting into this project but know for sure we really appreciate it.

Corfman Clan wrote:Right now, we're working towards completing what we feel is needed to apply for the Geocaching.com access keys for the production API. That will allow us to move off of the staging DB and start gathering up to date data.

This is great news! Any chance of a getting a sneak peak of the website running off the static snapshot database? You know, for beta testing?

Boy I wish I could get there out more -- I'm going to show up pretty low on the stats! But DGP motivated me to go for some high CP caches, so I expect LCP will do the same. Looking forward to it!

rocketsciguy wrote:This is great news! Any chance of a getting a sneak peak of the website running off the static snapshot database? You know, for beta testing?

Yes, we are looking forward to that. Last week, I was able to get an account set up for a small group of people to use (beta testers) to access the test website. We're not quite ready yet, but it should be soon.

Corfman Clan wrote: Yes, we are looking forward to that. Last week, I was able to get an account set up for a small group of people to use (beta testers) to access the test website. We're not quite ready yet, but it should be soon.

Is there a sign up list?

pick me...Pick Me ...PICK ME!!!!...Pleaaaaaaase

Gosh I am getting all jittery thinking about CP's and BC caches and leaderboards and roatrips....maybe its just all the coffee this morning...
Thanks for the good feelings!

First post ive done here...just wanted to say a big thank you...i glance at this site about twice a day...thought it time to say BIG THANKS!!!!

The June 2 news was great. We didn't realize until it was no longer available how important DGP was to our geocaching interests. So when AZtech passed to us that lonely cache was in development, we were beyond elated. We would definitely be willing to sponsor a premium membership for Groundspeak if it helps. For something as good as what you are developing, we wouldn't see it as unreasonable to impose an annual membership to help defray your costs. Bravo Zulu, Bravo Zulu, Bravo Zulu!!!

I'm happy with the changes we've made the last couple weeks. I've mentioned some of them in other threads but I'll comment on all of them here:

• We added the ability to track a cache owner's top finders. Along with this, we can enumerate who a cacher is a top finder for (what DGP called Owner Accolades). See here for more details.
• We refactored the way the leaderboards were being stored and computed in the DB. This change will improve website performance. See here for more details.
• The back country leader boards are now being created.

Near the end of April I mentioned that I was hoping we would be able to go live around mid June. Unfortunately, that is not going to happen. We are getting closer though. This past week we applied for the keys to access Geocaching.com's production database. Hopefully we'll receive them this coming week. Once we receive them, we will be able to retrieve and use the up to date Geocaching data.

Just Awesome, can't wait.....

This is great news. Thank you CC.

I can't wait either

Since I'll be leaving on vacation tomorrow morning and will not have access to the internet while I'm gone, I will give a quick status update now.

On July 19th, we received the keys to access the Geocaching.com production Live API. We moved the site from the staging API to the production API that day. So now the site is accessing the "Live" API and updating the database with active geocaching data. So far the site is about 45% up to date.

Once we received the keys, we created four geocaching.com accounts and through your generosity, all four of those accounts are now premium member accounts. Thank you very much to all of you that gifted a premium membership and thank you to everyone that was ready and willing to do the same.

Since the site went live, little work has been done with the GUI. Instead we have been monitoring the site logs and have been making updates to get the site to run as error free as we can and for it to properly handle any errors that do arise. It's taken about a week, but as of today, the site seems to be running pretty smoothly.

Again, thank you for your generosity, your continued patience, and your support for this project. It is truly appreciated!

VACATION! Enjoy grab a cache if you get close enough.

have fun but hurry back

I say you deserve a vacation just for all the hard work that has been put into the coding and formatting of the new site.

A lot has happened since the last update. I saw a bunch of monkeys, iguanas, caimans, and crocodiles, drank Costa Rican beer, and even snuck across the Nicaraguan border for a minute or so

Since I've been back, I've implemted the changes described here so that there will be no site downtime when the statistics are being updated.

The site is continuing to get up to date and is currently about 85% of the way there. The way the web server works is that it will tend to stop and start the website depending on its use. Since the site wasn't used much while I was on holiday it didn't update the cache data as much as I had hoped.

Yesterday, the site started to get a bunch of errors I'd never seen before. I think Groundspeak changed something and introduced a bug. Basically, the site would periodically receive a geocache log that had a null value for the log type (found it, DNF, etc.). The code didn't account for a null log type and an exception would be thrown. Anyway, LonelyCache now handles that situation gracefully.

My plan is to give those of you that have expressed an interesting in helping, access to the test site sometime Sunday. It may be late. There currently is a lot of deficiencies with several of the pages and especially the geocacher stats page. I'll take care of some of that this weekend. At any rate, I'll let you in sometime Sunday. I'll have the details spelled out when we start.