Revision as of 13:58, 14 November 2012

UvA-BiTS Data Model

All tables, views and functions related to UvA-BiTS are in the GPS schema in the database and begin with the prefix "uva_". The tables/views that do not begin with "_uva" are associated with a different project.

Different database tables are used depending on the version of UvA-BiTS firmware installed in the GPS device. For firmware version v0.9.3.6 till v1.0.0.X the tables UVA_ENGINEERING_DATA and UVA_TRACKING_DATA are used. But for the new firmware (versions 1.0.1.4 till 1.0.2.4) so much information was added that new tables were introduced. However for the most important tracking tables a view is created so that all similar tracking data for all types of firmware can be seen in one view. That is the UVA_TRACKING view.

Not everybody has access to all data. For most users access is limited to views with the postfix "_limited" that only give access to selected devices.

The majority of users will want to access the data found in the first five tables discussed below (uva_track_session, uva_tracking_data, uva_tracking_data101, uva_acceleration101 and uva_individual). The other tables have mainly administrative, engineering or data quality purposes.

UVA_DEVICE or UVA_DEVICE_LIMITED

To be able to track the different firmware versions and specific properties for the different devices the UVA_DEVICE table was added. This table also includes essential information needed to calibrate the accelerometers. For more information see the section below called Accelerometer calibration. Different GPS chips have been used for the tags over the years for more information see the section below called Information about GPS receivers.

Field	Type	Key	Constraints	Comments
device_info_serial	integer	PK	NOT NULL	Serial number of tag
firmware_version	text		NOT NULL	Firmware version installed on tag
device_mass	double			Mass of tag (g)
x_s	double			Calibration sensitivity of accelerometer in the x-axis
y_s	double			Calibration sensitivity of accelerometer in the y-axis
z_s	double			Calibration sensitivity of accelerometer in the z-axis
x_o	double			Calibration offset of accelerometer in the x-axis
y_o	double			Calibration offset of accelerometer in the y-axis
z_o	double			Calibration offset of accelerometer in the z-axis

UVA_TRACK_SESSION or UVA_TRACK_SESSION_LIMITED

This is essential metadata about GPS tag deployment. This table includes information about each measurement session and enables inclusion of data from new species and also from the test phase of different tags. To avoid analyzing data that was collected during testing, you should always select data that coincides with the start_data of a particular track session.

Field	Type	Key	Constraints	Comments
device_info_serial	int4	FK	NOT NULL	Serial number of GPS tag
ring_number	text	FK		ring number of individual, link to individual table – tests do not have a ring_number
start_date	timestamp			Timestamp without time zone (UTC) when tracking begins yyyy-mm-dd hh:mm:ss
start_latitude	float8		NOT NULL	latitude where tracking begins, unit = dec degress
start_longitude	float8		NOT NULL	longitude where tracking begins, unit = dec degrees
end_date	timestamptz			Timestamp without time zone (UTC) when tracking session ends if known yyyy-mm-dd hh:mm:ss
project_leader	text			Project leader and point of contact for questions about data
remarks	text
location	geometry			postgreSQL geometry

UVA_TRACKING_DATA Table or UVA_TRACKING_LIMITED View

Tracking data from UvA GPS tags for older firmware

Field	Type	Key	Constraints	Comments
device_info_serial	int4	PK	NOT NULL
date_time	timestamp	PK	NOT NULL	UTC
latitude	float8			Decimal degrees
longitude	float8			Decimal degrees
h_accuracy	float8			Horizontal accuracy (m)
v_accuracy	float8			Vertical accuracy (m)
altitude	float8			m
x_acceleration	Float8			x axis acceleration (m/s²2) movement to right is + and to left is -
y_acceleration	Float8			y axis acceleration (m/s²2) forward movement is + and backwards is -
z_acceleration	Float8			z axis acceleration (m/s²2) upwards movement is + downwards is -
speed	float8			m/s
course	float8			Degrees
pressure	float8			Pressure sensor (pascal)
temperature	Float8			Degrees celcius
solar_voltage	Float8			V
battery_voltage	Float8			V
userflag	Integer			Data flagged as unacceptable by user
“"location”"	geometry			postgreSQL geometry

UVA_TRACKING_DATA101 Table or UVA_TRACKING_LIMITED View

The tracking data for newer firmware (V 1.0.1.4 and higher).

