Turtle Sense/Database design

• field_name (data example) notes

Table definitions

Table:EVENTS

• Event_ID (000497) a sequential unique number to index the events
• Event_type (R=report, S=sensor registration, C=communications start, P=parameter change, P=prediction, B=boil, R=reset, D=disconnect, X=power shutdown low battery)
• Nest_ID
• Report_ID
• Event_date_time
• Sensor ID
• Comm_ID
• Battery_level
• Percent_capacity

Table: REPORTS

This table contains the header information of each report.

• Report_ID (003957) sequentially numbered key index
• File_name (2014-06-20_AA0014_r-005-03) can be used to determine nest record. Starts with date, and then the sensor
• Days_active (005) calendar days since the sensor was recorded. 1 is the day of recording, 2 is the next day, etc...
• Report_number (03) the number of the report issued for this nest, on this day
• Comm_ ID (C-AA0002) the comm unit that created the report
• Nest_ID ((000142) key index of NESTS
• Start_date_time (2014/06/24 19:45:27) time of last report, roughly the start time of data collection
• Report_date_time (2014/06/24 20:45:29) time of sending this report
• Secs_per_record (00015) Converted from Hex, number of seconds as reported
• Number_of_records (0120) Converted from Hex, number of records as reported
• Battery_level (0549) As reported
• Percent_capacity (99) Looked up from BATTERY_LEVELS
• Parameters_ID (0027) The parameters used to generate the data

Table: NESTS

Each unique nest is identified by the date a sensor was installed, and the serial number of the sensor. The registration device stores this information in the sensor, and every report sent from that sensor will start with the unique identifier

• Nest_ID (000142) a unique sequential number to index to the nest
• Long_nest_ID (2014-06-20_AA0014) From the file name of the report. This uniquely identifies a nest
• Reg_date_time (2014-06-20 08:32:15) file names.
• Sensor_ID (AA0014) the second term in the file name (see the field above)
• Reg_comm_ID (H-AA0005) The comm unit used to register the nest
• Nest_longitude (12226.3671W) dddmm.mmmm GPS data from sensor registration
• Nest_latitude (3746.3183N) ddmm.mmmm
• Active (Y) Yes or No
• Current_Parameters_ID (0027) The id of the parameters currently in use
• Activity_level (19) the current level of activity in the nest (this will be calculated)
• Boil_date_predicted (2014/06/28 20:45:29) predicted date and time of hatching
• Boil_date_actual (2014/06/29 02:45:29) observed date and time of hatching
• Prediction_method (A01) Method used to predict hatching

Table: RECORDS

Each report has multiple records.

• Record_ID (0000142) sequentially numbered index -- all records in the table
• Report_ID (003957) the index to the report that had this record
• Nest_ID (000142) the index to the nest that generated this data (This field is redundant Report_ID-->REPORTS:Nest_ID)
• Parameters_ID (0027) The parameters used to generate the data (This field is redundant Report_ID-->REPORTS:Parameters_ID)
• Record_number (0173) the number of the record in this report
• Date_time (2014/06/24 20:17:29) the time calculated from the time reported in Report_ID-->REPORTS:Start_date_time plus Record_number multiplied by Report_ID-->Secs_per_record
• Temperature_hex (02E3) Celsius, hex reading 0100 per 10 degrees C
• Temperature (28.9) Hex reading converted
• X_hex (FE97) These x,y and z hex readings are the static readings of the motion sensor.
• Y_hex (047F0 They are the vector of force acting on the meter, so should average 1G
• Z_hex (05D0)
• Magnitude This is the x, y and z hex readings converted to a magnitude (x^2 + y^2 +z^2)^1/2
• Readings (3E7) The total number of readings taken in this record
• Maximum (0A) The highest bid reading in the record
• Bin_A These are the top 10 hex bin readings for the record. Bin J is the number
• Bin_B of Maximum bin readings taken, and then everything below it counts down to zero.
• Bin_C If the maximum is 0A or less, then bin_A to bin_J corresponds to bin_01 to bin_10)
• Bin_D
• Bin_E
• Bin_F
• Bin_G
• Bin_H
• Bin_I
• Bin_J
• Bin_01 The hex bin readings above can be converted to decimal, and put in the appropriate bins here.
• Bin_02
• Bin_03
• Bin_04
• Bin_05
• Bin_06
• Bin_07
• Bin_08
• Bin_09
• Bin_10
• Bin_11
• Bin_12
• Bin_13
• Bin_14
• Bin_15
• Bin_16
• Bin_17
• Bin_18
• Bin_19
• Bin_20
• Bin_21
• Bin_22
• Bin_23
• Bin_24
• Bin_25
• Missing This would be the number of bins that are below the lowest bin reported, when Maximum is greater than 0A
• Energy This can be computed by integrating all the readings

Table: PARAMETERS

• Parameter_ID (0012) sequentially numbered key index
• THRESH_ACT (2 byte integer) ADXL sensor threshold (not used)
• THRESH_INACT (2 byte integer) ADXL sensor threshold (not used)
• TIME_INACT (2 byte integer) ADXL sensor threshold (not used)
• MAXRUN (4 byte long) The maximum number of seconds allowed for any record
• SLOWBIN (2 byte integer) The number of seconds for each record on days before the nests are active
• BINSEC (2 byte integer) The number of seconds for each record on days when the nests are active

the following are all one byte fields

• THRESH_ACT_L
• THRESH_ACT_H
• TIME_ACT
• THRESH_INACT_L
• THRESH_INACT_H
• TIME_INACT_L
• TIME_INACT_H
• ACT_INACT_CTL
• FIFO_CONTRO
• FIFO_SAMPLES
• INTMAP1
• INTMAP2
• FILTER_CTL
• POWER_CTL
• SLEEP_INTERVALS
• READ_SPEED
• SLOWBIN_LO
• SLOWBIN_HI
• MAXRUN1
• MAXRUN2
• MAXRUN3
• MAXRUN4H
• MAX_RECORDS_LO
• MAX_RECORDS_HI
• CALIBRATE_TEMP
• REPORT_HEADINGS
• BINSEC_LO
• BINSEC_HI
• SLOW_DAYS
• SLOWBIN_LO
• SLOWBIN_HI
• SPARE_1
• SPARE_2
• SPARE_3
• SPARE_4
• SPARE_5
• SPARE_6
• SPARE_7
• SPARE_8
• SPARE_9

Table: BATTERY_LEVELS

• Level (549) the ADC reading of the battery voltage converted from Hex
• Percent_capacity (99) the level converted to per cent of battery capacity remaining

Table: SENSORS

• Sensor_ID
• Software_version
• Date_manufactured
• Hardware_version
• Software_version
• In_use (Yes or No)
• Nest_ID

Table: COMMUNICATORS

• Comm_ ID (C-AA0002)
• Software_version
• Date_manufactured
• Hardware_version
• Software_version
• Type (C= communication tower, H=Hand-held)
• In_use (Yes or No)
• Nest_ID

Data parsing

Reports and logs are generated and stored at nps.turtlesense.org. The parsing routine can generate many fields directly from the report. Some need to be calculated. Once the reports and logs are processed they can be archived. Here is an example of a report:

Report: 2014-06-20_AA0013_r-006-03.txt
Sensor ID#: AA0013
Installed: 2014-06-20
Comm ID#: C-AA0002
Days active & report #: 006-03
Nest location: 3746.3898N,12226.3516W
Start date/time: 2014/06/25,01:05:12
Report date/time: 2014/06/25,07:05:15
Secs per rec: 0168
# of recs: 003C
Battery level: 023A

Rec#,Temp,   X,   Y,   Z, Cnt, Max,Bins: (Low) to (High)
0000,0231,0398,FDF4,FF03,8F93,000C,0743,0ECF,294B,2D07,134F,032A,008A,001F,0012,0003
0001,0233,0399,FDF3,FF03,8F93,0011,029D,00D0,0064,0035,0020,001A,0008,0001,0000,0001
0002,0231,0399,FDF3,FF07,8F8D,000C,0DC4,102C,2BCA,2D10,08AF,00C1,0033,001C,0005,0001
0003,0234,0393,FDEF,FF01,8F8D,0012,0036,0010,000B,0010,000D,000D,0002,0003,0004,0001
etc...

Here is how the data is parsed to create fields:

Report: 2014-06-20_AA0013_r-006-03.txt

A new record is created in REPORTS

• REPORTS:Filename: 2014-06-20_AA0013_r-006-03.txt

 Sensor ID#: AA0013

• REPORTS:Sensor_ ID: AA0013 A corresponding record in SENSORS should already exist
• REPORTS:Nest_ID ((000142) A corresponding record in NESTS should already exist. This field links the record in NESTS to REPORTS. This ID can be found by using REPORTS:Long_nest_ID-->REPORTS:Nest_ID)

Installed: 2014-06-20

• This data already exists in Nest_ID-->NESTS:Reg_date_time so nothing needs to be stored

Comm ID#: C-AA0002

• REPORTS:Comm_ID: C-AA0002

Days active & report #: 006-03

• REPORTS:Days_active: 006
• REPORTS:Report_number: 03

Nest location: 3746.3898N,12226.3516W

This information already should exist in Nest_ID-->NESTS:Nest_longitude and Nest_ID-->NESTS:Nest_latitude

Start date/time: 2014/06/25,01:05:12

• REPORTS:Start_date_time: 2014/06/25,01:05:12

Report date/time: 2014/06/25,07:05:15

• Report_date_time: 2014/06/25,07:05:15

Secs per rec: 0168

• Secs_per_record: 00360 Converted from Hexadecimal

# of recs: 003C

• Number_of_records: 0060 Converted from Hexadecimal

Battery level: 023A

• Battery_level: 0570 Converted from Hexadecimal
• Percent_capacity: 99 Looked up on BATTERY_LEVELS table. Battery_level-->BATTERY_LEVELS:Percent_capacity

Rec#,Temp,   X,   Y,   Z, Cnt, Max,Bins: (Low) to (High)
0000,0231,0398,FDF4,FF03,8F93,000C,0743,0ECF,294B,2D07,134F,032A,008A,001F,0012,0003
0001,0233,0399,FDF3,FF03,8F93,0011,029D,00D0,0064,0035,0020,001A,0008,0001,0000,0001
0002,0231,0399,FDF3,FF07,8F8D,000C,0DC4,102C,2BCA,2D10,08AF,00C1,0033,001C,0005,0001
0003,0234,0393,FDEF,FF01,8F8D,0012,0036,0010,000B,0010,000D,000D,0002,0003,0004,0001
etc...

Each line creates a new record in the RECORDS table. Here's how a line is parsed (using the third line as an example):

0002,0231,0399,FDF3,FF07,8F8D,000C,0DC4,102C,2BCA,2D10,08AF,00C1,0033,001C,0005,0001