Turtle Sense/Database design

From Nerds Without Borders
Revision as of 17:07, 29 June 2014 by Sam (talk | contribs) (more)
Jump to navigation Jump to search
  • field_name (data example) notes

Table definitions

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 reoirted
  • 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) sequentially numbered key index (not the nest identifier in the report file names.
  • Installed (2014-06-20) from the file name of the report. This and the Sensor_ID create the unique identifier used in report file names.
  • Sensor_ID (AA0014) the second term in the file name (see the field above)
  • 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
  • Capacity (99) the level converted to per cent of battery capacity remaining
Retrieved from "http://nerdswithoutborders.net/index.php?title=Turtle_Sense/Database_design&oldid=157"