Release 0.7.7

This update includes the following:

• Enhancement – Replaced cache name search with Lucene.Net library for optimized full text search to improve response time
• Enhancement – Added white outlines to Map cache type icons so they show up better
• Bug – Corrected coordinates were being overwritten by listed coordinates during pocket query processing
• Bug – Desktop Map search results containing quotes or apostrophes didn’t work

Georg BCaching Connector 0.9.2

Version 0.9.2 is now on the Android Market.

Configuring

1. Start BCaching connector to register with GeOrg
2. Go to GeOrg Settings and choose “Connector:…”, then select “BCaching Connector”
3. Go to GeOrg Map view, choose Menu => Source, then select “BCaching Connector”

New features (requires GeOrg 1.1.8 or later):

• Will now show both found and unfound hides. Use Georg Filter to select only found or only not-found
• Both caches and waypoints now shown on map.
• Show online now links to bcaching.com cache page
• New connector action “Download all” will batch download up to 30 nearby caches.

Release 0.7.6

A somewhat minor update, with a focus on batch GPX file processing performance.

– Added geolocation to desktop map view, for supported browsers, with a “Find Me” button. Note you may need to reload the page to enable it.
– Further optimization of batch .GPX file processing, including reorganization of some database indexes, and new counters for future performance analysis.

Spatial Indexing

Release 0.7.5 included a major change to how spatial data is indexed and queried. This post is for those of you that have some database background and are interested in the details.

Cache Table
BCaching uses a shared Cache table where data loaded from pocket queries is stored. This data includes CacheID, Latitude, Longitude, Waypoint, Name, Terrain, Difficulty, Archived, and so on. Previously there was also a spatial 2D Point column named “Location” that represented the Latitude and Longitude of each Cache. There was a spatial index on Location which allowed location-based queries to quickly identify a list of caches in a requested region.

CacherCacheAccess Table
Per groundspeak rules, cachers are only allowed access to caches they have uploaded themselves. Therefore there is also a CacherCacheAccess table which holds a record for every cacher and cache they have access to. The CacherCacheAccess table is also a convenient place to store cacher-specific attributes such as a FoundIt flag, IgnoreIt flag, private corrected coordinates, and some other things.

So every Cache query, location-based or not, needs to join to CacherCacheAccess to make sure the requesting cacher has access. Most queries also include filters to exclude archived caches and possibly found or ignored caches.

The Problem
Since a spatial index cannot include any other attributes besides a spatial column, every location-based query will identify all caches in a region, THEN filter them based on the other attributes like archived, cacherid, foundit, ignoreit, etc.

If a cacher is in a region that has lots of caches he’s already found and he wants to see the nearest unfound caches, the database will have to take the time to needlessly identify possibly hundreds of caches to join to CacherCacheAccess before filtering them back out again.

It sure would be nice if we could have an index that includes those other attributes as well as the spatial data.

Enter geocells.
A geocell coordinate system divides the world up into rectangular cells that are each represented by a hexadecimal digit. Each cell is then subdivided again and is represented by the next digit. Each subsequent digit represents an increasingly granular region. See the geocell link for more nitty gritty details.

For example, Groundspeak Headquarters (GCK25B) has posted coordinates of N47°38.000 W122°20.000. That location has a corresponding geocell of “a44b8aa9f4313” at 13-digit accuracy which is a range of about 2 feet. The shorter string “a44b8a” represents a 6 mile geocell that also includes that point.

Since geocells are represented text strings, they can be indexed as a text column along with other attributes. Searching a particular region simply requires identifying a short list of geocells that contain that region, and those geocells can be included along with the other criteria.

So the spatial column and index on the Cache table is now gone and a copy of the Cache Latitude, Longitude, and Geocell is kept in the CacherCacheAccess table where it can be overridden by each cacher with alternate corrected coordinates. Finally, there is a composite index used for proximity searches which includes CacherID, FoundIt, IgnoreIt, and Geocell.

Now each proximity query can use a single index to identify a much smaller set of candidate records before applying a join and additional criteria.

Have you noticed any improvements in performance? The Map and Nearest functions have been noticeably faster for me.

It’s only been 3 days, but the data looks good so far. The average proximity search response times on the desktop map view 2 weeks prior to the release was 2.3 seconds and after was 1.1 seconds.

That may not sound like much, but when queries are taking less CPU, less memory, and fewer disk reads, that frees up resources for other requests and more users.

Release 0.7.5

This release includes a major internal change to how spatial data is stored and queried along with a few bugfixes and enhancements. See the forums for full list of changes.

BCaching Stats July 2010

The past few months have seen explosive growth due largely to mobile apps on the Android platform, and GeoBeagle in particular.

* Over 1000 users uploaded one or more PQs in the past 30 days
* 567 PQs were processed per day on average in the month of July.

Site traffic is still increasing month to month
* Feb 2010: 69,064 hits
* Mar 2010: 94,328 hits
* Apr 2010: 143,208 hits
* May 2010: 168,026 hits
* June 2010: 285,600 hits
* July 2010: 377,313 hits

API requests from mobile apps break down like this for July (excluding login requests, error responses)
* GeoBeagle: 35,998 requests from 569 users
* GeoHunter: 13,770 requests from 122 users
* OpenGPX: 1,522 requests from 70 users
* Blackstar: 897 requests from 130 users
* GeOrg: 684 requests from 27 users

