Thread: Data in SQL (.db) files

I've just tried to disassemble BaseCamp.exe with IDA and .NET Reflector. Both of them can't find SQL request strings in the disassembled code, which is strange as they are clearly visible in BaseCamp.exe. But they are able to find some other strings. One more strange thing is that the SQL request strings are one-byte-per-char while the other strings are two-bytes-per-char.
So.. I just don't understand.. Will return to it tomorrow.

UPD
In fact those SQL request strings are used by a few native functions (compiled into x86 instructions). Gonna figure out how managed functions call native functions in .NET.
Or maybe... GPSFranz, are you absolutely sure that those coordinates are different from coordinates used in other files like gpx, etc? Maybe it's worth trying creating a custom GPX file and loading it into BaseCamp and checking of it fits the named trail?

Originally Posted by kunix

I've just tried to disassemble BaseCamp.exe with IDA and .NET Reflector. Both of them can't find SQL request strings in the disassembled code, which is strange as they are clearly visible in BaseCamp.exe. But they are able to find some other strings. One more strange thing is that the SQL request strings are one-byte-per-char while the other strings are two-bytes-per-char.
So.. I just don't understand.. Will return to it tomorrow.

UPD
In fact those SQL request strings are used by a few native functions (compiled into x86 instructions). Gonna figure out how managed functions call native functions in .NET.
Or maybe... GPSFranz, are you absolutely sure that those coordinates are different from coordinates used in other files like gpx, etc? Maybe it's worth trying creating a custom GPX file and loading it into BaseCamp and checking of it fits the named trail?

I am absolutely sure that those numbers (coded coordinates) in the SQL (.db) file tables are different from original GPS coordinates used in other files like gpx, etc.
This is where I stand:
Right now I am working on putting together excerpts from the SQL (.db) file tables for Topo Austria v3 and Topo France v3 SudEst.
For these excerpts I am trying to find some of the shortest 'Named Trails' in the respective SQL (.db) files.
Since the 'nodes' are only identified by node IDs (just integers, no names or other hints given) in the SQL tables, we do not know where the node is located on a given trail (but we do know where the trail is, because BaseCamp shows it).
Therefore, in my thinking, shorter trails should have the advantage of smaller location uncertainty (by picking one or a few nodes on a short trail your margin of location error is smaller than on a long trail).
The results (data from the SQL tables and original GPS coordinates) will be provided in the form of comma-delimited CSV files, and they will be uploaded and posted ASAP after I have finished working on it.
This process is somewhat time-consuming, so please be patient...

For new readers:
We are trying to figure out how coordinate data is coded and stored in the SQL (.db) files that accompany the newer Garmin topo maps (and allow searching for 'Named Trails' in BaseCamp, and in some of the newer GPS units).

The provided information enables us to compare the coded coordinate data from the SQL files with original .gpx coordinate information (and hopefully arrive at a way to decode the coded coordinate data).
If we are successful, we will have access to the route information stored in the SQL files.
This in turn will allow us to use this route information with products (mapping software and GPS units) that cannot access the SQL file information directly.
I have put together excerpts from the SQL (.db) file tables for Topo Austria v3 and the corresponding .gpx (track) files (archive size less than 15 kB):

[Only registered and activated users can see links. ]

For these excerpts I have tried to find some of the shortest 'Named Trails' in the SQL (.db) file 010-D1199-00.db for Topo Austria v3 (see previous posts).

The files in the archive are:

010-D1199-00SelectedTrailsC8231729.csv
Excerpt from table trails providing the selected trail IDs and names and boundary information.

2 files for each named trail (file name starts with trail ID, followed by trail name):
The .csv files are excerpts from the table trail_segs (trail segments); for the data provided please see previous posts.
The .gpx files provide track data from BaseCamp 3.3.3 (tracks matching the named trails as exactly as possible).

Please see previous posts for supplementary information.
Right now I need a break.
Then I may start working on doing the same with Topo France v3 SudEst (unless the 'big coordinate puzzle' is solved before).

I hope that we will find a solution.

This is a very pleasant surprise.
When I started this thread, I was hoping we could find a solution (through a collaborative effort) within a few weeks or months (with a little bit of luck).
Fortunately, merlinboy came across the thread and figured it out single-handedly.
This saved me (and probably some others) a lot of work. Thank you very much!
The data in the SQL (.db) files are accessible now. Great!
What Garmin did makes sense.
They saw the need to store large amounts of coordinate data in a database file.
Binary is the most efficient way to do this.
It is all clear now (with 20/20 hindsight).

