Difference between revisions of "Swarm Detection"

From HiveTool
Jump to: navigation, search
 
(One intermediate revision by the same user not shown)
Line 1: Line 1:
 +
==SQL==
 
CREATE TABLE daytime1(row_id char(250),hive_id char(250),doy decimal, year decimal,stamp timestamp, hive_weight_lbs decimal,quality char(250));
 
CREATE TABLE daytime1(row_id char(250),hive_id char(250),doy decimal, year decimal,stamp timestamp, hive_weight_lbs decimal,quality char(250));
  
Line 8: Line 9:
  
  
 +
==Results==
  
<table border="0" cellspacing="0" cellpadding="
+
 
 +
{| style="border-spacing:0;"
 +
| style="border-top:0.05pt solid #000000;border-bottom:0.05pt solid #000000;border-left:0.05pt solid #000000;border-right:none;padding:0.0382in;"| Early row id
 +
| style="border-top:0.05pt solid #000000;border-bottom:0.05pt solid #000000;border-left:0.05pt solid #000000;border-right:none;padding:0.0382in;"| hive
 +
| style="border-top:0.05pt solid #000000;border-bottom:0.05pt solid #000000;border-left:0.05pt solid #000000;border-right:none;padding:0.0382in;"| Day of year
 +
| style="border-top:0.05pt solid #000000;border-bottom:0.05pt solid #000000;border-left:0.05pt solid #000000;border-right:none;padding:0.0382in;"| year
 +
| style="background-color:#99ff33;border-top:0.05pt solid #000000;border-bottom:0.05pt solid #000000;border-left:0.05pt solid #000000;border-right:none;padding:0.0382in;"| early_time_stamp
 +
| style="background-color:#99ff33;border-top:0.05pt solid #000000;border-bottom:0.05pt solid #000000;border-left:0.05pt solid #000000;border-right:none;padding:0.0382in;"| Early weight
 +
 
 +
lbs
 +
| style="background-color:#99ff33;border-top:0.05pt solid #000000;border-bottom:0.05pt solid #000000;border-left:0.05pt solid #000000;border-right:none;padding:0.0382in;"| Early
 +
 
 +
quality
 +
| style="background-color:#ffff66;border-top:0.05pt solid #000000;border-bottom:0.05pt solid #000000;border-left:0.05pt solid #000000;border-right:none;padding:0.0382in;"| Later row id
 +
| style="background-color:#ffff66;border-top:0.05pt solid #000000;border-bottom:0.05pt solid #000000;border-left:0.05pt solid #000000;border-right:none;padding:0.0382in;"| Later time stamp
 +
| style="background-color:#ffff66;border-top:0.05pt solid #000000;border-bottom:0.05pt solid #000000;border-left:0.05pt solid #000000;border-right:none;padding:0.0382in;"| Later weight bs
 +
| style="background-color:#ffff66;border:0.05pt solid #000000;padding:0.0382in;"| Later quality
 +
 
 +
|-
 +
| style="border-top:none;border-bottom:0.05pt solid #000000;border-left:0.05pt solid #000000;border-right:none;padding:0.0382in;"| 1717532
 +
| style="border-top:none;border-bottom:0.05pt solid #000000;border-left:0.05pt solid #000000;border-right:none;padding:0.0382in;"| 20
 +
| style="border-top:none;border-bottom:0.05pt solid #000000;border-left:0.05pt solid #000000;border-right:none;padding:0.0382in;"| 249
 +
| style="border-top:none;border-bottom:0.05pt solid #000000;border-left:0.05pt solid #000000;border-right:none;padding:0.0382in;"| 2014
 +
| style="background-color:#99ff33;border-top:none;border-bottom:0.05pt solid #000000;border-left:0.

Latest revision as of 08:45, 1 February 2015

SQL

CREATE TABLE daytime1(row_id char(250),hive_id char(250),doy decimal, year decimal,stamp timestamp, hive_weight_lbs decimal,quality char(250));

create table splits2(early_row_id char(250), hive_id char(250),dayofyear decimal, year decimal, early_time_stamp timestamp, early_hive_weight_lbs decimal,early_quality char(250), later_row_id char(250), later_time_stamp timestamp, later_hive_weight_lbs decimal,later_quality char(250));

insert into daytime1 select row_id, hive_id,EXTRACT(doy FROM hive_observation_time_local),EXTRACT(year FROM hive_observation_time_local), hive_observation_time_local, hive_weight_lbs,quality from hivetool where EXTRACT(HOUR FROM hive_observation_time_local) BETWEEN 6 AND 18;

insert into splits2 select d1.row_id,d1.hive_id,d1.doy,d1.year,d1.stamp, d1.hive_weight_lbs,d1.quality, d2.row_id, d2.stamp, d2.hive_weight_lbs, d2.quality from daytime1 d1 inner join daytime1 d2 on d1.doy = d1.doy and d1.year = d2.year and d1.hive_id = d2.hive_id where (d1.hive_weight_lbs-d2.hive_weight_lbs) >2 and (d1.hive_weight_lbs-d2.hive_weight_lbs) <15 and (extract(epoch from d2.stamp- d1.stamp))<1200 and (extract(epoch from d2.stamp- d1.stamp))>30


Results

Early row id hive Day of year year early_time_stamp Early weight

lbs

Early

quality

Later row id Later time stamp Later weight bs Later quality
1717532 20 249 2014 2014-09-0616:10:01 119 5 1717566 2014-09-0616:20:01 114 5
1717532 20 249 2014 2014-09-0616:10:01 119 5 1717584 2014-09-0616:25:01 114 5
1717532 20 249 2014 2014-09-0616:10:01 119 5 1717550 2014-09-0616:15:01 115 5
1717516 20 249 2014 2014-09-0616:05:01 119 5 1717550 2014-09-0616:15:01 115 5
1717516 20 249 2014 2014-09-0616:05:01 119 5 1717566 2014-09-0616:20:01 114 5
1717498 20 249 2014 2014-09-0616:00:01 120 5 1717550 2014-09-0616:15:01 115 5
1717281 20 249 2014 2014-09-0614:55:01 47 5 1717294 2014-09-0615:00:02 33 5
1717230 20 249 2014 2014-09-0614:40:01 122 5 1717246 2014-09-0614:45:01 117 5
1717213 20 249 2014 2014-09-0614:35:01 122 5 1717246 2014-09-0614:45:01 117 5
1717196 20 249 2014 2014-09-0614:30:01 122 5 1717246 2014-09-0614:45:01 117 5
1459423 20 203 2014 2014-07-2211:20:01 131 5 1459442 2014-07-2211:25:01 128 5
1459400 20 203 2014 2014-07-2211:15:01 131 5 1459442 2014-07-2211:25:01 128 5
1459385 20 203 2014 2014-07-2211:10:02 131 5 1459442 2014-07-2211:25:01 128 5
1284648 20 174 2014 2014-06-2308:45:01 125 5 1284705 2014-06-2309:00:01 122 5
1284648 20 174 2014 2014-06-2308:45:01 125 5 1284683 2014-06-2308:55:01 122 5
1284625 20 174 2014 2014-06-2308:40:01 125 5 1284683 2014-06-2308:55:01 122 5
1224399 20 163 2014 2014-06-1213:20:01 153 5 1224455 2014-06-1213:35:01 150 5
1224399 20 163 2014 2014-06-1213:20:01 153 5 1224437 2014-06-1213:30:01 150 5
1224376 20 163 2014 2014-06-1213:15:01 158 5 1224437 2014-06-1213:30:01 150 5
1224376 20 163 2014 2014-06-1213:15:01 158 5 1224413 2014-06-1213:25:02 151 5
1224376 20 163 2014 2014-06-1213:15:01 158 5 1224399 2014-06-1213:20:01 153 5
1224364 20 163 2014 2014-06-1213:10:01 159 5 1224399 2014-06-1213:20:01 153 5
1224364 20 163 2014 2014-06-1213:10:01 159 5 1224413 2014-06-1213:25:02 151 5
1224346 20 163 2014 2014-06-1213:05:02 159 5 1224399 2014-06-1213:20:01 153 5
1083586 20 137 2014 2014-05-1710:35:01 128 5 1083609 2014-05-1710:40:01 115 5
1083568 20 137 2014 2014-05-1710:30:01 128 5 1083609 2014-05-1710:40:01 115 5
1083554 20 137 2014 2014-05-1710:25:01 128 5 1083609 2014-05-1710:40:01 115 5
883815 20 101 2014 2014-04-1111:35:02 105 6 883859 2014-04-1111:45:01 102 6
883815 20 101 2014 2014-04-1111:35:02 105 6 883881 2014-04-1111:50:01 102 6
883803 20 101 2014 2014-04-1111:30:02 105 6 883881 2014-04-1111:50:01 102 6
883803 20 101 2014 2014-04-1111:30:02 105 6 883859 2014-04-1111:45:01 102 6
866760 20 98 2014 2014-04-0811:35:01 104 6 866776 2014-04-0811:40:01 100 6
866760 20 98 2014 2014-04-0811:35:01 104 6 866795 2014-04-0811:45:01 100 5
866760 20 98 2014 2014-04-0811:35:01 104 6 866817 2014-04-0811:50:01 100 5
866738 20 98 2014 2014-04-0811:30:01 105 6 866795 2014-04-0811:45:01 100 5
866738 20 98 2014 2014-04-0811:30:01 105 6 866776 2014-04-0811:40:01 100 6
866718 20 98 2014 2014-04-0811:25:01 106 6 866776 2014-04-0811:40:01 100 6
851884 20 95 2014 2014-04-0513:25:01 106 6 851896 2014-04-0513:30:01 103 6
851871 20 95 2014 2014-04-0513:20:01 106 5 851896 2014-04-0513:30:01 103 6
851846 20 95 2014 2014-04-0513:15:01 106 5 851896 2014-04-0513:30:01 103 6
851830 20 95 2014 2014-04-0513:10:02 106 5 851896 2014-04-0513:30:01 103 6
842731 20 93 2014 2014-04-0317:35:01 111 5 842753 2014-04-0317:40:02 108 5
842731 20 93 2014 2014-04-0317:35:01 111 5 842785 2014-04-0317:50:01 108 5
842731 20 93 2014 2014-04-0317:35:01 111 5 842762 2014-04-0317:45:01 108 5
842715 20 93 2014 2014-04-0317:30:01 111 5 842762 2014-04-0317:45:01 108 5
842715 20 93 2014 2014-04-0317:30:01 111 5 842753 2014-04-0317:40:02 108 5
841919 20 93 2014 2014-04-0313:50:01 107 6 841978 2014-04-0314:05:01 104 6
841902 20 93 2014 2014-04-0313:45:01 108 6 841961 2014-04-0314:00:01 105 6
808364 20 86 2014 2014-03-2712:40:01 121 6 808396 2014-03-2712:50:02 111 6
808364 20 86 2014 2014-03-2712:40:01 121 6 808380 2014-03-2712:45:01 112 6
808364 20 86 2014 2014-03-2712:40:01 121 6 808411 2014-03-2712:55:02 111 5
808348 20 86 2014 2014-03-2712:35:01 121 6 808396 2014-03-2712:50:02 111 6
808348 20 86 2014 2014-03-2712:35:01 121 6 808380 2014-03-2712:45:01 112 6
808332 20 86 2014 2014-03-2712:30:01 122 6 808380 2014-03-2712:45:01 112 6
808316 20 86 2014 2014-03-2712:25:02 121 5 808380 2014-03-2712:45:01 112 6
694971 20 59 2014 2014-02-2816:00:02 125 5 695017 2014-02-2816:15:01 111 5
694971 20 59 2014 2014-02-2816:00:02 125 5 695001 2014-02-2816:10:01 111 5
694971 20 59 2014 2014-02-2816:00:02 125 5 694987 2014-02-2816:05:01 111 5
694951 20 59 2014 2014-02-2815:55:01 125 5 694987 2014-02-2816:05:01 111 5
694951 20 59 2014 2014-02-2815:55:01 125 5 695001 2014-02-2816:10:01 111 5
694936 20 59 2014 2014-02-2815:50:02 125 5 695001 2014-02-2816:10:01 111 5
694936 20 59 2014 2014-02-2815:50:02 125 5 694987 2014-02-2816:05:01 111 5
650901 20 49 2014 2014-02-1812:40:01 127 5 650914 2014-02-1812:45:01 116 5
650886 20 49 2014 2014-02-1812:35:01 128 5 650914 2014-02-1812:45:01 116 5
650870 20 49 2014 2014-02-1812:30:02 128 5 650914 2014-02-1812:45:01 116 5