access:access_server_database_schema
Differences
This shows you the differences between two versions of the page.
| Both sides previous revision Previous revision Next revision | Previous revision | ||
|
access:access_server_database_schema [2009/09/28 17:33] mmatthews |
access:access_server_database_schema [2012/10/10 17:05] (current) |
||
|---|---|---|---|
| Line 76: | Line 76: | ||
| ==== Asset/Device Association Table (association) ==== | ==== Asset/Device Association Table (association) ==== | ||
| + | The association table stores the list of times a GPS device is associated with an asset such as a vehicle. | ||
| ^Column ^ Description ^ Comments ^ | ^Column ^ Description ^ Comments ^ | ||
| - | | | | | | + | |handle |An integer value that uniquely identifies the association | | |
| + | |serial |A device serial number | | | ||
| + | |assetid |An asseit id | | | ||
| + | |start_time |The time the association began | | | ||
| + | |end_time |The time the association ended | | | ||
| ==== GPS Statistics Table (gps) ==== | ==== GPS Statistics Table (gps) ==== | ||
| + | The GPS statistics table maintains statistics for each GPS device the customer has received data from. This table was part of the modules table in the old Trackwork server. | ||
| ^Column ^ Description ^ Comments ^ | ^Column ^ Description ^ Comments ^ | ||
| - | | | | | | + | |serial |The GPS serial number |This has been expanded to 32 characters instead of 8 | |
| + | |time |The last time the GPS connected to the server | | | ||
| + | |total_points |The total number of positions uploaded from the GPS | | | ||
| + | |last_lat |The last valid latitude received from the GPS | | | ||
| + | |last_lon |The last valid longitude received from the GPS | | | ||
| + | |last_alt |The last valid altitude received from the GPS | | | ||
| + | |last_time |The last valid time received from the GPS | | | ||
| + | |last_version |The last known version of the GPS firmware |Not all devices report firmware version | | ||
| + | |rejected_dop |The number of positions that have been rejected because the position accuracy was poor | | | ||
| + | |rejected_time |The number of positions that have been rejected because the position time was invalid | | | ||
| + | |cleared_time |The last time the statistics for this entry were cleared (0 if never cleared) | | | ||
| + | |||
| + | ==== RTK Statistics Table (rtk) ==== | ||
| + | The RTK statistics table maintains statistics for each RTK GPS device the customer has received data from. | ||
| + | |||
| + | ^Column ^ Description ^ Comments ^ | ||
| + | |serial |The GPS serial number |This has been expanded to 32 characters instead of 8 | | ||
| + | |time |The last time the RTK system connected to the server | | | ||
| + | |total_points |The total number of positions uploaded from the GPS | | | ||
| + | |last_Northing |The last valid Northing received from the GPS | | | ||
| + | |last_Easting |The last valid Easting received from the GPS | | | ||
| + | |last_Elev |The last valid elevation received from the GPS | | | ||
| + | |last_time |The last valid time received from the GPS | | | ||
| + | |cleared_time |The last time the statistics for this entry were cleared (0 if never cleared) | | | ||
| + | |||
| ==== Project Table (project) ==== | ==== Project Table (project) ==== | ||
| + | The project table contains information including the physical boundaries of customer projects. | ||
| ^Column ^ Description ^ Comments ^ | ^Column ^ Description ^ Comments ^ | ||
| - | | | | | | + | |handle |An integer value that uniquely identifies the project | | |
| + | |name |The name of the project | | | ||
| + | |description |A description of the project | | | ||
| + | |start_date |The project start date | | | ||
| + | |status |Project status |0=Undefined, 1=New, 2=Active, 3=Closed | | ||
| + | |ne_lat |The latitude of the north-east corner | | | ||
| + | |ne_lon |The longitude of the north-east corner | | | ||
| + | |sw_lat |The latitude of the south-west corner | | | ||
| + | |sw_lon |The longitude of the south-west corner | | | ||
| ==== GPS Track Table (track) ==== | ==== GPS Track Table (track) ==== | ||
| + | The track table contains information about GPS tracks. A track is supposed to correspond to the work done during a single work period, typically a day. The server does not know what the customer's work day is so it attempts to create tracks by grouping together all GPS data that is within a specified maximum "gap" time. That time is currently defined as four hours. | ||
| ^Column ^ Description ^ Comments ^ | ^Column ^ Description ^ Comments ^ | ||
| - | | | | | | + | |handle |An integer value that uniquely identifies the track| | |
| + | |project |The handle to the project the track belongs to, 0 if not associated with a project | | | ||
| + | |serial |The serial number of the GPS that generated the track | | | ||
| + | |start_time |The time of the first point in the track | | | ||
| + | |end_time |The time of the last point in the track | | | ||
| + | |discovery_time |The time the first valid data associated with this track was received | | | ||
| + | |total_points |The total number of points the track contains | | | ||
| + | |gap_count |The number of gaps in the track | | | ||
| + | |largest_gap |The size of the largest gap in seconds | | | ||
| + | |||
| + | ==== RTK Track Table (rtktrack) ==== | ||
| + | The track table contains information about RTK GPS tracks. A track is supposed to correspond to the work done during a single work period, typically a day. The server does not know what the customer's work day is so it attempts to create tracks by grouping together all GPS data that is within a specified maximum "gap" time. That time is currently defined as four hours. (note: this behavior follows the GPS track model with specific exceptions for finding projects) | ||
| + | |||
| + | ^Column ^ Description ^ Comments ^ | ||
| + | |handle |An integer value that uniquely identifies the track| | | ||
| + | |project |The handle to the project the track belongs to, 0 if not associated with a project | | | ||
| + | |serial |The serial number of the GPS that generated the track | | | ||
| + | |start_time |The time of the first point in the track | | | ||
| + | |end_time |The time of the last point in the track | | | ||
| + | |discovery_time |The time the first data associated with this track was received | | | ||
| + | |total_points |The total number of points the track contains | | | ||
| + | |gap_count |The number of gaps in the track | | | ||
| + | |largest_gap |The size of the largest gap in seconds | | | ||
| ==== Vehicle Table (vehicle) ==== | ==== Vehicle Table (vehicle) ==== | ||
| + | The vehicle table contains information about customer vehicles. When a new vehicle is added the asset id is automatically generated by the server using the Asset ID table. The unique integer generated by the Asset ID table is used zero padding it to 8 characters. For example, if the Asset ID table generates the unique value "33" the vehicle asset id will be "00000033". | ||
| ^Column ^ Description ^ Comments ^ | ^Column ^ Description ^ Comments ^ | ||
| - | | | | | | + | |assetid |The unique asset id of the vehicle | | |
| + | |description |The vehicle description | | | ||
| + | |type |The vehicle type |No values defined for this yet, not currently used | | ||
| + | |color |The color used to display tracks associated with the vehicle |Not currently used | | ||
| + | |avgload |The average load for earth moving vehicles |Not currently used | | ||
| + | |status |The vehicle status |No values defined for this yet, not currently used | | ||
| + | |||
| + | ===== GPS File Contents ===== | ||
| + | |||
| + | The GPS data is stored in flat files rather than an SQL database for scalability. | ||
| + | |||
| + | For each GPS position the following information is stored: | ||
| + | |||
| + | ^ Description ^ Comments ^ | ||
| + | |Time of position |GMT timestamp as seconds offset from unix epoch | | ||
| + | |Latitude |Integer (latitude * 10000000) | | ||
| + | |Longitude |Integer (longitude * 10000000) | | ||
| + | |Altitude |Integer meteres (meters * 1000) | | ||
| + | |Horizontal accuracy |Integer (accuracy * 1000) | | ||
| + | |Vertical accuracy |Integer (accuracy * 1000) | | ||
| + | |||
| + | ===== RTK File Contents ===== | ||
| + | |||
| + | The RTK data is stored in flat files rather than an SQL database for scalability. | ||
| + | |||
| + | For each RTK position the following information is stored: | ||
| + | |||
| + | ^ Description ^ Comments ^ | ||
| + | |Time of position |GMT timestamp as seconds offset from unix epoch | | ||
| + | |Easting |? Float Integer (range is +/- 99,999,999.9999) | | ||
| + | |Northing |? Float Integer (range is +/- 99,999,999.9999) | | ||
| + | |Elevation |Integer feet (range is +/- 32,000.0000) | | ||
| + | |Units |Boolean? (Assumed feet if Yes ?) | | ||
| + | |Quality |Integer (0,1,2,3,4,5 corresponds to common NMEA flag standards) | | ||
access/access_server_database_schema.1254159198.txt.gz · Last modified: 2012/10/10 17:05 (external edit)
Page Tools
