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)
  • Event_date_time:
  • Org_ID: key field of the ORGANIZATIONS table
  • 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
  • Sensor ID: 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
  • Event_ ID: a sequential numbered key index of EVENTS
  • File_name (eg. 2014-06-20_AA0014_r-005-03) can be used to determine nest record. Starts with registration 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
  • 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
  • Average_magnitude: The average of the Magnitude field of all the RECORDS in this report
  • Highest_maximum: The highest maximum bin reported in any of the RECORDS in this report
  • Lowest_maximum: The lowest maximum bin reported in any of the RECORDS in this report
  • Highest_energy: The highest energy calculated in any of the RECORDS in this report
  • Lowest_energy: The lowest energy calculated in any of the RECORDS in this report
  • Average_temperature: The average of the converted Temperature field of all the RECORDS in this report
  • Highest_temperature: The highest temperature reported in any of the RECORDS in this report
  • Lowest_temperature: The lowest temperature reported in any of the RECORDS in this report
  • 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 (This is the database ID and not the organization's ID)
  • Long_nest_ID: (eg. 2014-06-20_AA0014) Also the beginning of the file name of reports. This uniquely identifies a nest
  • Org_ID: The ID of the organization managing the nest.
  • Org_nest_ID: The nest ID used by the managing organization.
  • Reg_date_time: The date is also the first term in the file name (see the Long_nest_ID field above)
  • Days_when_reg: (0) The number of days after the eggs were laid before registration (normally zero -- manually adjusted for reporting and prediction if later)
  • 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
  • Event_ID: The event that registered this 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
  • Turtle_Activity_level: the current level of turtle activity in the nest (this will be calculated)
  • Crab_Activity_level: the current level of crab 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
  • Event_ID: The event that created this record (this field is redundant Report_ID-->Reports:Event_ID)
  • 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: static x reading converted from hex
  • Y: static y reading converted from hex
  • Z: static z reading converted from hex
  • Magnitude: This is the x, y and z 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: The average energy level. This is calculated by integrating all the readings and then dividing by the number of readings
  • Max_jerk: The Maximum bin level converted to a G force

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
  • NiMH_12v_capacity: the level converted to per cent of battery capacity remaining for 8 AA NiMH Eneloop batteries
  • NiMH_9v_capacity: the level converted to per cent of battery capacity remaining for a9 V NiMH Tenergy battery

Table: SENSORS

  • Sensor_ID
  • Org_ID: The owner of the sensor
  • Software_version
  • Date_manufactured
  • Hardware_version
  • Software_version
  • In_use (Yes or No)
  • Date_in_use
  • Last_date_in use
  • Nest_ID
  • Calibration_on (Yes or No)
  • Temperature_offset (two byte signed)
  • Temperature_ratio (two byte signed)

Table: COMMUNICATORS

  • Comm_ ID
  • Org_ID: The owner of the communicator
  • Software_version
  • Date_manufactured
  • Hardware_version
  • Software_version
  • Type (C= communication tower, H=Hand-held)
  • Battery (which field to use in BATTERY_LEVELS to translate the battery reading)
  • In_use (Yes or No)
  • Date_in_use
  • Last_date_in use
  • Nest_ID

Table: ORGANIZATIONS

  • Org_ID: Unique key field for each organization
  • sub_domain: (nps)
  • sub_directory: (caha) multiple sub-organizations could share a subdomain. In this case Cape Hateras National seashore might share a subdomaine with the rest of the National Park Service
  • Name: Full name of the organization
  • Date_active: Date organization began collecting data
  • Active: (Yes or No)
  • Date_inactive: Date stopped collecting data
  • Contact_name:
  • Contact_title:
  • Contact_phone:
  • Contact_email:
  • Admin_password:

Table: USERS

  • User_ID:
  • Org_ID:
  • Name:
  • Date_active:
  • Active: (Yes or No)
  • Date_inactive:
  • Title:
  • Phone:
  • Email:
  • Sms:
  • Password:
  • Temp_password:
  • Email_verification (Yes or No)
  • Reg_alerts: (N-not requested, R-requested, A-approved, S-denied)
  • Reg_alert_format: (E - Email, S - SMS, B - Both)
  • Edit_rights (U = User Alerts, D = Database edits, A= Admin -- for approving reg_alerts and database editing rights, B= Bureaucrat -- all rights)

Table: NEST_ALERTS

Note: This depends upon USER rights (U= All users, D = Database access (researcher), A = Administrator of organization, B = Bureaucrat or System-wide access)

  • Org_ID
  • All_nests: (Yes or No) whether all nests for this organization will generate an alert (U,D,A,B)
  • Nest_ID: If All_nests is no, the specific nest that is being monitored
  • User_ID: The person monitoring the nest
  • All_updates: (Yes or No) whether alerts are sent regularly updating the status of the nest for all events (D,A,B)
  • Hatching: (Yes or No) whether alerts are sent only when hatching is imminent (U,D,A,B)
  • Problems: (Yes or No) whether alerts are sent when nests are disturbed, sensors are disconnected, power is reset or low batteries cause a shutdown. (A,B
  • Parameters: (Yes or No) whether alerts are sent when parameters are changed (A,B)
  • High_motion: (Yes or No) whether alerts are sent when motion above a threshold is detected (D,A,B)
  • Low_motion: (Yes or No) whether alerts are sent when motion below a threshold is detected (D,A,B)
  • High_temp: (Yes or No) whether alerts are sent when temperature above a threshold is detected (D,A,B)
  • Low_temp: (Yes or No) whether alerts are sent when temperature below a threshold is detected (D,A,B)
  • Motion_high_thresh: The high motion threshold for triggering an alert (D,A,B)
  • Motion_high_thresh: The low motion threshold for triggering an alert (D,A,B)
  • Temp_high_thresh: The high temperature threshold for triggering an alert (D,A,B)
  • Temp_low_thresh: The high temperature threshold for triggering an alert (D,A,B)
  • Hatching_alert_format: (E - Email, S - SMS, B - Both) (U, D,A,B)
  • Problem_alert_format: (E - Email, S - SMS, B - Both) (A,B)
  • Param_alert_format: (E - Email, S - SMS, B - Both) (A,B)
  • Thresh_alert_format: (E - Email, S - SMS, B - Both) for all alerts based on thresholds (D,A,B)

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: (0231)
  • Temperature: (22.0) Hex reading converted to decimal and then divided by 25.6, rounded to .1 degrees
  • X: (921) 0399 Hex converted to decimal
  • Y: (-525) FDF3 Hex (signed) converted to decimal
  • Z: (-249) FF07 Hex (signed) converted to decimal
  • Magnitude: (1089) 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: (36749) The total number of readings taken in this record converted
  • Maximum (12) The highest bid reading in the record
  • Bin_A (0DC4) These are the top 10 hex bin readings for the record. Bin J is the number
  • Bin_B (102C) of Maximum bin readings taken, and then everything below it counts down to zero.
  • Bin_C (2BCA) If the maximum is 0A or less, then bin_A to bin_J corresponds to bin_01 to bin_10
  • Bin_D (2D10)
  • Bin_E (08AF)
  • Bin_F (00C1)
  • Bin_G (0033)
  • Bin_H (001C)
  • Bin_I (0005)
  • Bin_J (0001)
  • Bin_01 (1919) missing readings are divided equally among the low bins that were not reported
  • Bin_02 (1919)
  • Bin_03 (3524) The hex bin readings from above can be converted to decimal, and put in the appropriate bins -- in this case bin3 to bin12.
  • Bin_04 (4140)
  • Bin_05 (11210)
  • Bin_06 (11536)
  • Bin_07 (2223)
  • Bin_08 (193)
  • Bin_09 (51)
  • Bin_10 (28)
  • Bin_11 (5)
  • Bin_12 (1)
  • Bin_13 (0) The rest of the bins are zero
  • Bin_14 (0)
  • Bin_15 (0)
  • Bin_16 (0)
  • Bin_17 (0)
  • Bin_18 (0)
  • Bin_19 (0)
  • Bin_20 (0)
  • Bin_21 (0)
  • Bin_22 (0)
  • Bin_23 (0)
  • Bin_24 (0)
  • Bin_25 (0)
  • Missing (3838) 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. Each bin value is multiplied by the bin energy level and then added to the energy value. The bin energy values are adjusted by the average magnitude for an entire report which should correspond to one G.
  • Max_jerk This can be computed by adjusting the Maximum bin level using the average Magnitude for the report to compute the jerk levels for each bin.
Retrieved from "http://nerdswithoutborders.net/index.php?title=Turtle_Sense/Database_design&oldid=431"