Turtle Sense/Database design

From Nerds Without Borders
Jump to navigation Jump to search
  • field_name (data example) notes

Table definitions

Table: EVENTS

This is the key table for all the events in the database. One record is created for each report. One record is created for each registration log, even though they are currently added to two files (sensor logs, and event logs)

  • Event_ID: a sequential unique number to index the events
  • Event_type (R=report, S=sensor registration, C=communications start, P=prediction, B=boil, D=disconnect)
  • Parameter_change (Yes or No)
  • Parameters_ID: The parameters set in the event -- set on-line in the future
  • Nest_ID: sequentially numbered key index of NESTS
  • Report_ID: sequentially numbered key index of REPORTS
  • Event_date_time:
  • Sensor IDD: sequentially numbered key index of SENSORS
  • Comm_ID: the comm unit that created the report
  • Reset (Yes or No)
  • Shutdown (Yes or No)
  • Battery_level: As reported, coverted from hex
  • Percent_capacity:(0-100) Looked up from BATTERY_LEVELS

Table: REPORTS

This table contains the header information of each report.

  • Report_ID: sequentially numbered key index
  • File_name (eg. 2014-06-20_AA0014_r-005-03) can be used to determine nest record. Starts with date, and then the sensor
  • Days_active: calendar days since the sensor was recorded. 001 is the day of recording, 002 is the next day, etc...
  • Report_number: the number of the report issued for this nest, on this day
  • Event_ ID: a sequential numbered key index of EVENTS
  • Start_date_time: time of last report, roughly the start time of data collection
  • Secs_per_record: Converted from Hex, number of seconds as reported that data was collected for each record
  • Number_of_records: Converted from Hex, number of records as reported
  • Parameters_ID: The parameters used to generate the data -- will be set on-line in the future

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: a unique sequential number to index to the nest
  • Long_nest_ID: (eg. 2014-06-20_AA0014) Also the beginning of the file name of reports. This uniquely identifies a nest
  • Reg_date_time: The date is also the first term in the file name (see the Long_nest_ID field above)
  • Sensor_ID: the second term in the file name (see the Long_nest_ID field above)
  • Reg_comm_ID: The comm unit used to register the nest
  • Nest_longitude (eg.12226.3671W) dddmm.mmmm GPS data from sensor registration
  • Nest_latitude (eg. 3746.3183N) ddmm.mmmm
  • Active: Yes or No -- whether the nest is still being monitored
  • Current_Parameters_ID: The id of the parameters currently in use
  • Activity_level: the current level of activity in the nest (this will be calculated)
  • Boil_date_predicted: predicted date and time of hatching
  • Boil_date_actual: observed date and time of hatching
  • Prediction_method: Method used to predict hatching

Table: RECORDS

Each report has multiple records.

  • Record_ID:sequentially numbered index -- all records in the table
  • Report_ID: the index to the report that had this record
  • Nest_ID :the index to the nest that generated this data (This field is redundant Report_ID-->REPORTS:Nest_ID)
  • Parameters_ID: The parameters used to generate the data (This field is redundant Report_ID-->REPORTS:Parameters_ID)
  • Record_number:the number of the record in this report
  • Date_time: 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: Celsius, hex reading 0100 per 10 degrees C
  • Temperature: Hex reading converted (converted to decimal and then divided by 25.6)

These x,y and z hex readings are the static readings of the motion sensor. They are the vector of force acting on the meter, so should average 1G

  • X_hex
  • Y_hex
  • Z_hex
  • Magnitude: This is the x, y and z hex readings converted to a magnitude (x^2 + y^2 +z^2)^1/2
  • Readings:The total number of sensor readings taken in this record
  • Maximum: The highest bin reading in the record

Bin_A through Bin_J are the top 10 hex bin readings for the record. Bin J is the number of Maximum bin readings taken, and then everything below it counts down to zero. If the maximum is 0A or less, then bin_A to bin_J corresponds to bin_01 to bin_10

  • Bin_A
  • Bin_B
  • Bin_C
  • 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 is the number of bins that are below the lowest bin reported, when Maximum is greater than 0A
  • Energy: This is calculated by integrating all the readings

