Difference between revisions of "Queries for linking bird tracks to wind and flow data"
From ecology
(→Combining devices with birds) |
(→Getting the bird track for one device) |
||
Line 19: | Line 19: | ||
=== Getting the bird track for one device === | === Getting the bird track for one device === | ||
+ | |||
+ | The following example gets the position of one bird as a function of the time for one day: | ||
SELECT date_time, latitude, longitude | SELECT date_time, latitude, longitude | ||
FROM gps.uva_tracking_data101 | FROM gps.uva_tracking_data101 | ||
− | WHERE | + | WHERE device_info_serial = '330' AND |
− | + | date_time >= '2010-05-23 0:00:00' AND | |
− | + | date_time <= '2010-05-24 0:00:00' | |
− | + | ORDER BY date_time; | |
− | ORDER BY date_time |
Revision as of 11:52, 20 October 2011
Combining devices with birds
Devices have no information directly about the bird it is attached to; the table 'gps.uva_track_session' can be used to get this information:
SELECT ud.device_info_serial, ts.ring_number FROM gps.uva_device ud LEFT JOIN gps.uva_track_session ts ON ud.device_info_serial = ts.device_info_serial;
Join with the table 'gps.uva_individual'; then list only 'Larus fuscus':
SELECT ud.device_info_serial, ui.species FROM gps.uva_device ud LEFT JOIN gps.uva_track_session ts ON ud.device_info_serial = ts.device_info_serial LEFT JOIN gps.uva_individual ui ON ts.ring_number = ui.ring_number WHERE ui.species = 'Larus fuscus';
Getting the bird track for one device
The following example gets the position of one bird as a function of the time for one day:
SELECT date_time, latitude, longitude FROM gps.uva_tracking_data101 WHERE device_info_serial = '330' AND date_time >= '2010-05-23 0:00:00' AND date_time <= '2010-05-24 0:00:00' ORDER BY date_time;