Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2015
    Posts
    4

    Unanswered: gps tracks and waypoints solution

    Every few months, I get an excel spreadsheet which columns like:
    track name, track group number, origin, destination, notes, waypoint1, waypoint2, waypoint3, waypoint4, waypoint5 ...

    Each row is a separate track.

    I typically take this sheet and may make minor corrections (errors) in place. then will put in color coding. Since this is done manually anyways, its not a big deal

    However, I will need to create a separate sheet where Column A is a unique list of waypoints. Then the remainder of the row is a horizontal listing of all track names which go through that waypoint. This is a rather tedious and error prone process.

    Therefore, I'd like to load this data into an Access database with tables:
    track:
    track id
    track name
    track group number
    notes

    waypoint:
    waypoint id
    waypoint name
    coordinates

    routes:
    track_id
    waypoint_id
    waypoint sequence

    Then I could do something like "SELECT track_id FROM routes where waypoint_id=?"

    I can easily create the table structure in Access, however how can I easily load the data in. It seems to expect that I already have the tables in excel format. If I already had that, I would have no need for the database.

    I'm using Access 2010. I've worked with sqlite, but have never used access. I'm choosing access because my data comes in with excel and I am doing the color coding.

    I'd appreciate any advice on how I can import the data. OR, if using access is just a bad choice in this case.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Access is as good a choice as any for this sort of task
    its probably better if you need to do reports based on the data. its probably not a good solution if you wan to use the data on a mobile device such as Android or iOS.

    its pretty trivial to move data to or from Access to Spreadsheets. the only real winkle is that importing a spreadsheet into Access can cuase data type issues if the first few rows dont' have data in, or don't have the right type of data (yes you cna import if the firt row cotnains column headings, but if there is no data the import assumes the rest of the data in that column is string/text.



    to import data use the file | get external data. in more recent versions of Access its get external data.
    I generally find with excel imports that I want to import the spreadsheet as is. then validate the data and move it within Access to its eventual destinations. that way round I don't let crappy data from a spreadsheet into the db. the validation is done through queries (often checking for related data elsewhere using the missing data query wizards), or checking the data format is correct (correcting any wrong data eg a lat long as dDD MM SS to dDD.dddddd or wahtever. the final import process is doen using insert into queries or just copying and pasting

    if the data is coming from the same spreadsheet formats then save the import formats

    if the people creating the spreadsheets are ill disciplined and beggaring about with the layout then consider an custom written VBA procedure to automate the import process and do data validation whilst importing

    i
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jan 2015
    Posts
    4
    Thanks for the reply. Yes I did notice that access likes to import the spreasheet as-is. This is the issue, It's not clear how I move the data into the table structure that I want within access.

    ie.
    track name, track group number, origin, destination, notes, waypoint1, waypoint2, waypoint3, waypoint4, waypoint5 ...


    to

    waypoint:
    waypoint id
    waypoint name
    coordinates

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    either use queries to do that (usually an insert into query)

    or

    open up both tables (the spreadsheet import and the destination) and providing the columns are in the same order cut and paste rows. if you can;'t line up the column order then run a query on the spreadsheet table which does get the columns in the same order

    or use a query on the spreadsheet that imports the data as required into the table(s), using one query per destiantion table
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Jan 2015
    Posts
    4
    Quote Originally Posted by healdem View Post
    either use queries to do that (usually an insert into query)

    or

    open up both tables (the spreadsheet import and the destination) and providing the columns are in the same order cut and paste rows. if you can;'t line up the column order then run a query on the spreadsheet table which does get the columns in the same order

    or use a query on the spreadsheet that imports the data as required into the table(s), using one query per destiantion table
    The original table has a route for each row showing the list of waypoints that makeup the route in columns.

    However, the waypoint table is a unique list of waypoints
    So I have to take all the columns and rows and turn them into one column of unique names.

    But then for the routes table, I have to map that back using the column names to set the sequence.

    Also, There is alot of color coding in the excel table. I'd like to map that into "yes/no" entries but I dont see anywhere that reads the color

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    original table, or spreadsheet. I know its pesky terminology but tables have a specific meanign in a db context.

    the spreadsheet way is not the database way of doing thigns.

    in a spreassheet you can stick data wherever you like it, mix up presentation with data storage, in a database storage is seaparate from presentation. maipulation sort of straddles the storage and presentation. in Access you have a databases, alongsiode a very powerfull and flexible reporting / form presentation layer.

    the whole point about databases is that mapping back to whatever is easy and straightforward, providing your basuc data is stored according to good db relational design principles. as an aside, unless the data model represents a fiendishly complex business a well designed, well formed data model will get whatever result you are looking for. failing to gett he basic data model right usually means a frightmare as the origianl design reflected assumptions of the day and no thought was given on how to go forward.

    the fact that you have a pile of data in spreadsheets is OK as a source of information, its fine lots of peopel recognise that although spreadsheets are a great way to start collecting data, very quickly you hit problems. the spreadsheet way is not the relational db way. the closest you get in the spreadsheet way is have a different workbook/worksheet (or different block in a worksheet) for each data type.

    in navigation terms a waypoint is a specific co ordinate that you are travelling to or from or via,
    a track is a list of co-ordinates taken n times per time period. A standard GPS runs around 2 seconds per observation. but each observation isnt' a waypoint).

    route could be a specific pre planned route
    a track can be of a route, or of the journey that sort of followed that route, so there isn't, neccesarily, a realtionship between a route and a track

    so in table design Id probably expect something like, a table for:-
    waypoint types: identifying waypoitn types, symbols used (icons, colours etc)
    waypoints: specific locations of instances of a waypoitn type (eg Ponderosa Cafe, 53.023951N, 3.204552W
    Routes: describe the header inforamtion about the route (eg description, route type)
    RouteComposition: identifies what waypoints comprise this route
    Tracks: describe a journey (eg datetiem started, descritpion etc)
    TrackBreadcrumbs: defines individual observation that comprise a track. that probably means a track Id, a track stepno/seqno and the co-ordinates. you could possible add in an optional column which identified what waypoint this observation happened to be

    colouring of data in the presentation is probably determined at track level and or route level. so you could have an active route colour, and active track colour. but specifying colours per route and track as part of the tabel design is probably futile, after all there may not be enough different colours to uniquely idetify each route.

    how do you plan on presenting the data... what you could do is use a web page and google maps (using data sent to that page using a KML or similar file. or use google earth
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Jan 2015
    Posts
    4
    I receive the data in the form of an excel spreadsheet. There is no changing that fact. Unfortunately that's the way the data comes.

    Columns are:
    track name, track group number, origin, destination, notes, waypoint1, waypoint2, waypoint3, waypoint4, waypoint5 ...

    example:
    name1,1,wpa,wpb,wpc,wpd,wpe,wpf
    name2,2,wpg,wpc,wpa
    name3,3,wph,wpi,wpg


    At MINIMUM, all I want is the following in a table or spreadsheet. No fancy mapping or other parameters.

    Column 1: unique list of waypoints
    Column 2: List of all track names where the associated Column 1 waypoint appears.

    Column 2 can either be a comma separated list or several columns

    example:
    wpa,name1,name2
    wpb,name1
    wpc,name1,name2
    wpd,name1
    wpe,name1
    wpf,name1
    wpg,name2,name3
    wph,name3
    wpi,name3


    I can create Column 1 easily by doing a select query for each waypoint column and union.
    I don't understand how I can create column2

    I'll be happy if I can just do this, I can work on extra stuff (ie. reading color from excel) later.
    Last edited by eng3; 01-08-15 at 16:25.

Posting Permissions

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