Difference between revisions of "High-Throughput Phenotyping Database"

From Poland Lab Wiki
Jump to: navigation, search
(Undo revision 564 by Mlucas (talk))
m (HTP Database Table Structures)
 
(14 intermediate revisions by the same user not shown)
Line 1: Line 1:
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 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 images table stores metadata about the images that were collected during each data collection run.
Line 17: Line 19:
 
[[File:HTP_Database_Tables.png]]
 
[[File:HTP_Database_Tables.png]]
  
== HTP Database Table Structure ==
+
== PheMU HTP Database ==
{|class="wikitable"
+
 
|+Table Name: phemu_htp
+
The core set of tables for the PheMU database are the phemu_images table, the phemu_htp table and the phemu_run table.
!style="text-align:center;"|Column Name
+
 
!Description
+
*The phemu_images table stores metadata about the images that were collected during each data collection run.
!Data Type
+
 
!Required? <br />(Y/N)
+
*The phemu_htp table stores metadata about sensor measurements (observations) that were collected during each run.
!Constraints
+
 
|-
+
*The phemu_run table stores information about each data collection run such as date/time and geographical extent of the run.
|        record_id                || A sequential number generated when record is inserted. Used as a synthetic prim|| integer    || Y          || Unique sequential integer
+
 
|-
+
The core PheMU tables also relate to the plot_map table and the htp_instrument table.
|        run_id                  || An ID used to identify a data collection run.<br />Format: <platform>_<start_da|| text      || Y          ||
+
 
|-
+
*The plot_map table contains information about the geographical coordinates of each plot associated with an experiment (field).
|        plot_id                  || The name of the plot that the sensor observation is associated with.<br /> Note|| char(10)  || N          ||
+
 
|-
+
*The htp_instrument table contains information about cameras and sensors used on each platform.
|        sensor_id                || The identifier associated with the sensor used in the data collection.        || text      || Y          || Should match sensor_id in the htp_instrument table
+
 
|-
+
The diagram below illustrates the set of PheMU database tables and their relationships.
|        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          ||
+
[[File:Phemu htp medium.png]]
|-
+
 
|        absolute_sensor_position_x|| The sensor UTM x coordinate (easting).                                         || double(255,5)|| Y          ||
+
== Future HTP Database  ==
|-
+
 
|        absolute_sensor_position_y|| The sensor UTM y coordinate (northing).                                       || double(255,5)|| Y          ||
+
The future HTP database schema is designed to be data collection platform independent and will support both land-based and aerial data collection platforms.
|-
+
 
|        absolute_sensor_position_z|| The sensor altitude (above mean sea level) in meters.                         || double(255,5)|| Y          ||
+
The key schema design changes are:
|-
+
<ol>
|        sampling_time_utc        || The UTC time that the sensor observation was recorded.                         || time      || Y          ||
+
<li> A single measurement metadata table for both sensors and cameras.</li>
|-
+
 
|        sampling_date            || The UTC date that the sensor observation was recorded                          || date      || Y          ||
+
<li>  Separate observation tables which can support single or multi-valued measurements and different data types.</li>
|-
+
 
|        left_utc                || The UTC time on the left GPS when the sensor observation was recorded.         || double(255,5)|| Y          ||
+
<li>  Full support of GIS spatial columns for positions, plots and experiments.</li>
|-
+
 
|        left_elevation          || The left GPS altitude (above mean sea level) in meters.                        || double(255,5)|| Y          ||
+
<li>  Introduction of a new platform state table that records the position and orientation of the data collection platform over time.</li>
|-
+
 
|        left_long                || The left GPS longitude.                                                       || double(255,7)|| Y          ||
+
<li>  Inclusion of orientation (roll,pitch and yaw) columns for both sensors and the data collection platform.</li>
|-
+
 
|        left_lat                || The left GPS latitude.                                                        || double(255,7)|| Y          ||
+
<li>  Standardization of all position coordinate data to utilize WGS84 latitude/longitude positions.</li>
|-
+
 
|        long_zone                || The longitude zone of the left GPS e.g. 14.                                    || text      || Y          ||
+
<li>  Separation of sensor offset information from measurement metadata table to a sensor offsets table that records offsets on a per run basis instead of per observation.</li>
|-
+
 
|        lat_zone                || The latitude zone of teh left GPS e.g. S.                                      || text      || Y          ||
+
<li>  Simplification of the HTP instruments table.</li>
|-
+
</ol>
|        left_utm_x              || The UTM x position of the left GPS.                                            || double(255,5)|| Y          ||
+
|-
+
The diagram below illustrates the set of future HTP database tables and their relationships.
|        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          ||
+
[[File:HTP Database Schema Rev 4.png ]]
|-
+
 
|        right_elevation          || The right GPS altitude (above mean sea level) in meters.                      || double(255,5)|| N          ||
+
== HTP Database Table Structures ==
|-
+
 
|        right_long              || The right GPS longitude.                                                       || double(255,7)|| N          ||
+
UNDER CONSTRUCTION.
|-
+
 
|        right_lat                || The right GPS latitude.                                                       || double(255,7)|| N          ||
+
[[phemu_htp table]]
|-
+
|        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 19:53, 18 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

PheMU HTP Database

The core set of tables for the PheMU database are the phemu_images table, the phemu_htp table and the phemu_run table.

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

The core PheMU tables also relate 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 set of PheMU database tables and their relationships.


Phemu htp medium.png

Future HTP Database

The future HTP database schema is designed to be data collection platform independent and will support both land-based and aerial data collection platforms.

The key schema design changes are:

  1. A single measurement metadata table for both sensors and cameras.
  2. Separate observation tables which can support single or multi-valued measurements and different data types.
  3. Full support of GIS spatial columns for positions, plots and experiments.
  4. Introduction of a new platform state table that records the position and orientation of the data collection platform over time.
  5. Inclusion of orientation (roll,pitch and yaw) columns for both sensors and the data collection platform.
  6. Standardization of all position coordinate data to utilize WGS84 latitude/longitude positions.
  7. Separation of sensor offset information from measurement metadata table to a sensor offsets table that records offsets on a per run basis instead of per observation.
  8. Simplification of the HTP instruments table.

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


HTP Database Schema Rev 4.png

HTP Database Table Structures

UNDER CONSTRUCTION.

phemu_htp table