Table: PARAMETERS

  • Parameter_ID: 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: the ADC reading of the battery voltage converted from Hex
  • Percent_capacity: 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)
  • Date_in_use
  • Last_date_in use
  • Nest_ID

Table: COMMUNICATORS

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

Data parsing

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

Here is an example of a registration event: 

REGISTRATION EVENT Date/Time: 2014/06/23, 16:11:29
Sensor ID#: AA0007 
Registration Communicator ID#: H-AA0005
Nest GPS Location: 3746.3048N, 12226.3747W
Battery level: 0230

Parsing this data will create new a new record in NESTS:

  • Nest_ID (the next sequential number to index to the nest)
  • Long_nest_ID (2014-06-23_AA0007) This uniquely identifies a nest
  • Reg_date_time (2014/06/23, 16:11:29)
  • Sensor_ID (AA0007) 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.3747W) dddmm.mmmm GPS data from sensor registration
  • Nest_latitude (3746.3048N) ddmm.mmmm
  • Active (Y)

A new record is also created in EVENTS:

  • Event_ID: (the next sequential unique number to index the event)
  • Event_type:S
  • Parameter_change:N
  • Nest_ID: (The ID of the record in NESTS created above)
  • Event_date_time: 2014/06/23, 16:11:29
  • Sensor ID: AA0007
  • Comm_ID: H-AA0005
  • Battery_level: 0560 (Converted from Hexadecimal)
  • Percent_capacity:99 (Looked up from 560-->BATTERY_LEVELS:Percent_capacity)

Records are also created in SENSORS and COMMUNICATORS if the IDs are not found.

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...

A new record is created in EVENTS:

  • Event_ID: (the next sequential unique number to index the event)
  • Event_type:R
  • Parameter_change: (This depends upon whether there is a message at the end of the report)
  • Parameters_ID (The parameters set in the event -- parameter updating will happen on-line in the future)
  • Nest_ID: 2014-06-20_AA0013-->NESTS:Nest_ID
  • Report_ID (the report ID created -- see below)
  • Event_date_time: 2014/06/25,07:05:15
  • Sensor ID: AA0013
  • Comm_ID: C-AA0002
  • Reset (This depends upon whether there is a message at the end of the report)
  • Shutdown (This depends upon whether there is a message at the end of the report)
  • Battery_level (0549) As reported
  • Percent_capacity: 99

A new record is also created in REPORTS:

  • File_name: 2014-06-20_AA0013_r-006-03
  • Days_active: 006
  • Report_number: 03
  • Event_ ID (the ID set in EVENTS above)
  • Start_date_time: 2014/06/25,01:05:12
  • Secs_per_record: 0060 (converted from Hexadecimal)
  • Number_of_records: 0240 (converted from Hexadecimal)
  • Parameters_ID (The parameters set in the event -- parameter updating will happen on-line in the future)
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
  • Record_ID (the next sequentially numbered index)
  • Report_ID (the index to the report that had this record set above)
  • Nest_ID (the index to the nest that generated this data (This field is redundant Report_ID-->REPORTS:Nest_ID)
  • Parameters_ID (The parameters used to generate the data. Parameter updating will happen on-line in the future)
  • Record_number: 00002 (Rec# converted from Hexadecimal)
  • Date_time: 2014/06/25,01:17:12 (the starting time of the record calculated from the time reported in Report_ID-->REPORTS:Start_date_time plus Record_number multiplied by Report_ID-->Secs_per_record)
  • Temperature_hex: 0234
  • Temperature: 22.0 (Hex reading converted to decimal and then divided by 25.6, rounded to .1 degrees)
  • X: 0398
  • Y: FDF4
  • Z: FF03
  • Magnitude: This is the x, y and z hex readings converted to decimal and then combined to calculate the magnitude (x^2 + y^2 +z^2)^1/2
  • Readings: 3E7) The total number of readings taken in this record converted
  • 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
Retrieved from "http://nerdswithoutborders.net/index.php?title=Turtle_Sense/Database_design&oldid=174"