merlinboy, thanks for your efforts.
It looks like the Garmin people did some 'interesting' things.
In an attempt to understand what they did, I converted the Garmin binary coordinates for the Sonnblick trail and created waypoints for the 30 org_pnt/dst_pnt (origin point/distant point) pairs in BaseCamp.
The waypoints are in the following archive (around 3 kB):

[Only registered and activated users can see links. ]

They are labeled 01O through 30O for the origin points, and 01D through 30D for the distant points (origin points are green, distant points are red).
Since a hike normally starts at the trailhead, the waypoint numbering starts with 01O/01D at/near the trailhead.
Of course, all waypoints are on the Sonnblick trail.
The first surprise is that the trailhead waypoint is distant point 01D (as if you were hiking back to the trailhead; origin point 01O is around 40 m up the trail).
The distances (in m) between origin point and distant point for the waypoint pairs vary quite a bit:

Code:

Please Login or Register to see the links

Up to the 21O/21D waypoint pair, the distant point is closer to the trailhead than the origin point (as if you were hiking out).
For the 22O/22D and 23O/23D waypoint pairs, the origin point is closer to the trailhead than the distant point (as if you were hiking in).
For the 24O/24D through 28O/28D waypoint pairs, the distant point is closer to the trailhead than the origin point (as if you were hiking out).
For the 29O/29D and 30O/30D waypoint pairs, the origin point is closer to the trailhead than the distant point (as if you were hiking in).
At the moment I have no idea why Garmin did it this way.

It is possible that (for their purposes) it does not matter which point in a waypoint pair is called origin point or distant point.
When I tried to create a 'New Route' from the trailhead to the end of the Sonnblick trail, BaseCamp produced a route that was slightly different from the 'named' Sonnblick trail.
On my second attempt I added some intermediate points, and the resulting route was identical to the 'named' Sonnblick trail.
This (together with the 'switching' of origin and distant points) makes me think that the origin point/distant point pairs in the trail_segs (trail segments) table possibly only serve as via points (or 'guiding' points) for their routing algorithms (to make sure that they produce a route that follows the real 'named' trail).
Since the distances (in m) between origin point and distant point for the waypoint pairs vary so much, using only the waypoints for creating tracks or routes would lead to significant errors (deviations from the real trail coordinates).
I think only some routing algorithms in combination with the waypoints in the trail_segs (trail segments) table (and of course the trail lines from the corresponding maps) can produce accurate tracks or routes.
We have the waypoints in the trail_segs table (and the maps, of course).
How do we get the routing algorithms?

Is there a way to use BaseCamp's algorithms for our purposes?
Could we somehow get the waypoints for a given trail into BaseCamp and ask it to create the route for us?
Or, even better (because we could simply select the 'named trail' and let BaseCamp do its thing):
Since BaseCamp is creating the route (just in a different color) when you ask it to display one of the 'Named Trails', could there be a way to 'capture' this route information?
In order to be displayed, this information must exist somewhere in the system (I think).
They must create the 'named trail' route somewhere in the system. Then they probably call a 'display route' routine to display it.
(At least, that is how this non-expert sees it. Could be wrong, of course.)

After adding the following ideas to the end of my previous (and pretty long) post, I realized that the chances of them being read are probably relatively slim.
Therefore I repeat them here in a separate (and short) post:

How do we get the routing algorithms?

Is there a way to use BaseCamp's algorithms for our purposes?
Could we somehow get the waypoints for a given trail into BaseCamp and ask it to create the route for us?

Or, even better (because we could simply select the 'named trail' and let BaseCamp do its thing):
Since BaseCamp is creating the route (just in a different color) when you ask it to display one of the 'Named Trails', could there be a way to 'capture' this route information?
In order to be displayed, this information must exist somewhere in the system (I think).
They must create the 'named trail' route somewhere in the system. Then they probably call a 'display route' routine to display it.
(At least, that is how this non-expert sees it. Could be wrong, of course.)

Here is the promised summary report:

1. The SQL (.db) files contain two relevant tables: 'trails' (listing the 'Named Trails') and 'trail_segs' (listing the trail segments for these trails).

2. The table 'trail_segs' defines segments of the 'Named Trails' by endpoints given in Garmin binary coordinates.

3. The routing algorithms follow trail polylines between the segment endpoints and assemble the 'Named Trails' from the trail segments.

This may sound simple now.
However, it was quite an effort to find out how it works.
The essential discoveries were made by merlinboy.