Database is now 5.7 GB with 1,015,261 caches (including 97,245 archived) and over 14 million logs!

A minor release was posted Sunday night to address a few issues.

This past week groundspeak updated the geocaching site and broke a couple key bcaching features:
1. ALL email addresses used on the geocaching site have to be validated now and there was no way to see messages received at your bcaching account in order to click on the validation URL. So there is now a new Email Messages page that allows you to view recent messages sent to your bcaching email address. See this post for more details.

2. Field note processing was tweaked a little here and there and no longer supports multi-line notes in unicode format. So field note files are now being saved in utf-8 format and that seems to have resolved the problem.

The bcaching mobile API synchronization process was also improved slightly which should result in faster response times when requesting synchronization updates.

Finally, the migration from shared hosting to the VPS was stalled for a little while but is back on track. Hopefully that will get wrapped up in a few weeks.

Release 0.7.2

The latest release includes GCVote integration, quality scores, and custom labels to help select caches of particular interest. All new attributes are visible in summary and detail views.

Create your custom label names on the bcaching profile page.

See here for full details.

BCaching Stats June 2010

Still growing. Especially with usage from the latest mobile apps

* 546 users uploaded 2 or more PQs and visited the site in the past 30 days.
* Over 400 PQs were processed per day on average over the past few weeks.

Overall site traffic is increasing.
* Feb 2010: 69,064 hits
* Mar 2010: 94,328 hits
* Apr 2010: 143,208 hits
* May 2010: 168,026 hits
* June 2010: 258,572 hits (partial)

There is a lot of activity coming from mobile apps these days, with the majority from GeoBeagle (over 500 users). GeoHunter, Blackstar, and OpenGPX all appear to have in the 100 – 200 user range, and GeOrg around 50 users. One of these days I’ll try to nail down more detailed stats about them.

Database size is about 4.4 GB with over 850 thousand caches (which includes 70,000 archived) and over 11 million cache logs.

What about site response time? Not so good… Only 77% of requests were under 3 seconds. Another 18% of requests took between 3 and 15 seconds (too long) and 5% of requests took a whopping 65 seconds on average!! It appears that many of the slower responses were for mobile app synchronization API requests and not so much the main application but clearly there needs to be some improvements.

The past several weeks have been spent on some new features, a few bug fixes, and some performance improvements. Since moving the database to a dedicate server, it is now possible to record exactly which queries take too long and that helps to bring the problem areas into focus. Unfortunately though it will still be a few weeks yet before the next release.

We are going to be vacationing in Seattle, WA for this year’s GeoWoodstock VIII MegaEvent over the next few weeks and plan to put the new release through its paces during the trip. If all goes well there will be a new release shortly after our return.

If you’re also planning to attend the event, maybe we’ll see you there. We’ll even have a couple bcaching t-shirts to give away (only two, talk about limited supplies!). If you’d like to purchase a bcaching t-shirt or some other merchandise, we’ve set up a cafepress store here: http://www.cafepress.com/bcaching.

Keep on caching.

Growing Pains

BCaching continues to grow and the native mobile apps department is taking off, at least for Android devices.

This week, both GeoHunter and OpenGPX published new versions that integrate with bcaching to retrieve geocache data. GeoBeagle is also planning integration in the near future.

The database is still growing and unfortunately is now banging on the ceiling of the maximum disk space available with the current hosting plan (3 GB). Since a plan upgrade is unavoidable in the near future, a choice had to be made about which plan would be best. We could purchase additional disk space on the current shared hosting plan or upgrade to a higher tier shared hosting plan with additional disk space.

There is another problem that bcaching has been encountering of late, and that is slow database query response times or time-out errors. This could in theory be due to excessive bcaching use, but based on the logs and the errors it doesn’t appear to have much to do with bcaching activity. The current belief is that it is due to being on a shared hosting plan and we are at the mercy of other applications on the same servers that are not playing nice with the given resources.

The best option for the long run therefore appears to be to migrate to a dedicated hosting service, namely a virtual private server, where the database server will be for bcaching alone and not shared with any other applications. It is more expensive but should be worth it for the headaches it saves. It also offers about 20 GB of free space which should be a decent amount of breathing room.

The process of setting up the new environment is under way and will hopefully be completed within a week or two. The first stage will be to move the database to the new server and the web site will have to be brought down for an “extended” period while this is completed. Hopefully the down time will be less than an hour.

Stay tuned.

BCaching Stats April 2010

Wow. Last month saw a significant increase in membership and site use.

* 358 users uploaded 2 or more PQs in the past 30 days. A 57% increase from a month ago.
* Over 250 PQs are processed per day (was 190 a month ago).

Some new stats:
* 145 Blackstar Navigation app (Blackberry) users requesting nearest caches
* 58 GeOrg app (Android) users viewing and downloading nearby caches

Overall site traffic is increasing.
* Feb 2010: 69,064 hits
* Mar 2010: 94,328 hits
* Apr 2010: 110,413 hits (only 3/4 into the month)

With that in mind, how are the response times?
– 95% of the requests took under 3 seconds with 87% at an average of 0.25 seconds. Not bad
– 4% took under 10 seconds
– 1% took over 10 seconds

Database size is about 2.4 GB with just under 500,000 caches and just under 7,000,000 cache logs.