Revision as of 09:41, 27 September 2010

Useful Queries

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

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

or

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');

If you want to do this for all ID's at the same time you can do something like :

select device_info_serial,  (select sum(distance)/1000
  from gps.get_uvagps_track_speed(device_info_serial, '2010-01-01 00:00:00', '2011-01-01 00:00:00')) as total_distance
from gps.uva_device(_limited)

The GROUP BY clause in SQL can be used to count the total number of measurements for each device :

select device_info_serial, count(*) 
from gps.uva_tracking(_limited) 
group by device_info_serial;

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.uva_tracking 
where latitude is not null 
group by device_info_serial;

If you to see want both the count of valid and invalid GPS fixes per device you can do something like :

 select device_info_serial, latitude is not null as valid, count(*) 
 from gps.uva_tracking 
 group by device_info_serial, valid 
 order by device_info_serial, valid;

Ik zal dit in de WIKI zetten .

Met vriendelijke groet,

Bart Heupers

Difference between revisions of "Useful Queries"

Revision as of 09:41, 27 September 2010

Useful Queries

Navigation menu

Views

Personal tools

Navigation

Search

Tools

@@ Line 1: / Line 1: @@
 = Useful Queries =
+A query to determine the total distance a bird has flown for a time period is :
-Judy had al een query om de totale afstand te bepalen voor een ID. Dat was :
+ select sum(distance)/1000 as total_distance
+ from gps.get_uvagps_track_speed('''<ID>''');
-select sum(distance)/1000 as total_distance
+or
-from gps.get_uvagps_track_speed(355);
+ 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');
-Als je dat voor alle ID’s tegelijk wilt doen krijg je bijvoorbeeld iets van :
-select device_info_serial,  (select sum(distance)/1000
+If you want to do this for all ID's at the same time you can do something like :
-   from gps.get_uvagps_track_speed(device_info_serial)) as total_distance
-from gps.uva_device
-dat duurt even.
+  select device_info_serial,  (select sum(distance)/1000
-Ik kan me voorstellen dat je  dat alleen voor een  bepaalde tijdsperiode wilt zien. Dan heb je bijvoorbeeld :
-select device_info_serial,  (select sum(distance)/1000
     from gps.get_uvagps_track_speed(device_info_serial, '2010-01-01 00:00:00', '2011-01-01 00:00:00')) as total_distance
-from gps.uva_device
+ from gps.uva_device(_limited)
-)
-In SQL heb je de GROUP BY clause, die het mogelijk maakt om aggregaties zoals som, aantal, gemiddelde, te groeperen volgens bepaalde criteria zoals ID
-Het totaal aantal positiemetingen per ID is dan :
-select device_info_serial, count(*) from gps.uva_tracking group by device_info_serial;
-Als je alleen de valide metingen met gps fix wilt hebben voeg je nog een conditie toe waar de latitude niet null is
-select device_info_serial, count(*) from gps.uva_tracking where latitude is not null group by device_info_serial;
+The GROUP BY clause in SQL can be used to count the total number of measurements for each device :
+ select device_info_serial, count(*)
+ from gps.uva_tracking(_limited)
+ group by device_info_serial;
-Een als je zowel het aantal meteingen  met gps fix als zonder wilt zien per device  kun je iets doen als :
+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.uva_tracking
+ where latitude is not null
+  group by device_info_serial;
+If you to see want both the count of valid and invalid GPS fixes per device you can do something like :
-select device_info_serial, latitude is not null as valid, count(*) from gps.uva_tracking group by device_info_serial, valid order by device_info_serial, valid;
+  select device_info_serial, latitude is not null as valid, count(*)
+  from gps.uva_tracking
+  group by device_info_serial, valid
+  order by device_info_serial, valid;