CREATE TABLE <my_table> (
  row_id SERIAL PRIMARY KEY,
  row_entry_date TIMESTAMP with time zone,
  row_update_date TIMESTAMP with time zone,
  platform_id INT NOT NULL,
  sensor_id INT,
  measurement_date TIMESTAMP with time zone,
  measurement_value_<my_var> FLOAT,
  -- other associated measurement_value_<my_var> added here as well
  latitude FLOAT,
  longitude FLOAT,
  z FLOAT,
  z_desc VARCHAR(20),
  qc_level INT,
  qc_flag VARCHAR(32)
);

   * <my_table> and <my_var> could be the same for simple variables like 
     'sst'( <my_table> = 'sst', <my_var> = 'sst'> ) or different for tandemly 
     collected variables like where ( <my_table> = 'winds', 
     <my_var_1> = 'wind_speed', <my_var_2> = 'wind_from_direction', 
     <my_var_3> = 'wind_gust' ).
   * row_id, row_entry_date, row_update_date are my preferences for internal 
     table row references
   * platform_id (or station_id, container_id) – an integer code which references
     a lookup table for platform information.  Could also be a string label for 
     simpler configurations. 
   * sensor_id - an integer code which references a lookup table for platform 
     information.  Not neccessarily required for simpler configurations(or 
     could be a string label) but adding an additional degree of detail and 
     flexibility at the sensor level.  
   * measurement_date - the time the variable was measured with time zone
     reference
   * measurement_value_<my_var> - the value(s) of the measured variable(s).
     There could be one or several measurement_value_<my_var> listed here.
   * latitude, longitude - unit decimal degree
   * z - depth or height reference, unit meters
   * z_desc - a string label which describes z for averaged values or specific 
     boundaries such as 'surface', 'bottom', 'average', 'bin1','bin2'...  
     Optional.
   * qc_level - integer value of [0,1,2,3,-9] corresponding to high-level qc 
     process
   * qc_flag - space separated listing of qc flags which may apply to this 
     measurement

Adding a general index(assuming a station level reference to individual variables) to prevent duplicate measurements.

   * CREATE UNIQUE INDEX i_<my_table> ON <my_table>
    (platform_id,sensor_id,z,z_desc,measurement_date);

Adding a PostGIS 2-dimensional geospatial reference column type (see this link on PostGIS enabling a PostgreSQL database).

   -- add the geometries
      select AddGeometryColumn('<my_dbinstance>','<my_table>','the_geom',-1,'POINT',2);

   -- add geometry index
      create index <my_table>_gist on <my_table> using gist(the_geom);

-- CharltonPurvis - 23 Mar 2005
to top