gnuplot

code change to effect 12am/12pm instead o 0am/0pm

Below are the lines in gnuplot's time.c that should be changed(run make to generate the new libraries/executable) to get 12am/12pm instead of 0am/0pm in the graphs.

From http://groups.google.com/group/comp.graphics.apps.gnuplot/browse_thread/thread/c11ce06b9bda8ca8/c5e37c2137e20a72?lnk=st&q=gnuplot+%220+pm%22&rnum=1&hl=en#c5e37c2137e20a72 OK, sorry, the previous patch only works for midnight. This one works for 12 pm as well (and yes, there is a 12 pm and no 0 pm).

-------------------
--- gnuplot-4.0.0/src/time.c.orig       Fri Jan 28 23:43:13 2005
+++ gnuplot-4.0.0/src/time.c    Sat Jan 29 00:17:19 2005

case 'I': - FORMAT_STRING(1, 2, tm->tm_hour % 12); /* %02d */ + FORMAT_STRING(0, 2, (tm->tm_hour % 12 ? tm->tm_hour % 12 : 12));/* %2d */ break;

case 'j':

case 'l': - FORMAT_STRING(0, 2, tm->tm_hour % 12); /* %2d */ + FORMAT_STRING(0, 2, (tm->tm_hour % 12 ? tm->tm_hour % 12 : 12)); /* %2d */ break;

case 'm':

#!/usr/bin/perl -Tw # force taint checks, and print warnings use strict; # install all three strictures $|++; # force auto flush of output buffer

creating shapefiles

The following script loops through a control file to determine the scenario track_id, time step (step_id) and uses this data to produce a database query against a PostGIS^? table (table 'hurricane') which returns a shapefile using PostGIS^? pgsql2shp function.

#pgsql2shp documentation
http://postgis.refractions.net/docs/ch04.html#id2854856

#!perl
#the following file just provides a control file which changes the query and output filename according to the track_id, step_id
open(RANK_FILE, "surge_list_seq.csv");
my $line;
while ($line = <RANK_FILE>) {
        #ignore lines which start with hash
        if ($line =~ /^#/) {
                next;
        }
        #process line
        my ($track_id, $step_id) = split(/,/, $line);
print "processing sql track_id: $track_id ";
print `date`;
$filename = 'scenario_'.$track_id;
#get rid of previous run
`rm track*.dbf; rm track*.shp; rm track*.shx`;
#generate shapefiles
`/usr/local/pgsql/bin/pgsql2shp -u postgres -h neptune.baruch.sc.edu -f $filename hurricane "select measurement_value,the_geom from surge_products where catalog_id = 1 and track_id = $track_id and product_id = 1 and time_frame = $step_id"`;
#generate zip
`find . -name "track*" -print | zip shapefile_$filename -@`;
}
close(RANK_FILE);
exit 0;

Xenia package

I've been trying to reduce and simplify what we're doing in terms of data collection and sharing for ocean observations down to a single set of relational database tables and scripts. See XeniaPackage .

PostgreSQL^? vs MySQL^?

I've talked to several people who ask what PostgreSQL^? offers that MySQL^? doesn't. My list on this is the following:

