Difference between revisions of "HTP Database"

From Poland Lab Wiki
Jump to: navigation, search
(HTP Database Table Structure)
 
Line 18: Line 18:
  
 
== HTP Database Table Structure ==
 
== HTP Database Table Structure ==
 +
{|class="wikitable"
 +
|+Table Name: phemu_htp
 +
!style="text-align:center;"|Column Name
 +
!Description
 +
!Data Type
 +
!Required? <br />(Y/N)
 +
!Constraints
 +
|-
 +
|        record_id                || A sequential number generated when record is inserted. Used as a synthetic prim|| integer    || Y          || Unique sequential integer
 +
|-
 +
|        run_id                  || An ID used to identify a data collection run.<br />Format: <platform>_<start_da|| text      || Y          ||
 +
|-
 +
|        plot_id                  || The name of the plot that the sensor observation is associated with.<br /> Note|| char(10)  || N          ||
 +
|-
 +
|        sensor_id                || The identifier associated with the sensor used in the data collection.        || text      || Y          || Should match sensor_id in the htp_instrument table
 +
|-
 +
|        sensor_observation      || The measurement value recorded by the sensor.                                  || double(255,5)|| Y          ||
 +
|-
 +
|        position                || GIS spatial representation of the x,y UTM coordinates of the sensor.          || point      || Y          ||
 +
|-
 +
|        absolute_sensor_position_x|| The sensor UTM x coordinate (easting).                                        || double(255,5)|| Y          ||
 +
|-
 +
|        absolute_sensor_position_y|| The sensor UTM y coordinate (northing).                                        || double(255,5)|| Y          ||
 +
|-
 +
|        absolute_sensor_position_z|| The sensor altitude (above mean sea level) in meters.                          || double(255,5)|| Y          ||
 +
|-
 +
|        sampling_time_utc        || The UTC time that the sensor observation was recorded.                        || time      || Y          ||
 +
|-
 +
|        sampling_date            || The UTC date that the sensor observation was recorded                          || date      || Y          ||
 +
|-
 +
|        left_utc                || The UTC time on the left GPS when the sensor observation was recorded.        || double(255,5)|| Y          ||
 +
|-
 +
|        left_elevation          || The left GPS altitude (above mean sea level) in meters.                        || double(255,5)|| Y          ||
 +
|-
 +
|        left_long                || The left GPS longitude.                                                        || double(255,7)|| Y          ||
 +
|-
 +
|        left_lat                || The left GPS latitude.                                                        || double(255,7)|| Y          ||
 +
|-
 +
|        long_zone                || The longitude zone of the left GPS e.g. 14.                                    || text      || Y          ||
 +
|-
 +
|        lat_zone                || The latitude zone of teh left GPS e.g. S.                                      || text      || Y          ||
 +
|-
 +
|        left_utm_x              || The UTM x position of the left GPS.                                            || double(255,5)|| Y          ||
 +
|-
 +
|        left_utm_y              || The UTM y position of the left GPS.                                            || double(255,5)|| Y          ||
 +
|-
 +
|        right_utc                || The UTC time on the right GPS when the sensor observation was recorded.        || double(255,5)|| N          ||
 +
|-
 +
|        right_elevation          || The right GPS altitude (above mean sea level) in meters.                      || double(255,5)|| N          ||
 +
|-
 +
|        right_long              || The right GPS longitude.                                                      || double(255,7)|| N          ||
 +
|-
 +
|        right_lat                || The right GPS latitude.                                                        || double(255,7)|| N          ||
 +
|-
 +
|        right_utm_x              || The UTM x position of the right GPS.                                          || double(255,5)|| N          ||
 +
|-
 +
|        right_utm_y              || The UTM x position of the right GPS.                                          || double(255,5)|| N          ||
 +
|-
 +
|        sensor_offset_x_from_left_gps|| The x distance offset from the sensor and the left GPS in cm.                  || double(255,5)|| Y          ||
 +
|-
 +
|        sensor_offset_y_from_left_gps|| The x distance offset from the sensor and the left GPS in cm.                  || double(255,5)|| Y          ||
 +
|-
 +
|        sensor_offset_z_from_left_gps|| The z distance offset from the sensor and the left GPS in cm.                  || double(255,5)|| Y          ||
 +
|-
 +
|        note                    || Optional notes related to the observation.                                    || longtext  || N          ||
 +
|}

Latest revision as of 16:23, 12 May 2016

The High-Throughput Phenotyping database consists of a set of platform-specific tables for each data collection platform: pheMU, phenocart and phenocam. Each platform has an images table, an htp table and a run table.

  • The images table stores metadata about the images that were collected during each data collection run.
  • The htp table stores metadata about sensor measurements (observations) that were collected during each run.
  • The run table stores information about each data collection run such as date/time and geographical extent of the run.

Each set of platform-specific tables also relates to the plot_map table and the htp_instrument table.

  • The plot_map table contains information about the geographical coordinates of each plot associated with an experiment (field).
  • The htp_instrument table contains information about cameras and sensors used on each platform.

The diagram below illustrates the generalized set of HTP tables and their relationships.

HTP Database Tables.png

HTP Database Table Structure

Table Name: phemu_htp
Column Name Description Data Type Required?
(Y/N)
Constraints
record_id A sequential number generated when record is inserted. Used as a synthetic prim integer Y Unique sequential integer
run_id An ID used to identify a data collection run.
Format: <platform>_<start_da
text Y
plot_id The name of the plot that the sensor observation is associated with.
Note
char(10) N
sensor_id The identifier associated with the sensor used in the data collection. text Y Should match sensor_id in the htp_instrument table
sensor_observation The measurement value recorded by the sensor. double(255,5) Y
position GIS spatial representation of the x,y UTM coordinates of the sensor. point Y
absolute_sensor_position_x The sensor UTM x coordinate (easting). double(255,5) Y
absolute_sensor_position_y The sensor UTM y coordinate (northing). double(255,5) Y
absolute_sensor_position_z The sensor altitude (above mean sea level) in meters. double(255,5) Y
sampling_time_utc The UTC time that the sensor observation was recorded. time Y
sampling_date The UTC date that the sensor observation was recorded date Y
left_utc The UTC time on the left GPS when the sensor observation was recorded. double(255,5) Y
left_elevation The left GPS altitude (above mean sea level) in meters. double(255,5) Y
left_long The left GPS longitude. double(255,7) Y
left_lat The left GPS latitude. double(255,7) Y
long_zone The longitude zone of the left GPS e.g. 14. text Y
lat_zone The latitude zone of teh left GPS e.g. S. text Y
left_utm_x The UTM x position of the left GPS. double(255,5) Y
left_utm_y The UTM y position of the left GPS. double(255,5) Y
right_utc The UTC time on the right GPS when the sensor observation was recorded. double(255,5) N
right_elevation The right GPS altitude (above mean sea level) in meters. double(255,5) N
right_long The right GPS longitude. double(255,7) N
right_lat The right GPS latitude. double(255,7) N
right_utm_x The UTM x position of the right GPS. double(255,5) N
right_utm_y The UTM x position of the right GPS. double(255,5) N
sensor_offset_x_from_left_gps The x distance offset from the sensor and the left GPS in cm. double(255,5) Y
sensor_offset_y_from_left_gps The x distance offset from the sensor and the left GPS in cm. double(255,5) Y
sensor_offset_z_from_left_gps The z distance offset from the sensor and the left GPS in cm. double(255,5) Y
note Optional notes related to the observation. longtext N