Useful Queries - Revision history

Stefan verhoeven: /* Re-sample tracking data to select data with a predefined interval between fixes */ Do resample only on requested trackers

2015-10-13T09:29:40Z

‎Re-sample tracking data to select data with a predefined interval between fixes: Do resample only on requested trackers

Judy: /* Calculating summary statistics */

2015-03-13T07:59:13Z

‎Calculating summary statistics

Judy: /* Total number of measurements per tag */

2015-01-19T19:02:32Z

‎Total number of measurements per tag

Judy: /* Re-sample tracking data to select data with a predefined interval between fixes */

2015-01-19T14:31:33Z

‎Re-sample tracking data to select data with a predefined interval between fixes

Judy: /* Select tracking data and distinguish between distinct GPS deployments */

2015-01-19T14:30:38Z

‎Select tracking data and distinguish between distinct GPS deployments

Judy: /* Re-sample tracking data to select data with a predefined interval between fixes */

2015-01-19T14:28:57Z

‎Re-sample tracking data to select data with a predefined interval between fixes

Judy: /* Filtering tracking data */

2015-01-19T12:58:52Z

‎Filtering tracking data

Judy: /* Calculating summary statistics */

2015-01-19T12:06:13Z

‎Calculating summary statistics

Judy: /* UvAGPS functions */

2015-01-19T12:05:31Z

‎UvAGPS functions

Judy: /* How to separate birds or tag deployments when querying data where the same GPS logger has been used more than once or the same individual has been tracked with more than one logger */

2015-01-14T09:05:13Z

‎How to separate birds or tag deployments when querying data where the same GPS logger has been used more than once or the same individual has been tracked with more than one logger

@@ Line 106: / Line 106: @@
                 FLOOR(EXTRACT(EPOCH FROM date_time)/(5*60)) intervalid
                 FROM gps.ee_tracking_speed_limited
                 GROUP BY device_info_serial, intervalid
    ) b
    USING (device_info_serial, date_time)
-  WHERE device_info_serial = 355 AND date_time BETWEEN '2010-06-27' AND '2010-06-29'
    ORDER BY date_time
@@ Line 123: / Line 124: @@
                 FLOOR(EXTRACT(EPOCH FROM date_time)/(5*60)) intervalid
                 FROM gps.ee_tracking_speed_limited
                 GROUP BY device_info_serial, intervalid
    ) b
    USING (device_info_serial, date_time)
    ORDER BY date_time

← Older revision		Revision as of 07:59, 13 March 2015
Line 177:		Line 177:
	JOIN gps.ee_individual_limited i		JOIN gps.ee_individual_limited i
	ON V.individual_id = i.individual_id		ON V.individual_id = i.individual_id
		+	= Extra Metadata =
		+	== Extract all track sessions for a project ==
		+	This query provides all the track sessions for a specific project as well as the selected ID of the individual birds that were tracked. In the example below the project name needs to be filled in, for example s.key_name = 'HG_IVES'
		+
		+	SELECT s.device_info_serial, i.colour_ring, s.track_session_id, s.key_name
		+	FROM gps.ee_individual_limited i
		+	JOIN gps.ee_track_session_limited s
		+	ON s.individual_id = i.individual_id
		+	WHERE s.key_name = ''''< project name >''''
		+	ORDER BY i.colour_ring

← Older revision		Revision as of 19:02, 19 January 2015
Line 158:		Line 158:
	group by device_info_serial		group by device_info_serial
	ORDER BY device_info_serial ASC;		ORDER BY device_info_serial ASC;
		+
		+	Count the number of GPS fixes within a track session and for all track sessions within a specific project.
		+	In the example below the project name needs to be filled in, for example s.key_name = 'HG_IVES'
		+
		+	WITH
		+	V AS (
		+	SELECT s.device_info_serial, count(t.*) AS point_count, s.track_session_id, s.individual_id
		+	FROM gps.ee_track_session_limited s
		+	JOIN gps.ee_tracking_speed_limited t
		+	ON t.latitude IS NOT NULL
		+	AND s.device_info_serial = t.device_info_serial
		+	AND t.date_time BETWEEN s.start_date AND s.end_date
		+	WHERE s.key_name = ''''< project name >''''
		+	GROUP BY s.track_session_id, s.device_info_serial, s.individual_id
		+	)
		+	SELECT i.colour_ring, V.device_info_serial, V.point_count, V.track_session_id
		+	FROM V
		+	JOIN gps.ee_individual_limited i
		+	ON V.individual_id = i.individual_id

@@ Line 126: / Line 126: @@
    ) b
    USING (device_info_serial, date_time)