• spatial indexes - compliance and awareness of OGC specs ( http://opengeospatial.org ) for geometric datatypes and support of bounding box and other functionality via the PostGIS^? modules ( http://www.postgis.org ).
• PostGIS^? also supports PostgreSQL^? to ESRI shapefile, ESRI shapefile to PostgreSQL^? conversion utilities.
• more closely follows conventional SQL standards and database functionality from proprietary enterprise databases like Oracle and SQL Server.

A few other links echoing similarly from a Slashdot post September 2005

http://ask.slashdot.org/comments.pl?sid=161173&cid=13484662 http://ask.slashdot.org/comments.pl?sid=161173&threshold=1&commentsort=0&mode=thread&cid=13487926

• not currently used but a possible alternative to MapLab^? is uDig

multi_obs form

Lately(January 2006) I've been rethinking some of the table structure issues. While on the one hand the one table per observation approach has been working fine, my temptation is to want to collapse these singular similar structured observation tables into one mega-table with an extra index of observation type. The advantage to this approach is hopefully easier code and database maintenance as there are less individual table references involved, but the disadvantage is also that a singular table reference can get into an all or nothing scenario when it comes to performance or problems at the database table level. See more notes at MultiObsSchema showing a sample schema and implementation.

Appendix I - Installation Tips

Appendix II - Troubleshooting (Gotcha's and Gremlins)

PostgreSQL^?

Postgresql documentation

http://www.postgresql.org/docs/

Additional links

http://database.sarang.net/database/postgres/optimizing_postgresql.html

kernel shared memory
http://www.redhat.com/docs/manuals/database/RHDB-2.1-Manual/admin_user/kernel-resources.html

When trying to optimize against operating system parameters, it's best to try changing one thing gradually at a time on a non-critical system to get an idea of how this effects things(run some experiments where you can afford to learn from mistakes). An easy mistake to make is to set some resource parameter(like shared memory) too high or low and a database or other external process becomes hung as a result.

PostgreSQL^? GUI tool

Here's the Postgres GUI interface tool we use if needed. I don't use the GUI tools that much since I can get most of what I need done via command line(live by the GUI die by the GUI), but it is helpful for getting a quick view of table structures or data(shared development environment) or learning the functionality of the database(basically the command line arguments are wrapped in the GUI).

PostgreSQL^? Manager(free 30 day evaluation download, single user license approximately $200) quoting from website http://ems-hitech.com/pgmanager/

EMS PostgreSQL^? Manager is a powerful graphical tool for PostgreSQL^? administration and development. It makes creating and editing PostgreSQL^? database objects easy and fast, and allows you to run SQL scripts, manage users and their privileges, build SQL queries visually, extract, print and search metadata, export data to 14 available formats and import them from most popular formats, view and edit BLOB fields, and many more...

30 day trial version
http://nautilus.baruch.sc.edu/resources/misc/pgmanager.zip

Postgres operations (update, fetch, etc) seem to hang

PostGIS^?

PostGIS^? documentation

WKT examples http://sunsite.mff.cuni.cz/MIRRORS/ftp.mysql.com/doc/en/GIS_WKT_examples.html

WFS queries against a table have disappeared

mapfile FILTER query doesn't return map - get sql error

can't get COPY to run

General packages

Application packages

Visualization tools(time series, maps, compositing)

• Graphs - time series, 3D, etc
  • GnuPlot

• Maps (color ramps, contours, etc)

• Animations
  • GIFsicle - produces animated gifs
  • Anis - Java-based applet for animating images

• Compositing (image overlays)
  • ImageMagick

mapfile FILTER query doesn't return map - get sql error

If the FILTER condition fields are outside (like a joined table) the same table reference as 'the_geom' or geometry column the query is selected from, then make sure the field references are included in the DATA section of the mapfile and the DATA section is also a valid SQL selection.

can't get COPY to run

Very hard to find this documented anywhere, but naming the columns you are copying to seems to be more effective than leaving them unnamed as in the example below. Also note that the file system reference is local to the postgresql database instance system which the copy statement is being run against and not the local filesystem where the copy statement is run from. So if the below statement is run from machine A against a postgresql database on machine B, the file system reference is for machine B.

#file md_0.cpy

1,089,1,0,0.000656,POINT(-79.988844 32.700249)
1,089,1,0,0.000984,POINT(-79.988011 32.700249)
1,089,1,0,0.00164,POINT(-79.987179 32.700249)

#copy statement

copy surge_products (catalog_id,track_id,product_id,time_frame,measurement_value,the_geom) from '/home/jsmith/cpy_files/md_0.cpy' using delimiters ',';

GMT (Generic Mapping Tools)

The below code lists examples of how GMT is used to create contour or color ramped images for later image query by mapserver. This is a preferred method where there might be an unnecessarily large number of mapfile layer classes needed to simulate a color ramp or where a mapping technique does not exist as with contours.

trailed from machine trident, user scmodel crontab:

nautilus:/usr2/home/scmodel/scripts/mk_gmt_grids.pl

runs an automated regular(hourly) psql query which returns a text file (lon, lat, value)

  $psql_command = $psql_prefix
    .' -c "select lon, lat, prmsl'.$units.' from met_sea_level_pressure_unc_stage'
    .' where prmsl'.$units.' > -1 and time_stamp = timestamp without time zone'
    ." '$time_stamp';\"";
  $cmd = $psql_command
    .' | '.$psql_suffix
    .' > '."$txt_dest_dir/met_sea_level_pressure".$units."_unc_prod_$formatted_time_stamp.txt";

This text value is passed to GMT 'surface' function which will take a text file and return a grid (.grd) file

  $surface_cmd = 'surface'
    ." $txt_dest_dir/met_sea_level_pressure".$units."_unc_prod_$formatted_time_stamp.txt"
    ." -R$grd_surface_bounds"
    ." -I$grd_surface_sample_incr"
    ." -G$txt_dest_dir/met_sea_level_pressure".$units."_unc_prod_$formatted_time_stamp.grd";

see GMT surface command http://gmt.soest.hawaii.edu/gmt/doc/html/surface.html
and GMT color palette tables (.cpt files) http://gmt.soest.hawaii.edu/gmt/doc/html/GMT_Docs/node51.html

these grid files are later used by nautilus:usr2/maps/seacoos/ui/portal_model/wrapper/map_session.php which pulls just the grid area of interest using the appropriate contour scale via nautilus:/usr2/maps/seacoos/util/mk_contour.php

• Dell PowerEdge2650 dual Xeon servers each with 2 gigabytes of RAM (2 of these, switching roles between query database and loading database). Further purchase info on the PowerEdge^? purchase info available here

Appendix II - Troubleshooting (Gotcha's and Gremlins)

A section for notes on common bugs or problems and their fixes, especially those that are difficult to diagnose and address.

PostgreSQL^?

Database tuning links
http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html
http://www.varlena.com/varlena/GeneralBits/Tidbits/annotated_conf_e.html

Postgres operations (update, fetch, etc) seem to hang

With the database running fine for several months, one day postgres processes (ps -eaf | grep postgres) seem to be piling up and preventing other processes from completing. Turns out that this database instance was not being vacuumed at all. After running vacuum/analyze on the database with the below command, processes stopped stacking up and things ran smoothly again.

/usr/local/pgsql/bin/vacuumdb -U postgres -d sea_coos_obs -h neptune.baruch.sc.edu -f -v --analyze

If you're running the 'cluster' command tables contained within the above database, you'll want to run the cluster command before the vacuum/analyze command (see http://archives.postgresql.org/pgsql-admin/2004-05/msg00217.php)

PostGIS^?

WFS queries against a table have disappeared

After a table which has been spatially enabled using PostGIS^? indexes has been running for several months, the WFS queries against the table stop returning data. The problem here is that if you are using the default postgresql 'oid' seqeunce for your sequence reference, this is good till about 2 million references and then ceases to be a usable index(see http://mapserver.gis.umn.edu/data2/wilma/mapserver-users/0405/msg00013.html). The solution is to always define your own unique sequence id per table and make sure the maximum value is well high enough or it's ok to cycle the sequence.

Adding a sequence column to an existing table 'my_table'

create sequence my_table_seq increment 1 minvalue 1 maxvalue 9223372036854775807 start 1 cache 1;
alter table my_table add column seq integer not null default nextval('public.latest_obs_by_station_id_seq'::text);

Reference within mapfile

  LAYER
    NAME "my_layer"
    STATUS OFF
    DATA "the_geom from my_table as foo USING UNIQUE seq USING SRID=-1"
    TYPE POINT

Application Performance

Note: The following equipment is dated to around 2003. We are still tending towards using commodity equipment from Dell using Red Hat Linux or perhaps Fedora. We don't have ongoing staff with specialized skills to support the latest software/hardware optimized setups.

By and large the application response for this system is good. We are able to levarage off of

• Dell PowerEdge2650 dual Xeon servers each with 2 gigabytes of RAM (2 of these, switching roles between query database and loading database)
• a spatially indexed relational database ( PostgreSQL^? + PostGIS^? )
• a relatively simple and straightforward table design which either support in situ measurements or raster images(see below topic about 'Data Structures' on this).

and all software components are open source to boot.

New data is collected periodically from all of the remote distributed sites, but queries against the GIS application are run against the already collected data on the central server database.

Our overall architecture incorporates 3 separate servers - a webserver which alternately points to one of two database servers. The two database servers switch roles between 1)collecting and populating the database(write operations) and 2)servicing queries(read operations). This allows us to keep a steady query response time not impacted by data upload.

Mapfile Example

The code snippet from this mapfile which supports the OGC WMS/WFS services listed below shows an example of the mapfile query against the underlying database.

  LAYER
    NAME "wind_obs_hourly_recent"
    STATUS OFF
    DATA "the_geom from (select station_id, time_stamp, z, label_z, wind_speed, wind_speed_knots, normalized_wind_speed, normalized_wind_speed_knots, wind_from_direction, label_theta, %wind_label_char_column% as this_label_char, label_char, normalized_label_char, lon, lat, title, institution, institution_url, institution_dods_url, source, refs, contact, report_time_stamp, the_geom, wind_from_direction_compass, wind_speed_mph, normalized_wind_speed_mph, label_char_knots, label_char_mph, normalized_label_char_knots, normalized_label_char_mph, value, value_knots, value_mph, normalized_value, normalized_value_knots, normalized_value_mph, seq, can_be_normalized from wind_map) as foo USING UNIQUE seq USING SRID=-1"
    FILTER "report_time_stamp = date_trunc('hour',timestamp without time zone '%formatted_time_stamp%') and station_id like '%%station_id%%'"
    DUMP TRUE
    TEMPLATE "dummy"
    TOLERANCE 5
    TOLERANCEUNITS pixels
    TYPE POINT
    CONNECTIONTYPE POSTGIS
    CONNECTION "user=postgres dbname=sea_coos_obs host=neptune.baruch.sc.edu"
    METADATA
      "wms_srs" "EPSG:4269 EPSG:4326"
      "wms_extent" "-180 -90 180 90"
      "wms_title" "In-situ sea wind speed and direction (hourly)"
      "wms_abstract" "*** OGC NOTE :: Do not access this layer directly.  This layer is a subset of a hidden layer called 'wind_obs_hourly'.  Make requests to 'wind_obs_hourly' instead (allow the server to decide whether the data will be pulled from recent obs or archived obs).  Optional parameter is station_id which will return records whose station_id contains the parameter as a substring.  Optional parameter is wind_speed_units that can be one of mps, mph, knots (mps is default). *** Using previously existing observing systems operating at the sub-regional level, SEACOOS works towards increasing the quantity and quality of environmental information from the coastal ocean of the Southeast and to facilitate the application of that data in a variety of forums and discourse communities. These observing systems collect data across a four state region (North Carolina, South Carolina, Georgia, and Florida) using a variety of sensors, which is integrated into the SEACOOS real-time data stream. The observing systems currently cooperating with SEACOOS are: SeaKeepers - an international nonprofit organization that installs sensors on platforms ranging from lighthouses to ocean-going vessels. Explorer of the Seas - The University of Miami and Royal Caribbean Cruise Lines outfitted an 1000 ft. cruise vessel with observation equipment. CaroCOOPS - an array off of the coast of the Carolinas operated by USC, N.C. State, and UNC-Wilmington. Coastal Ocean Monitoring and Prediction System - the University of South Florida operates this system observing the West Florida Shelf. South Atlantic Bight Synoptic Offshore Observational Network - is currently operated by Skidaway Institute of Oceanography in Savannah, Georgia in collaboration with the US Navy, UNC-Chapel Hill and SC Department of Natural Resources."
      "wms_keywordlist" "wind speed, wind direction, real time"
      "wfs_srs" "EPSG:4269 EPSG:4326"
      "wfs_extent" "-180 -90 180 90"
      "wfs_title" "In-situ sea wind speed and direction (hourly)"
      "wfs_abstract" "*** OGC NOTE :: Do not access this layer directly.  This layer is a subset of a hidden layer called 'wind_obs_hourly'.  Make requests to 'wind_obs_hourly' instead (allow the server to decide whether the data will be pulled from recent obs or archived obs).  Optional parameter is station_id which will return records whose station_id contains the parameter as a substring.  Optional parameter is wind_speed_units that can be one of mps, mph, knots (mps is default). *** Using previously existing observing systems operating at the sub-regional level, SEACOOS works towards increasing the quantity and quality of environmental information from the coastal ocean of the Southeast and to facilitate the application of that data in a variety of forums and discourse communities. These observing systems collect data across a four state region (North Carolina, South Carolina, Georgia, and Florida) using a variety of sensors, which is integrated into the SEACOOS real-time data stream. The observing systems currently cooperating with SEACOOS are: SeaKeepers - an international nonprofit organization that installs sensors on platforms ranging from lighthouses to ocean-going vessels. Explorer of the Seas - The University of Miami and Royal Caribbean Cruise Lines outfitted an 1000 ft. cruise vessel with observation equipment. CaroCOOPS - an array off of the coast of the Carolinas operated by USC, N.C. State, and UNC-Wilmington. Coastal Ocean Monitoring and Prediction System - the University of South Florida operates this system observing the West Florida Shelf. South Atlantic Bight Synoptic Offshore Observational Network - is currently operated by Skidaway Institute of Oceanography in Savannah, Georgia in collaboration with the US Navy, UNC-Chapel Hill and SC Department of Natural Resources."
      "wfs_keywordlist" "wind speed, wind direction, real time"    
    END
    LABELITEM "this_label_char"
    LABELANGLEITEM "label_theta"
    CLASS
      EXPRESSION ([wind_speed] * 1.945 >= 3)
      LABEL
        FORCE TRUE
        TYPE TRUETYPE
        FONT weather
        ANTIALIAS TRUE
        COLOR 0 0 0
        POSITION CC
        OFFSET 2 -9
        BUFFER 0
        PARTIALS TRUE
        SIZE 25
      END
      STYLE END
    END
    CLASS
      EXPRESSION ([wind_speed] * 1.942 < 3)
      SIZE 5
      SYMBOL 'circle'
      OUTLINECOLOR 0 0 0
      COLOR 255 255 255
    END
  END

When doing a WFS request, a selection on the geometry column from a table will actually return all of the row fields(xml tagged) for the selected rows along with the associated geometry returned in GML tags - what we do to better support this functionality is keep subtables which are populated with just the latest data formatted in the row layout we want to return to a http query and run WFS queries against the associated subtables.

Equipment and Variable Inventory

This inventory is a database and queriable map housing detailled information about observation platforms, sensor equipment, and environmental variables measured across the SEACOOS region. It presents the spatial resolution of sensors and variable measurements while also serving to facilitate technical discussion amongst the SEACOOS observation community. Visit the SEACOOS Equipment and Variable Inventory

An additional goal is to link this information as metadata to specific observations and thus assess the quality of measurements made by specific equipment over time. This equipment assessment requires creating a database with an eye towards historical data, since QA/QC procedures may later determine a problem with particular data and thus an individual sensor. For example, that all of the measurements made by the thermosalinagraph Z on platform X between March and April were off by three degrees. If you are storing observations, you need to store the equipment metadata for those observations as long as the observation data themselves.

Our basic inventory schema is as follows: [need to create a graphic to go in here]

Institutions manage Platforms (towers, buoys, etc. - right now this is restricted to in situ sensors)

• Platforms house Equipment (sensors and support equipment)
  • Equipment (sensors) measure variable(s)

• Contacts are associated with a piece of Equipment

• GIFsicle - produces animated gifs
• Anis - Java-based applet for animating images

• MapLab - MapServer application builder
• GMT - Generic Mapping Tools

Equipment and Variable Inventory

• Installation Process – precursor configurations/libraries ready? See Appendix I below.

• Apache (latest version)

• postgresql 7.4.1

• PostGIS 0.8.1 (follow installation instructions here)

• Perl 5.8.0 (get latest version from perl.com)
  • netcdf-perl-1.2.2 (for data scout)
  • udunits-1.12.1 (for data scout)

• ImageMagick 5.5.7 (build the PHP .so)

     ./configure --with-perl=/usr/local/perl/bin/perl

• PHP 4.3.2 (both the cgi and cli binaries)

     ./configure \
     --enable-shared \
     --with-regex=system \
     --with-gd \
     --with-ttf=/usr \
     --enable-gd-native-ttf \
     --with-jpeg-dir=/usr \
     --with-png-dir=/usr \
     --with-zlib \
     --enable-force-cgi-redirect \
     --enable-dbase \
     --with-config-file-path=/etc/httpd/conf \
     --with-freetype-dir=/usr/include/freetype \
     --with-pgsql \
     --with-dbasel

• MapServer-related installs
  • proj-4.4.7
  • gd-2.0.28
  • gdal-1.2.4
  • libwww-5.3.2-20011211
• MapServer 4.0.1 (latest version is 4.4.x, but let's stick w/ 4.0.1 for now)

        ./configure --with-proj \
        --with-wmsclient \
        --with-libwww \
        --with-gdal \
        --enable-runpath \
        --with-php=../php-4.3.2 \
        --without-tiff \
        --with-gd=/usr/local \
        --enable-force-freetype1 \
        --enable-internal-ld-detect \
        --with-postgis \
        --with-wfs \
        --with-ogr

• GMT 3.4.3

-Step----------------------------------------------Time---------------Have/Need?--

UNIX command line

knowledge of RDBMS

Perl/PHP

system administration

web server admin

image

Red Hat Enterprise Linux

Apache web server free

(Assess who are your data providers, what are they collecting, how often are they reporting, etc. The time also depends on whether or not you can/want to use the existing SEACOOS NetCDF.)

Purchase, set up, and install software and hardware 4-8 weeks

(try it out on MapLab^? first)

Configure PHP files to talk to map files 4-10 weeks

enable OGC WMS functionality 2-6 weeks

Security

Software Overview

• GIFsicle - produces animated gifs
• Anis - javascript-based animated gifs
• MapLab^? -

AppendixI^? - Installation Tips

The following is a very simplified 30,000 foot view of the visualization implementation, broken down to help the techs help the administrators get a handle on what is involved. The idea is to move through the list, determine what steps need to happen first, and assess each step along the following guidelines:

Most of these answers will depend entirely on the organization, and the organization's existing infrastructure, experience, and skillsets. As any experienced technician knows, it is impossible to foresee every complication, and things rarely work exactly as promised in the manual. Still, one can make some basic estimates. For example, it should take no more than an hour to download and install the needed Perl libraries. This assumes, however, that you did your homework and know the versions and compatibility of your software, hardware, etc., and that you know what you're doing. If you really don't know what you're doing, there's no way we can write everything into this cookbook to save you. Find the nearest exit and proceed calmly out of the tech building.

-Step----------------------------------------------Time---------------Have/Need?------Cost (if new)

Apache web server v.? free

PostgreSQL^? v.? free

netcdf-perl module free

udunits library free

PHP v.4 free

MapServer^? v.? free

Review hardware to be used

(This is very specific to your project, and naturally depends on the breadth and depth of your data and your audience. Take the time to think about this now.)

Equipment Inventory

Our equipment inventory is really a station/equipment/variable inventory that attempts to show the spatial resolution of sensors and equipment. The goal is to tie this information to specific observations made so that, over time, one can begin to assess the quality of measurements made by specific equipment. The equipment assessment part requires designing your database with an eye towards historical data, since QA/QC flags may later determine a problem with the sensor. For example, that all of the measurements made by the thermosalinagraph on platform XYZ between March and April were off by three degrees. If you are storing observations, you need to store the metadata about the equipment that recorded those observations as long as the observations are in your archives.

Our basic inventory schema is as follows: [need to create a graphic to go in here]

Institutions have Platforms (towers, buoys, etc. - right now this is restricted to in situ sensors)

Platforms have equipment on them

Equipment can measure variable(s)

Institutions also have Contacts

Contacts are associated with a piece of equipment

The Supervisor's Overview

The following is a very simplified 30,000 foot view of the visualization implementation, broken down to help the techs help the administrators of a given project get a handle on what is involved. The idea is to move through the list, determine what steps need to happen first, and assess each step along the following guidelines: What do we already have? What do we need to acquire (and what will it cost)? In what order do the steps needs to proceed (e.g., does it make sense to buy the software before buying the hardware?) How long should each step take (in a perfect, works-the-way-it's-supposed-to world)? How long until we are up & running? i.e, Other people are watching and waiting - how do I determine a reasonable delivery date that won't set me up to fail?

Most of these answers will depend entirely on the organization, and the organization's existing infrastructure, experience, and skillsets. As any experienced technician knows, it is impossible to foresee every complication, and things rarely work exactly as promised in the manual. Still, one can make some basic estimates. For example, it should take no more than an hour to download and install the needed Perl libraries. This assumes, however, that you did your homework and know the versions of all your software, hardware, etc., and that you know what you're doing. If you really don't know what you're doing, there's no way we can write everything into this cookbook to save you. Find the nearest exit and proceed calmly out of the tech building.

[Maybe this could be a downloadable Word or Excel file?] In this list, we have given the software versions that we have used because we know the combination(s) work. This does not mean other versions will not work, you'll just have to do the background work or experiment

-Step- -Time- -Have/Need?- Cost (if new) Review software needed 30 mins Apache web server v.? PostgreSQL^? v.? Perl v.? netcdf-perl module udunits library ImageMagick^? v.? GnuPlot^? v.? GMT PHP v.4 MapServer^? v.? Proj4 Gdal GD LibWWW^?

Define variables and metadata to be collected 1-5 weeks (depends on whether or not you can/want to use the existing SEACOOS NetCDF)

Build database according to equipment, variable, and model info to be stored Uses PostgreSQL^? 3 days

Install and test data scout 1 day

[...insert other installation steps here...]

• GMT

PostgreSQL database with PostGIS extension

Technology Development Overview

This document covers the major steps SEACOOS followed to create a near real-time data stream of in-situ observations, model output, and remotely sensed imagery. Our hope is that an understanding of this process will help other ocean observing efforts as they formulate their initial technology strategies. This process is outlined in a linear fashion below, recognizing however, that iteration between several steps at once is likely.

1. Conceptualize available data types and consider possible storage schemas: SEACOOS collects data on ocean state variables and biogeochemistry in the form of in-situ observations, circulation models, and satellite remote sensing. The spatial and temporal frequency of these data is highly variable and required considerable forethought to address all possible data configurations.

2. Develop and standardize data ontologies, file formats, and transport protocols: Developing a dictionary of common language to refer to our disparate data was a significant achievement. This was crucial in the development of SEACOOS data-specific file formats using netCDF. Although DODS/!OPeNDAP servers are available at partner institutions, currently the raw netCDF files are uploaded directly, bypassing these servers.

3. Determine desired applications and requisite software packages: SEACOOS visualizes data spatially and graphically, providing researchers and an external audience with access to this information in near real-time. Open source GIS (MapServer) and graphics packages (GnuPlot) are used to drive these applications.

4. Determine database schemas for observations, model output, satellite imagery, and equipment metadata: With both the data and application ends of the data stream conceptualized, a database schema to enable their connection was developed. The open source PostgreSQL database, with PostGIS extension for mapping, is used by SEACOOS.

5. Address hardware ramifications of implementing the database and applications under consideration: [We have not talked about hardware anywhere in this doc so far, so someone make sure I’ve stated this correctly?] SEACOOS utilizes separate servers to house the database, web mapping application, and project website.

6. Implement schemas and applications: Intermediary code development is crucial in automating and connecting these disparate technologies to handle a near real-time data stream. SEACOOS uses perl as the primary scripting language to obtain, parse, and store incoming data in the PostgreSQL database. PHP/!MapScript is used to create interactive mapping applications, embedding MapServer controls within HTML pages.

7. Leverage solutions “outward” to external audiences: As part of the IOOS push, SEACOOS cascades much of its data into this larger data aggregation effort. The OGC services are utilized to transfer map images and raw data to external GIS applications.

Software Overview

General packages:

• Apache web server
• PostgreSQL database
  • PostGIS extension
• Perl
  • netcdf-perl module
  • udunits library
• ImageMagick
• GnuPlot

Application packages:

• PHP4 - web based templates
• MapServer - web mapping application
  • Proj4 - cartographic projection library
  • Gdal - geospatial data abstraction library
  • GD - graphic library
  • LibWWW - w3c protocol library

PostgreSQL^? database with PostGIS extension

• Installation Process - precursor configurations/libraries ready? See seacoos_install.txt.

• • PostgreSQL database
  • PostGIS extension

• [CP's caro-coops BB post here?][PHP and MapServer installation tips]

Visualization fields

• Unit conversion (for example, celsius to fahrenheit conversions)

Mapfile Details

Model Output

1. Database schema preparation: Pick a physical variable of interest (like wind speed & direction, sea surface temperature). Each variable is defined within a separate database table (one record for each measurement). One table would contain station id, time, latitude, longitude, depth, wind_speed, wind_direction, and associated metadata fields. Another table would contain station id, time, latitude, longitude, depth, sea_surface_temperature, and associated metadata fields. Table joins are possible using SQL, but are not currently used. Instead each separate table generates a GIS layer which can then be superimposed.
2. Determine how the measurements will be defined in time and space: SEACOOS uses the same base time frame of seconds elapsed since 2025-01-01 (this can be a floating point number for subsecond intervals). For spatial considerations SEACOOS has developed a framework for datatypes relating to the degrees of locational freedom of the measurement point(s). This provides guidelines on how these should be defined in a netCDF file via dimensions and attributes.
3. Additional considerations for display purposes: Metadata fields are added which take into consideration:

• whether the data point should be shown in the display
• whether the data point can be normalized given an agreed upon normalization formula
• how the data point is orientated as it relates to a specific locational framework
• how the data are interpolated and chosen for display

• USC's perl data scout which gather the latest data(..._latest.nc) from providers on a periodic basis and converts these netCDF files to SQL INSERT statements to populate the relational database.
• Documentation on the current SEA-COOS CDL v2.0 format SEACOOSv2.0.

Remote Sensing

!PostgreSQL database with PostGIS extension

The PostgreSQL database is “spatially enabled” using the PostGIS extension for PostgreSQL. PostGIS adds several geospatial objects to the supported datatypes in PostgreSQL. This functions as the spatial database engine for all subsequent GIS data visualization. This extension converts the raw locations of SEACOOS observation data and stores them in a GIS-accessible geometry column, enabling mapping and spatial query functionality. GIS mapping applications utilize these new columns to render the geometric topologies they contain. PostGIS fields can also be populated from other common GIS data formats such as ESRI shapefiles. For details, see above.

• 'by variable' where the tablename is that of the variable measured and each row corresponds to a measurement time, station id, and possibly lat, long, and depth describing the measurement point and the measurand value. Example below...

Currently the GIS favors a 'by variable' approach which corresponds to variable data layers. This format is concise, amenable to query, and resultset packaging (the ability to mix and match variables which have a similar reference scheme on each variable table). Issues of varying temporal sampling resolutions across multiple stations are also better handled in this form. SEACOOS is developing programs to convert other table formats to this format. See here.

Click here for database descriptions of the wind and sst tables which USC currently utilizes. We make efforts to keep from 'normalizing' the table into subtables preferring a single table approach with redundancy in certain fields. Since the storage needs are initially low, the database remains conceptually and operationally simple. Table performance can be further optimized by partitioning and use of VACUUM, COPY, CLUSTER commands and other indexing schemes applied similarly across these repeated table structures.

Visualization fields

• GIS display purposes (should this be displayed in the GIS or not)

Remote Sensing Particulars

• Installation Process
  • precursor configurations/libraries ready? See seacoos_install.txt.

Web mapping with MapServer

• Installation Process – precursor configurations/libraries ready? See seacoos_install.txt

WMS and WFS instances

• • Wrapper script (PHP?) around a mapfile with set of metadata requirements

One key consideration was to develop solutions for the most complex data model and let everything else fall out as a subset of that case. With this in mind, SEACOOS chose to model the data by making all variables (including latitude, longitude and depth) a function of time. Other data forms allowed for programmatic 'shortcuts' based on the types of dimensions presented in the file - for instance, if latitude and longitude were each a dimension of 1 point, then the file was processed as a fixed station. Most of the debate centered around whether description should be carried in the variable attributes or in the dimension or variable naming convention. COARDS, CF conventions were adopted where possible. SEACOOS uses the netCDF format to transport in-situ observations and model output.

Format discussion

• All the canonical forms which were under consideration.
• Documentation on the current SEA-COOS CDL v2.0 format SEACOOSv2.0.

Quick links

• MapServer-based Observations Interactive Map
• DODS/OPeNDAP data access
• CSV (Comma Separated Value/Excel) files and perl generation programs

Format discussion

Quick links

• MapServer-based Interactive Model Display
• Documentation on the current SEA-COOS CDL v2.0 format SEACOOSv2.0.

• MapServer-based Remote Sensing Interactive Map

1. Database schema preparation: Pick a physical variable of interest (like wind speed & direction, sea surface temperature). Each variable is defined within a separate database table (one record for each measurement). One table would contain station id, time, latitude, longitude, depth, wind_speed, wind_direction, and associated metadata fields. Another table would contain station id, time, latitude, longitude, depth, sea_surface_temperature, and associated metadata fields.
2. Determine how the measurments will be defined in time and space: SEACOOS uses the same base time frame of seconds elapsed since 2025-01-01 (this can be a floating point number for subsecond intervals). For spatial considerations SEACOOS has developed a framework for datatypes relating to the degrees of locational freedom of the measurement point(s). This provides guidelines on how this should be defined in a netCDF file via dimensions and attributes.
3. Considerations for display purposes: metadata fields are added which take into consideration the measurement and - whether it should be shown in the display - can be normalized given an agreed upon normalization formula - orientation as it relates to the agreed group locational framework - how data is interpolated and chosen for display

• Documentation on the current SEA-COOS CDL v2.0 format SEACOOSv2.0.
• Charlton's perl programs which gather the latest data(..._latest.nc) from providers on a periodic basis (which has come to be termed the data 'scout') and converts these netCDF files to SQL INSERT statements to populate the relational databse.

PHP/MapScript

Mapfile Details (content under development)

• MapServer-based interactive map

• MapServer-based interactive map

• MapServer-based interactive map

DB Additions for Visualization

• GIS display purposes (for example, should this be displayed in the GIS or not) Certain in-situ data requires MapServer-specific fields to be populated in order to create maps. In-situ data must be collected into an hourly snapshot table. See function set_wind_prod_show() as an example of how in-situ winds are selected to appear in an hourly snapshot table. The flow of information is that raw in-situ data is inserted into a production table, e.g. wind_prod. set_wind_prod_show() is run after a complete raw ingestion is finished. The resulting records that are flagged as being eligible to be included in the hourly snapshot table,are collected and inserted into wind_map.
• Unit conversion fields (for example, celsius to fahrenheit conversions)
• Frame of reference (for example, does a positive z value correspond to up or down)
• Contact or standards metadata (for example, provider URL, standard XML fields for WMS, WFS or other standards metadata)

Remote Sensing Idiosyncrasies (content under development)

• PostgreSQL database schema [*NEED HELP : show schema*]

Mapfile Details (content under development) I've cut and pasted lots here and am now organizing - JC (northern one)

• “data” parameter: SQL to hit PostgreSQL DB
  • Most of the time- and space-specific programming occurs in map_session.php. MapScript allows for the dynamic setting of the DATA and FILTER parameters in the base .map file which MapServer will use to determine what time and space to display. Here is a snip from map_session.php that shows how the wind_obs FILTER is set.
  • In addition to time and space sensitivities, map_session.php also handles unit specifications, e.g. wind speeds in knots, miles per hour, and meters per seconds. MapScript adjusts the DATA parameter accordingly.

• Remote Sensing visualization details – lookup tables [explained above]
  • For a given request of a remotely sensed layer at a given time, MapScript code references the control .map file and sets the DATA clause to define the base raster image to fulfill the users space and time query

Software Installation and configuration

• Installation Process – precursor configurations/libraries ready?
• See seacoos_install.txt

OGC web services

External presentation of SEACOOS data is enabled compliant with standards set by the Open Geospatial Consortium (OGC). OGC web mapping services are XML based and are intended to be platform independent, web-enabled, representations of GIS data. The services can be accessed, controlled, and presented by web browsers as well as other GIS software platforms (i.e. ESRI Interoperability toolbar, GAIA application). SEACOOS OGC compliant services rely on the mapserv CGI engine. SEACOOS provides a Web Mapping Service (WMS) and Web Feature Service (WFS). The WMS feed returns a static, georeferenced image to a user’s browser or GIS platform, while the WFS feed returns actual feature data, allowing visualization control and spatial analysis on these data externally.

Other ocean observing research projects currently ingest SEACOOS OGC feeds, the OpenIOOS project and the GoMOOS project. Several other regional GIS projects also ingest and display SEACOOS data served via WMS: NCOneMap. This method of integration is a critical step in the desired aggregation of ocean observing data across regional, national, and global scales.

• WMS and WFS instances and links
  • Wrapper script around a mapfile with set of metadata requirements
  • Customized Perl wrappers were written to accommodate time parameters. Here is an example of how SEACOOS remotely sensed WMS requests are handled.
  • Descriptions of how to access these OGC-ready layers can be found here: WMS and WFS in-situ layers and WMS remotely sensed layers.
  • Open Geospatial Consortium

Support Visualization Applications

Data Types

In-situ Observations

Model Ouput

Remote Sensing

Aggregation Process

In-situ Observations and Model Output

Remote Sensing

Database Specifications

PostgreSQL^? database with PostGIS^? extension

Visualization Process

• Implementation details

Database specifications

PostgreSQL^? database

PostGIS geospatial enablement

Data structures / canonical forms

• or 'by variable' where the tablename is that of the variable measured and each row corresponds to a measurement time, station id and possibly lat, long, and depth describing the measurement point and the measurand value.

point form

Remote Sensing idiosyncrasies (content under development)

• • Processing by Charlton – e.g., composite procedure, etc.
    • general image fetching
      • fetch file based on embedded timestamp
      • remove background and watermarks using ImageMagick
      • cleaned image has embedded timestamp in the filename, associated .wld file, and has a matching reference in a DB lookup table
    • compositing
      • MODIS RGB images are also part of a compositing process
      • After a new RGB has been fetched, a script called build_composite.php is called which creates a resultant image of the past n passes (in our case 4) that are no older than y days (in our case 4).
      • This new image is similarly included as part of the DB lookup process as non composites are.
  • Storage – file system with pointer stored in PostgreSQL
    • file system
      • remotely sensed images are stored on the file system based on their layer name. e.g.: avhrr_sst, modis_chl, modis_rgb_composite, etc.
      • And the files under these directories have embedded timestamps as part of their filenames as well as a corresponding georeferncing file that MapServer requires. E.g. avhrr_sst_2004_04_01_03_06.png, avhrr_sst_2004_04_01_03_06.wld, avhrr_sst_2004_04_01_04_49.png, avhrr_sst_2004_04_01_04_49.wld.
    • PostgreSQL pointer
      • Distinct tables contain pointers to all remotely sensed images on the filesystem and is accessed by timestamp. E.g. The raster_oi_sst table DDL whose contents may be:

   pass_timestamp    |           local_filename           
---------------------+------------------------------------
 2025-08-01 17:30:00 | oi_sst/oi_sst_2004_08_01_17_30.png
 2025-08-02 17:30:00 | oi_sst/oi_sst_2004_08_02_17_30.png
 2025-08-03 17:30:00 | oi_sst/oi_sst_2004_08_03_17_30.png

Software Installation and configuration

Display process

After SEACOOS data are collected and aggregated, visualization procedures are implemented to represent this data for constituent user groups. These procedures provide immediate feedback and validation of SEACOOS data aggregation efforts, quickly addressing integration issues about data projection and resolution. These procedures are built using open source solutions where possible. The methods presented below encompass a significant amount of development work that has coalesced into a robust data visualization effort. This effort is an initial step toward leveraging SEACOOS project data into national and international ocean observing efforts.

Support Visualization Applications

SEACOOS was presented with the chance to define data standards and integrate in-situ observational, modeling and remote sensing data products from several institutions and programs. The GIS visualization provides immediate feedback and validation as to the effectiveness of integration efforts while also raising further questions about the quality and display of data.

The integration of the remotely sensed data into the SEACOOS program has provided a regional context for the real-time in-situ time series data, as well as baselines for the region’s surface waters from the historical satellite data records. The combination of these data sources will facilitate analyses by both coastal resource managers and researchers. The redundant data paths being established as part of the remote sensing activities will ensure that in the case of extreme events, the information flow to interested users will not be interrupted.

SEACOOS Data Management and Visualization Cookbook

Table of contents

Background

SEACOOS presented the chance to define data standards and integrate in-situ observational, modeling and remote sensing data products from several institutions and programs. The GIS visualization provides immediate feedback and validation as to the effectiveness of integration efforts while also raising further questions about the quality and display of data.

The in-situ and remote sensing layers are presented in the MapServer GIS and allow for raster query - drilling through data layers to obtain the point values. This allows calibration/validation between these products. GIS animation and time series graphs are also provided for all products. For the in-situ and modeling products, each involved institution was able to setup a DODS/OPeNDAP netCDF server to share a netCDF file representing the past 2 weeks worth of data. While this data is available via DODS/OPeNDAP, the amount of data being transmitted was only a few kilobytes, so for performance reasons when aggregating the data at the central relational database the file was uploaded directly(not utilizing the DODS/OPeNDAP API) and parsed with perl netCDF libraries.

Data types

In-situ observational

Quick links

• MapServer-based interactive map
• DODS/OPeNDAP data access
• CSV (Comma Separated Value/Excel) files and perl generation programs

Data locations

• http://nccoos.unc.edu/data/nos/proc_data/latest_v2.0
• http://seacoos.skio.peachnet.edu/proc_data/latest_v2.0
• http://nccoos.unc.edu/data/nws_metar/proc_data/latest_v2.0
• http://trident.baruch.sc.edu/usgs_data
• http://trident.baruch.sc.edu/storm_surge_data/latest
• http://seacoos.marine.usf.edu/data/seacoos_rt_v2
• http://oceanlab.rsmas.miami.edu/ELWX5/v2
• http://aigeann.ucsc.edu/cimtLatest
• http://trident.baruch.sc.edu/po.daac_data/OVW
• http://nccoos.unc.edu/data/nc-coos/latest_v2.0
• http://oceanlab.rsmas.miami.edu/wera

Formatting conventions

• All the canonical forms which were under consideration.
• Documentation on the current SEA-COOS CDL v2.0 format SEACOOSv2.0.

Program code

• Charlton's perl programs which gather the latest data(..._latest.nc) from providers on a periodic basis (which has come to be termed the data 'scout') and converts these netCDF files to SQL INSERT statements to populate the relational databse.

Modeling

Quick links

• MapServer-based interactive map

Data locations

• http://nccoos.unc.edu/data/nc-coos/model_data/quoddy/forecast
• http://nccoos.unc.edu/data/nc-coos/model_data/met/awip12
• http://nccoos.unc.edu/data/nc-coos/model_data/adcirc
• http://seacoos.marine.usf.edu/data/wfsmodel
• http://efsis.rsmas.miami.edu/netcdf

Formatting conventions

Remote sensing

Quick links

• MapServer-based interactive map
• Remote sensing listserv

Aggregation process

In-situ observational and modeling

The process for in situ and model data which is being formatted to a netCDF file is:

1. Pick a physical variable of interest (like wind speed & direction, sea surface temperature). Each variable is defined within it's own table(one record for each measurement). So there is a table which contains station id, time, latitude, longitude, depth, wind_speed, wind_direction, associated metadata fields. Another table which contains row_id,station id, time, latitude, longitude, depth, sea_surface_temperature, associated metadata fields. And so on... SQL allows us to join separate table data into views of combined data according to user query needs(so far we haven't needed to do this). Currently the separate variable tables are used to generate separate variable data maps and these maps can be superimposed via the GIS.
2. Define how the variable will be defined in time and location for SEACOOS, everyone has decided to use the same time frame of seconds(this can be a floating point number to indicate subsecond intervals) since 2025-01-01 for location considerations SEACOOS has developed a framework for datatypes(relating to the degrees of locational freedom of the measurement point(s)) which gives guidelines on how this should be defined in a netCDF file via dimensions and attributes.
3. Considerations for display purposes - metadata fields are added which take into consideration the measurement and - whether it should be shown in the display - can be normalized given an agreed upon normalization formula - orientation as it relates to the agreed group locational framework - how data is interpolated and chosen for display

The above steps result in the SEACOOS netCDF convention as a specific syntax of dimensions and attributes which can be programmatically parsed by Charlton's perl script (scout) which downloads the netCDF files via HTTP from data providers and populates the variable tables or alerts the provider when there is a problem with the data being input.

Charlton then uses MapServer functions to convert from the table data to a raster image which is displayed as a data layer in the GIS.

For each new physical in situ variable to be added, aside from what the naming convention that will be for the new variable, step 3 is the only step which should be repeated. Steps 1 & 2 are a one time initial group discussion/decision processes which are subject to periodic consideration and revision if need be. Step 3 also tends to take the product (GIS in this case) considerations into mind, whereas the work accomplished in steps 1 & 2 should be universally applicable for aggregation needs across a variety of products.

Remote sensing

The process for remote sensing data using PNG files is:

• The PNG files themselves contain locational metadata used for placement/interpretation of the raster image within the GIS and the filenames contain timestamp information and this timestamp is used by Charlton's code to determine which image should be displayed for given temporal conditions. No underlying relational database support is needed for the initial display of the images within the GIS.

• Relational database support is currently used to support raster query functions(the raster image is converted to a table lookup of RGB values which correspond to variable measurement). This functionality should be temporary as MapServer should better support raster query in the future.

Biological

The process for biological data is still up for discussion. One way of thinking of datatypes is as point, raster and vector/coverage. I think we have working models for working with point (in situ) and raster (PNG, etc), but with biological datasets, I'd say my thinking would be to possibly add a time dependent vector/coverage which defines a collection or population locational boundary. As a table representation, this would mean that each population/collection would have its own time indexed table with a link to a vector/coverage subtable. This would keep time as a rigid searchable index and allow a population to be defined as a collection of points or a boundary of an area. Populations could still be described at a point or as part of a raster (image), the vector/coverage datatype would be more to accommodate population datatypes which don't fit well into our existing framework of point and raster datatypes.

Display process

After SEACOOS data are collected and aggregated, visualization procedures are implemented to represent this data for constituent user groups. These procedures provide immediate feedback and validation of SEACOOS data aggregation efforts, quickly addressing integration issues about data projection and resolution. These procedures are built using open source solutions where possible. The methods presented below encompass a significant amount of development work that has coalesced into a robust data visualization effort. This effort is an initial step toward leveraging SEACOOS project data into national and international ocean observing efforts.

Database specifications

SEACOOS uses the open source PostgreSQL relational database to store in-situ and remotely sensed data collected from project partners. PostgreSQL can be accessed by a number of front-end applications and can also be enabled for spatial querying and mapping. This enablement is achieved using the PostGIS extension for PostgreSQL. PostGIS adds several geometric objects to the supported datatypes in PostgreSQL. This combination can then function as the spatial database engine for all subsequent SEACOOS data visualization.

PostgreSQL database

SEACOOS data is stored in two PostgreSQL database instances at the University of South Carolina (USC). One instance contains the in-situ observations and remotely sensed data. The other contains model output data and duplicate in-situ observations, used for “round-robin” updating. The databases are partitioned into separate tables for each in-situ observation variable, remotely sensed raster layer, and model variable layer per hour. The remotely sensed tables do not house the actual images but pointers to the image files and their ancillary boundary files. The database is used to execute raster queries, which require the image RGB values to be referenced against a look-up table of actual variable measurements.

• Remote Sensing idiosyncrasies (content under development)
  • Processing by Charlton – e.g., composite procedure, etc.
    • general image fetching
      • fetch file based on embedded timestamp
      • remove background and watermarks using ImageMagick
      • cleaned image has embedded timestamp in the filename, associated .wld file, and has a matching reference in a DB lookup table
    • compositing
      • MODIS RGB images are also part of a compositing process
      • After a new RGB has been fetched, a script called build_composite.php is called which creates a resultant image of the past n passes (in our case 4) that are no older than y days (in our case 4).
      • This new image is similarly included as part of the DB lookup process as non composites are.
  • Storage – file system with pointer stored in PostgreSQL
    • file system
      • remotely sensed images are stored on the file system based on their layer name. e.g.: avhrr_sst, modis_chl, modis_rgb_composite, etc.
      • And the files under these directories have embedded timestamps as part of their filenames as well as a corresponding georeferncing file that MapServer requires. E.g. avhrr_sst_2004_04_01_03_06.png, avhrr_sst_2004_04_01_03_06.wld, avhrr_sst_2004_04_01_04_49.png, avhrr_sst_2004_04_01_04_49.wld.
    • PostgreSQL pointer
      • Distinct tables contain pointers to all remotely sensed images on the filesystem and is accessed by timestamp. E.g. The raster_oi_sst table DDL whose contents may be:

   pass_timestamp    |           local_filename           
---------------------+------------------------------------
 2025-08-01 17:30:00 | oi_sst/oi_sst_2004_08_01_17_30.png
 2025-08-02 17:30:00 | oi_sst/oi_sst_2004_08_02_17_30.png
 2025-08-03 17:30:00 | oi_sst/oi_sst_2004_08_03_17_30.png

Data structures / canonical forms

The structure of temporal, geospatial data as it is stored in various formats should hopefully be capable of having its structural elements described in a handful of forms. Describing and labeling these forms(deciding what should be abstracted away as well) are the beginning steps before automated programmatic conventions, labels and processing can be utilized in data transformation. As an example, two predictable forms for storing buoy data are:

• 'by station' where the tablename is that of the station and each row corresponds to all the variable reading for a given time measurement
• or 'by variable' where the tablename is that of the variable measured and each row corresponds to a measurement time, station id and possibly lat, long and depth describing the measurement point and the measurand value.

point form

The following represents a basic table layout which might be implemented on a postgresql database.

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)
);

More info: generic table creation details.

Database descriptions of the wind and sst tables which Charlton currently utilizes. There are matches to the structure listed above with additional fields added for:

• GIS display purposes (for example, should this be displayed in the GIS or not)
• unit converted fields (for example, celsius to fahrenheit conversions)
• frame of reference (for example, does a positive z value correspond to up or down)
• contact or standards metadata (for example, provider URL, standard XML fields for WMS, WFS or other standards metadata)

We make efforts to keep from 'normalizing' the table into subtables preferring a single table approach with redundancy in certain fields. Since the storage needs are low to begin with this means we can keep things conceptually and operationally simple. Table preformance can be further optimized by partitioning, and familiarity /use of VACUUM, COPY, CLUSTER commands and other indexing schemes which can be applied similarly across these repeated table stuctures.

• • Access by mapfile – lookup tables and projection files?
    • lookup tables and projection files
      • For a given request of a remotely sensed layer at a given time, MapScript code references the control .map file and sets the DATA clause to define the base raster image to fulfill the users space and time query

• Implementation details (content under development)
  • PostgreSQL database schema [*NEED HELP : show schema*]
  • Database massaging for visualization purposes - fields added?
    • Certain in-situ data requires MapServer-specific fields to be populated in order to create maps. In-situ data must be collected into an hourly snapshot table. See function set_wind_prod_show() as an example of how in-situ winds are selected to appear in an hourly snapshot table. The flow of information is that raw in-situ data is inserted into a production table, e.g. wind_prod. set_wind_prod_show() is run after a complete raw ingestion is finished. The resulting records that are flagged as being eligible to be included in the hourly snapshot table,are collected and inserted into wind_map.
  • How to access the PostgreSQL instances for mapping
    • “Data” parameter in mapfile
      • Most of the time- and space-specific programming occurs in map_session.php. MapScript allows for the dynamic setting of the DATA and FILTER parameters in the base .map file which MapServer will use to determine what time and space to display. Here is a snip from map_session.php that shows how the wind_obs FILTER is set.
      • In addition to time and space sensitivities, map_session.php also handles unit specifications, e.g. wind speeds in knots, miles per hour, and meters per seconds. MapScript adjusts the DATA parameter accordingly.

PostGIS geospatial enablement

The PostgreSQL database is “spatially enabled” using the PostGIS extension, creating a back-end spatial database. This extension converts the raw locations of SEACOOS observation data and stores them in a GIS-accessible geometry column, enabling mapping and spatial query functionality. GIS mapping applications then point to these new columns to render the geometric topologies they contain. PostGIS fields can also be populated from other common GIS data formats such as ESRI shapefiles. For details, see above.

Software Installation and configuration

• Installation Process – precursor configurations/libraries ready?
  • See seacoos_install.txt.
• Servers and connections
  • PostgreSQL database: http://www.postgresql.org
  • PostGIS extension: http://postgis.refractions.net

Web mapping with MapServer

Visualization of SEACOOS data over the web utilizes an open source mapping platform known as MapServer. MapServer is well adapted for use with PostgreSQL/!PostGIS and can power open web mapping services within a flexible, scriptable environment. Although MapServer can parse ESRI data formats, these are not required and open source customization is encouraged. MapServer utilizes a “mapfile” (*.map) to setup parameters and control map images for each map instance. Elements of this mapfile may then be manipulated via URL query statements, giving MapServer its web mapping flexibility. The instance powering SEACOOS maps is housed at USC, local to the SEACOOS aggregate database.

Mapserv CGI

MapServer is used in two modes to map SEACOOS data. For more basic applications, the mapserv CGI is used. The gives control over basic elements of the mapfile via a URL query: toggling map layers on/off, adjusting the map size, and panning the map extents. More detailed changes require editing the MapServer mapfile served from USC. For custom GIS applications, GUIs are created that generate URL query strings, which control the map images from the USC MapServer. The mapserv CGI powers the OGC web services (WMS and WFS) available from USC.

• Implementation Details (content under development)
  • Mapfile details
    • “data” parameter: SQL to hit PostgreSQL DB [explained above]
  • WMS and WFS instances and links
    • SEACOOS maps and underlying data are accessible via WMS and WFS OGC requests.
      • Customized Perl wrappers were written to accommodate time parameters. Here is an example of how SEACOOS remotely sensed WMS requests are handled.
      • Descriptions of how to access these OGC-ready layers can be found here: WMS and WFS in-situ layers and WMS remotely sensed layers.
  • Getting images into MapServer [explained above]
  • Remote Sensing mapfile and visualization details – lookup tables [explained above]

PHP-MapScript

A second mode of function for MapServer utilizes an additional module called MapScript to control the same base mapfile. SEACOOS uses the PHP variant of MapScript because of PHP’s ability to work with HTML. This functionality enables much more control over the mapfile from a web browser. Most parameters in the mapfile can be controlled and specified via the URL query string. In addition to the abovementioned CGI functionality, URL queries can be constructed to adjust the size and location of the scalebar, move the legend placement, change layer unit settings, and place watermark images. This functionality works behind the GUI products driving the Interactive Observations Map, the Interactive Model Display, and the Remote Sensing Development Map now served at USC (links below). Components of this functionality have been leveraged to drive some of the internal data exploration efforts mentioned below.

Product links (listed above)

• Implementation details (content under development)
  • PHP MapScript Code? [map_session.php explained above]
    • [* NEED HELP* : give link to portal_rs source code tree] In addition to map_session.php, seacoos_bathy_contents.php and seacoos_bathy.phtml are top-level control files that contain references to underlying code that defines the functionality of the interactive maps.
  • SEACOOS region extents
    • Predefined regions for quick access are defined within the .map file and are referenced upon interactive map startup.

Support applications

Several other open source solutions are used to graph, animate, and query SEACOOS data. GIFsicle and AnimationS (AniS) are used to create and control data animations over the web. ImageMagick (and perl::ImageMagick) is used for image manipulation and to execute raster data queries. Mouseover query functionality is enabled with the searchmap component of MapServer, creating an imagemap of the existing map image for queries. Graph Plotting Module for Perl (GD::Graph) is used to generate time series graphs. All of these tools are scriptable and run behind the SEACOOS interactive maps.

• Immplementation details
  • ImageMagick details for image manipulation and raster queries. Here is an example of extracting a sea surface temperature from an image.
  • Animation Routine – user details. Here is an example of how to produce an animated gif of map images.

Data exploration applications

Further data exploration and visualization has been enabled to allow researchers quick access to the SEACOOS database. These tools are web pages that rely on PHP to interact with the PostgreSQL database and MapServer, presenting database content ranges and simple maps. The following pages are in use by SEACOOS researchers and are updated in near real time:

A data overview page displays a list of min and max timestamps for all SEACOOS interactive map data (model input data and observations data). It also provides links to individual pages for each data layer displaying the specific time slices available for each individual layer. Access is also available to map images of each layer and each time and date stamp, across 3 selectable regions. While these pages are not intended for the general public, they provide on-demand access and visualization to the entire SEACOOS database for a distributed research community.

The data animation page takes URL query string parameters and creates animations of data ingested by SEACOOS. The animation routine combines maps and graphs for most SEACOOS data. Users have control over the GIS layers, scale, platforms to graph, and time step. These animations are then served via another PHP generated page with full animation movement controls. These animations are created, stored, and served at USC until the user asks for them to be removed. Example here.

The flexibility of PHP – MapScript allows other SEACOOS institutions to utilize the USC database for further visualization development. A cached observation page serves static images each hour for a variety of SEACOOS data layers and sub regions. This page is supplied by a script that sends modified URL query strings to the USC MapServer and caches the map images that are returned.

OGC web services

External presentation of SEACOOS data is enabled compliant with standards set by the Open Geospatial Consortium (OGC). OGC web mapping services are XML based and are intended to be platform independent, web-enabled, representations of GIS data. The services can be accessed, controlled, and presented by web browsers as well as other GIS software platforms (i.e. ESRI Interoperability toolbar, GAIA application). SEACOOS OGC compliant services rely on the mapserv CGI engine. SEACOOS provides a Web Mapping Service (WMS) and Web Feature Service (WFS). The WMS feed returns a static, georeferenced image to a user’s browser or GIS platform, while the WFS feed returns actual feature data, allowing visualization control over these data externally.

External web service audience

Other ocean observing research projects currently ingest SEACOOS OGC feeds, the OpenIOOS project and the GoMOOS project. Several other local GIS projects also display SEACOOS data via WMS feeds. This method of integration is a critical step in the desired aggregation of ocean observing data across regional, national, and global scales.

• Implementation details (content under development)
  • Wrapper script around a mapfile with set of metadata requirements [explained above]
  • Open Geospatial Consortium

-- CharltonPurvis - 23 Mar 2025