Field	Type	Key	Constraints	Comments
device_info_serial	int4	PK	NOT NULL
date_time	timestamp	PK	NOT NULL	UTC
latitude	float			Decimal degrees
longitude	float			Decimal degrees
altitude	integer			height above sea level (meters)
pressure	integer			in pascal (1mBar = 100Pa, 1pressuremeter = ~12Pa)
temperature	float			in celcius degrees
satellites_used	smallint			satellites used for fix
gps_fixtime	float			GpsFixTime in seconds
positiondop	float			Position DOP in units
h_accuracy	float			Horizontal accuracy (units of meter)
v_accuracy	float			Vertical accuracy (units of meter)
x_speed	float			Speed in x-axis (m/s)
y_speed	float			Speed in y-axis (m/s)
z_speed	float			Speed in z-axis (m/s)
speed_accuracy	float			Speed accuracy (m/s)
userflag	Integer			Data flagged as unacceptable by user if not 0
location	geometry			lat/long as PostGIS geometry

UVA_TRACKING or UVA_TRACKING_LIMITED

View to show identical fields for older and newer firmware tracking data

Field	Type	Key	Constraints	Comments
device_info_serial	int4	PK	NOT NULL
date_time	timestamp	PK	NOT NULL	UTC
latitude	float
longitude	float
altitude	float
pressure	integer
temperature	float
h_accuracy	float
v_accuracy	float
x_speed	float			meters/second
y_speed	float			meters/second
z_speed	float			meters/second
gps_fixtime	float
userflag	integer			Flagged as inacceptable by user if not 0
satellites_used	smallint
positiondop	float
speed_accuracy	float
location	geometry

UVA_TRACKING_SPEED or UVA_TRACKING_SPEED_LIMITED

Additional view that computes with the x_speed, y_speed and z_speed components the vnorth, veast and vdown speed components as well as the 3D speed in meters/second.

In addition to the fields in the UVA_TRACKING view this view adds :

Field	Type	Key	Constraints	Comments
vnorth	float			- vx * sin(lat) * cos(long) - vy * sin(lat) * sin (long) + vz * cos(lat)
veast	float			- vx * sin(lon) + vy * cos(lon)
vdown	float			- vx * cos(lat) * cos(lon) - vy * cos(lat) * sin(lon) - vz * sin(lat)
speed	float			sqrt(vnorth^2 + veast^2)
speed3d	float			sqrt(vnorth^2 + veast^2 + vdown^2)
direction	float			heading in degrees; 0 is north, 90 is east

UVA_ACCELERATION101 or UVA_ACCELERATION_LIMITED

Acceleration entries if accelerometer was turned on. Only for newer firmware. Actual measurements have shown that the range of values for x,y and z are larger then indicated by the manufacturer. For more information about calibration and interpretation of accelerometer data see Accelerometer calibration

Field	Type	Key	Constraints	Comments
device_info_serial	int4	PK	NOT NULL
date_time	timestamp	PK	NOT NULL	UTC
index	smallint	PK	NOT NULL	row index (from 0) of accelerometer measurement series
x_acceleration	smallint			acceleration in X-axis (-2666 till 2666)
y_acceleration	smallint			acceleration in Y-axis (-2666 till 2666)
z_acceleration	smallint			acceleration in Z-axis (-2666 till 2666)

UVA_ACC_START102 or UVA_ACC_START_LIMITED

General acceleration entry associated with a set of accelerations. Added in Firmware 1.0.2.7 because there line numbers were introduced for acceleration lines so that these lines can be tracked.

Field	Type	Key	Constraints	Comments
device_info_serial	int4	PK	NOT NULL
date_time	timestamp	PK	NOT NULL
line_counter	integer			Number of lines associated with this set of accelerations
timesynced	smallint			TimeSynced (0=not synced, 1=user set, 2=Gps Synced)
accii	integer			Accelero measurement interval
accsn	integer			Number of samples (0-65536)
f	smallint			Accelero sample frequency (0=20Hz, 1=10Hz, 2=5Hz, 3=1Hz)

UVA_ENGINEERING_DATA

Engineering data from UvA GPS tags for older firmware (up to and including V 1.0.0.3). These tags used the UBlox gps chip LEAS4S.

Field	Type	Key	Constraints	Comments
device_info_serial	int4	PK	NOT NULL
date_time	timestamp	PK	NOT NULL	UTC
t_syncsts	smallint			0: not synced, 1: user synced, 2: Gps synced
t_entzt	smallint			0: UCT time,1: TZT time (timezone time)
gzp_currentrategps	int4
gzp_currentratecom	int4
gzp_currentratepwr	int4
sbm_voltagesolarpanel	float8
sbm_voltagebattery	float8
sbm_chargemode	int2
egzp_mainrategps	integer
egzp_mainratecom	integer
egzp_mainratepwr	integer
smg_on	text
smg_off	text
smg_esavegpsrate	integer
smz_on	text
smz_off	text
smz_esavecomrate	integer
gpsnavigationmode	smallint			This is the Kalman filter used for GPS navigation mode often called the "Dynamic platform model". The following codes are provided by the GPS chip manufacturer for UBlox chip LEAS4S: 1 = stationary, 2 = pedestrian, 3 = automotive, 4 = sea, 5 = airborne 1G, 6 = airborne 2G, 7 = airborne 3G
gpsfixtimeout	smallint
eh	text

UVA_COMM101 or UVA_COMM_LIMITED

Communication table indicates status and result of communications with ground stations. Only for newer firmware (V 1.0.1.4 and higher).

