Useful Queries

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 for a 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(<device_info_serial>);

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

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.ee_tracking_speed_limited 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.ee_tracking_speed_limited 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); 

Calculating summary statistics

Total distance

A query to determine the total distance a bird has flown is :

select sum(distance)/1000 as total_distance
from gps.get_uvagps_track_speed(<ID>);

or for a fixed time period :

select sum(distance)/1000 as total_distance
from gps.get_uvagps_track_speed(<ID>, '2010-01-01 00:00:00', '2011-01-01 00:00:00');

Total number of measurements per tag

Count the total number of measurements for each device using the GROUP BY clause:

select device_info_serial, count(*) 
from gps.ee_tracking_speed_limited
group by device_info_serial
ORDER BY device_info_serial ASC; 

If you want to count the number of measurements with valid GPS fixes you can add a where clause :

select device_info_serial, count(*) 
from gps.ee_tracking_speed_limited
where latitude is not null 
group by device_info_serial
ORDER BY device_info_serial ASC;