This is an old revision of the document!
Trackwork Server Tables
Notes:
Unless otherwise specified all times stored in the database are integer seconds offset from the unix epoch where 0 is January 1, 1970 GMT.
Loc table
In the future this table should go away and be replaced with device/day based file system for scalability
| Name | Description | Comments |
| asset | Asset ID of associated vehicles (8 char max) | Why are we storing this? It's already part of the associated track. |
| time | time of this data point | |
| lat | latitude (integer – latitude * 10*7) | |
| lon | longitude (coded like latitude) | |
| alt | altitude (integer) | |
| hacc | horizontal DOP value | |
| vacc | vertical DOP value | |
| TMM_serial | device serial number (8 char max) | |
Track_Data table
| Name | Description | Comments |
| handle | a unique integer reference for this track | |
| description | track description (max 256 char) | Do we need this? |
| serial | serial number for this track (max 8 char) | |
| asset_ID | vehicle associated with this track (max 8 char) | Shouldn't this be looked up when the track is retrieved? |
| start_time | time of first data in this track | |
| end_time | time of last data in this track | |
| total_points | total data points in the track | |
| gap_count | number gaps | What do we do with this? |
| largest_gap | largest gap | This too |
| discovery_time | time of track discovery | |
| project_handle | handle of project that contains this track | |
Projects table
| Name | Description | Comments |
| handle | unique integer identifier | |
| name | project name (64 char max) | |
| description | project description (256 char max) | |
| bounds_NE_lat | lat-long of NE and SW corners of bounding box | |
| bounds_NE_long | | If we ever represent the boundary as a polyline, this will need it's own table |
| bounds_SW_lat | | |
| bounds_SW_long | | |
| bounds_NE_North | Northing, Easting equivalent of Lat-Long | proposed - optional data but this creates a common point for transforming NE to Lat-Long |
| bounds_NE_East | | proposed |
| bounds_SW_North | | proposed |
| bounds_SW_East | | proposed |
| start_date | project start date | |
| status | project status (NEW, ACTIVE, CLOSED) | We don't use status right now but it would be the natural way to limit the project tree visibility. (Mike M): The server does use this field |
| last_track_time | date of last data association | |
| modified | The date this entry was last modified | |
Association Table
| Name | Description | Comments |
| handle | a unique integer reference for this entry | |
| TMM_serial | device serial number (8 char max) | |
| asset_ID | vehicle ID (8 char max) | |
| start_time | first time this association is valid | |
| end_time | final time this association is valid | |
Vehicles table
This table will probably change to set more vehicle properties as global values. Color is a no-brainer and I could see other values used for haul planning, vehicle type (scraper, paver, etc.). First step is probably only color.
| Name | Description | Comments |
| asset_ID | vehicle identifier (8 char max) | |
| asset_descr | vehicle description (256 char max) | |
| color | default color of vehicle | proposed |
| avgload | a default volume | proposed |
| type | what type of vehicle (Scraper, Paver, Truck) | proposed, we could use this to set behaviors |
RTK table
The RTK table exists in the current server but has no method of summarizing (tracks) or downloading the data. It's also missing the key field of quality.
| Name | Description | Comments |
| pointid | Point number | |
| northing | Northing and Easting for job | |
| easting | | |
| elev | elevation of point | we may want to only store elevation for fixed data |
| time | time of point capture | |
| jobid | | This is probably more of a description than an ID unless we store job files in the track server. NE are localized so I could see this as being a description or possibly a Project ID if we do not create tracks. |
| instrumentid | the device serial #?, probably from the capture device (Fujitsu, HP) | |
| comment | ? Kill this unless someone knows why we need it | takes up lots of space |
| quality | Fixed, Float, Autonomous, can be integer 0,1,2,3 | Proposed. We don't currently store the quality which is an oversight |
RTK Thoughts
Off the top of my head I can think of two uses for RTK data.
Progressive surveying where we can use the points with a fix status to create a survey.
Treat RTK as a very expensive tracking device and use all data it generates, autonomous or better.
A key feature of RTK is that it is stored as Northing and Eastings derived from a job file instead of Lat/Long. This makes it very specific to a job and the coordinate system created for it. Part of any system here must retain the Job/Project context to make the RTK data be relevant. Since most of the time we know the Northing and Easting when a boundary is created for a project, we could add fields to the project table that were the NE equivalent of the bounding box corners.
App Presentation
How we want to use this data determines the interface and the nature of the what we ask for from the server.Based on the uses above I think we have two interfaces. RTK of all data regardless of quality, and RTK of just the fixed data with elevation. In thinking about it, I'm not sure we want Trackwork to read anything except the All data option and we need to present it just like any other track. Trackwork will need to connect between the line breaks caused by changes in quality state. I think it's preferable to do that in the app rather than on the server.
RTK only quality data is probably only used by GradeModel (or other Earthwork products) because at this point we've only identified progressive surveying as an application. There is current functionality (unreleased) that reads the RTK fixed quality data in via the import window and processes it into a progress topo. The only functionality probably needed is to show a different path on the File Open Dialog that shows the RTK data.
Data Flow
From App to Server
Server to App
Modules table
This is mostly about TMM's. My proposal is that we don't touch this because it's fairly small and doing so might create problems with the TMM's that we don't have much control over.
| Name | Description | Comments |
| serial | device serial number (8 char max) | |
| params_defaulted | device has default characteristics if true | |
| sample_interval | primary operating parameters | |
| upload_interval | | |
| tag_interval | | |
| a2d_interval | | |
| motion_timeout | | |
| filename | firmware filename (64 char max) | |
| version | firmware version (int) | |
| latest_time | time of latest upload | |
| hostname | TSM hostname (64 char max)ie:trackwork.agtek.com | |
| port | TSM contact port ie; 23420 | |
| last_lat | last-received valid GPS position | |
| last_long | | |
| last_alt | | |
| last_time | | |
| track | last associated track | |
| battery | last reported battery voltage | |
| last_known_version | last reported firmware version | |
| total_blks | statistics information | |
| total_gps | | |
| rej_gps_time | | |
| rej_gps_dop | | |
| total_a2d | | |
| rej_a2d_time | | |
| total_pwr | | |
| rej_pwr_time | | |
| stats_cleared | time statistics last cleared | |
A2D Table (a2d)
I believe the server populates this table with TMM data, but I don't think the any of
the client applications access it.
| Name | Description | Comments |
| TMM_serial | Device serial number (8 char max) | |
| time | Time data was sampled | |
| data_0 | A2D data fields | |
| data_1 | | |
| data_2 | | |
| data_3 | | |
| data_4 | | |
| data_5 | | |
| data_6 | | |
| data_7 | | |
Power Management Table (power_mgmt)
I believe the server populates this table with TMM data, but I don't think the any of
the client applications access it.
| Name | Description | Comments |
| TMM_serial | Device serial number (8 char max) | |
| time | Time data was sampled | |
| power | Power value (off, on, stopped, off_no_wakeup) | |
Asset ID Table (asset_ID)
| Name | Description | Comments |
| handle | Unique integer value | Is this used to generate unique asset ids? |
AGD Serial Number Table (agd_serial_numbers)
| Name | Description | Comments |
| serial | Unique integer value | |
| time | | |
Module Groups Table (module_groups)
| Name | Description | Comments |
| handle | Unique integer value | |
| group name | Group name (64 characters max) | |
| ref_time | Time of….? | |
Group Members Table (group_members)
| Name | Description | Comments |
| group_handle | The handle to the associated module group | |
| serial | The device serial number (8 characters max) | |