Field	Type	Key	Constraints	Comments
device_info_serial	integer	PK	NOT NULL
date_time	timestamp	PK	NOT NULL	timestamp of start communication
date_time_end	timestamp			timestamp of end communication
date_time_utc	timestamp			date_time in utc (if available)
date_time_end_utc	timestamp			date_time_end in utc (if available)
action	text			JOINED / RESCHEDULE ??
bytes_communicated	integer			Number of bytes communicated (total of all bytes per DATA line)
status	text			READY, TIMEOUT, LOWPOWER, MISSING
network_join_time	integer			milliseconds
number_identical_lines	integer			Number identical lines in data block. Gives indication
non_ascii_char	integer			Number of non ascii characters (indicated by question-mark ?)
sts_time	timestamp			derived from S0 hh,mi
timesynced	smallint			TimeSynced (0=not sy:wnced, 1=user set, 2=Gps Synced)
devicemode	smallint			DeviceMode (0-99). This describes whether the device is in a special operational mode. 0 = normal operational mode; 80 hex = Memory save mode (memory is almost full and all activity is drastically reduced so the circular memory probably won't be overwritten or, if it does, it will be kept to a minimum)
vbat	float			Battery voltage in V (0-~4.200V)
logb	integer			logged bytes, waiting for download (0-4Mbyte)
logp	smallint			Logged percentage (units 0.1%) of memory waiting to be downloaded (0-1000)
gpsii	integer			GpsIval, current (0-65535)
comii	integer			ComIval, current (0-65535)
accii	integer			AcceleroIval, current (0-65535)
swddd	integer			User switches, current (0-65535)
last_gps_fix	timestamp			timestamp of last fix
latitude	float			Latitude of last fix. Decimal degrees
longitude	float			Longitude of last fix. Decimal degrees

UVA_SETTINGS101 or UVA_SETTINGS_LIMITED

Settings are either from C0-C1-C9 lines or parsed from uplink communication lines If C0-C1 is parsed its values are compared with the previous value and if the settings are the same no new records needs to be added If uplink settings are parsed wait till the last setting has been seen, (DL command or EOF) then store all the new settings in one record. Only for newer firmware.

Field	Type	Key	Constraints	Comments
device_info_serial	int4	PK	NOT NULL
date_time	timestamp	PK	NOT NULL	Derive timestamp from Hh:mi fields in C0 combined with computer timestamp in JOINED line
timesynced	smallint
timezone_time	smallint
swddd	integer			SpecialUserSwitches
gpsnavmode	smallint			GpsNavMode 0-8. This is the Kalman filter used for GPS navigation mode often called the "Dynamic platform model". The following codes are provided by the GPS chip manufacturer for the UBlox GPS chip LEAS4S: 1 = stationary, 2 = pedestrian, 3 = automotive, 4 = sea, 5 = airborne 1G, 6 = airborne 2G, 7 = airborne 3G. The UBlox GPS chip LEAS6S uses the following codes: 0 = portable, 2 = stationary, 3 = pedestrian, 4 = automotive, 5 = sea, 6 = airborne with < 1g acceleration, 7 = airborne with < 2g acceleration, 8 = airborne with < 4g acceleration
gpsreceivermode	smallint			GpsReceiverNode 0-3
gpsfixtime	smallint			GpsFixTime 0-255
gps_ivaln_on_h	smallint			hours Gps IvalN on
gps_ivaln_on_m	smallint			minutes Gps IvalN on
gps_ivaln	integer			GpsIvalN (0-65530)
gps_ivale_on_h	smallint			hours Gps IvalE on
gps_ivale_on_m	smallint			minutes GpS IvalE on
gps_ivale	integer			GpsIvalE
com_ivaln_on_h	smallint			hours Com IvalN on
com_ivaln_on_m	smallint			minutes Com IvalN on
com_ivaln	integer			ComIvalN (0-65530)
com_ivale_on_h	smallint			hours Com IvalE on
com_ivale_on_m	smallint			minutes Com IvalE on
com_ivale	integer			ComIvalE
accii	integer			Accelero measurement interval
accbn	integer			Accelero number of blocks
accsn	integer			Accelero number of samples (0-65536)
f	smallint			Accelero sample frequency (0=20Hz, 1=10Hz, 2=5Hz, 3=1Hz)
pdp	smallint			PdopMsk (3-250)
pac	smallint			PaccMsk (10-100)
tdp	smallint			TdopMsk (3-250)
tac	smallint			TaccMsk (30-300)
ga	smallint			Additional Fixes GpsIval mode (3-25) before interval fix is logged
aol	smallint			AccMemOffLev (100 - 950) units 0.1%; if Memfull above this level all Accelero activity will be overruled and switched off untill MemFull < level - 5
eventnum	text			12 eventnumbers (0-0xff) only printed if not zero, therfore converted to text

UVA_ENERGY101 or UVA_ENERGY_LIMITED

Only for newer firmware.

Field	Type	Key	Constraints	Comments
device_info_serial	int4	PK	NOT NULL
date_time	timestamp	PK	NOT NULL
timesynced	smallint			TimeSynced (0=not synced, 1=user set, 2=Gps synced)
devicemode	smallint			(0-99)
vsll	float			Solar Voltage during charging in mV (0-~2500)
vbat	float			Battery Voltage in V (0-~4.200)
ssw	smallint			Solar Cell Switches ... (0-255) fw1022 and up
				extended fields if special user switch is on
vsoo	float			Solar voltage open in V (0-~2.500)
vdrf	float			reference voltage Dac in V (0-~2.435)
vchr	float			charge current voltage
g	smallint			Gps battery threshold OK
c	smallint			Com battery threshold OK
m	smallint			Charge Mode (0,1,2 Com battery threshold OK
pressure	integer			in pascal (1mBar = 100Pa, 1pressuremeter = ~12Pa)
temperature	float			in celcius degrees

UVA_WARNING102 or UVA_WARNING_LIMITED

Since firmware version 1.0.2.7 this table was added as catchall for tags that do not occur very often (C10, C20) or errors that occur during parsing. For example all CRC failures can be selected with :

 select * from gps.uva_warning102 
 where warning = 'CRC8 failure'
 order by device_info_serial;

Field	Type	Key	Constraints	Comments
device_info_serial	integer	PK	NOT NULL
filename	text	PK	NOT NULL	UvAGPS log filename
linenumber	integer	PK	NOT NULL	linenumber in file
warning	text			The warming message or tag
line	text			The actual line in the file

UVA_INDIVIDUAL or UVA_INDIVIDUAL_LIMITED

Table includes important information used to identify an individual tracked with a GPS tag. Remarks can also include a URL linking to a file with additional information about the individual (eg additional biometric measurements)

Field	Type	Key	Constraints	Comments
ring_number	text	PK	NOT NULL	metal ring number
color_ring	text			code for color ring without punctuation
species	text		NOT NULL	Scientific name of species
sex	text			update if or when known
mass	float			mass of bird at start of tracking session
Remarks	text			If additional information of this individual is available please provide the file with relevant data (eg biometric data). This field should include the file name and extension

UVA_NEST or UVA_NEST_LIMITED

Table includes information, if available, used to identify the nest of an individual tracked with a GPS tag.

Field	Type	Key	Constraints	Comments
ring_number	text	FK	NOT NULL	metal ring number
nest_id	text	FK		individual code for nest or territory
date_time	timestamp			Timestamp without time zone (UTC) when nest first marked yyyy-mm-dd hh:mm:ss
latitude	float			latitude of nest or territory (decimal degrees)
longitude	float			longitude of nest or territory (decimal degrees)
Remarks	text			If additional breeding information is available please provide the file with relevant data. This field should include the file name and extension

UvAGPS functions

get_uvagps_track_speed

Compute distance (meters), time-interval (time stamp hh:mm:ss), speed (meters/second) and heading (degrees) for a series of subsequent tracking entries with specific device_info_serial. For the first entry in the sequence these values will always be missing because they are computed as the difference between subsequent rows.

Usage examples :

select * from gps.get_uvagps_track_speed(119, '2008-01-01 00:00:00', '2010-01-01 00:00:00');

select * from gps.get_uvagps_track_speed(119);

select t.*, a.distance, a.interval, a.speed, a.direction 
from gps.get_uvagps_track_speed(119, '2008-01-01 00:00:00', '2010-01-01 00:00:00') a 
join gps.uva_tracking t using(device_info_serial, date_time);

Distances and heading are calculated with spherical correction with the Haversine formula as described here. The heading is the initial heading. PostGIS does not calculate the heading, so therefore we don't use PostGIS.

In these functions the records that have been flagged as invalid by setting the userflag to 1 are not taken into account and discarded. If speed calculations are required for all records an additional boolean argument get raw data can be given. i.e.

select * from gps.get_uvagps_track_speed(119, '2008-01-01 00:00:00', '2010-01-01 00:00:00', true);

select * from gps.get_uvagps_track_speed(119, true);

get_uvagps_track_distance

Compute distance (meters) for UvAGPS tracks to arbitrary point in lat/long or geometry location

Example usage :

select * from gps.get_uvagps_track_distance(119, 52.1897017, 6.1996606); 

select * from gps.get_uvagps_track_distance(119, (select location from gps.uva_tracking where device_info_serial = 119 and date_time = '2009-05-07 19:55:08'));

Here the distance is calculated with the PostGIS distance_sphere function which uses spherical correction.

get_uvagps_track_distance_direction

Compute distance (meters) and direction (degrees) for UvAGPS tracks from arbitrary point in lat/long or geometry location

Example usage:

select * from gps.get_uvagps_track_distance_direction(119, 52.1897017, 6.1996606);

Distances and direction are calculated with spherical correction with the Haversine formula as described here. The direction is the direction from the given point.

In this function the records where the userflag has been set are discarded. To see these records a additional boolean argument for show raw data should be added like :

select * from gps.get_uvagps_track_distance_direction(119, 52.1897017, 6.1996606, true);

Accelerometer calibration

The accelerometer measures the acceleration in 3D (20 times per second). Acceleration is caused by gravity (static component) and by a change in speed (dynamic component). When the logger is static in horizontal position (solar cells up), gravity (green arrow) shows a heave (vertical acceleration, z) of +1 g (1 g = 9.81 m.s^-2) while surge and heave are both 0. When the front of the logger is vertically pointing down (forward acceleration, x), surge is +1g and when it is rolled to the right (sidewards acceleration, y) sway is +1g. Due to gravity, the pitch of a logger is thus translated into the values of heave, surge and sway (or z, x, y).

However, the values are measured in mV instead of g. Before delivering the GPS-loggers, they have been calibrated at UvA. The acceleration (A) expressed in g is calculated from the measured value (M) and the calibration factors offset (O) and sensitivity (S).

Thus acceleration for heave (z), surge (x) and sway (y) is calculated as: A_z = (M_z-O_z)/S_z ; A_x = (M_x-O_x)/S_x; A_y = (M_y-O_y)/S_y ;

The values of O (in general ranging from 0-60, sometimes even 300) and S (ranging from 1300-1450) are available in the device table of the data base. If not available, use the factory defaults O = 0 and S = 1365.

|

In the figure above heave (z) is shown in green, surge(x) in red and sway (y) in blue, all values are expressed in g (on the y-axis).

Data processing and filtering

The raw log files are processed before being entered into the database. Each firmware version has a dedicated parser for the task. In certain cases data that is found in the log files is not stored in the database. This can occur for two reasons:

1. The data did not pass the CRC8 checksum (cyclic redundancy check). This is a standard procedure designed to detect accidental changes to raw computer data.

2. If the data has an invalid signature. For example, if a certain data format (eg numeric) or number of values (eg 4 digits) is expected but not returned, this record is not stored in the database.

When a line is deleted, the log file name, line number and the type of error is stored in the table gps.uva_warning102

Information about GPS receivers

The first series of tags used the UBlox LEAS4S chip (~up to tag 541), newer tags use UBlox LEAS6S. The series developed in 2012 will use the Max6G receivers. From 2012 receiver versions will be stored in the database. The UBX protocol is used for programming firmware, packaging and processing data. For more information about the receivers themselves see the ublox documentation. Ublox 4 Protocol specification [1] Ublox 6 Protocol specification [2]

Difference between revisions of "UvAGPS"