Welcome guest, is this your first visit? Click the "Create Account" button now to join.
Page 1 of 2 12 LastLast
Results 1 to 10 of 20
  1. #1
    Master

    Data in SQL (.db) filesData in SQL (.db) files GPSFranz's Avatar
    Join Date
    Sep 2010
    Location
    Germany
    Age
    39
    Posts
    51
    Rep Power
    78

    Guide Data in SQL (.db) files

    The SQL (.db) files contain the information that is used to display the routes of the 'Named Trails' (in BaseCamp, and in some of the newer GPS units).
    It would be nice to use the data in these files to create routes, tracks and/or waypoints for the older GPS units (and for MapSource).
    Since the SQL (.db) files are not locked, it is relatively easy to extract the data in the two relevant tables 'trails' and 'trail_segs' (trail segments).
    I started with the SQL file 010-D1215-00.db (for TOPO France v3 DOM-TOM Pro), because it is relatively small (and probably easier to figure out than the larger ones).
    When I search for 'Named Trails' in TOPO France v3 DOM-TOM Pro, only one is found ('GR G1', going roughly North-South on Guadeloupe).
    The table 'trails' lists several trails (with different IDs and names), but I guess these separate trails are part of the whole 'GR G1'.
    The table 'trail_segs' (trail segments) has the column headings dbg_line_id (??), map_id (obviously map ID), link_id (a link is possibly several nodes linked together), trail_id (IDs from table 'trails'), org_node_id (probably origin node ID), dst_node_id (probably distant node ID), org_pnt_lat (probably origin point latitude), org_pnt_lon (probably origin point longitude), dst_pnt_lat (probably distant point latitude), and dst_pnt_lon (probably distant point longitude).
    It should contain most of the interesting data (the GPS coordinates of the route - or track - nodes).
    Unfortunately, the GPS coordinates given are not absolute GPS coordinates, and it is not obvious how the Garmin people arrived at the values given in the table.
    My guess is the values given are somehow coded (or maybe relative) coordinates.
    I sorted the table 'trail_segs' (in a spreadsheet) in various ways (by the data in the different columns).
    From this it is possible to find out how some of the nodes go together to form a chain (or maybe link - a tiny part of the whole trail).
    This is as far as I got.
    Maybe someone with more experience (and/or brainpower) can take a look (and possibly figure it out)?
    Last edited by GPSFranz; 21st August 2012 at 06:28 PM.
    Oregon 450 and Mobile XT 5.00.20w on WM 6.5

    Avatar: Yellow-bellied Marmot (Marmota flaviventris - 'Gelbbauch-Murmeltier' in German).

  2.    Advertissements


  3. #2
    p!xeldealer
    Guest

    Default

    Thanks for your interesting explanations. I had already the same idea, but not the time yet to do something.

    I can't imagine, that the coordinates are relative coords. I think they are in somehow format, that must be possible to translate to any usable format.
    Could you post here an part of these data?

    I also think, it must be possible, to make a gpx file with all trails from this SQL files. From such gpx it is easily possible to make a .gpi-file or a transparent layer in .img format.

  4. #3
    Master

    Data in SQL (.db) filesData in SQL (.db) files GPSFranz's Avatar
    Join Date
    Sep 2010
    Location
    Germany
    Age
    39
    Posts
    51
    Rep Power
    78

    Default

    @p!xeldealer:
    I'll reply in detail tomorrow morning (using my phone right now).
    Oregon 450 and Mobile XT 5.00.20w on WM 6.5

    Avatar: Yellow-bellied Marmot (Marmota flaviventris - 'Gelbbauch-Murmeltier' in German).

  5. #4
    Master

    Data in SQL (.db) filesData in SQL (.db) files GPSFranz's Avatar
    Join Date
    Sep 2010
    Location
    Germany
    Age
    39
    Posts
    51
    Rep Power
    78

    Default

    Quote Originally Posted by p!xeldealer View Post
    Thanks for your interesting explanations. I had already the same idea, but not the time yet to do something.

    I can't imagine, that the coordinates are relative coords. I think they are in somehow format, that must be possible to translate to any usable format.
    Could you post here an part of these data?

    I also think, it must be possible, to make a gpx file with all trails from this SQL files. From such gpx it is easily possible to make a .gpi-file or a transparent layer in .img format.
    The SQL file 010-D1215-00.db (for TOPO France v3 DOM-TOM Pro) has 3 tables named 'trails', 'trail_segs', and 'garmin_metadata'.

    The uploaded archive (around 39 kB)

    [Only registered and activated users can see links. ]

    contains the 3 tables in comma-delimited CSV format:

    010-D1215-00trails.csv
    010-D1215-00trail_segs.csv
    010-D1215-00garmin_metadata.csv

    The CSV format can be imported into most spreadsheet programs.
    As I said, by sorting the table 'trail_segs' (in a spreadsheet) in various ways it is possible to find out how some of the nodes go together to form a chain (or maybe link - a tiny part of the whole trail).
    The big puzzle is how to get the GPS coordinates from the (integer values) org_pnt_lat (probably origin point latitude), org_pnt_lon (probably origin point longitude), dst_pnt_lat (probably distant point latitude), and dst_pnt_lon (probably distant point longitude).

    PS:
    I also found this in the SQL file 010-D1215-00.db:
    CREATE INDEX adj_index ON trail_segs (map_id ASC, link_id ASC, org_node_id ASC, dst_node_id ASC)
    CREATE INDEX trail_id_bbox ON trail_segs (trail_id ASC, org_pnt_lat ASC, org_pnt_lon ASC)
    CREATE INDEX trails_bbox on trails(min_lon ASC, max_lon ASC, min_lat ASC, max_lat ASC)
    Some guesses:
    adj_index may mean adjoint or adjunction index (see en.wikipedia.org/wiki/Adjoint)
    trail_id_bbox may mean trail_id boundary box
    trails_bbox may mean trails boundary box
    Last edited by GPSFranz; 22nd August 2012 at 11:56 AM. Reason: Revised link, added info
    Oregon 450 and Mobile XT 5.00.20w on WM 6.5

    Avatar: Yellow-bellied Marmot (Marmota flaviventris - 'Gelbbauch-Murmeltier' in German).

  6. #5
    Navigation software expert

    acleite's Avatar
    Join Date
    Dec 2009
    Location
    Brazil
    Age
    54
    Posts
    24
    Rep Power
    76

    Default

    IMHO, org_pnt is Origin Point and dst_pnt is Destination Point of the trail.
    And Lat/Lon data in csv file are in Decimal degrees. (separate the first two digits before the comma and the rest after the comma, and test)
    Then -250320992 should be the same as -25.0320992
    And so on for all coordinates.
    Checking on Google Maps, this sample point (-25.0320992,66.0638272) is in sea at a point described in the coverage area of the map you used. (TOPO France v3 DOM-TOM Pro)

    See the coverage area map:
    Code:
    Please Login or Register to see the links
    Well, it's only my humble opinion ...

  7. #6
    Master

    Data in SQL (.db) filesData in SQL (.db) files GPSFranz's Avatar
    Join Date
    Sep 2010
    Location
    Germany
    Age
    39
    Posts
    51
    Rep Power
    78

    Default

    Quote Originally Posted by acleite View Post
    IMHO, org_pnt is Origin Point and dst_pnt is Destination Point of the trail.
    And Lat/Lon data in csv file are in Decimal degrees. (separate the first two digits before the comma and the rest after the comma, and test)
    Then -250320992 should be the same as -25.0320992
    And so on for all coordinates.
    Checking on Google Maps, this sample point (-25.0320992,66.0638272) is in sea at a point described in the coverage area of the map you used. (TOPO France v3 DOM-TOM Pro)

    See the coverage area map: .....

    Well, it's only my humble opinion ...
    When you look at TOPO France v3 DOM-TOM Pro, you see that the trail GR G1 on Guadeloupe extends roughly North-South between a point named 'N2 and Alley1' (latitude 16.353294, longitude -61.751871) and a point named 'Light House1' (latitude 15.948334, longitude -61.707566).
    The integer values for latitude and longitude given in the table 'trail_segs' do not fall into this range (when adjusted according to your suggestion).
    In fact, your mentioned sample point (latitude -25.0320992, longitude 66.0638272) lies in the Indian Ocean (approximately 1900 km E of southern Madagascar). That is not where Guadeloupe is located.
    Also, the integer values for the (presumed) latitudes and longitudes given in the table 'trail_segs' can be positive as well as negative (indicating N or S latitudes and E or W longitudes).
    The solution to the (coordinate) puzzle is probably not as simple as making some decimal adjustments.
    Oregon 450 and Mobile XT 5.00.20w on WM 6.5

    Avatar: Yellow-bellied Marmot (Marmota flaviventris - 'Gelbbauch-Murmeltier' in German).

  8. #7
    Navigation software expert

    acleite's Avatar
    Join Date
    Dec 2009
    Location
    Brazil
    Age
    54
    Posts
    24
    Rep Power
    76

    Default

    You're right.
    I'll keep trying to solve this puzzle ...
    Maybe some conversion of Datum?
    Let us study more!

  9. #8
    Navigation software Moderator kunix's Avatar
    Join Date
    Sep 2011
    Location
    Belarus
    Posts
    908
    Rep Power
    438

    Default

    Can we just imagine some generic coordinate conversion formula and calculate its constants by comparing encoded and original coordinates for a few points?
    In case of a plane it's something like (Xenc,Yenc) = (X*A11+Y*A12,X*A21+Y*A22) + (B1,B2).

    Also what is the program that uses those .db files? I can't find "trails", "trail_segs" strings in the MapSource.exe.
    Got it, it's basecamp.
    Last edited by kunix; 22nd August 2012 at 05:44 PM.

  10. #9
    Master

    Data in SQL (.db) filesData in SQL (.db) files GPSFranz's Avatar
    Join Date
    Sep 2010
    Location
    Germany
    Age
    39
    Posts
    51
    Rep Power
    78

    Default

    Quote Originally Posted by kunix View Post
    Can we just imagine some generic coordinate conversion formula and calculate its constants by comparing encoded and original coordinates for a few points?
    In case of a plane it's something like (Xenc,Yenc) = (X*A11+Y*A12,X*A21+Y*A22) + (B1,B2).

    Also what is the program that uses those .db files? I can't find "trails", "trail_segs" strings in the MapSource.exe.
    Got it, it's basecamp.
    The .db files are SQLite database files containing tables of trails and trail segments (see previous posts).
    These are used by BaseCamp and some of the newer GPS units to find and display 'Named Trails' for a given map and area.
    This feature was added recently, and therefore (as far as I know) Mapsource does not support it.

    I think comparing encoded and original coordinates for a few points is a good idea.
    The tables in 010-D1215-00.db give plenty of points (I guess more than 1000) and their encoded coordinates.
    Unfortunately, the tables provide only numerical node IDs (177569, 177607, 177367, etc.) for the points (not names or anything giving a hint at their location).
    How can we find out where a given point identified only by its node ID is located?
    Maybe this will be of help:
    The table 'trails' lists several trails (with different IDs and names) which, I guess, are separate trails that are part of the whole trail commonly called 'GR G1'. Let's call them subtrails.
    This could help to narrow down the geographic area for a given subset of 'nodes'.
    The subtrails listed in the table 'trails' are (I provide trail ID and name):

    50002 GR G1
    50003 GR M1
    50004 GR MT1
    50005 GR R1
    50006 GR R2
    50007 GR R3
    50008 Le sentier Mangrove de Dapani
    50009 Le sentier Padza de Bouéni
    50010 Le sentier Vanille de Chiconi
    50011 Le sentier Ylang de Dzoumonyé

    It would be nice have a person familiar with the area here on Noeman (but I don't count on it).
    So I have started to use Google to search for the subtrail names (to find hints at their original coordinates), with limited success.
    Ideally, we would want to have .gpx files with tracks, routes and/or waypoints for these subtrails.
    Any ideas?

    PS: There is a 'subtrail' called GR G1. This is not the whole trail commonly called GR G1 (at least as far as I understand it).
    Last edited by GPSFranz; 22nd August 2012 at 06:57 PM. Reason: clarification
    Oregon 450 and Mobile XT 5.00.20w on WM 6.5

    Avatar: Yellow-bellied Marmot (Marmota flaviventris - 'Gelbbauch-Murmeltier' in German).

  11. #10
    Master

    Data in SQL (.db) filesData in SQL (.db) files GPSFranz's Avatar
    Join Date
    Sep 2010
    Location
    Germany
    Age
    39
    Posts
    51
    Rep Power
    78

    Default

    Some ideas:
    The SQL file 010-D1215-00.db was good for figuring out some basic things (the different tables, their contents, etc.).
    Its small size made it easy to handle and examine.
    Unfortunately, it seems to be difficult to get reliable original coordinate data for the 'nodes' in its tables.
    So, maybe, we should switch now to an area where reliable original coordinate information is readily available.
    Such an area would be Europe.
    Unfortunateley, the associated .db files are much larger.
    I will look into the matter and report back here.
    Oregon 450 and Mobile XT 5.00.20w on WM 6.5

    Avatar: Yellow-bellied Marmot (Marmota flaviventris - 'Gelbbauch-Murmeltier' in German).

 

 

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •