Load Report

Method used by Marta Mihoff to load data

Total time: 3.5 hours

Order of operations:

  1. create csv files from spreadsheets (one for each worksheet)
  2. create schema  tables from model schema (script skel.sql)
  3. load raw tables into schema
  4. insert all new stations into xxx.stations  (from raw deploy table)
  5. insert STATION records into xxx.moorings
  6. insert all new deployment records into table rcvr_locations (catalognumber must be unique)
  7. update rcvr_locations with recover dates and set recoverind for recoverd  (Y) or lost (L) receivers.
  8. check rcvr_locations for reasonableness (overlapping depoly periods for a receiver, deploy date < recover date)
  9. insert RECEIVER records into xxx.moorings
  10. insert DOWNLOAD records into xxx.moorings using join of raw download table and rcvr_locations table. The join is reuquired to pick up the station_name, and the catalognumber to use for the relatedcatalogitem of the DOOWNLOAD record.
  11. Check it all out.
  12. Alter table xxx.moorings inherit obis.moorings.

Spreadsheets modifications:

  • reformatted dates to yyyy-mm-dd hh:mm:ss
  • deleted extra lines above column headers
  • saved as csv

Loaded into postgres database using R-script

  • requires R-studio installed on desktop
  • set up ODBC connection to database postunit

Reformatted raw data into tables: stations and rcvr_locations

  • required custom scripts as raw data column names are different from our standard instrument metadata
  • differences in use of column STATUS_OUT

Inserted STATION and RECEIVER records into child table of obis.moorings

  • used our standard insert script

Insert DOWNLOADrecords into child table of obis.moorings

  • used custom script to insert direct (column headings different)

Added inheritance property to moorings table

Moved data to DEVL database OTN and refreshed geoserver.stations using our standard method

Stations show up in GoogleEarth kmz on DEVL site.