Difference between revisions of "Queries for linking bird tracks to wind and flow data"
From ecology
(Created page with "=== 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...") |
(→Combining devices with birds) |
||
Line 8: | Line 8: | ||
ON ud.device_info_serial = ts.device_info_serial; | ON ud.device_info_serial = ts.device_info_serial; | ||
− | Join with the table 'gps.uva_individual': | + | Join with the table 'gps.uva_individual'; then list only 'Larus fuscus': |
SELECT ud.device_info_serial, ui.species | SELECT ud.device_info_serial, ui.species | ||
Line 15: | Line 15: | ||
ON ud.device_info_serial = ts.device_info_serial | ON ud.device_info_serial = ts.device_info_serial | ||
LEFT JOIN gps.uva_individual ui | LEFT JOIN gps.uva_individual ui | ||
− | ON ts.ring_number = ui.ring_number; | + | ON ts.ring_number = ui.ring_number |
+ | WHERE ui.species = 'Larus fuscus'; | ||
+ | |||
+ | === Getting the bird track for one device === | ||
+ | |||
+ | SELECT date_time, latitude, longitude | ||
+ | FROM gps.uva_tracking_data101 | ||
+ | WHERE | ||
+ | device_info_serial = '540' AND | ||
+ | date_time >= '20 | ||
+ | |||
+ | ORDER BY date_time |
Revision as of 11:22, 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
SELECT date_time, latitude, longitude FROM gps.uva_tracking_data101 WHERE device_info_serial = '540' AND date_time >= '20 ORDER BY date_time