Page 2 of 2
Re: What's Next?
Posted: November 10th, 2013, 10:39 pm
by Corfman Clan
Just for grins I took a look at how many caches that would be. It was more than I figured:
Code: Select all
Delaware: 2,733
Maryland: 7,536
New Jersey: 15,209
New York: 28,479
Pennsylvania: 35,281
Virginia: 15,085
Washington DC: 174
West Virginia: 5,715
Total: 110,212
Re: What's Next?
Posted: November 13th, 2013, 7:38 pm
by Corfmania
Hmmm. That's less than California!
Re: What's Next?
Posted: December 5th, 2013, 12:57 pm
by Corfman Clan
Corfman Clan wrote:rocketsciguy wrote:What else is on my wish list? In no particular order
- I hate asking for additional capabilities on the Nearby page because that's already a very computationally intensive, slow-loading page. I don't know how it's currently done, but there might be a way to do some indexing to speed it up by dividing the territory into zones and filtering the query behind this page to only return caches within the center point's zone and as many adjacent zones necessary to capture the search radius. Once those filtered results are returned, then compute great circle distance and sort. The zones would be an extra field in the Geocache table where the coords are stored. If this is of interest, I have suggestions about how to implement these "zones" that I can share.
How's that for starters?
I meant to address this suggestion before but missed it. As suggested, indexing can speed up this sort of operation dramatically and we are already utilizing a
spatial index for finding nearby geocaches.
So I've started looking at implementing something similar to DGP's "My DGP Most Wanted" and realized that the Nearby query doesn't actually take advantage of the spatial index.
Currently it just calculates the distance to most caches then selects the nearest 100. I've been playing with modifying the query to take advantage of the index and the results look promising.
For you geeks it's actually fairly simple:
Code: Select all
-- Set @Center to the cache location
SELECT @Center = Location FROM Geocache WHERE ID = @Cache
-- Set @Area to a circle around the cache with a 50K radius
set @Area = @Center.STBuffer(50000)
-- Add the following to the nearby cache selection where clause
AND g.Location.STIntersects(@Area) = 1
This speeds things up because now the distances from the cache to the nearby caches only needs to be calculated for caches that fall inside the circle (perhaps a few thousand) instead of for most caches in LonelyCache (currently close to 120,000).
Things could be speeded up more by decreasing the size of the circle. How far from a cache should we go before it's not considered nearby, 10, 20, 31 miles?
Re: What's Next?
Posted: December 13th, 2013, 6:40 pm
by Team Opjim
I concur on Wyoming, Idaho being added.
Re: What's Next?
Posted: January 14th, 2014, 9:44 pm
by Corfman Clan
Since we've solved the issue with the nightly update of the leader boards and statistics taking a long time to occur and sometimes failing, we've been able to entertain the idea of expansion. So after an initial discussion with the web hosting company, we've been given the go ahead to add Wyoming and Montana.
So as of today, I've started the process of adding Wyoming to LonelyCache. I'm not sure of Montana at this time. I'd like to see how things go with Wyoming first. So far, most of the caches have been added to the database and well over half of them have been updated to include all finds. I expect all caches to be added and updated sometime tomorrow.
Brother, I also mentioned to the web hosting company that you would like a mid atlantic LonelyCache and if we get the okay to expand that much, then we'll need some more premium member accounts to manage that. I do have a feeling our current member base would prefer we expand to the west first though.
We have yet to add the county definitions for Wyoming but hope to have that complete in the next week or so. On that line, I have no plans to create comparable DGP Regions for Wyoming, though I'm not against someone investigating that.