-WHERE device_info_serial = <ID> AND date_time BETWEEN 'yyyy-mm-dd' AND 'yyyy-mm-dd'
+  WHERE device_info_serial = '''<ID>''' AND date_time BETWEEN ''''yyyy-mm-dd'''' AND ''''yyyy-mm-dd''''
-ORDER BY date_time
+  ORDER BY date_time
 = Calculating summary statistics =

@@ Line 84: / Line 84: @@
      ON t.device_info_serial = s.device_info_serial
    AND t.date_time BETWEEN s.start_date AND s.end_date
-   WHERE s.key_name = '<Project name>' AND t.userflag = 0
+   WHERE s.key_name = ''''<Project name>'''' AND t.userflag = 0
    ORDER BY t.device_info_serial, t.date_time

@@ Line 64: / Line 64: @@
 </pre>
 = Filtering tracking data =
 = Calculating summary statistics =

← Older revision		Revision as of 12:06, 19 January 2015
Line 94:		Line 94:
	group by device_info_serial		group by device_info_serial
	ORDER BY device_info_serial ASC;		ORDER BY device_info_serial ASC;
−
−	~~== Select tracking data from the beginning of a track session ==~~
−
−	Select only those points that were collected during a valid track session (i.e. data that begins from the start_date of a track session) including 2D GPS speed. Filter our points where no GPS fix is returned (latitude is not null) and where a user has flagged the data as inappropriate (user_flag <> 1):
−
−	~~SELECT t.device_info_serial, t.date_time, t.latitude, t.longitude, t.altitude, t.temperature, t.speed, t.userflag~~
−	~~FROM gps.ee_tracking_speed_limited t, gps.ee_track_session_limited s~~
−	~~WHERE t.device_info_serial = s.device_info_serial AND t.date_time >= s.start_date AND t.device_info_serial = '''<ID>'''~~
−	~~AND t.latitude IS NOT NULL AND t.userflag <> 1~~
−
−	~~== How to separate birds or tag deployments when querying data where the same GPS logger has been used more than once or the same individual has been tracked with more than one logger ==~~
−	In this query you will select the tracking data related to each deployment of a GPS logger. This query is slightly more complex than the query above. If you have used the same logger on more than one individual bird, or have tracked the same individual with more than one logger, this query will enable you to make that distinction, by showing both GPS logger id (device_info_serial) and a unique identifier for your individual (eg ring_number or colour_ring). Furthermore, as above, this will only select tracking data from the start and end of a particular track session.
−
−	~~SELECT t.device_info_serial, t.date_time, t.latitude, t.longitude, t.altitude, t.speed_2d, i.colour_ring, s.track_session_id, s.key_name~~
−	~~FROM gps.ee_track_session_limited s~~
−	~~JOIN gps.ee_individual_limited i~~
−	~~ON s.individual_id = i.individual_id~~
−	~~JOIN gps.ee_tracking_speed_limited t~~
−	~~ON t.device_info_serial = s.device_info_serial~~
−	~~AND t.date_time BETWEEN s.start_date AND s.end_date~~
−	~~WHERE s.key_name = '<Project name>' AND t.userflag = 0~~
−	~~ORDER BY t.device_info_serial, t.date_time~~
−
−	~~HINT: WHERE s.key_name = 'HG_TEXEL'~~

@@ Line 63: / Line 63: @@
 select * from gps.get_uvagps_track_distance_direction(119, 52.1897017, 6.1996606, true);
 </pre>
 = Calculating summary statistics =

← Older revision		Revision as of 09:05, 14 January 2015
Line 104:		Line 104:

	== How to separate birds or tag deployments when querying data where the same GPS logger has been used more than once or the same individual has been tracked with more than one logger ==		== How to separate birds or tag deployments when querying data where the same GPS logger has been used more than once or the same individual has been tracked with more than one logger ==
−	~~NOTE: The current query does not work yet and can only be implemented in a testing environment - we expect to have this ready by January 2015~~
−
	In this query you will select the tracking data related to each deployment of a GPS logger. This query is slightly more complex than the query above. If you have used the same logger on more than one individual bird, or have tracked the same individual with more than one logger, this query will enable you to make that distinction, by showing both GPS logger id (device_info_serial) and a unique identifier for your individual (eg ring_number or colour_ring). Furthermore, as above, this will only select tracking data from the start and end of a particular track session.		In this query you will select the tracking data related to each deployment of a GPS logger. This query is slightly more complex than the query above. If you have used the same logger on more than one individual bird, or have tracked the same individual with more than one logger, this query will enable you to make that distinction, by showing both GPS logger id (device_info_serial) and a unique identifier for your individual (eg ring_number or colour_ring). Furthermore, as above, this will only select tracking data from the start and end of a particular track session.