cages.txt

Serving CAGES Data from Databases via ERDDAP
============================================
Steven K. Baum
v0.5, 2013-02-18
:doctype: book
:toc:
:icons:

:numbered!:

[preface]

Executive Summary
-----------------

Using MySQL/PostgreSQL and ERDDAP to Serve CAGES Data.

Preface
-------

The use MySQL with CAGES data.

:numbered:

Required Basic MySQL Commands and Information
---------------------------------------------

In the course of preparing tables within MySQL for export to ERDDAP,
we found the following MySQL commands, procedures and datatype
information most useful.

Login
~~~~~

-----
mysql -u root -p
Enter password:  *******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1274228
Server version: 5.5.27-log Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>
-----

Database Commands
~~~~~~~~~~~~~~~~~

Check for Database Names
^^^^^^^^^^^^^^^^^^^^^^^^

-----
SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| CAGES              |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.03 sec)
-----

Select a Database to Use
^^^^^^^^^^^^^^^^^^^^^^^^

-----
use CAGES;
-----

Table Commands
~~~~~~~~~~~~~~

Show the Tables in the Database
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

-----
show TABLES;
+------------------------------------------+
| Tables_in_CAGES                          |
+------------------------------------------+
| Alabama_CPUE                             |
| Alabama_Gear                             |
| Alabama_Hydrological                     |
| Alabama_Lengths                          |
...
| Mississippi_Trawls                       |
| TMPTAB                                   |
| Texas_Bays                               |
| Texas_CPUE                               |
| Texas_Hydrological                       |
| Texas_Lengths                            |
| Texas_Samples                            |
| Texas_Species                            |
| Texas_Stations                           |
| Texas_SubBays                            |
| Texas_Trawls                             |
| example                                  |
+------------------------------------------+
64 rows in set (0.00 sec)
-----

Change a Table Name
^^^^^^^^^^^^^^^^^^^

Use backticks for names with spaces.

-----
rename table `Texas_CPUE` to Texas_CPUE;
-----

List Rows of Table
^^^^^^^^^^^^^^^^^^

-----
select * from Texas_SubBays;
-----

To limit the number of rows listed:

-----
select * from Texas_SubBays limit 5;
-----

Find Total Number of Rows in Table
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

-----
select count(*) from Texas_CPUE;
-----

Delete a Table
^^^^^^^^^^^^^^

-----
drop table Texas_CPUE_New;
-----

Clone a Table
^^^^^^^^^^^^^

First you recreate the structure of the table to be cloned, and then
you populate that structure.

-----
create table CAGES_Texas_IOOS_Biology_10 like CAGES_Texas_IOOS_Biology;
insert CAGES_Texas_IOOS_Biology_10 select * from CAGES_Texas_IOOS_Biology;
-----

Remove All Rows from a Table
^^^^^^^^^^^^^^^^^^^^^^^^^^^^

This will delete all the data in the table and return the number
of rows deleted.

-----
delete from CAGES_Texas_IOOS_Biology_10;
-----

This will quickly delete all the data in the table.

-----
truncate table CAGES_Texas_IOOS_Biology_10;
-----

Create an Index for a Table
^^^^^^^^^^^^^^^^^^^^^^^^^^^

-----
create index name_index on table(name);
-----

Show the Index for a Table
^^^^^^^^^^^^^^^^^^^^^^^^^^

-----
show index from Texas_Trawls;
-----

to obtain:

-----
+------------+------------+-------------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table      | Non_unique | Key_name                | Seq_in_index | Column_name  | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+------------+------------+-------------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Texas_Bays |          1 | Texas_Bays_Bay_Location |            1 | Bay_Location | A         |          15 |     NULL | NULL   | YES  | BTREE      |         |               |
| Texas_Bays |          1 | Texas_Bays_Bay_Code     |            1 | Bay_Code     | A         |          15 |     NULL | NULL   | YES  | BTREE      |         |               |
+------------+------------+-------------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)
-----

from a table where:

-----
describe Texas_Bays;
-----

yields:

-----
+--------------+--------------+------+-----+---------+-------+
| Field        | Type         | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| Bay_Code     | int(11)      | YES  | MUL | NULL    |       |
| Bay_Location | varchar(255) | YES  | MUL | NULL    |       |
+--------------+--------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
-----


Column Commands
~~~~~~~~~~~~~~~

Add a Column to a Table
^^^^^^^^^^^^^^^^^^^^^^^

This is explained at:

http://www.tech-recipes.com/rx/378/add-a-column-to-an-existing-mysql-table/[http://www.tech-recipes.com/rx/378/add-a-column-to-an-existing-mysql-table/]

-----
alter table CAGES add longitude varchar(100) after latitude;
-----

List Columns in Table
^^^^^^^^^^^^^^^^^^^^^

-----
describe Texas_CPUE;
-----

Change a Column Name
^^^^^^^^^^^^^^^^^^^^

-----
alter table Texas_CPUE change `Bay Code` Bay_Code int(11);
-----

Change the Datatype in a Column
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

http://stackoverflow.com/questions/1356866/how-do-i-change-the-data-type-for-a-column-in-mysql[+http://stackoverflow.com/questions/1356866/how-do-i-change-the-data-type-for-a-column-in-mysql+]

You only have to specify the new datatype, e.g. INTEGER in the following:

-----
ALTER TABLE tablename MODIFY columnname INTEGER;
-----

or in the real world:

-----
alter table CAGES_Texas_Trawls_Lengths_IOOS_Standard modify minimumDepthInMeters double;
-----

Privileges
~~~~~~~~~~

Grant Database Privileges to a Remote User
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

-----
grant all privileges on CAGES2.* to baum@165.91.85.30;
grant all privileges on CAGES2.* to baum@165.91.85.30 identified by 'password';
-----

List Users and Their Privileges
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

-----
select user,host from mysql.user;
-----

Datatypes and Manipulations
~~~~~~~~~~~~~~~~~~~~~~~~~~~

Change a TIMESTAMP into a VARCHAR with String Modification
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

We want to change, e.g. 

-----
2013-03-13 21:38:35
-----

into, e.g.

-----
2013-03-13T21:38:35Z
-----

First, we change the initial string from a TIMESTAMP variable
into a VARCHAR variable via:

-----
alter table CAGES_Texas_Trawls_Lengths_IOOS_Standard modify modified varchar(65);
-----

Then, we insert the T via:

-----
update CAGES_Texas_Trawls_Lengths_IOOS_Standard set modified = replace(modified,' ','T');
-----

Then, we append a Z to this string via:

-----
update CAGES_Texas_Trawls_Lengths_IOOS_Standard set modified = concat(modified,'Z');
-----

Prepend Days/Months/Years with Zeros
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

-----
ALTER TABLE `table` CHANGE `zip` `zip` CHAR(5); #changes type
UPDATE table SET `zip`=LPAD(`zip`, 5, '0'); #pads everything
-----

Data Types
^^^^^^^^^^

These are described at:

-----
http://www.htmlite.com/mysql003.php
-----

The *text* types are:

-----
CHAR( )	A fixed section from 0 to 255 characters long.
VARCHAR( )	A variable section from 0 to 255 characters long.
TINYTEXT	A string with a maximum length of 255 characters.
TEXT	A string with a maximum length of 65535 characters.
BLOB	A string with a maximum length of 65535 characters.
MEDIUMTEXT	A string with a maximum length of 16777215 characters.
MEDIUMBLOB	A string with a maximum length of 16777215 characters.
LONGTEXT	A string with a maximum length of 4294967295 characters.
LONGBLOB	A string with a maximum length of 4294967295 characters.
-----

The *number* types are:

-----
TINYINT( )	-128 to 127 normal
                0 to 255 UNSIGNED.
SMALLINT( )	-32768 to 32767 normal
                0 to 65535 UNSIGNED.
MEDIUMINT( )	-8388608 to 8388607 normal
                0 to 16777215 UNSIGNED.
INT( )	        -2147483648 to 2147483647 normal
                0 to 4294967295 UNSIGNED.
BIGINT( )	-9223372036854775808 to 9223372036854775807 normal
                0 to 18446744073709551615 UNSIGNED.
FLOAT	        A small number with a floating decimal point.
DOUBLE( , )	A large number with a floating decimal point.
DECIMAL( , )	A DOUBLE stored as a string , allowing for a fixed decimal point.
-----

The *date* types are:

-----
DATE	YYYY-MM-DD.
DATETIME	YYYY-MM-DD HH:MM:SS.
TIMESTAMP	YYYYMMDDHHMMSS.
TIME	HH:MM:SS.
-----

The *misc* types are:

-----
ENUM ( )	Short for ENUMERATION which means that each column may have
                one of a specified possible values.
SET	        Similar to ENUM except each column may have more than one of the
                specified possible values.
-----

Mapping MySQL Datatypes Into Java
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

See
http://www.roseindia.net/jdbc/jdbc-mysql/mapping-mysql-data-types-in-java.shtml[http://www.roseindia.net/jdbc/jdbc-mysql/mapping-mysql-data-types-in-java.shtml].

-----
MySQL Type      Java Type

CHAR       	String
VARCHAR 	String
LONGVARCHAR 	String
NUMERIC 	java.math.BigDecimal
DECIMAL 	java.math.BigDecimal
BIT     	boolean
TINYINT 	byte
SMALLINT 	short
INTEGER 	int
BIGINT 	        long
REAL     	float
FLOAT   	double
DOUBLE  	double
BINARY  	byte []
VARBINARY 	byte []
LONGVARBINARY 	byte []
DATE    	java.sql.Date
TIME    	java.sql.Time
TIMESTAMP 	java.sql.Tiimestamp
-----

Create a Table with Columns with Specified Datatypes
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

This is explained at:

-----
http://www.tutorialspoint.com/mysql/mysql-create-tables.htm
-----

A PostgreSQL statement of the form:

-----
CREATE TABLE "CAGES_Texas" (
"modified" varchar(50),
"verbatimModified" varchar(50),
"minimumDepthInMeters" decimal,
"OccurrenceRemarks" text
) WITH OIDS;
-----

translates into a MySQL statement of the form:

-----
CREATE TABLE CAGES Texas (
modified varchar(100),
verbatimModified varchar(100),
minimumDepthInMeters decimal,
OccurrenceRemarks text
);
-----

PostgreSQL vs. MySQL Data Types
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

These are compared at:

-----
http://sidu.sourceforge.net/sidu/data-type-mapping.php
-----

Database File Backup and Manipulation
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Shrinking ibdata1
^^^^^^^^^^^^^^^^^

This is described at:

-----
http://stackoverflow.com/questions/3456159/how-to-shrink-purge-ibdata1-file-in-mysql
-----

If you want to reclaim the space from +ibdata1+ you have to delete the file
and recreate it.  This is done via the following procedure:

* Dump all databases using +mysqldump+, e.g. 

** +mysqldump -u root -ppasswd CAGES > cages_backup.sql+

* Drop all databases except the +mysql+ database, e.g.

** +drop database CAGES;+

* Stop mysql.

** +/etc/init.d/mysqld stop+

* Delete the files +ibdata1+ and +ib_log+.

* Start mysql.

** +/etc/init.d/mysqld start+

* Restore from the dump files. 

** +mysql -u root -p+
** +create database CAGES;+
** +exit+
** +mysql -u root -ppasswd CAGES < cages2_backup.sql+

Optimizations
~~~~~~~~~~~~~

Optimizing MySQL for Large Databases
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

See
http://linuxadminzone.com/optimize-mysql-on-a-large-database-server/[+http://linuxadminzone.com/optimize-mysql-on-a-large-database-server/+]:

. Use the +my-huge.cnf+ configuration file if you have more than 2GB of RAM.

. Add the following to the configuration file:

-----
        max_connections = 1000
        query_cache_size = 128M
        table_cache = 2048M
        open_files_limit = 2000
-----

See
http://www.biblibre.com/en/blog/entry/mysql-default-config-and-very-large-koha-database[+http://www.biblibre.com/en/blog/entry/mysql-default-config-and-very-large-koha-database+]:

Errors
~~~~~~

Error Message +InnoDB: Could not open or create data files+
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

A remedy for this can be found at:

+http://forums.mysql.com/read.php?22,428579,428773#msg-428773+

which is detailed below:

* +mysqldump+ everything

* stop +mysqld+

* add +innodb_file_per_table+ to +my.cnf+

* deleted +ibdata1+

* start +mysqld+

* reload the data (as seen in the immediately previous section)

Checking Remote MySQL Database with Python
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

From barataria we check the connection to gcoos1:

-----
import MySQLdb
db = MySQLdb.connect(host="gcoos1.tamu.edu",user="baum",passwd="password",db="CAGES")
cursor = db.cursor()
sql = "SELECT * FROM Texas_Bays"
cursor.execute(sql)
results = cursor.fetchall()
for row in results:
	print row[0],row[1]
   ....:     
1 SABINE LAKE
2 GALVESTON BAY
3 MATAGORDA BAY
4 SAN ANTONIO BAY
5 ARANSAS BAY
6 CORPUS CHRISTI BAY
7 UPPER LAGUNA MADRE
8 LOWER LAGUNA MADRE
9 EAST MATAGORDA BAY
11 CEDAR LAKES
17 off Sabine Lake
18 off Galveston-Freeport
19 off Matagorda-San Antonio-Aransas
20 off Corpus Christi-Upper Laguna Madre
21 off upper Laguna Madre-lower Laguna Madre
-----

CAGES Database Modifications
----------------------------

. All spaces in table and variable names replaced with underscores.

. Variable name +Length (Units)+ changed to +Length_Units+.

. Use the +OPTIMIZE TABLE+ command.


CAGES Texas Tables
------------------

The tables in the Texas portion of the CAGES dataset are:

-----
Texas_Bays                     15
Texas_CPUE              1,025,700
Texas_Hydrological         39,450
Texas_Lengths           1,283,332
Texas_Samples              39,450
Texas_Species                  26
Texas_Stations              1,420
Texas_SubBays                  39
Texas_Trawls            1,025,700
-----

and the columns in each are:

+Texas_Bays+

-----
+--------------+--------------+------+-----+---------+-------+
| Field        | Type         | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| Bay_Code     | int(11)      | YES  |     | NULL    |       |
| Bay_Location | varchar(255) | YES  |     | NULL    |       |
+--------------+--------------+------+-----+---------+-------+
-----

+Texas_CPUE+

-----
+-----------------+--------------+------+-----+---------+-------+
| Field           | Type         | Null | Key | Default | Extra |
+-----------------+--------------+------+-----+---------+-------+
| Bay_Code        | int(11)      | YES  |     | NULL    |       |
| Site_Number     | int(11)      | YES  |     | NULL    |       |
| YYYY            | int(11)      | YES  |     | NULL    |       |
| MM              | int(11)      | YES  |     | NULL    |       |
| DD              | int(11)      | YES  |     | NULL    |       |
| Species_Code    | int(11)      | YES  |     | NULL    |       |
| Scientific_Name | varchar(255) | YES  |     | NULL    |       |
| cpue            | double       | YES  |     | NULL    |       |
+-----------------+--------------+------+-----+---------+-------+
-----

+Texas_Hydrological+

-----
+-------------+---------+------+-----+---------+-------+
| Field       | Type    | Null | Key | Default | Extra |
+-------------+---------+------+-----+---------+-------+
| Sample_Code | int(11) | YES  |     | NULL    |       |
| Salinity    | double  | YES  |     | NULL    |       |
| Temperature | double  | YES  |     | NULL    |       |
| Depth       | double  | YES  |     | NULL    |       |
| Turbidity   | double  | YES  |     | NULL    |       |
| Tow_Time    | double  | YES  |     | NULL    |       |
+-------------+---------+------+-----+---------+-------+
-----

+Texas_Lengths+

-----
+--------------+---------+------+-----+---------+-------+
| Field        | Type    | Null | Key | Default | Extra |
+--------------+---------+------+-----+---------+-------+
| Sample_Code  | int(11) | YES  |     | NULL    |       |
| Species_Code | int(11) | YES  |     | NULL    |       |
| Length       | int(11) | YES  |     | NULL    |       |
+--------------+---------+------+-----+---------+-------+
-----

+Texas_Samples+

-----
+--------------+---------+------+-----+---------+-------+
| Field        | Type    | Null | Key | Default | Extra |
+--------------+---------+------+-----+---------+-------+
| Sample_Code  | int(11) | YES  |     | NULL    |       |
| Station_Code | int(11) | YES  |     | NULL    |       |
| YYYY         | int(11) | YES  |     | NULL    |       |
| MM           | int(11) | YES  |     | NULL    |       |
| DD           | int(11) | YES  |     | NULL    |       |
+--------------+---------+------+-----+---------+-------+
-----

+Texas_Species+

-----
+-----------------+--------------+------+-----+---------+-------+
| Field           | Type         | Null | Key | Default | Extra |
+-----------------+--------------+------+-----+---------+-------+
| Species_Code    | int(11)      | YES  |     | NULL    |       |
| Scientific_Name | varchar(255) | YES  |     | NULL    |       |
| Common_Name     | varchar(255) | YES  |     | NULL    |       |
+-----------------+--------------+------+-----+---------+-------+
-----

+Texas_Stations+

-----
+--------------+---------+------+-----+---------+-------+
| Field        | Type    | Null | Key | Default | Extra |
+--------------+---------+------+-----+---------+-------+
| Station_Code | int(11) | YES  |     | NULL    |       |
| Site_Number  | int(11) | YES  |     | NULL    |       |
| Bay_Code     | int(11) | YES  |     | NULL    |       |
| SubBay_Code  | int(11) | YES  |     | NULL    |       |
| Latitude     | double  | YES  |     | NULL    |       |
| Longitude    | double  | YES  |     | NULL    |       |
+--------------+---------+------+-----+---------+-------+
-----

+Texas_SubBays+

-----
+-----------------+--------------+------+-----+---------+-------+
| Field           | Type         | Null | Key | Default | Extra |
+-----------------+--------------+------+-----+---------+-------+
| SubBay_Code     | int(11)      | YES  |     | NULL    |       |
| Bay_Code        | int(11)      | YES  |     | NULL    |       |
| SubBay_Location | varchar(255) | YES  |     | NULL    |       |
+-----------------+--------------+------+-----+---------+-------+
-----

+Texas_Trawls+

-----
+--------------+---------+------+-----+---------+-------+
| Field        | Type    | Null | Key | Default | Extra |
+--------------+---------+------+-----+---------+-------+
| Sample_Code  | int(11) | YES  |     | NULL    |       |
| Species_Code | int(11) | YES  |     | NULL    |       |
| Number       | int(11) | YES  |     | NULL    |       |
+--------------+---------+------+-----+---------+-------+
-----


Method 1
--------

Creating Indexes for Table Columns
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Painful experience having shown that it will take forever to perform the
upcoming +Step 1+ without creating them, we will now create indexes for
various table columns.  The generic procedure is:

-----
CREATE INDEX index_name ON table_name(column_name)
-----

Starting with the +Texas_Bays+ table, we create the needed indexes via:

-----
CREATE INDEX Texas_Bays_Bay_Location ON Texas_Bays(Bay_Location);
CREATE INDEX Texas_Bays_Bay_Code ON Texas_Bays(Bay_Code);
-----

Now we do the same with the +Texas_Samples+ table:

-----
CREATE INDEX Texas_Samples_YYYY ON Texas_Samples(YYYY);
CREATE INDEX Texas_Samples_MM ON Texas_Samples(MM);
CREATE INDEX Texas_Samples_DD ON Texas_Samples(DD);
CREATE INDEX Texas_Samples_Station_Code ON Texas_Samples(Station_Code);
CREATE INDEX Texas_Samples_Sample_Code ON Texas_Samples(Sample_Code);
-----

And for +Texas_Species+:

-----
CREATE INDEX Texas_Species_Scientific_Name ON Texas_Species(Scientific_Name);
CREATE INDEX Texas_Species_Common_Name ON Texas_Species(Common_Name);
CREATE INDEX Texas_Species_Species_Code ON Texas_Species(Species_Code);
-----

And for +Texas_Stations+:

-----
CREATE INDEX Texas_Stations_SubBay_Code ON Texas_Stations(SubBay_Code);
CREATE INDEX Texas_Stations_Latitude ON Texas_Stations(Latitude);
CREATE INDEX Texas_Stations_Longitude ON Texas_Stations(Longitude);
CREATE INDEX Texas_Stations_Site_Number ON Texas_Stations(Site_Number);
CREATE INDEX Texas_Stations_Bay_Code ON Texas_Stations(Bay_Code);
CREATE INDEX Texas_Stations_Station_Code ON Texas_Stations(Station_Code);
-----

And for +Texas_Hydrological+:

-----
create index Texas_Hydrological_Sample_Code on Texas_Hydrological(Sample_Code);
create index Texas_Hydrological_Salinity on Texas_Hydrological(Salinity);
create index Texas_Hydrological_Temperature on Texas_Hydrological(Temperature);
create index Texas_Hydrological_Depth on Texas_Hydrological(Depth);
create index Texas_Hydrological_Turbidity on Texas_Hydrological(Turbidity);
create index Texas_Hydrological_Tow_Time on Texas_Hydrological(Tow_Time);
-----

-----
show index from Texas_Hydrological;
+--------------------+------------+--------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table              | Non_unique | Key_name                       |
Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed |
Null | Index_type | Comment | Index_comment |
+--------------------+------------+--------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Texas_Hydrological |          1 | Texas_Hydrological_Sample_Code |
1 | Sample_Code | A         |       39777 |     NULL | NULL   | YES  | BTREE
|         |               |
| Texas_Hydrological |          1 | Texas_Hydrological_Salinity    |
1 | Salinity    | A         |         200 |     NULL | NULL   | YES  | BTREE
|         |               |
| Texas_Hydrological |          1 | Texas_Hydrological_Temperature |
1 | Temperature | A         |         200 |     NULL | NULL   | YES  | BTREE
|         |               |
| Texas_Hydrological |          1 | Texas_Hydrological_Depth       |
1 | Depth       | A         |         200 |     NULL | NULL   | YES  | BTREE
|         |               |
| Texas_Hydrological |          1 | Texas_Hydrological_Turbidity   |
1 | Turbidity   | A         |         200 |     NULL | NULL   | YES  | BTREE
|         |               |
| Texas_Hydrological |          1 | Texas_Hydrological_Tow_Time    |
1 | Tow_Time    | A         |         200 |     NULL | NULL   | YES  | BTREE
|         |               |
+--------------------+------------+--------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
-----

And for +Texas_Trawls+:

-----
create index Texas_Trawls_Sample_Code on Texas_Trawls(Sample_Code);
create index Texas_Trawls_Species_Code on Texas_Trawls(Species_Code);
create index Texas_Trawls_Number on Texas_Trawls(Number);
-----

We didn't create indexes for all the columns of all the tables, basically
because this many was enough to bring the time for creation of our combined
table down under 10 minutes, as opposed to grinding on it for over 3 days
without finishing before we created the indexes.

Now on to a three step method for creating our final database.

Join Source Tables Into a Combined +CAGES_Texas_Join_Trawls_Lengths_Image+ Table
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

This is a three step process.

Create the +CAGES_Texas_Join_Trawls_Lengths_Image+ Table
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

Create the table +CAGES_Texas_Join_Trawls_Lengths_Image+.

-----
CREATE TABLE
  CAGES_Texas_Join_Trawls_Lengths_Image
SELECT
  Texas_Trawls.Sample_Code,
  Texas_Samples.Station_Code,
  Texas_Stations.Site_Number,
  Texas_Stations.Bay_Code,
  Texas_Bays.Bay_Location,
  Texas_Stations.SubBay_Code,
  Texas_Stations.Latitude,
  Texas_Stations.Longitude,
  Texas_Samples.YYYY,
  Texas_Samples.MM,
  Texas_Samples.DD,
  Texas_Trawls.Species_Code,
  Texas_Species.Scientific_Name,
  Texas_Species.Common_Name,
  Texas_Trawls.Number,
  NULL as Length,
  Texas_Hydrological.Depth,
  Texas_Hydrological.Tow_Time,
  NULL as cpue
FROM
  Texas_Trawls,
  Texas_Hydrological,
  Texas_Samples,
  Texas_Species,
  Texas_Stations,
  Texas_Bays
WHERE
  Texas_Trawls.Number=0
AND
  Texas_Hydrological.Sample_Code=Texas_Trawls.Sample_Code
AND
  Texas_Samples.Sample_Code=Texas_Trawls.Sample_Code
AND
  Texas_Stations.Station_Code=Texas_Samples.Station_Code
AND
  Texas_Species.Species_Code=Texas_Trawls.Species_Code
AND
  Texas_Stations.Station_Code=Texas_Samples.Station_Code
AND
  Texas_Bays.Bay_Code=Texas_Stations.Bay_Code;
-----

Create table +CAGES_Texas_Join_Trawls_Lengths_Image+

-----
CREATE TABLE CAGES_Texas_Join_Trawls_Lengths_Image (
Sample_Code int(11),
Station_Code int(11),
Site_Number int(11),
Bay_Code int(11),
Bay_Location varchar(50),
SubBay_Code int(11),
Latitude double,
Longitude double,
YYYY int(11),
MM int(11),
DD int(11),
Species_Code int(11),
Scientific_Name varchar(50),
Common_Name varchar(50),
Number int(11),
Length int(11),
Depth double,
Tow_Time double,
cpue double
);
-----

This tables looks like (via +describe
CAGES_Texas_Join_Trawls_Lengths_Image;+):

-----
+-----------------+-------------+------+-----+---------+-------+
| Field           | Type        | Null | Key | Default | Extra |
+-----------------+-------------+------+-----+---------+-------+
| Sample_Code     | int(11)     | YES  |     | NULL    |       |
| Station_Code    | int(11)     | YES  |     | NULL    |       |
| Site_Number     | int(11)     | YES  |     | NULL    |       |
| Bay_Code        | int(11)     | YES  |     | NULL    |       |
| Bay_Location    | varchar(50) | YES  |     | NULL    |       |
| SubBay_Code     | int(11)     | YES  |     | NULL    |       |
| Latitude        | double      | YES  |     | NULL    |       |
| Longitude       | double      | YES  |     | NULL    |       |
| YYYY            | int(11)     | YES  |     | NULL    |       |
| MM              | int(11)     | YES  |     | NULL    |       |
| DD              | int(11)     | YES  |     | NULL    |       |
| Species_Code    | int(11)     | YES  |     | NULL    |       |
| Scientific_Name | varchar(50) | YES  |     | NULL    |       |
| Common_Name     | varchar(50) | YES  |     | NULL    |       |
| Number          | int(11)     | YES  |     | NULL    |       |
| Length          | int(11)     | YES  |     | NULL    |       |
| Depth           | double      | YES  |     | NULL    |       |
| Tow_Time        | double      | YES  |     | NULL    |       |
| cpue            | double      | YES  |     | NULL    |       |
+-----------------+-------------+------+-----+---------+-------+
-----

First +CAGES_Texas_Join_Trawls_Lengths_Image+ Insert
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

First insert step into +CAGES_Texas_Join_Trawls_Lengths_Image+.

-----
insert into
  CAGES_Texas_Join_Trawls_Lengths_Image(Sample_Code,
                                        Station_Code,
                                        Site_Number,
                                        Bay_Code,
                                        Bay_Location,
                                        SubBay_Code,
                                        Latitude,
                                        Longitude,
                                        YYYY,
                                        MM,
                                        DD,
                                        Species_Code,
                                        Scientific_Name,
                                        Common_Name,
                                        Number,
                                        Length,
                                        Depth,
                                        Tow_Time,
                                        cpue)
select
  Texas_Trawls.Sample_Code,
  Texas_Samples.Station_Code,
  Texas_Stations.Site_Number,
  Texas_Stations.Bay_Code,
  Texas_Bays.Bay_Location,
  Texas_Stations.SubBay_Code,
  Texas_Stations.Latitude, 
  Texas_Stations.Longitude,
  Texas_Samples.YYYY,
  Texas_Samples.MM,
  Texas_Samples.DD,
  Texas_Trawls.Species_Code,
  Texas_Species.Scientific_Name,
  Texas_Species.Common_Name,
  Texas_Trawls.Number,
  NULL as Length,
  Texas_Hydrological.Depth,
  Texas_Hydrological.Tow_Time,
  NULL as cpue
FROM
  Texas_Trawls,
  Texas_Hydrological,
  Texas_Samples,
  Texas_Species,
  Texas_Stations,
  Texas_Bays
WHERE
  Texas_Trawls.Number=0
AND
  Texas_Hydrological.Sample_Code=Texas_Trawls.Sample_Code
AND
  Texas_Samples.Sample_Code=Texas_Trawls.Sample_Code
AND
  Texas_Species.Species_Code=Texas_Trawls.Species_Code
AND
  Texas_Stations.Station_Code=Texas_Samples.Station_Code
AND
  Texas_Bays.Bay_Code=Texas_Stations.Bay_Code;
----

Second +CAGES_Texas_Join_Trawls_Lengths_Image+ Insert
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

Second insert step into +CAGES_Texas_Join_Trawls_Lengths_Image+.

-----
insert into
  CAGES_Texas_Join_Trawls_Lengths_Image(Sample_Code,
                                        Station_Code,
                                        Site_Number,
                                        Bay_Code,
                                        Bay_Location,
                                        SubBay_Code,
                                        Latitude,
                                        Longitude,
                                        YYYY,
                                        MM,
                                        DD,
                                        Species_Code,
                                        Scientific_Name,
                                        Common_Name,
                                        Number,
                                        Length,
                                        Depth,
                                        Tow_Time,
                                        cpue)
SELECT
  Texas_Lengths.Sample_Code,
  Texas_Samples.Station_Code,
  Texas_Stations.Site_Number,
  Texas_Stations.Bay_Code,
  Texas_Bays.Bay_Location,
  Texas_Stations.SubBay_Code,
  Texas_Stations.Latitude, 
  Texas_Stations.Longitude,
  Texas_Samples.YYYY,
  Texas_Samples.MM,
  Texas_Samples.DD,
  Texas_Lengths.Species_Code,
  Texas_Species.Scientific_Name,
  Texas_Species.Common_Name,
  Texas_Trawls.Number,
  Texas_Lengths.Length,
  Texas_Hydrological.Depth,
  Texas_Hydrological.Tow_Time,
  NULL as cpue
FROM
  Texas_Trawls,
  Texas_Hydrological,
  Texas_Lengths,
  Texas_Samples,
  Texas_Species,
  Texas_Stations,
  Texas_Bays
WHERE
  Texas_Hydrological.Sample_Code=Texas_Trawls.Sample_Code
AND
  Texas_Samples.Sample_Code=Texas_Lengths.Sample_Code
AND
  Texas_Species.Species_Code=Texas_Lengths.Species_Code
AND
  Texas_Trawls.Sample_Code=Texas_Lengths.Sample_Code
AND
  Texas_Trawls.Species_Code=Texas_Lengths.Species_Code
AND
  Texas_Stations.Station_Code=Texas_Samples.Station_Code
AND
  Texas_Bays.Bay_Code=Texas_Stations.Bay_Code;
-----

Create and Populate +CAGES_Texas_Trawls_Lengths_IOOS_Standard+ Table
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Create and populate.

Create +CAGES_Texas_Trawls_Lengths_IOOS_Standard+
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

-----
CREATE TABLE CAGES_Texas_Trawls_Lengths_IOOS_Standard (
modified timestamp,
verbatimModified varchar(65),
dataset_ID varchar(65),
datasetName varchar(65),
higherInstitutionCode varchar(65),
institutionCode varchar(65),
ownerInstitutionCode varchar(65),
collectionCode varchar(65),
catalogNumber varchar(65),
observationDateTime timestamp,
observationTimeZone varchar(65),
observationDateTimeAnnotation varchar(65),
verbatimObservationDateTime varchar(65),
nominalObservationDateTime timestamp,
nominalObservationDateTimeUncertainty varchar(65),
latitude decimal(10,3),
longitude decimal(10,3),
footprintWKT varchar(65),
coordinateUncertaintyInMeters decimal(10,1),
verbatimCoordinates varchar(65),
verbatimCoordinateSystem varchar(65),
verbatimSRS varchar(65),
geodeticDatum varchar(65),
georeferencedBy varchar(65),
georeferenceProtocol varchar(65),
minimumDepthInMeters decimal(10,1),
maximumDepthInMeters decimal(10,1),
basisOfRecord varchar(65),
recordedBy varchar(65),
vernacularName varchar(65),
scientificName varchar(65),
taxonRank varchar(65),
aphiaID varchar(65),
tsn varchar(65),
genus varchar(65),
subgenus varchar(65),
species varchar(65),
infraspecificEpithet varchar(65),
scientificNameAuthorship varchar(65),
identifiedBy varchar(65),
identificationDate varchar(65),
identificationQualifier varchar(65),
identificationRemarks varchar(65),
individualCount int(11),
weightInKg varchar(65),
sex varchar(65),
lifeStage varchar(65),
observedIndividualLengthInCm varchar(65),
observedMeanLengthInCm varchar(65),
observedMaxLengthInCm varchar(65),
observedMinLengthInCm varchar(65),
lengthType varchar(65),
OccurrenceRemarks text,
surveyEventID varchar(65),
sampleID varchar(65),
subsampleID varchar(65),
samplingProtocol varchar(65),
samplingEffort varchar(65),
samplingConditions varchar(65),
sampleShape varchar(65),
sampleLengthInMeters varchar(65),
sampleWidthInMeters decimal(10,1),
sampleHeightInMeters decimal(10,1),
sampleRadiusInMeters varchar(65),
sampleAreaInSquareMeters varchar(65),
sampleVolumeInCubicMeters varchar(65),
visibilityInMeters varchar(65),
visibilityType varchar(65),
waterTemperatureInCelsius varchar(65),
habitat varchar(65),
bottomType varchar(65),
quantificationType varchar(65),
quantificationVocabulary varchar(65),
quantificationName varchar(65),
quantificationValue varchar(65),
quantificationUnit varchar(65),
quantificationUncertainty varchar(65),
quantificationDeterminedDate varchar(65),
quantificationDeterminedBy varchar(65),
quantificationMethod varchar(65),
waterBody varchar(65),
islandGroup varchar(65),
island varchar(65),
locality varchar(65),
country varchar(65),
stateProvince varchar(65),
county varchar(65),
municipality varchar(65),
bio_kingdom varchar(65),
bio_phylum varchar(65),
bio_class varchar(65),
bio_order varchar(65),
bio_family varchar(65)
); 
-----

Populate Variable +CAGES_Texas_Trawls_Lengths_IOOS_Standard+ Columns
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

-----
insert into
  CAGES_Texas_Trawls_Lengths_IOOS_Standard(observationDateTime,
                            nominalObservationDateTime,
                            latitude,
                            longitude,
                            vernacularName,
                            scientificName,
                            sampleID,
                            waterBody,
                            minimumDepthInMeters,
                            maximumDepthInMeters,
                            individualCount,
                            observedIndividualLengthInCm,
                            subsampleID,
                            samplingEffort,
                            samplingConditions,
                            quantificationType,
                            locality)
select
                            timestamp(concat(convert(YYYY,char),lpad(convert(MM,char),2,'0'),lpad(convert(DD,char),2,'0'))),
                            timestamp(concat(convert(YYYY,char),lpad(convert(MM,char),2,'0'),lpad(convert(DD,char),2,'0'))),
                            Latitude,
                            Longitude,
                            Common_Name,
                            Scientific_Name,
                            concat(Sample_Code),
                            concat('bay code = ',Bay_Code,', subbay = ',SubBay_Code),
                            Depth,
                            Depth,
                            case
                              when Number=0 then 0
                              else 1
                            end,
                            concat((Length+0.0)/10.),
                            Species_Code,
                            concat('Trawl duration (in fraction of 1 hour) = ',Tow_Time),
                            concat('Total for this species in this sample = ',Number),
                            case
                              when Number=0 then 'A'
                              else 'P'
                            end,
                            concat('Station Code = ',Station_Code)
from
  CAGES_Texas_Join_Trawls_Lengths_Image;
-----

The PostgreSQL versions of the conversions are:

-----
individualCount

CASE
WHEN "Number" = '0' THEN '0'
ELSE '1'
END

observedIndividualLengthInCm

left(("Length"::numeric / 10)::character varying,char_length("Length")+1

quantificationType:

CASE
WHEN "Number" = '0' THEN 'A'
ELSE 'P'
END
-----

-----
        	TX	        LA	        MS	        AL	      FL
Sample Shape    Rectangle       Rectangle       Rectangle       Rectangle     Rectangle
Sample Length	unk	        unk	        unk	        unk	      unk
Sample Width (m)	6.1	4.9	4.9	4.9	6.1		
Sample Height (m)	0.5	0.36	0.46	0.32	0.46	
Sample Radius	unk	unk	unk	unk	unk
Sample Area	unk	unk	unk	unk	unk
Sample Vol	unk	unk	unk	unk	unk
Visibility	unk	unk	unk	unk	unk
Sample Duration (min)	10	10	10	10	10
-----




Populate Constant +CAGES_Texas_Trawls_Lengths_IOOS_Standard+ Columns
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

-----
update
  CAGES_Texas_Trawls_Lengths_IOOS_Standard
set
  dataset_ID = 'CAGES_Texas_Trawls_Lengths',
  datasetName = 'CAGES Texas Trawl Data with Counts and Lengths',
  higherInstitutionCode = 'DOC;NOAA;NOAA Fisheries Service;SEFSC;Fishery Ecology Branch',
  institutionCode = 'NOAA Fisheries Service',
  ownerInstitutionCode = 'NOAA Fisheries Service',
  collectionCode = 'CAGES Texas',
  nominalObservationDateTimeUncertainty = '43200',
  coordinateUncertaintyInMeters = 100.,
  basisOfRecord= 'Human Observation',
  geodeticDatum = 'EPSG:4326 WGS84',
  sampleID = 'Sample Code',
  samplingProtocol = 'Species subsampled and individual lengths recorded',
  sampleShape = 'Rectangle',
  sampleLengthInMeters = 'unk',
  sampleWidthInMeters = 6.1,
  sampleHeightInMeters = 0.5,
  sampleRadiusInMeters = 'unk',
  sampleAreaInSquareMeters = 'unk',
  sampleVolumeInCubicMeters = 'unk',
  visibilityInMeters = 'unk';
-----

Configuring ERDDAP for MySQL Tables
-----------------------------------


Step 1 - Create +CAGES_Texas_Join1_Image+
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

In the first method, we create our CAGES table for Texas by:

. SELECT-FROM-WHERE

The MySQL code for this is:

-----
CREATE TABLE 
  CAGES_Texas_Join1_Image
SELECT 
  Texas_Trawls.Sample_Code,
  Texas_Samples.Station_Code,
  Texas_Stations.Site_Number,
  Texas_Stations.Bay_Code,
  Texas_Bays.Bay_Location,
  Texas_Stations.SubBay_Code,
  Texas_Stations.Latitude,
  Texas_Stations.Longitude,
  Texas_Samples.YYYY,
  Texas_Samples.MM,
  Texas_Samples.DD,
  Texas_Trawls.Species_Code,
  Texas_Species.Scientific_Name,
  Texas_Species.Common_Name,
  Texas_Trawls.Number 
FROM 
  Texas_Trawls,
  Texas_Samples,
  Texas_Species,
  Texas_Stations,
  Texas_Bays 
WHERE 
  Texas_Samples.Sample_Code=Texas_Trawls.Sample_Code 
AND 
  Texas_Stations.Station_Code=Texas_Samples.Station_Code 
AND 
  Texas_Species.Species_Code=Texas_Trawls.Species_Code 
AND 
  Texas_Bays.Bay_Code=Texas_Stations.Bay_Code;
-----

or to include subbay location instead of code:

-----
CREATE TABLE
  CAGES_Texas_Join2_Image
SELECT
  Texas_Trawls.Sample_Code,
  Texas_Samples.Station_Code,
  Texas_Stations.Site_Number,
  Texas_Stations.Bay_Code,
  Texas_Bays.Bay_Location,
  Texas_Stations.SubBay_Code,
  Texas_SubBays.SubBay_Location,
  Texas_Stations.Latitude,
  Texas_Stations.Longitude,
  Texas_Samples.YYYY,
  Texas_Samples.MM,
  Texas_Samples.DD,
  Texas_Trawls.Species_Code,
  Texas_Species.Scientific_Name,
  Texas_Species.Common_Name,
  Texas_Trawls.Number
FROM
  Texas_Trawls,
  Texas_Samples,
  Texas_Species,
  Texas_Stations,
  Texas_Bays,
  Texas_SubBays
WHERE
  Texas_Samples.Sample_Code=Texas_Trawls.Sample_Code
AND
  Texas_Stations.Station_Code=Texas_Samples.Station_Code
AND
  Texas_Species.Species_Code=Texas_Trawls.Species_Code
AND
  Texas_Bays.Bay_Code=Texas_Stations.Bay_Code
AND
  Texas_SubBays.SubBay_Code=Texas_Stations.SubBay_Code;
-----

We obtain:

-----
select * from Texas_Join_Limit_10;
-----

we find:

-----
+-------------+--------------+-------------+----------+---------------+-------------+----------+------------+------+------+------+--------------+-----------------+------------------+--------+
| Sample_Code | Station_Code | Site_Number | Bay_Code | Bay_Location  | SubBay_Code | Latitude | Longitude  | YYYY | MM   | DD   | Species_Code | Scientific_Name | Common_Name      | Number |
+-------------+--------------+-------------+----------+---------------+-------------+----------+------------+------+------+------+--------------+-----------------+------------------+--------+
|       11840 |          631 |         322 |        3 | MATAGORDA BAY | 360         |   28.575 | -96.141667 | 1982 |    5 |    5 |          602 | M               | Atlantic croaker |      3 |
|       11841 |          631 |         322 |        3 | MATAGORDA BAY | 360         |   28.575 | -96.141667 | 1983 |    4 |   19 |          602 | M               | Atlantic croaker |      9 |
|       11842 |          631 |         322 |        3 | MATAGORDA BAY | 360         |   28.575 | -96.141667 | 1984 |    4 |    9 |          602 | M               | Atlantic croaker |     16 |
|       11843 |          631 |         322 |        3 | MATAGORDA BAY | 360         |   28.575 | -96.141667 | 1984 |    5 |    1 |          602 | M               | Atlantic croaker |      1 |
|       11844 |          631 |         322 |        3 | MATAGORDA BAY | 360         |   28.575 | -96.141667 | 1985 |    8 |   20 |          602 | M               | Atlantic croaker |      2 |
|       11845 |          631 |         322 |        3 | MATAGORDA BAY | 360         |   28.575 | -96.141667 | 1986 |    3 |   24 |          602 | M               | Atlantic croaker |      4 |
|       11846 |          631 |         322 |        3 | MATAGORDA BAY | 360         |   28.575 | -96.141667 | 1986 |    5 |   16 |          602 | M               | Atlantic croaker |     37 |
|       11847 |          631 |         322 |        3 | MATAGORDA BAY | 360         |   28.575 | -96.141667 | 1987 |    9 |    1 |          602 | M               | Atlantic croaker |      1 |
|       11848 |          631 |         322 |        3 | MATAGORDA BAY | 360         |   28.575 | -96.141667 | 1988 |    8 |    2 |          602 | M               | Atlantic croaker |      1 |
|       11849 |          631 |         322 |        3 | MATAGORDA BAY | 360         |   28.575 | -96.141667 | 1989 |    1 |    4 |          602 | M               | Atlantic croaker |      0 |
+-------------+--------------+-------------+----------+---------------+-------------+----------+------------+------+------+------+--------------+-----------------+------------------+--------+
10 rows in set (0.00 sec)
-----

Duplicating the Table
^^^^^^^^^^^^^^^^^^^^^

We developed a need for quickly replicating a table when things didn't
go quite right and we needed an experimental version of our table.
This is done via a two part
process where we first create the structure of our original table
+CAGES_Texas_Join1_Image+ as the skeleton for our new table
+CAGES_Texas_Join2_Image+.

-----
create table CAGES_Texas_Join2_Image like CAGES_Texas_Join1_Image;
-----

Then we insert all of the data from the old table into the
new table via:

-----
insert CAGES_Texas_Join2_Image select * from CAGES_Texas_Join1_Image;
-----

If this isn't done properly, then we can delete the contents of the
table and try again via:

-----
delete from CAGES_Texas_Join2_Image;
-----

or we can just delete the table via:

-----
drop table CAGES_Texas_Join2_Image;
-----



Step 2 - Create Table for IOOS Data
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

We can create a new, unpopulated table +CAGES_Texas_IOOS_Biology+ via
the following lengthy command:

*+CAGES_Texas_IOOS_Biology+ Table Creation - Step 1*
-----
create table CAGES_Texas_IOOS_Biology (
modified timestamp,
verbatimModified varchar(100),
dataset_ID varchar(100),
datasetName varchar(100),
higherInstitutionCode varchar(100),
institutionCode varchar(100),
ownerInstitutionCode varchar(100),
collectionCode varchar(100),
catalogNumber varchar(100),
observationDateTime timestamp,
observationTimeZone varchar(100),
observationDateTimeAnnotation varchar(100),
verbatimObservationDateTime varchar(100),
nominalObservationDateTime timestamp,
nominalObservationDateTimeUncertainty varchar(100),
latitude varchar(50),
longitude varchar(50),
footprintWKT varchar(50),
coordinateUncertaintyInMeters decimal(10,1),
verbatimCoordinates varchar(50),
verbatimCoordinateSystem varchar(50),
verbatimSRS varchar(50),
geodeticDatum varchar(50),
georeferencedBy varchar(50),
georeferenceProtocol varchar(50),
minimumDepthInMeters decimal(10,2),
maximumDepthInMeters decimal(10,2),
basisOfRecord varchar(50),
recordedBy varchar(50),
vernacularName varchar(50),
scientificName varchar(50),
taxonRank varchar(50),
aphiaID varchar(50),
tsn varchar(50),
genus varchar(50),
subgenus varchar(50),
species varchar(50),
infraspecificEpithet varchar(50), 
scientificNameAuthorship varchar(50),
identifiedBy varchar(50),
identificationDate varchar(50),
identificationQualifier varchar(50),
identificationRemarks varchar(100),
individualCount int(11),
sex varchar(100),
lifeStage varchar(100),
observedIndividualLengthInCm varchar(100),
observedMeanLengthInCm varchar(50),
observedMaxLengthInCm varchar(50),
observedMinLengthInCm varchar(50),
lengthType varchar(50),
OccurrenceRemarks text, 
surveyEventID varchar(50),
sampleID varchar(50),
subsampleID varchar(50),
samplingProtocol varchar(50),
samplingEffort varchar(50),
samplingConditions varchar(50),
sampleShape varchar(50),
sampleLengthInMeters varchar(50),
sampleWidthInMeters varchar(50),
sampleHeightInMeters varchar(50),
sampleRadiusInMeters varchar(50),
sampleAreaInSquareMeters varchar(50),
sampleVolumeInCubicMeters varchar(50),
visibilityInMeters varchar(50),
visibilityType varchar(50),
waterTemperatureInCelsius varchar(50),
habitat varchar(50),
bottomType varchar(50),
quantificationType varchar(50),
quantificationVocabulary varchar(50),
quantificationName varchar(50),
quantificationValue varchar(50),
quantificationUnit varchar(50),
quantificationUncertainty varchar(50),
quantificationDeterminedDate varchar(50),
quantificationDeterminedBy varchar(50),
quantificationMethod varchar(50),
waterBody varchar(50),
islandGroup varchar(50),
island varchar(50),
locality varchar(50),
country varchar(50),
stateProvince varchar(50),
county varchar(50),
municipality varchar(50),
bio_kingdom varchar(50),
bio_phylum varchar(50),
bio_class varchar(50),
bio_order varchar(50),
bio_family varchar(50)
);
-----

The final table will look
like - via the +describe CAGES_Texas_IOOS_Biology+ command - like this:

-----
+---------------------------------------+---------------+------+-----+---------+-------+
| Field                                 | Type          | Null | Key | Default
| Extra |
+---------------------------------------+---------------+------+-----+---------+-------+
| modified                              | double        | YES  |     | NULL
| verbatimModified                      | varchar(100)  | YES  |     | NULL
| dataset_ID                            | varchar(100)  | YES  |     | NULL
| datasetName                           | varchar(100)  | YES  |     | NULL
| higherInstitutionCode                 | varchar(100)  | YES  |     | NULL
| institutionCode                       | varchar(100)  | YES  |     | NULL
| ownerInstitutionCode                  | varchar(100)  | YES  |     | NULL
| collectionCode                        | varchar(100)  | YES  |     | NULL
| catalogNumber                         | varchar(100)  | YES  |     | NULL
| observationDateTime                   | double        | YES  |     | NULL
| observationTimeZone                   | varchar(100)  | YES  |     | NULL
| observationDateTimeAnnotation         | varchar(100)  | YES  |     | NULL
| verbatimObservationDateTime           | varchar(100)  | YES  |     | NULL
| nominalObservationDateTime            | double        | YES  |     | NULL
| nominalObservationDateTimeUncertainty | varchar(100)  | YES  |     | NULL
| latitude                              | varchar(50)   | YES  |     | NULL
| longitude                             | varchar(50)   | YES  |     | NULL
| footprintWKT                          | varchar(50)   | YES  |     | NULL
| coordinateUncertaintyInMeters         | decimal(10,0) | YES  |     | NULL
| verbatimCoordinates                   | varchar(50)   | YES  |     | NULL
| verbatimCoordinateSystem              | varchar(50)   | YES  |     | NULL
| verbatimSRS                           | varchar(50)   | YES  |     | NULL
| geodeticDatum                         | varchar(50)   | YES  |     | NULL
| georeferencedBy                       | varchar(50)   | YES  |     | NULL
| georeferenceProtocol                  | varchar(50)   | YES  |     | NULL
| minimumDepthInMeters                  | decimal(10,0) | YES  |     | NULL
| maximumDepthInMeters                  | decimal(10,0) | YES  |     | NULL
| basisOfRecord                         | varchar(50)   | YES  |     | NULL
| recordedBy                            | varchar(50)   | YES  |     | NULL
| vernacularName                        | varchar(50)   | YES  |     | NULL
| scientificName                        | varchar(50)   | YES  |     | NULL
| taxonRank                             | varchar(50)   | YES  |     | NULL
| aphiaID                               | varchar(50)   | YES  |     | NULL
| tsn                                   | varchar(50)   | YES  |     | NULL
| genus                                 | varchar(50)   | YES  |     | NULL
| subgenus                              | varchar(50)   | YES  |     | NULL
| species                               | varchar(50)   | YES  |     | NULL
| infraspecificEpithet                  | varchar(50)   | YES  |     | NULL
| scientificNameAuthorship              | varchar(50)   | YES  |     | NULL
| identifiedBy                          | varchar(50)   | YES  |     | NULL
| identificationDate                    | varchar(50)   | YES  |     | NULL
| identificationQualifier               | varchar(50)   | YES  |     | NULL
| identificationRemarks                 | varchar(100)  | YES  |     | NULL
| individualCount                       | int(11)       | YES  |     | NULL
| sex                                   | varchar(100)  | YES  |     | NULL
| lifeStage                             | varchar(100)  | YES  |     | NULL
| observedIndividualLengthInCm          | varchar(100)  | YES  |     | NULL
| observedMeanLengthInCm                | varchar(50)   | YES  |     | NULL
| observedMaxLengthInCm                 | varchar(50)   | YES  |     | NULL
| observedMinLengthInCm                 | varchar(50)   | YES  |     | NULL
| lengthType                            | varchar(50)   | YES  |     | NULL
| OccurrenceRemarks                     | text          | YES  |     | NULL
| surveyEventID                         | varchar(50)   | YES  |     | NULL
| sampleID                              | varchar(50)   | YES  |     | NULL
| subsampleID                           | varchar(50)   | YES  |     | NULL
| samplingProtocol                      | varchar(50)   | YES  |     | NULL
| samplingEffort                        | varchar(50)   | YES  |     | NULL
| samplingConditions                    | varchar(50)   | YES  |     | NULL
| sampleShape                           | varchar(50)   | YES  |     | NULL
| sampleLengthInMeters                  | varchar(50)   | YES  |     | NULL
| sampleWidthInMeters                   | varchar(50)   | YES  |     | NULL
| sampleHeightInMeters                  | varchar(50)   | YES  |     | NULL
| sampleRadiusInMeters                  | varchar(50)   | YES  |     | NULL
| sampleAreaInSquareMeters              | varchar(50)   | YES  |     | NULL
| sampleVolumeInCubicMeters             | varchar(50)   | YES  |     | NULL
| visibilityInMeters                    | varchar(50)   | YES  |     | NULL
| visibilityType                        | varchar(50)   | YES  |     | NULL
| waterTemperatureInCelsius             | varchar(50)   | YES  |     | NULL
| habitat                               | varchar(50)   | YES  |     | NULL
| bottomType                            | varchar(50)   | YES  |     | NULL
| quantificationType                    | varchar(50)   | YES  |     | NULL
| quantificationVocabulary              | varchar(50)   | YES  |     | NULL
| quantificationName                    | varchar(50)   | YES  |     | NULL
| quantificationValue                   | varchar(50)   | YES  |     | NULL
| quantificationUnit                    | varchar(50)   | YES  |     | NULL
| quantificationUncertainty             | varchar(50)   | YES  |     | NULL
| quantificationDeterminedDate          | varchar(50)   | YES  |     | NULL
| quantificationDeterminedBy            | varchar(50)   | YES  |     | NULL
| quantificationMethod                  | varchar(50)   | YES  |     | NULL
| waterBody                             | varchar(50)   | YES  |     | NULL
| islandGroup                           | varchar(50)   | YES  |     | NULL
| island                                | varchar(50)   | YES  |     | NULL
| locality                              | varchar(50)   | YES  |     | NULL
| country                               | varchar(50)   | YES  |     | NULL
| stateProvince                         | varchar(50)   | YES  |     | NULL
| county                                | varchar(50)   | YES  |     | NULL
| municipality                          | varchar(50)   | YES  |     | NULL
| kingdom                               | varchar(50)   | YES  |     | NULL
| phylum                                | varchar(50)   | YES  |     | NULL
| class                                 | varchar(50)   | YES  |     | NULL
| order                                 | varchar(50)   | YES  |     | NULL
| family                                | varchar(50)   | YES  |     | NULL
+---------------------------------------+---------------+------+-----+---------+-------+
-----

Step 3
~~~~~~

Now we must populate the columns of +CAGES_Texas_IOOS_Biology+ with one of
three things:

* a single variable, e.g. set +modified+ to today's date, e.g. +2013-01-19+;

* the variable values in the corresponding +CAGES_Texas_Join1_Image+ database;

* the NULL value, with which the unpopulated columns of
 +CAGES_Texas_IOOS_Biology+ were filled when it was created.

We will also have to combine the values of one or more columns in
+CAGES_Texas_Join1_Image+ to create some columns in
+CAGES_Texas_IOOS_Biology+, as well as perform data conversions
from one table to the other.
The corresponding columns are:

[width="70%",options="header"]
|==========================================================================
| CAGES_Texas_IOOS_Biology | Datatype | CAGES_Texas_Join1_Image | Datatype 

| +observationDateTime+ | varchar(100) | +YYYY+,+MM+,+DD+ | int(11)

| +nominalObservationDateTime+ | varchar(100) | +YYYY+,+MM+,+DD+ | int(11)

| +latitude+ | double | +Latitude+ | double

| +longitude+ | double | +Longitude+ | double

| +vernacularName+ | varchar(255) | +Common_Name+ | varchar(255)

| +scientificName+ | varchar(255) | +Scientific_Name+ | varchar(255)

| +individualCount+ | int(11) | +Number+ | int(11)

| +sampleID+ | varchar(50) | +Sample_Code+ | int(11)

| +waterBody+ | varchar(50) | +Bay_Code+,+SubBay_Code+ | int(11)

| +locality+ | varchar(50) | +Station_Code+ | int(11) 

|==========================================================================

The columns that need to be set to a single variable are:

[width="70%",options="header"]
|==========================================================================
| Column | Value

| +modified+ | '2013-01-31'

| +dataset_ID+ | 'CAGES_Texas'

| +datasetName+ | 'CAGES Texas Trawl Data with Counts'

| +higherInstitutionCode+ | 'DOC;NOAA;NOAA Fisheries Service;SEFSC;Fishery Ecology Branch'

| +institutionCode+ | 'NOAA Fisheries Service'

| +ownerInstitutionCode+ | 'NOAA Fisheries Service'

| +collectionCode+ | 'CAGES Texas'

| +nominalObservationDateTimeUncertainty+ | '43200'

| +coordinateUncertaintyInMeters+ | '100'

| +geodeticDatum+ | 'EPSG:4326 WGS84'

|==========================================================================


Transferring Variable Columns Across Tables
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

The PostgreSQL syntax for populating the non-null, variable columns in
+CAGES_Texas_IOOS_Biology+
from the corresponding columns in +CAGES_Texas_Join1_Image+ is:

-----
INSERT INTO "CAGES_Texas_IOOS_Biology"
SELECT
...
--observationDateTime
"YYYY" || '-' || "MM" || '-' "DD",
...
--nominalObservationDateTime
"YYYY" || '-' || "MM" || '-' "DD" || 'T00:12:00Z',
...
--vernacularName
"Commmon Name",
--scientificName
"Scientific_Name",
...
--individualCount
"Number",
...
--sampleID
"Sample Code",
...
--waterBody
"Name/Location" || '(bay code = ' || "Bay Code" || ' sub bay code = ' ||
"SubBay Code" || ')',
...
--locality
'Station Code = ' || "Station Code",
...
FROM "CAGES_Texas_Join1_Image"
;
-----

We desire to populate the newly created +CAGES_Texas_IOOS_Biology+
table with the appropriate columns from +CAGES_Texas_Join1_Image+.
Start by using the following to insert column +Bay_Location+ (+varchar(255)+)
from
the latter into the former's +waterBody+ (+varchar(50)+) column.

-----
insert into CAGES_Texas_IOOS_Biology(waterBody) select Bay_Location from
CAGES_Texas_Join1_Image;
-----

The success of this is shown via the command:

-----
select waterBody from CAGES_Texas_IOOS_Biology limit 5;
-----

which shows that the +waterBody+ column of the +CAGES_Texas_IOOS_Biology+
table has been populated by the values from the +Bay_Location+ column of
the +CAGES_Texas_Join1_Image+ table.

-----
+-------------+
| waterBody   |
+-------------+
| SABINE LAKE |
| SABINE LAKE |
| SABINE LAKE |
| SABINE LAKE |
| SABINE LAKE |
+-------------+
-----

We need to insert the column values from +CAGES_Texas_Join1_Image+
into the appropriate columns of +CAGES_Texas_IOOS_Biology+.
Some special MySQL string functions are used for this, with the documentation
found at:

http://dev.mysql.com/doc/refman/5.1/en/string-functions.html[http://dev.mysql.com/doc/refman/5.1/en/string-functions.html]

First, we used the +LPAD+ function to left-pad parts of the date, e.g. the day
and month, with zeroes.  For instance, if the day of the month variable +DD+ is less
than 10, say 9,
we can left-pad it with a zero using:

-----
lpad(DD,2,'0')
-----

If we want to convert an integer into a character, we use the +CONVERT+
function.  If we have an integer year +YYYY+, this is done via:

-----
convert(YYYY,char)
-----

If we want to concatenate string we use the +CONCAT+ function, for
example the year, month and day:

-----
concat(YYYY,MM,DD)
-----

Finally, if we want to convert a YYYYMMDD string into a timestamp we
use the +TIMESTAMP+ function, e.g.

-----
timestamp(YYYY,MM,DD)
-----

Since our YYYY,MM,DD are integers and the MM and DD values can be either
one or two digits, we have to combine the above functions as follows:

-----
timestamp(concat(convert(YYYY,char),lpad(convert(MM,char),2,'0'),lpad(convert(DD,char),2,'0')))
-----

wherein - from the inside out - we first convert the integer YYYY,MM,DD values
to strings, left-pad the string with zeroes if needed, concatenate all three
strings, and then convert them to a timestamp.

Using these functions where needed, we can transfer all of the columns with
variable values from +CAGES_Texas_Join1_Image+ to
+CAGES_Texas_IOOS_Biology+ with:

*+CAGES_Texas_IOOS_Biology+ Table Creation - Step 2*
-----
insert into
  CAGES_Texas_IOOS_Biology(observationDateTime,
                            nominalObservationDateTime,
                            latitude,
                            longitude,
                            vernacularName,
                            scientificName,
                            individualCount,
                            sampleID,
                            waterBody,
                            locality)
select
                            timestamp(concat(convert(YYYY,char),lpad(convert(MM,char),2,'0'),lpad(convert(DD,char),2,'0'))),
                            timestamp(concat(convert(YYYY,char),lpad(convert(MM,char),2,'0'),lpad(convert(DD,char),2,'0'))),
                            Latitude,
                            Longitude,
                            Common_Name,
                            Scientific_Name,
                            concat(Number),
                            concat(Sample_Code),
                            concat('bay code = ',Bay_Code,', subbay = ',SubBay_Code),
                            concat(Station_Code)
from
  CAGES_Texas_Join1_Image;
-----


Populating Constant-Value Columns
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

We also need to populate the columns in +CAGES_Texas_IOOS_Biology+ for which
there are no corresponding columns in +CAGES_Texas_Join1_Image+ with default
values.
An example that will populate the +CAGES_Texas_IOOS_Biology+ column
+modified+ with the string +2013-01-12+ is:

-----
update CAGES_Texas_IOOS_Biology set modified = '2013-01-12';
-----

This is done simultaneously for all columns that have but a single non-NULL value via:

*+CAGES_Texas_IOOS_Biology+ Table Creation - Step 3*
-----
update
  CAGES_Texas_IOOS_Biology
set
  dataset_ID = 'CAGES_Texas',
  datasetName = 'CAGES Texas Trawl Data with Counts',
  higherInstitutionCode = 'DOC;NOAA;NOAA Fisheries Service;SEFSC;Fishery Ecology Branch',
  institutionCode = 'NOAA Fisheries Service',
  ownerInstitutionCode = 'NOAA Fisheries Service',
  collectionCode = 'CAGES Texas',
  nominalObservationDateTimeUncertainty = '43200',
  coordinateUncertaintyInMeters = 100.,
  geodeticDatum = 'EPSG:4326 WGS84';
-----

Configuring ERDDAP for MySQL Tables
-----------------------------------

The bible of ERDDAP configuration can be found at:

http://coastwatch.pfeg.noaa.gov/erddap/download/setupDatasetsXml.html[http://coastwatch.pfeg.noaa.gov/erddap/download/setupDatasetsXml.html]

This details how to configure ERDDAP for different types of
datasets, which at this point can be divided into gridded and tabular
datasets.  Our data is stored in an MySQL database, which puts it
into the tabular dataset category known as +EDDTable+ datasets.
There are 16 subtypes of datasets under +EDDTable+ as of this writing,
with the +EDDTableFromDatabase+ type the one we need to use.  The 
instructions for +EDDTableFromDatabase+ datasets are found at:

http://coastwatch.pfeg.noaa.gov/erddap/download/setupDatasetsXml.html#EDDTableFromDatabase[http://coastwatch.pfeg.noaa.gov/erddap/download/setupDatasetsXml.html#EDDTableFromDatabase]

Preparing ERDDAP to serve a MySQL dataset involves two steps: (1) installing
the appropriate Java database driver for MySQL; and (2) creating an XML
configuration file appropriate for your database.

Obtaining and Installing the MySQL JDBC Driver
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

You must first obtain a MySQL JDBC driver from:

http://dev.mysql.com/downloads/connector/j/[http://dev.mysql.com/downloads/connector/j/]

Click on the appropriate button to download either a zip or tar.gz archive of
the MySQL JDBC driver.  At the time of this writing, we obtained the file:

-----
mysql-connector-java-5.1.23.tar.gz
-----

Open it up with:

-----
tar xzvf mysql-connector-java-5.1.23.tar.gz
-----

Go into the upper level directory of the subdirectory hierarchy
you just created:

-----
cd mysql-connector-java-5.1.23
-----

and there should be a file therein of the form:

-----
mysql-connector-java-5.1.23-bin.jar
-----

This file needs to the copied or moved to the proper place in
your Tomcat directory.  If you've installed Tomcat in

-----
/opt/tomcat6
-----

then you need to place the file in:

-----
/opt/tomcat6/webapps/erddap/WEB-INF/lib
-----

Configuring +datasets.xml+
~~~~~~~~~~~~~~~~~~~~~~~~~~

An example skeleton for configuring a +datasets.xml+
file for a +EDDTableFromDatabase+ type of dataset is:

-----
<dataset type="EDDTableFromDatabase" datasetID="..." active="..." >
  <sourceUrl>...</sourceUrl>
    <!-- Put the database name at the end, for example, 
      "jdbc:postgresql://123.45.67.89:5432/databaseName". REQUIRED. -->
  <driverName>...</driverName>
    <!-- The high-level name of the database driver, e.g., 
      "org.postgresql.Driver".  You need to put the actual database 
      driver .jar file (for example, postgresql.jdbc.jar) in 
      [tomcat]/webapps/erddap/WEB-INF/lib.  REQUIRED. -->
  <connectionProperty name="name">value</connectionProperty>
    <!-- The names (e.g., "user", "password", and "ssl") and values 
      of the properties needed for ERDDAP to establish the connection
      to the database.  0 or more. -->
  <catalogName>...</catalogName>
    <!-- The name of the catalog which has the schema which has the 
      table, default = "".  OPTIONAL. -->
  <schemaName>...</schemaName> <!-- The name of the 
    schema which has the table, default = "".  OPTIONAL. -->
  <tableName>...</tableName>  <!-- The name of the 
    table, default = "".  REQUIRED. -->
  <orderBy>...</orderBy>  <!-- A comma-separated list of
    sourceNames to be used in an ORDER BY clause at the end of the 
    every query sent to the database (unless the user's request
    includes an &orderBy() filter, in which case the user's 
    orderBy is used).  The order of the sourceNames is important. 
    The leftmost sourceName is most important; subsequent 
    sourceNames are only used to break ties.  Only relevant 
    sourceNames are included in the ORDER BY clause for a given user 
    request.  If this is not specified, the order of the returned 
    values in not specified. Default = "".  OPTIONAL. -->
  <sourceNeedsExpandedFP_EQ>true(default)|false</sourceNeedsExpandedFP_EQ>
  <accessibleTo>...</accessibleTo> <!-- 0 or 1 -->
  <reloadEveryNMinutes>...</reloadEveryNMinutes>
  <fgdcFile>...</fgdcFile> <!-- 0 or 1 -->
  <iso19115File>...</iso19115File> <!-- 0 or 1 -->
  <onChange>...</onChange> <!-- 0 or more -->
  <altitudeMetersPerSourceUnit>...</altitudeMetersPerSourceUnit>
  <addAttributes>...</addAttributes>
  <dataVariable>...</dataVariable> <!-- 1 or more.
     For date and timestamp database columns, set dataType=double and 
     units=seconds since 1970-01-01T00:00:00Z -->
</dataset>
-----

Next you have to configure the +datasets.xml+ file so ERDDAP
can find the MySQL server and obtain the tables it needs to
display. 

Accessing the Local or Remote MySQL Server
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

The commands needed in the configuration file for ERDDAP to access
the CAGES MySQL databases are:

-----
<dataset type="EDDTableFromDatabase" datasetID="CAGES_Texas_IOOS_Biology">
    <sourceUrl>jdbc:mysql://165.91.85.243:3306/CAGES</sourceUrl>
    <driverName>com.mysql.jdbc.Driver</driverName>
    <connectionProperty name="user">user</connectionProperty>
    <connectionProperty name="password">password</connectionProperty>
    <tableName>CAGES_Texas_IOOS_Biology</tableName>
    <reloadEveryNMinutes>10080</reloadEveryNMinutes>
...
    <addAttributes>
     ...
    </addAttributes>
    <dataVariable>
     ...
    </dataVariable>
</dataset>
-----

where:

* the +sourceURL+ attribute value +165.91.85.243+ will be replaced
by the IP number of the machine upon which your MySQL server
is running;

* the +user+ and +password+ values in the +connectionProperty+
attributes will be replaced by the username and password that you've
enabled to access your MySQL server; and

* the +CAGES_Texas_IOOS_Biology+ value of the +tableName+ attribute
will be replaced by the name of your table.

Specifying the MySQL Table Columns to Be Read
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

You specify the MySQL table columns you want ERDDAP to read and process
via the +subsetVariables+ attribute in the +datasets.xml+ file.
You can specify all of the available columns or any subset thereof.
The full +CAGES_Texas_IOOS_Biology+ table
created in Step 2 had too many columns for ERDDAP to handle.  Via tedious
trial-and-error, we discovered that ERDDAP could handle the 32 columns
up to and including +taxonRank+, but would throw an error is just one
more column was added.  After discussion with other folks on the project,
we decided to limit the number of columns ERDDAP would read to the 20
in +CAGES_Texas_IOOS_Biology+ that were non-NULL.

This was done by only listing the 20 non-NULL variables in the
+subsetVariables+ attribute of the ERDDAP +datasets.xml+
configuration file.

-----
<att name="subsetVariables">modified,dataset_ID,datasetName,higherInstitutionCode,institutionCode,
           ownerInstitutionCode,collectionCode,observationDateTime,nominalObservationDateTime,
           nominalObservationDateTimeUncertainty,latitude,longitude,coordinateUncertaintyInMeters,
           geodeticDatum,vernacularName,scientificName,individualCount,sampleID,waterBody,locality</att>
-----

Configuring Individual Table Columns
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

Individual table columns are configured via the +dataVariable+ attribute.
A skeleton for this section is:

-----
    <dataVariable>
        <sourceName>remote_variable_name</sourceName>
        <destinationName>local_variable_name</destinationName>
        <dataType>datatype</dataType>
        <addAttributes>
          ...
        </addAttributes>
    </dataVariable>
-----

The entirety of our +dataVariable+ section for the non-NULL columns
in +CAGES_Texas_IOOS_Biology+ is:

-----
    <dataVariable>
        <sourceName>modified</sourceName>
        <destinationName>modified</destinationName>
        <dataType>double</dataType>
        <addAttributes>
            <att name="ioos_category">Biology</att>
            <att name="description">None.</att>
             <att name="units">seconds since 1970-01-01T00:00:00Z</att>
        </addAttributes>
    </dataVariable>
    <dataVariable>
        <sourceName>dataset_ID</sourceName>
        <destinationName>dataset_ID</destinationName>
        <dataType>String</dataType>
        <addAttributes>
            <att name="ioos_category">Biology</att>
            <att name="description">The time expressed in this
element...</att>
        </addAttributes>
    </dataVariable>
    <dataVariable>
        <sourceName>datasetName</sourceName>
        <destinationName>datasetName</destinationName>
        <dataType>String</dataType>
        <addAttributes>
            <att name="ioos_category">Biology</att>
            <att name="description">The position of the observation...</att>
        </addAttributes>
    </dataVariable>
    <dataVariable>
        <sourceName>higherInstitutionCode</sourceName>
        <destinationName>higherInstitutionCode</destinationName>
        <dataType>String</dataType>
        <addAttributes>
            <att name="ioos_category">Biology</att>
            <att name="description">The time expressed in this
element...</att>
        </addAttributes>
    </dataVariable>
    <dataVariable>
        <sourceName>institutionCode</sourceName>
        <destinationName>institutionCode</destinationName>
        <dataType>String</dataType>
        <addAttributes>
            <att name="ioos_category">Biology</att>
            <att name="description">The position of the observation...</att>
        </addAttributes>
    </dataVariable>
    <dataVariable>
        <sourceName>ownerInstitutionCode</sourceName>
        <destinationName>ownerInstitutionCode</destinationName>
        <dataType>String</dataType>
        <addAttributes>
            <att name="ioos_category">Biology</att>
            <att name="description">The time expressed in this
element...</att>
        </addAttributes>
    </dataVariable>
    <dataVariable>
        <sourceName>collectionCode</sourceName>
        <destinationName>collectionCode</destinationName>
        <dataType>String</dataType>
        <addAttributes>
            <att name="ioos_category">Biology</att>
            <att name="description">The position of the observation...</att>
        </addAttributes>
    </dataVariable>
    <dataVariable>
        <sourceName>observationDateTime</sourceName>
        <destinationName>observationDateTime</destinationName>
        <dataType>double</dataType>
        <addAttributes>
            <att name="ioos_category">Biology</att>
            <att name="description">The position of the observation...</att>
            <att name="units">seconds since 1970-01-01T00:00:00Z</att>
        </addAttributes>
    </dataVariable>
    <dataVariable>
        <sourceName>nominalObservationDateTime</sourceName>
        <destinationName>nominalObservationDateTime</destinationName>
        <dataType>double</dataType>
        <addAttributes>
            <att name="ioos_category">Biology</att>
            <att name="description">The position of the observation...</att>
            <att name="units">seconds since 1970-01-01T00:00:00Z</att>
        </addAttributes>
    </dataVariable>
    <dataVariable>
        <sourceName>nominalObservationDateTimeUncertainty</sourceName>
        <destinationName>nominalObservationDateTimeUncertainty</destinationName>
        <dataType>String</dataType>
        <addAttributes>
            <att name="ioos_category">Biology</att>
            <att name="description">The time expressed in this
element...</att>
        </addAttributes>
    </dataVariable>
    <dataVariable>
        <sourceName>latitude</sourceName>
        <destinationName>latitude</destinationName>
        <dataType>String</dataType>
        <addAttributes>
            <att name="ioos_category">Biology</att>
            <att name="description">The position of the observation...</att>
        </addAttributes>
    </dataVariable>
    <dataVariable>
        <sourceName>longitude</sourceName>
        <destinationName>longitude</destinationName>
        <dataType>String</dataType>
        <addAttributes>
            <att name="ioos_category">Biology</att>
            <att name="description">The time expressed in this
element...</att>
        </addAttributes>
    </dataVariable>
    <dataVariable>
        <sourceName>coordinateUncertaintyInMeters</sourceName>
        <destinationName>coordinateUncertaintyInMeters</destinationName>
        <dataType>double</dataType>
        <addAttributes>
            <att name="ioos_category">Biology</att>
            <att name="description">The time expressed in this
element...</att>
        </addAttributes>
    </dataVariable>
    <dataVariable>
        <sourceName>geodeticDatum</sourceName>
        <destinationName>geodeticDatum</destinationName>
        <dataType>String</dataType>
        <addAttributes>
            <att name="ioos_category">Biology</att>
            <att name="description">The time expressed in this
element...</att>
        </addAttributes>
    </dataVariable>
    <dataVariable>
        <sourceName>vernacularName</sourceName>
        <destinationName>vernacularName</destinationName>
        <dataType>String</dataType>
        <addAttributes>
            <att name="ioos_category">Biology</att>
            <att name="description">The position of the observation...</att>
        </addAttributes>
    </dataVariable>
    <dataVariable>
        <sourceName>scientificName</sourceName>
        <destinationName>scientificName</destinationName>
        <dataType>String</dataType>
        <addAttributes>
            <att name="ioos_category">Biology</att>
            <att name="description">The time expressed in this
element...</att>
        </addAttributes>
    </dataVariable>
    <dataVariable>
        <sourceName>individualCount</sourceName>
        <destinationName>individualCount</destinationName>
        <dataType>int</dataType>
        <addAttributes>
            <att name="ioos_category">Biology</att>
            <att name="description">The position of the observation...</att>
        </addAttributes>
    </dataVariable>
    <dataVariable>
        <sourceName>sampleID</sourceName>
        <destinationName>sampleID</destinationName>
        <dataType>String</dataType>
        <addAttributes>
            <att name="ioos_category">Biology</att>
            <att name="description">The position of the observation...</att>
        </addAttributes>
    </dataVariable>
    <dataVariable>
        <sourceName>waterBody</sourceName>
        <destinationName>waterBody</destinationName>
        <dataType>String</dataType>
        <addAttributes>
            <att name="ioos_category">Biology</att>
            <att name="description">The position of the observation...</att>
        </addAttributes>
    </dataVariable>
    <dataVariable>
        <sourceName>locality</sourceName>
        <destinationName>locality</destinationName>
        <dataType>String</dataType>
        <addAttributes>
            <att name="ioos_category">Biology</att>
            <att name="description">The time expressed in this
element...</att>
        </addAttributes>
    </dataVariable>
-----

Handling Date/Time Data
^^^^^^^^^^^^^^^^^^^^^^^

Java can only deal with instantaneous date+times with a time zone, which leads
to having to be careful with the way date/time information is handled all the
way back at the MySQL table level.  Basically, if you have any MySQL columns
containing date and/or time data, you have to use the MySQL datatype that
corresponds to the JDBC "timestamp with time zone" datatype.  The
corresponding
MySQL datatype is the +TIMESTAMP+ datatype.  Thus, we created our
date/time table entries as follows:

-----
create table CAGES_Texas_IOOS_Biology (
modified timestamp,
...
observationDateTime timestamp,
...

nominalObservationDateTime timestamp,
...
);
-----

and will configure them in the +datasets.xml+ file
+dataVariable+ section as:

-----
    <dataVariable>
        <sourceName>modified</sourceName>
        <destinationName>modified</destinationName>
        <dataType>double</dataType>
        <addAttributes>
             <att name="units">seconds since 1970-01-01T00:00:00Z</att>
        </addAttributes>
    </dataVariable>
-----

with the show +dataType+ and +units+ attribute values required.


CAGES Louisiana Tables
----------------------

PostgreSQL Basics
~~~~~~~~~~~~~~~~~

Useful Pages
^^^^^^^^^^^^

*PostgreSQL Tips and Tricks* - http://www.gabrielweinberg.com/blog/2011/05/postgresql.html[+http://www.gabrielweinberg.com/blog/2011/05/postgresql.html+]

*PostgreSQL Tips and Tricks* - http://blog.gtuhl.com/2009/08/07/postgresql-tips-and-tricks/[+http://blog.gtuhl.com/2009/08/07/postgresql-tips-and-tricks/+]


Incompatibility Errors
^^^^^^^^^^^^^^^^^^^^^^

The +/etc/init.d/postgresql-9.1 start+ command fails with:

-----
Starting postgresql-9.1 service:                           [FAILED]
-----

Running the more basic command +pg_ctl start+ supplies us with the more
informative:

-----
FATAL:  database files are incompatible with server
DETAIL:  The data directory was initialized by PostgreSQL version 9.0, which is not compatible with this version 9.1.5.
-----

The file +/var/lib/pgsql/9.1/pgstartup.log+ tells us:

-----
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale en_US.UTF-8.
The default database encoding has accordingly been set to UTF8.
The default text search configuration will be set to "english".

fixing permissions on existing directory /var/lib/pgsql/9.1/data ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 32MB
creating configuration files ... ok
creating template1 database in /var/lib/pgsql/9.1/data/base/1 ... ok
initializing pg_authid ... ok
initializing dependencies ... ok
creating system views ... ok
loading system objects' descriptions ... ok
creating collations ... ok
creating conversions ... ok
creating dictionaries ... ok
setting privileges on built-in objects ... ok
creating information schema ... ok
loading PL/pgSQL server-side language ... ok
vacuuming database template1 ... ok
copying template1 to template0 ... ok
copying template1 to postgres ... ok

Success. You can now start the database server using:

    /usr/pgsql-9.1/bin/postgres -D /var/lib/pgsql/9.1/data
or
    /usr/pgsql-9.1/bin/pg_ctl -D /var/lib/pgsql/9.1/data -l logfile start

FATAL:  invalid list syntax for "listen_addresses"
LOG:  could not bind IPv4 socket: Cannot assign requested address
HINT:  Is another postmaster already running on port 5432? If not, wait a few
seconds and retry.
WARNING:  could not create listen socket for "10.136.60.10"
LOG:  could not bind IPv4 socket: Cannot assign requested address
HINT:  Is another postmaster already running on port 5432? If not, wait a few
seconds and retry.
WARNING:  could not create listen socket for "10.136.60.172"
FATAL:  could not create any TCP/IP sockets
LOG:  could not bind IPv6 socket: Address already in use
HINT:  Is another postmaster already running on port 5432? If not, wait a few
seconds and retry.
LOG:  could not bind IPv4 socket: Address already in use
HINT:  Is another postmaster already running on port 5432? If not, wait a few
seconds and retry.
WARNING:  could not create listen socket for "*"
FATAL:  could not create any TCP/IP sockets
LOG:  could not bind IPv6 socket: Address already in use
HINT:  Is another postmaster already running on port 5432? If not, wait a few
seconds and retry.
LOG:  could not bind IPv4 socket: Address already in use
HINT:  Is another postmaster already running on port 5432? If not, wait a few
seconds and retry.
WARNING:  could not create listen socket for "*"
FATAL:  could not create any TCP/IP sockets
LOG:  could not bind IPv6 socket: Address already in use
HINT:  Is another postmaster already running on port 5432? If not, wait a few
seconds and retry.
LOG:  could not bind IPv4 socket: Address already in use
HINT:  Is another postmaster already running on port 5432? If not, wait a few
seconds and retry.
WARNING:  could not create listen socket for "*"
FATAL:  could not create any TCP/IP sockets
LOG:  could not bind IPv6 socket: Address already in use
HINT:  Is another postmaster already running on port 5432? If not, wait a few
seconds and retry.
LOG:  could not bind IPv4 socket: Address already in use
HINT:  Is another postmaster already running on port 5432? If not, wait a few
seconds and retry.
WARNING:  could not create listen socket for "*"
FATAL:  could not create any TCP/IP sockets
LOG:  could not bind IPv6 socket: Address already in use
HINT:  Is another postmaster already running on port 5432? If not, wait a few
seconds and retry.
LOG:  could not bind IPv4 socket: Address already in use
HINT:  Is another postmaster already running on port 5432? If not, wait a few
seconds and retry.
WARNING:  could not create listen socket for "*"
FATAL:  could not create any TCP/IP sockets
runuser: cannot set groups: Operation not permitted
runuser: cannot set groups: Operation not permitted
runuser: cannot set groups: Operation not permitted
runuser: cannot set groups: Operation not permitted
runuser: cannot set groups: Operation not permitted
runuser: cannot set groups: Operation not permitted
runuser: cannot set groups: Operation not permitted
runuser: cannot set groups: Operation not permitted
runuser: cannot set groups: Operation not permitted
-----

Running +nmap -v -A gcoos1.tamu.edu+ shows that port 5432 is not open:

-----
Interesting ports on gcoos1.tamu.edu (165.91.85.243):
Not shown: 1674 closed ports
PORT     STATE SERVICE VERSION
22/tcp   open  ssh     OpenSSH 4.3 (protocol 2.0)
80/tcp   open  http    Apache httpd 2.2.22 ((Unix) mod_ssl/2.2.22 OpenSSL/0.9.8e-fips-rhel5 DAV/2 PHP/5.4.5)
111/tcp  open  rpc
3306/tcp open  mysql   MySQL 5.5.27-log
8009/tcp open  ajp13?
8080/tcp open  http    Apache Tomcat/Coyote JSP engine 1.1
-----

The command for starting PostgreSQL found in +/etc/init.d/postgresql-9.1+ is:

-----
$SU -l postgres -c "$PGENGINE/postmaster -p '$PGPORT' -D '$PGDATA' ${PGOPTS} &" >> "$PGLOG" 2>&1 < /dev/null
-----

or if we replace all the pertinent variables:

-----
/usr/pgsql-9.1/bin/postmaster -p 5432 -D '/var/lib/pgsql/9.1/data'
-----

Another log can be found at
+/var/lib/pgsql/9.1/data/pg_log/postgresql-Tue.log+ and tells us:

-----
LOG:  invalid IP mask "md5": Name or service not known
CONTEXT:  line 91 of configuration file "/var/lib/pgsql/9.1/data/pg_hba.conf"
FATAL:  could not load pg_hba.conf
-----

We attempt to get around this by changing line 91 from:

-----
host    all             all             64.71.92.160            md5
-----

to

-----
host    all             all             64.71.92.160            trust 
-----

and get the same error message with +trust+ instead of +md5+.

If I remove line 91 and run +/etc/init.d/postgresql-9.1 start+, the
messages in +/var/lib/pgsql/9.1/data/pg_log/postgresql-Tue.log+ become:

-----
LOG:  database system was shut down at 2014-01-18 09:52:26 EST
LOG:  autovacuum launcher started
LOG:  database system is ready to accept connections
LOG:  received fast shutdown request
LOG:  aborting any active transactions
LOG:  autovacuum launcher shutting down
LOG:  shutting down
LOG:  database system is shut down
-----

Location of Data
^^^^^^^^^^^^^^^^

On +gcoos1+ the data directories are under:

-----
/var/lib/pgsql/9.1
-----

where the directories are:

-----
drwx------  2 postgres postgres 4096 Dec  5 14:54 backups
drwx------ 14 postgres postgres 4096 Jan 21 10:17 data
-rw-------  1 postgres postgres 3859 Jan 18 10:47 pgstartup.log
-----

The +data+ subdirectory, i.e.

-----
/var/lib/pgsql/9.1/data
-----

looks like:

-----
drwx------ 16 postgres postgres  4096 Jan 15 11:36 base
drwx------  2 postgres postgres  4096 Jan 18 09:52 global
drwx------  2 postgres postgres  4096 Jan 10  2012 pg_clog
-rw-------  1 postgres postgres  4602 Jan 18 10:27 pg_hba.conf
-rw-------  1 postgres postgres  1636 Jan 10  2012 pg_ident.conf
drwx------  2 postgres postgres  4096 Jan 20 11:10 pg_log
drwx------  4 postgres postgres  4096 Jan 10  2012 pg_multixact
drwx------  2 postgres postgres  4096 Jan 21 10:17 pg_notify
drwx------  2 postgres postgres  4096 Jan 10  2012 pg_serial
drwx------  2 postgres postgres  4096 Jan 18 09:52 pg_stat_tmp
drwx------  2 postgres postgres  4096 Jan 10  2012 pg_subtrans
drwx------  2 postgres postgres  4096 Jan 10  2012 pg_tblspc
drwx------  2 postgres postgres  4096 Jan 10  2012 pg_twophase
-rw-------  1 postgres postgres     4 Jan 10  2012 PG_VERSION
drwx------  3 postgres postgres  4096 Jan 17 11:15 pg_xlog
-rw-------  1 postgres postgres 19136 Jan  2 16:20 postgresql.conf
-rw-------  1 postgres postgres    71 Jan 21 10:17 postmaster.opts
-----




Upgrading PostgreSQL
^^^^^^^^^^^^^^^^^^^^

The steps are outlined here:

http://www.postgresql.org/docs/9.2/static/pgupgrade.html[+http://www.postgresql.org/docs/9.2/static/pgupgrade.html+]


Backup a Database
^^^^^^^^^^^^^^^^^

http://www.thegeekstuff.com/2009/01/how-to-backup-and-restore-postgres-database-using-pg_dump-and-psql/[+http://www.thegeekstuff.com/2009/01/how-to-backup-and-restore-postgres-database-using-pg_dump-and-psql/+]

-----
pg_dump -U baum cages_louisiana -f cages_louisiana.sql
-----

Delete a Database
^^^^^^^^^^^^^^^^^

-----
drop database cages_louisiana_bad;
-----

Change a Database Name
^^^^^^^^^^^^^^^^^^^^^^

This must be done as user +postgres+.

-----
su
su - postgres
psql cages_mississippi
alter database cages_louisiana rename to cages_louisiana_bad;
-----

Restore a Database
^^^^^^^^^^^^^^^^^^

http://www.thegeekstuff.com/2009/01/how-to-backup-and-restore-postgres-database-using-pg_dump-and-psql/[+http://www.thegeekstuff.com/2009/01/how-to-backup-and-restore-postgres-database-using-pg_dump-and-psql/+]

This must be done as user +postgres+ as follows:

-----
su
su - postgres
psql cages_mississippi
create database cages_louisiana;
\q
exit
chown postgres /home/baum/cages_louisiana.sql
chgrp postgres /home/baum/cages_louisiana.sql
mv /home/baum/cages_louisiana.sql /var/lib/pgsql/9.1/backups
su - postgres
psql -U postgres -d cages_louisiana -f /var/lib/pgsql/9.1/backups/cages_louisiana.sql
-----

Get a List of Databases
^^^^^^^^^^^^^^^^^^^^^^^

-----
psql -l
-----

to obtain, e.g.

-----
                                      List of databases
       Name        |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-------------------+----------+----------+-------------+-------------+-----------------------
 cages_alabama     | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 cages_louisiana   | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres         +
                   |          |          |             |             | postgres=CTc/postgres+
                   |          |          |             |             | baum=CTc/postgres
 cages_mississippi | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres         +
                   |          |          |             |             | postgres=CTc/postgres+
                   |          |          |             |             | baum=CTc/postgres
-----

Connect to a Specific Database
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

-----
psql cages_louisiana
-----

obtains the following:

-----
psql (9.1.5, server 9.1.4)
Type "help" for help.

cages_louisiana=>
-----

Show Tables in a Database
^^^^^^^^^^^^^^^^^^^^^^^^^

-----
cages_louisiana=> \dt
-----

to obtain, e.g.

-----
                              List of relations
 Schema |                        Name                         | Type  | Owner 
--------+-----------------------------------------------------+-------+-------
 public | CAGES_Louisiana_CPUEImage                           | table | baum
 public | CAGES_Louisiana_HydrologicalImage                   | table | baum
 public | CAGES_Louisiana_LengthsImage                        | table | baum
 public | CAGES_Louisiana_Lengths_CPUE_Join_20130822          | table | baum
 public | CAGES_Louisiana_SamplesImage                        | table | baum
 public | CAGES_Louisiana_SpeciesImage                        | table | baum
 public | CAGES_Louisiana_StationsImage                       | table | baum
 public | CAGES_Louisiana_TrawlsImage                         | table | baum
 public | CdtCstReferenceTable                                | table | baum
 public | cages_louisiana_lengths_cpue_ioos_standard_20130822 | table | baum
(10 rows)
-----

Rename a Column in a Table
^^^^^^^^^^^^^^^^^^^^^^^^^^

-----
alter table "cages_louisiana_lengths_cpue_ioos_standard_20130822" rename column "stateProvince" to "stateprovince";
-----

Optimize a Table Using VACUUM
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

-----
vacuum verbose analyze "cages_louisiana_lengths_cpue_ioos_standard_20130822";
-----

to obtain, e.g.

-----
INFO:  vacuuming "public.cages_louisiana_lengths_cpue_ioos_standard_20130822"
INFO:  "cages_louisiana_lengths_cpue_ioos_standard_20130822": found 0 removable, 298 nonremovable row versions in 337950 out of 1007383 pages
DETAIL:  0 dead row versions cannot be removed yet.
There were 3672255 unused item pointers.
0 pages are entirely empty.
CPU 4.12s/1.35u sec elapsed 29.46 sec.
INFO:  vacuuming "pg_toast.pg_toast_8046942"
INFO:  index "pg_toast_8046942_index" now contains 0 row versions in 1 pages
DETAIL:  0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  "pg_toast_8046942": found 0 removable, 0 nonremovable row versions in 0 out of 0 pages
DETAIL:  0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  analyzing "public.cages_louisiana_lengths_cpue_ioos_standard_20130822"
INFO:  "cages_louisiana_lengths_cpue_ioos_standard_20130822": scanned 30000 of 1007383 pages, containing 108495 live rows and 0 dead rows; 30000 rows in sample, 2541251 estimated total rows
VACUUM
-----

Create an Index
^^^^^^^^^^^^^^^

-----
create index idx_latitude on "cages_louisiana_lengths_cpue_ioos_standard_20130822"("latitude");
create index idx_longitude on "cages_louisiana_lengths_cpue_ioos_standard_20130822"("longitude");
create index idx_time on "cages_louisiana_lengths_cpue_ioos_standard_20130822"("time");
create index idx_kingdom on "cages_louisiana_lengths_cpue_ioos_standard_20130822"("kingdom");
-----

Obtain the Details of a Table
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

-----
cages_louisiana=> \d "CdtCstReferenceTable"
-----

to obtain, e.g.

-----
 Table "public.CdtCstReferenceTable"
  Column  |     Type      | Modifiers 
----------+---------------+-----------
 YYYY     | character(32) | 
 MM       | character(32) | 
 DD       | character(32) | 
 Date8    | character(32) | 
 TimeZone | character(32) |
-----

or get extended info via:

-----
cages_louisiana=> \d+ "CdtCstReferenceTable"
-----

to obtain, e.g.

-----
              Table "public.CdtCstReferenceTable"
  Column  |     Type      | Modifiers | Storage  | Description 
----------+---------------+-----------+----------+-------------
 YYYY     | character(32) |           | extended | 
 MM       | character(32) |           | extended | 
 DD       | character(32) |           | extended | 
 Date8    | character(32) |           | extended | 
 TimeZone | character(32) |           | extended | 
Has OIDs: no
-----

List the Values in a Specific Column in a Table
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

-----
select "YYYY" from "CdtCstReferenceTable" limit 2;
-----

to obtain:

-----
               YYYY               

 1986                            
 1986                            
(2 rows)
-----

Change the Format of the Time Variable from String to Date Format
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

Find the present format of the string containing the +time+ variable.

-----
select "time" from "cages_louisiana_lengths_cpue_ioos_standard_20130822" limit 1;
-----

to obtain:

-----
 1990-05-10T05:40:00Z
(1 row)
-----

http://stackoverflow.com/questions/2592412/how-to-convert-column-type-from-varchar-to-date-in-postgresql[+http://stackoverflow.com/questions/2592412/how-to-convert-column-type-from-varchar-to-date-in-postgresql+]

http://www.postgresql.org/docs/9.3/interactive/functions-formatting.html[+http://www.postgresql.org/docs/9.3/interactive/functions-formatting.html+]

From these pages we learn that the function +to_timestamp(text, text)+ returns
the data type +timestamp with time zone+.  It converts a string to a time
stamp.

The changes needed for the Louisiana combined table are:

-----
update "cages_louisiana_lengths_cpue_ioos_standard_20130822" set time=to_timestamp(time,'YYYY-MM-DDTHH:MI:SSZ');
update "cages_louisiana_lengths_cpue_ioos_standard_20130822" set modified=to_timestamp(modified,'YYYY-MM-DD');
update "cages_louisiana_lengths_cpue_ioos_standard_20130822" set "eventDate"=to_timestamp("eventDate",'YYYY-MM-DDTHH:MI');
-----


New Version
~~~~~~~~~~~

The new version consists of eight tables originally received as text files:

------
Louisiana_CPUE.txt
Louisiana_Hydrological.txt
Louisiana_Lengths.txt
Louisiana_Samples.txt
Louisiana_Species.txt
Louisiana_Stations.txt
Louisiana_Trawls.txt
Louisiana_Trawls.txt
------

and one more containing the table variables:

-----
Louisiana_Table_Varibles.txt
-----

which contains:

-----
Louisiana CPUE
Sample Code    YYYY    MM    DD    Water Body     Species Code  Scientific Name CPUE

Louisiana Hydrological
Sample Code    Surface Salinity    Bottom Salinity    Air Temperature    Surface Water Temperature   Bottom Water Temperature        Turbidity

Louisiana Lengths
Sample Code    Species Code    Length

Louisiana Samples
Sample Code    Station Code    YYYY    MM    DD    Time     Duration

Louisiana Species
Species Code    Scientific Name    Common Name

Louisiana Stations
Station Code    CSA    Station    Latitude    Longitude    Site Name    Water Body

Louisiana Trawls
Sample Code    Species Code    Total Number
-----

Creating and Connecting to a Database
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

The syntax is +CREATE DATABASE name+.  An example is:

-----
create database CAGES_Louisiana;
-----

You connect to this database via:

-----
\c CAGES_Louisiana;
-----

which gets the response:

-----
# \c CAGES_Louisiana;
psql (9.1.5, server 9.1.4)
You are now connected to database "CAGES_Louisiana" as user "postgres".
CAGES_Louisiana=#
-----

Creating the Tables in the Database
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

http://www.ntchosting.com/postgresql/[+http://www.ntchosting.com/postgresql/+]

Summary of Table Commands
^^^^^^^^^^^^^^^^^^^^^^^^^

*Create a Table*

Note:  The table name and column names must be placed within quotations for capitalization to be preserved.


-----
create table "Test_Table"(column1 VARCHAR(32), column2 VARCHAR(32));
-----

*List Tables*

-----
\dt
-----

*Delete a Table*

Note:  Quotations must also be used here.

-----
drop table "Test_Table";
-----

*Querying a Table*

This will list all the rows of the table:

-----
select * from "Test_Table";
-----

Or you can limit the listing to N rows via:

-----
select * from "Test_Table" limit N;
-----

*Adding a New User*

-----
create user newuser with password 'newuserpassword';
-----

*Granting Database Privileges to a User*

-----
grant all privileges on database "CAGES_Louisiana" to newuser;
-----

*Logging in as a Specific User to a Specific Database*

-----
psql -d "CAGES_Louisiana" -U baum
-----

*Rename an Existing Table*

-----
alter table oldname rename to newname;
-----

*Select Entries from a Specific Column in a Table*

-----
select latitude from cages_mississippi_lengths_cpue_ioos_standard_20130827 limit 2;
-----

to obtain:

-----
 latitude 
 ________
 30.28
 30.28
(2 rows)
-----

Creating the Louisiana Tables
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

Notes:

* All of the variables are imported as character variables, and then
typed as needed during the insert.  To quote Philip Goldstein: "I import as character
for more control, so no numeric tricks happen - truncation, rounding, offing
leading or trailing zeroes, altering date formats ... we want to capture
original data at the first step verbatim."

* The names of the tables and the columns must be enclosed within quotation
marks to preserve capitalization.

* All spaces within variable names are replaced with underscores since the
Java database JDBC downstream chokes on spaces within variable names.

The Louisiana tables were created with:

-----
CREATE TABLE "Louisiana_CPUE"("Sample_Code" CHAR(32), "YYYY" CHAR(32), "MM" CHAR(32), "DD" CHAR(32), "Water_Body" CHAR(32), "Species_Code" CHAR(32), "Scientific_Name" CHAR(32), "CPUE" CHAR(32));

CREATE TABLE "Louisiana_Hydrological"("Sample_Code" CHAR(32), "Surface_Salinity" CHAR(32), "Bottom_Salinity" CHAR(32), "Air_Temperature" CHAR(32), "Surface_Water_Temperature" CHAR(32), "Bottom_Water_Temperature" CHAR(32), "Turbidity" CHAR(32));

CREATE TABLE "Louisiana_Lengths"("Sample_Code" CHAR(32), "Species_Code" CHAR(32), "Length" CHAR(32));

CREATE TABLE "Louisiana_Samples"("Sample_Code" CHAR(32), "Station_Code" CHAR(32), "YYYY" CHAR(32), "MM" CHAR(32), "DD" CHAR(32), "Time_Duration" CHAR(32));

CREATE TABLE "Louisiana_Species"("Species_Code" CHAR(32), "Scientific_Name" CHAR(32), "Common_Name" CHAR(32));

CREATE TABLE "Louisiana_Stations"("Station_Code" CHAR(32), "CSA" CHAR(32), "Station" CHAR(32), "Latitude" CHAR(32), "Longitude" CHAR(32), "Site_Name" CHAR(32), "Water_Body" CHAR(32));

CREATE TABLE "Louisiana_Trawls"("Sample_Code" CHAR(32), "Species_Code" CHAR(32), "Total_Number" CHAR(32));

CREATE TABLE "CdtCstReferenceTable"("YYYY" CHAR(32), "MM" CHAR(32), "DD" CHAR(32), "Date8" CHAR(32), "TimeZone" CHAR(32));
-----

Now list the tables in +CAGES_Louisiana+:

-----
CAGES_Louisiana=# \dt
                 List of relations
 Schema |          Name          | Type  |  Owner   
--------+------------------------+-------+----------
 public | Louisiana_CPUE         | table | postgres
 public | Louisiana_Hydrological | table | postgres
 public | Louisiana_Lengths      | table | postgres
 public | Louisiana_Samples      | table | postgres
 public | Louisiana_Species      | table | postgres
 public | Louisiana_Stations     | table | postgres
 public | Louisiana_Trawls       | table | postgres
(7 rows)
-----

Query one of the still empty tables:

-----
CAGES_Louisiana=# select * from "Louisiana_CPUE";
 Sample_Code | YYYY | MM | DD | Water_Body | Species_Code | Scientific_Name | CPUE 
-------------+------+----+----+------------+--------------+-----------------+------
(0 rows)
-----

Populating the Tables in the Database
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Summary of Table CSV Import Commands
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

-----
COPY "Test_Table" FROM '/path/to/csv/test_table_values.txt' DELIMITER ',' CSV;
-----

Importing the Louisiana Tables from CSV Files
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

*Note*: Attempting to read the CSV files from my home directory resulted
in "permission denied" errors for both the +postgres+ and +baum+ users
from within the +psql+ shell.  The first solution I found to get around this
was to create a +postgres+ directory under +home+, i.e.

-----
su
mkdir /home/postgres
chown -R postgres:postgres /home/postgres
-----

and then copy all the CSV files to this directory, i.e.

-----
cd /home/baum/CAGES_Louisiana
cp Louis*.txt /home/postgres
-----

at which point I could read the files without error as user
+baum+ under the +psql+ shell.

The successful import commands are:

-----
\copy "Louisiana_CPUE" from '/home/postgres/Louisiana_CPUE.txt' DELIMITER ',' CSV;

\copy "Louisiana_Hydrological" from '/home/postgres/Louisiana_Hydrological.txt' DELIMITER ',' CSV;

\copy "Louisiana_Lengths" from '/home/postgres/Louisiana_Lengths.txt' DELIMITER ',' CSV;

\copy "Louisiana_Samples" from '/home/postgres/Louisiana_Samples.txt' DELIMITER ',' CSV;

\copy "Louisiana_Species" from '/home/postgres/Louisiana_Species.txt' DELIMITER ',' CSV;

\copy "Louisiana_Stations" from '/home/postgres/Louisiana_Stations.txt' DELIMITER ',' CSV;

\copy "Louisiana_Trawls" from '/home/postgres/Louisiana_Trawls.txt' DELIMITER ',' CSV;

\copy "CdtCstReferenceTable" from '/home/postgres/CdtCstReferenceTable_20130822.txt';
-----

Note: The +CdtCstReferenceTable_20130822.txt+ file contains columns separated by
blank spaces rather than commas, so that command was suitably modified.

A quick test to see if the tables were populated is:

-----
select * from "Louisiana_CPU" limit 5;
-----

which yields:

-----
           Sample_Code            |           Species_Code           |           Total_Number           
----------------------------------+----------------------------------+----------------------------------
 1                                | 2004                             | 19                              
 2                                | 2021                             | 1                               
 2                                | 2005                             | 3                               
 3                                | 2004                             | 178                             
 3                                | 2026                             | 1                               
(5 rows)
-----

All of the tables have been created and populated at this point.

Table and Column Name Modifications
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

The table names will now be modified so the commands in upcoming sections
don't have to be modified.

-----
alter table "Louisiana_CPUE" rename to "CAGES_Louisiana_CPUEImage";

alter table "Louisiana_Hydrological" rename to "CAGES_Louisiana_HydrologicalImage";

alter table "Louisiana_Lengths" rename to "CAGES_Louisiana_LengthsImage";

alter table "Louisiana_Samples" rename to "CAGES_Louisiana_SamplesImage";

alter table "Louisiana_Species" rename to "CAGES_Louisiana_SpeciesImage";

alter table "Louisiana_Stations" rename to "CAGES_Louisiana_StationsImage";

alter table "Louisiana_Trawls" rename to "CAGES_Louisiana_TrawlsImage";
-----

Now we have:

-----
CAGES_Louisiana=> \dt
                     List of relations
 Schema |               Name                | Type  | Owner 
--------+-----------------------------------+-------+-------
 public | CAGES_Louisiana_CPUEImage         | table | baum
 public | CAGES_Louisiana_HydrologicalImage | table | baum
 public | CAGES_Louisiana_LengthsImage      | table | baum
 public | CAGES_Louisiana_SamplesImage      | table | baum
 public | CAGES_Louisiana_SpeciesImage      | table | baum
 public | CAGES_Louisiana_StationsImage     | table | baum
 public | CAGES_Louisiana_TrawlsImage       | table | baum
 public | CdtCstReferenceTable              | table | baum
-----

Join the Source Tables
~~~~~~~~~~~~~~~~~~~~~~

The following command to join the source tables is identical to that
received from Philip Goldstein except for blank spaces being replaced
by underscores in all column names, e.g. +Sample Code+ was
changed to +Sample_Code+.

-----
SELECT
"CAGES_Louisiana_LengthsImage"."Sample_Code",
"CAGES_Louisiana_LengthsImage"."Species_Code",
"CAGES_Louisiana_SpeciesImage"."Scientific_Name",
"CAGES_Louisiana_SpeciesImage"."Common_Name",
"CAGES_Louisiana_LengthsImage"."Length",
"CAGES_Louisiana_SamplesImage"."Station_Code",
"CAGES_Louisiana_SamplesImage"."YYYY",
"CAGES_Louisiana_SamplesImage"."MM",
"CAGES_Louisiana_SamplesImage"."DD",
"CAGES_Louisiana_SamplesImage"."Time",
"CdtCstReferenceTable"."TimeZone",
"CAGES_Louisiana_SamplesImage"."Duration",
"CAGES_Louisiana_StationsImage"."Station",
"CAGES_Louisiana_StationsImage"."Latitude",
"CAGES_Louisiana_StationsImage"."Longitude",
"CAGES_Louisiana_StationsImage"."Site_Name",
"CAGES_Louisiana_StationsImage"."Water_Body",
"CAGES_Louisiana_TrawlsImage"."Total_Number",
"CAGES_Louisiana_CPUEImage"."CPUE"
INTO "CAGES_Louisiana_Lengths_CPUE_Join_20130822"
FROM
"CAGES_Louisiana_LengthsImage"
LEFT JOIN "CAGES_Louisiana_SamplesImage" ON
"CAGES_Louisiana_SamplesImage"."Sample_Code" = "CAGES_Louisiana_LengthsImage"."Sample_Code"
LEFT JOIN "CAGES_Louisiana_StationsImage" ON
"CAGES_Louisiana_StationsImage"."Station_Code" = "CAGES_Louisiana_SamplesImage"."Station_Code"
LEFT JOIN "CAGES_Louisiana_SpeciesImage" ON
"CAGES_Louisiana_SpeciesImage"."Species_Code" = "CAGES_Louisiana_LengthsImage"."Species_Code"
LEFT JOIN "CAGES_Louisiana_TrawlsImage" ON
"CAGES_Louisiana_TrawlsImage"."Sample_Code" = "CAGES_Louisiana_LengthsImage"."Sample_Code"
AND
"CAGES_Louisiana_TrawlsImage"."Species_Code" = "CAGES_Louisiana_LengthsImage"."Species_Code"
LEFT JOIN "CAGES_Louisiana_CPUEImage" ON
"CAGES_Louisiana_CPUEImage"."Sample_Code" = "CAGES_Louisiana_SamplesImage"."Sample_Code"
AND
"CAGES_Louisiana_CPUEImage"."Species_Code" = "CAGES_Louisiana_LengthsImage"."Species_Code"
LEFT JOIN "CdtCstReferenceTable" ON
"CdtCstReferenceTable"."YYYY" = "CAGES_Louisiana_SamplesImage"."YYYY"
AND
"CdtCstReferenceTable"."MM" = "CAGES_Louisiana_SamplesImage"."MM"
AND
"CdtCstReferenceTable"."DD" = "CAGES_Louisiana_SamplesImage"."DD"
;
-----

This was successful, with a +select+ command showing:

-----
AGES_Louisiana=> select * from "CAGES_Louisiana_Lengths_CPUE_Join_20130822" limit 1;
           Sample_Code            |           Species_Code           |         Scientific_Name          |           Common_Name            |              Length              |   
        Station_Code           |               YYYY               |                MM                |                DD                |               Time               |      
       TimeZone             |             Duration             |             Station              |             Latitude             |            Longitude             |         
   Site_Name             |            Water_Body            |           Total_Number           |               CPUE               
----------------------------------+----------------------------------+----------------------------------+----------------------------------+----------------------------------+---
-------------------------------+----------------------------------+----------------------------------+----------------------------------+----------------------------------+------
----------------------------+----------------------------------+----------------------------------+----------------------------------+----------------------------------+---------
-------------------------+----------------------------------+----------------------------------+----------------------------------
 1077                             | 2038                             | CYNOSCION NEBULOSUS              | SPOTTED SEATROUT                 | 202                              | 37
                               | 1990                             | 5                                | 10                               | 1040                             | -05:0
0                           | 10                               | 65                               | 30.00                            | -89.87                           | Bayou Bi
envenue                  | Lake Borgne                      | 2                                | 6.4                             
(1 row)
-----

[[create_louisiana_table]]
Create the IOOS Standard Table
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

-----
CREATE TABLE "CAGES_Louisiana_Lengths_CPUE_IOOS_Standard_20130822" (
"modified" character varying,
"verbatimModified" character varying,
"datasetID" character varying,
"datasetName" character varying,
"higherInstitutionCode" character varying,
"institutionCode" character varying,
"ownerInstitutionCode" character varying,
"collectionCode" character varying,
"catalogNumber" character varying,
"eventDate" character varying,
"eventDateTimeZone" character varying,
"eventDateRemarks" character varying,
"verbatimEventDate" character varying,
"time" character varying,
"timeUncertainty" integer,
"latitude" character varying,
"longitude" character varying,
"footprintWKT" character varying,
"coordinateUncertaintyInMeters" decimal,
"verbatimCoordinates" character varying,
"verbatimCoordinateSystem" character varying,
"verbatimSRS" character varying,
"geodeticDatum" character varying,
"georeferencedBy" character varying,
"georeferenceProtocol" character varying,
"depth" decimal,
"basisOfRecord" character varying,
"recordedBy" character varying,
"vernacularName" character varying,
"scientificName" character varying,
"taxonRank" character varying,
"aphiaID" character varying,
"tsn" character varying,
"genus" character varying,
"subgenus" character varying,
"species" character varying,
"infraspecificEpithet" character varying,
"scientificNameAuthorship" character varying,
"identifiedBy" character varying,
"identificationDate" character varying,
"identificationQualifier" character varying,
"identificationRemarks" character varying,
"individualCount" integer,
"weightInKg" character varying,
"sex" character varying,
"lifeStage" character varying,
"dynamicProperties" character varying,
"observedIndividualLengthInCm" character varying,
"observedMeanLengthInCm" character varying,
"observedMaxLengthInCm" character varying,
"observedMinLengthInCm" character varying,
"lengthType" character varying,
"occurrenceRemarks" text,
"surveyEventID" character varying,
"sampleID" character varying,
"subsampleID" character varying,
"samplingProtocol" character varying,
"samplingEffort" character varying,
"samplingConditions" character varying,
"totalInSample" character varying,
"sampleShape" character varying,
"sampleLengthInMeters" character varying,
"sampleWidthInMeters" character varying,
"sampleHeightInMeters" character varying,
"sampleRadiusInMeters" character varying,
"sampleAreaInSquareMeters" character varying,
"sampleVolumeInCubicMeters" character varying,
"visibilityInMeters" character varying,
"visibilityType" character varying,
"waterTemperatureInCelsius" character varying,
"habitat" character varying,
"bottomType" character varying,
"quantificationType" character varying,
"quantificationVocabulary" character varying,
"quantificationName" character varying,
"quantificationValue" character varying,
"quantificationUnit" character varying,
"quantificationUncertainty" character varying,
"quantificationDeterminedDate" character varying,
"quantificationDeterminedBy" character varying,
"quantificationMethod" character varying,
"waterBody" character varying,
"islandGroup" character varying,
"island" character varying,
"locality" character varying,
"country" character varying,
"stateProvince" character varying,
"county" character varying,
"municipality" character varying,
"kingdom" character varying,
"phylum" character varying,
"class" character varying,
"order" character varying,
"family" character varying
) 
WITH OIDS;
-----

Create a *lowercase* version of the table:

[[create_louisiana_table_lowercase]]
-----
CREATE TABLE "cages_louisiana_lengths_cpue_ioos_standard_lc" (
"modified" character varying,
"verbatimmodified" character varying,
"datasetid" character varying,
"datasetname" character varying,
"higherinstitutioncode" character varying,
"institutioncode" character varying,
"ownerinstitutioncode" character varying,
"collectioncode" character varying,
"catalognumber" character varying,
"eventdate" character varying,
"eventdatetimezone" character varying,
"eventdateremarks" character varying,
"verbatimeventdate" character varying,
"time" character varying,
"timeuncertainty" integer,
"latitude" character varying,
"longitude" character varying,
"footprintwkt" character varying,
"coordinateuncertaintyinmeters" decimal,
"verbatimcoordinates" character varying,
"verbatimcoordinatesystem" character varying,
"verbatimsrs" character varying,
"geodeticdatum" character varying,
"georeferencedby" character varying,
"georeferenceprotocol" character varying,
"depth" decimal,
"basisofrecord" character varying,
"recordedby" character varying,
"vernacularname" character varying,
"scientificname" character varying,
"taxonrank" character varying,
"aphiaid" character varying,
"tsn" character varying,
"genus" character varying,
"subgenus" character varying,
"species" character varying,
"infraspecificepithet" character varying,
"scientificnameauthorship" character varying,
"identifiedby" character varying,
"identificationdate" character varying,
"identificationqualifier" character varying,
"identificationremarks" character varying,
"individualcount" integer,
"weightinkg" character varying,
"sex" character varying,
"lifestage" character varying,
"dynamicproperties" character varying,
"observedindividuallengthincm" character varying,
"observedmeanlengthincm" character varying,
"observedmaxlengthincm" character varying,
"observedminlengthincm" character varying,
"lengthtype" character varying,
"occurrenceremarks" text,
"surveyeventid" character varying,
"sampleid" character varying,
"subsampleid" character varying,
"samplingprotocol" character varying,
"samplingeffort" character varying,
"samplingconditions" character varying,
"totalinsample" character varying,
"sampleshape" character varying,
"samplelengthinmeters" character varying,
"samplewidthinmeters" character varying,
"sampleheightinmeters" character varying,
"sampleradiusinmeters" character varying,
"sampleareainsquaremeters" character varying,
"samplevolumeincubicmeters" character varying,
"visibilityinmeters" character varying,
"visibilitytype" character varying,
"watertemperatureincelsius" character varying,
"habitat" character varying,
"bottomtype" character varying,
"quantificationtype" character varying,
"quantificationvocabulary" character varying,
"quantificationname" character varying,
"quantificationvalue" character varying,
"quantificationunit" character varying,
"quantificationuncertainty" character varying,
"quantificationdetermineddate" character varying,
"quantificationdeterminedby" character varying,
"quantificationmethod" character varying,
"waterbody" character varying,
"islandgroup" character varying,
"island" character varying,
"locality" character varying,
"country" character varying,
"stateprovince" character varying,
"county" character varying,
"municipality" character varying,
"kingdom" character varying,
"phylum" character varying,
"class" character varying,
"order" character varying,
"family" character varying
)
WITH OIDS;
-----

This was successful as is, with a +select+ command showing:

-----
CAGES_Louisiana=> select * from
"CAGES_Louisiana_Lengths_CPUE_IOOS_Standard_20130822" limit 1;
 modified | verbatimModified | datasetID | datasetName | higherInstitutionCode
| institutionCode | ownerInstitutionCode | collectionCode | catalogNumber |
eventDate | eventD
ateTimeZone | eventDateRemarks | verbatimEventDate | time | timeUncertainty |
latitude | longitude | footprintWKT | coordinateUncertaintyInMeters |
verbatimCoordinates | ver
batimCoordinateSystem | verbatimSRS | geodeticDatum | georeferencedBy |
georeferenceProtocol | depth | basisOfRecord | recordedBy | vernacularName |
scientificName | taxonRa
nk | aphiaID | tsn | genus | subgenus | species | infraspecificEpithet |
scientificNameAuthorship | identifiedBy | identificationDate |
identificationQualifier | identificat
ionRemarks | individualCount | weightInKg | sex | lifeStage |
dynamicProperties | observedIndividualLengthInCm | observedMeanLengthInCm |
observedMaxLengthInCm | observedMin
LengthInCm | lengthType | occurrenceRemarks | surveyEventID | sampleID |
subsampleID | samplingProtocol | samplingEffort | samplingConditions |
totalInSample | sampleShape |
 sampleLengthInMeters | sampleWidthInMeters | sampleHeightInMeters |
sampleRadiusInMeters | sampleAreaInSquareMeters | sampleVolumeInCubicMeters |
visibilityInMeters | visib
ilityType | waterTemperatureInCelsius | habitat | bottomType |
quantificationType | quantificationVocabulary | quantificationName |
quantificationValue | quantificationUnit 
| quantificationUncertainty | quantificationDeterminedDate |
quantificationDeterminedBy | quantificationMethod | waterBody | islandGroup |
island | locality | country | stat
eProvince | county | municipality | kingdom | phylum | class | order | family 
----------+------------------+-----------+-------------+-----------------------+-----------------+----------------------+----------------+---------------+-----------+-------
------------+------------------+-------------------+------+-----------------+----------+-----------+--------------+-------------------------------+---------------------+----
----------------------+-------------+---------------+-----------------+----------------------+-------+---------------+------------+----------------+----------------+--------
---+---------+-----+-------+----------+---------+----------------------+--------------------------+--------------+--------------------+-------------------------+------------
-----------+-----------------+------------+-----+-----------+-------------------+------------------------------+------------------------+-----------------------+------------
-----------+------------+-------------------+---------------+----------+-------------+------------------+----------------+--------------------+---------------+-------------+
----------------------+---------------------+----------------------+----------------------+--------------------------+---------------------------+--------------------+------
----------+---------------------------+---------+------------+--------------------+--------------------------+--------------------+---------------------+--------------------
+---------------------------+------------------------------+----------------------------+----------------------+-----------+-------------+--------+----------+---------+-----
----------+--------+--------------+---------+--------+-------+-------+--------
(0 rows)
-----

A table listing shows:

-----
CAGES_Louisiana=> \dt
                              List of relations
 Schema |                        Name                         | Type  | Owner 
--------+-----------------------------------------------------+-------+-------
 public | CAGES_Louisiana_CPUEImage                           | table | baum
 public | CAGES_Louisiana_HydrologicalImage                   | table | baum
 public | CAGES_Louisiana_LengthsImage                        | table | baum
 public | CAGES_Louisiana_Lengths_CPUE_IOOS_Standard_20130822 | table | baum
 public | CAGES_Louisiana_Lengths_CPUE_Join_20130822          | table | baum
 public | CAGES_Louisiana_SamplesImage                        | table | baum
 public | CAGES_Louisiana_SpeciesImage                        | table | baum
 public | CAGES_Louisiana_StationsImage                       | table | baum
 public | CAGES_Louisiana_TrawlsImage                         | table | baum
 public | CdtCstReferenceTable                                | table | baum
(10 rows)
-----

[[populate_louisiana_table]]
Insert Into IOOS Standard from Join
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

-----
INSERT INTO "CAGES_Louisiana_Lengths_CPUE_IOOS_Standard_20130822b"
SELECT
--modified
'2013-08-14',
--verbatimModified
NULL,
--datasetID
'CAGES_Louisiana_Lengths_IOOS_Standard',
--datasetName
'CAGES Louisiana Length Data with CPUE',
--higherInstitutionCode
'DOC;NOAA;NMFS;SEFSC;Fishery Ecology Branch',
--institutionCode
'NMFS',
--ownerInstitutionCode
'NMFS',
--collectionCode
'CAGES Louisiana',
--catalogNumber
NULL,
--eventDate
"YYYY" || '-' || lpad("MM",2,'0') || '-' || lpad("DD",2,'0') || 'T' || substr(lpad("Time",4,'0'),1,2) || ':' || substr(lpad("Time",4,'0'),3,2),
--eventDateTimeZone
"TimeZone",
--eventDateRemarks
NULL,
--verbatimEventDate
NULL,
--time
replace((("YYYY" || '-' || lpad("MM",2,'0') || '-' || lpad("DD",2,'0') || ' ' || substr(lpad("Time",4,'0'),1,2) || ':' || substr(lpad("Time",4,'0'),3,2))::timestamp + "TimeZone"::interval)::character varying,' ','T') || 'Z',
--timeUncertainty
360,
--latitude
"Latitude",
--longitude
"Longitude",
--footprintWKT
NULL,
--coordinateUncertaintyInMeters
1250,
--verbatimCoordinates
NULL,
--verbatimCoordinateSystem
NULL,
--verbatimSRS
NULL,
--geodeticDatum
'EPSG:4326 WGS84',
--georeferencedBy
'LDWF',
--georeferenceProtocol
'Coordinate uncertainty is based on starting point plus travel during trawl.',
--depth
NULL,
--basisOfRecord
'Human Observation',
--recordedBy
'LDWF',
--vernacularName
initcap(split_part("Common_Name",' ',1)) || ' ' || lower(split_part("Common_Name",' ',2)) || ' ' || lower(split_part("Common_Name",' ',3)),
--scientificName
initcap(split_part("Scientific_Name",' ',1)) || ' ' || lower(split_part("Scientific_Name",' ',2)) || ' ' || lower(split_part("Scientific_Name",' ',3)),
--taxonRank
CASE
-- no taxon rank if no sci name
WHEN "Scientific_Name" IS NULL THEN NULL
-- no taxon rank for the following list
WHEN "Species_Code" IN ('999','1120','2000','9999') THEN NULL
-- taxon rank = Genus for the following list
WHEN "Species_Code" IN ('993','1115','1116','1210','1213','1409','1506','2057','2127','2142','2184','2185','2186','2188','2190','2191','2197','2199','2200','2201','2202','2211','2221','2222','2227','2230','2233','2240','2252','2258','2313','2322','2366','2385','2392','2429','2434','2446','2454','2459') THEN 'Genus'
-- taxon rank = Family for the following list
WHEN "Species_Code" IN ('994','1521','2194','2300','2321','2387','2425','2427','2428','2441','2457','2458','2460') THEN 'Family'
-- taxon rank = Order for the following list
WHEN "Species_Code" IN ('992','2380','2383','2384') THEN 'Order'
-- taxon rank = Subclass for the following list
WHEN "Species_Code" IN ('2386') THEN 'Subclass'
-- taxon rank = Class for the following list
WHEN "Species_Code" IN ('989','990','991','995','996','997') THEN 'Class'
-- taxon rank = Subphylum for the following list
WHEN "Species_Code" IN ('998') THEN 'Subphylum' ELSE 'Species'
END,
--aphiaID
NULL,
--tsn
NULL,
--genus
CASE
WHEN "Species_Code" IN ('989','990','991','992','994','995','996','997','998','999','1120','1521','2000','2194','2300','2321','2380','2383','2384','2386','2387','2425','2427','2428','2441','2457','2458','2460','9999') THEN NULL
ELSE initcap(split_part("Scientific_Name",' ',1))
END,
--subgenus
NULL,
--species
CASE
WHEN "Species_Code" IN ('989','990','991','992','993','994','995','996','997','998','999','1115','1116','1120','1210','1213','1409','1506','1521','2000','2057','2127','2142','2184','2185','2186','2188','2190','2191','2194','2197','2199','2200','2201','2202','2211','2221','2222','2227','2230','2233','2240','2252','2258','2300','2313','2321','2322','2366','2380','2383','2384','2385','2386','2387','2392','2425','2427','2428','2429','2434','2441','2446','2454','2457','2458','2459','2460','9999') THEN NULL
ELSE lower(split_part("Scientific_Name",' ',2))
END,
--infraspecificEpithet
NULL,
--scientificNameAuthorship
NULL,
--identifiedBy
'LDWF',
--identificationDate
"YYYY" || '-' || lpad("MM",2,'0') || '-' || lpad("DD",2,'0'),
--identificationQualifier
NULL,
--identificationRemarks
NULL,
--individualCount
1,
--sex
NULL,
--weightInKg
NULL,
--lifeStage
NULL,
--dynamicProperties
NULL,
--observedIndividualLengthInCm
to_char("Length"::numeric/10, 'FM999.9')::numeric,
--observedMeanLengthInCm
NULL,
--observedMaxLengthInCm
NULL,
--observedMinLengthInCm
NULL,
--lengthType
CASE
WHEN "Species_Code" IN ('988','2003','2072','2118','2159','2160','2161','2162','2163','2164','2165','2183','2184','2185','2196','2230','2240','2241','2242','2243','2244','2245','2250','2257','2259','2268','2273','2275','2276','2279','2281','2283','2284','2285','2299','2302','2304','2312','2314','2318','2328','2337','2377','2378','2389','2402','2418','2422','2424','2425','2426','2428','2432','2442','2443','2445','2446','2449','2451','2458','2462') THEN 'Carapace Width'
ELSE 'Total_Length'
END,
--occurrenceRemarks
NULL,
--surveyEventID
NULL,
--sampleID
"Sample_Code",
--subsampleID
"Sample_Code" || '-' || "Species_Code",
--samplingProtocol
'Each sample was subsampled by species and individual lengths recorded.',
--samplingEffort
'Duration = ' || "Duration" || ' minutes',
--samplingConditions
NULL,
--totalInSample
'Count = ' || "Total_Number",
--sampleShape
NULL,
--sampleLengthInMeters
NULL,
--sampleWidthInMeters
NULL,
--sampleHeightInMeters
NULL,
--sampleRadiusInMeters
NULL,
--sampleAreaInSquareMeters
NULL,
--sampleVolumeInCubicMeters
NULL,
--visibilityInMeters
NULL,
--visibilityType
NULL,
--waterTemperatureInCelsius
NULL,
--habitat
NULL,
--bottomType
NULL,
--quantificationType
'P',
--quantificationVocabulary
NULL,
--quantificationName
'CPUE',
--quantificationValue
"CPUE",
--quantificationUnit
'hectare^-1',
--quantificationUncertainty
NULL,
--quantificationDeterminedDate
NULL,
--quantificationDeterminedBy
'SEFSC',
--quantificationMethod
'refer to metadata',
--waterBody
"Water_Body",
--islandGroup
NULL,
--island
NULL,
--locality
"Site_Name",
--country
'USA',
--stateProvince
'Louisiana',
--county
NULL,
--municipality
NULL,
--kingdom
NULL,
--phylum
NULL,
--class
NULL,
--order
NULL,
--family
NULL
FROM "CAGES_Louisiana_Lengths_CPUE_Join_20130822"
;
-----

Or a *lowercase* version:

[[populate_louisiana_table_lowercase]]

-----
INSERT INTO "cages_louisiana_lengths_cpue_ioos_standard_lc"
SELECT
--modified
'2013-08-14',
--verbatimmodified
NULL,
--datasetid
'CAGES_Louisiana_Lengths_IOOS_Standard',
--datasetname
'CAGES Louisiana Length Data with CPUE',
--higherinstitutioncode
'DOC;NOAA;NMFS;SEFSC;Fishery Ecology Branch',
--institutioncode
'NMFS',
--ownerinstitutioncode
'NMFS',
--collectioncode
'CAGES Louisiana',
--catalognumber
NULL,
--eventdate
"YYYY" || '-' || lpad("MM",2,'0') || '-' || lpad("DD",2,'0') || 'T' || substr(lpad("Time",4,'0'),1,2) || ':' || substr(lpad("Time",4,'0'),3,2),
--eventdatetimezone
"TimeZone",
--eventdateremarks
NULL,
--verbatimeventdate
NULL,
--time
replace((("YYYY" || '-' || lpad("MM",2,'0') || '-' || lpad("DD",2,'0') || ' ' || substr(lpad("Time",4,'0'),1,2) || ':' || substr(lpad("Time",4,'0'),3,2))::timestamp + "TimeZone"::interval)::character varying,' ','T') || 'Z',
--timeuncertainty
360,
--latitude
"Latitude",
--longitude
"Longitude",
--footprintwkt
NULL,
--coordinateuncertaintyinmeters
1250,
--verbatimcoordinates
NULL,
--verbatimcoordinatesystem
NULL,
--verbatimsrs
NULL,
--geodeticdatum
'EPSG:4326 WGS84',
--georeferencedby
'LDWF',
--georeferenceprotocol
'Coordinate uncertainty is based on starting point plus travel during trawl.',
--depth
NULL,
--basisofrecord
'Human Observation',
--recordedby
'LDWF',
--vernacularname
initcap(split_part("Common_Name",' ',1)) || ' ' || lower(split_part("Common_Name",' ',2)) || ' ' || lower(split_part("Common_Name",' ',3)),
--scientificname
initcap(split_part("Scientific_Name",' ',1)) || ' ' || lower(split_part("Scientific_Name",' ',2)) || ' ' || lower(split_part("Scientific_Name",' ',3)),
--taxonrank
CASE
-- no taxon rank if no sci name
WHEN "Scientific_Name" IS NULL THEN NULL
-- no taxon rank for the following list
WHEN "Species_Code" IN ('999','1120','2000','9999') THEN NULL
-- taxon rank = Genus for the following list
WHEN "Species_Code" IN ('993','1115','1116','1210','1213','1409','1506','2057','2127','2142','2184','2185','2186','2188','2190','2191','2197','2199','2200','2201','2202','2211','2221','2222','2227','2230','2233','2240','2252','2258','2313','2322','2366','2385','2392','2429','2434','2446','2454','2459') THEN 'Genus'
-- taxon rank = Family for the following list
WHEN "Species_Code" IN ('994','1521','2194','2300','2321','2387','2425','2427','2428','2441','2457','2458','2460') THEN 'Family'
-- taxon rank = Order for the following list
WHEN "Species_Code" IN ('992','2380','2383','2384') THEN 'Order'
-- taxon rank = Subclass for the following list
WHEN "Species_Code" IN ('2386') THEN 'Subclass'
-- taxon rank = Class for the following list
WHEN "Species_Code" IN ('989','990','991','995','996','997') THEN 'Class'
-- taxon rank = Subphylum for the following list
WHEN "Species_Code" IN ('998') THEN 'Subphylum' ELSE 'Species'
END,
--aphiaid
NULL,
--tsn
NULL,
--genus
CASE
WHEN "Species_Code" IN ('989','990','991','992','994','995','996','997','998','999','1120','1521','2000','2194','2300','2321','2380','2383','2384','2386','2387','2425','2427','2428','2441','2457','2458','2460','9999') THEN NULL
ELSE initcap(split_part("Scientific_Name",' ',1))
END,
--subgenus
NULL,
--species
CASE
WHEN "Species_Code" IN ('989','990','991','992','993','994','995','996','997','998','999','1115','1116','1120','1210','1213','1409','1506','1521','2000','2057','2127','2142','2184','2185','2186','2188','2190','2191','2194','2197','2199','2200','2201','2202','2211','2221','2222','2227','2230','2233','2240','2252','2258','2300','2313','2321','2322','2366','2380','2383','2384','2385','2386','2387','2392','2425','2427','2428','2429','2434','2441','2446','2454','2457','2458','2459','2460','9999') THEN NULL
ELSE lower(split_part("Scientific_Name",' ',2))
END,
--infraspecificepithet
NULL,
--scientificnameauthorship
NULL,
--identifiedby
'LDWF',
--identificationdate
"YYYY" || '-' || lpad("MM",2,'0') || '-' || lpad("DD",2,'0'),
--identificationqualifier
NULL,
--identificationremarks
NULL,
--individualcount
1,
--sex
NULL,
--weightinkg
NULL,
--lifestage
NULL,
--dynamicproperties
NULL,
--observedindividuallengthincm
to_char("Length"::numeric/10, 'FM999.9')::numeric,
--observedmeanlengthincm
NULL,
--observedmaxlengthincm
NULL,
--observedminlengthincm
NULL,
--lengthtype
CASE
WHEN "Species_Code" IN ('988','2003','2072','2118','2159','2160','2161','2162','2163','2164','2165','2183','2184','2185','2196','2230','2240','2241','2242','2243','2244','2245','2250','2257','2259','2268','2273','2275','2276','2279','2281','2283','2284','2285','2299','2302','2304','2312','2314','2318','2328','2337','2377','2378','2389','2402','2418','2422','2424','2425','2426','2428','2432','2442','2443','2445','2446','2449','2451','2458','2462') THEN 'Carapace Width'
ELSE 'Total_Length'
END,
--occurrenceremarks
NULL,
--surveyeventid
NULL,
--sampleid
"Sample_Code",
--subsampleid
"Sample_Code" || '-' || "Species_Code",
--samplingprotocol
'Each sample was subsampled by species and individual lengths recorded.',
--samplingeffort
'Duration = ' || "Duration" || ' minutes',
--samplingconditions
NULL,
--totalinsample
'Count = ' || "Total_Number",
--sampleshape
NULL,
--samplelengthinmeters
NULL,
--samplewidthinmeters
NULL,
--sampleheightinmeters
NULL,
--sampleradiusinmeters
NULL,
--sampleareainsquaremeters
NULL,
--samplevolumeincubicmeters
NULL,
--visibilityinmeters
NULL,
--visibilitytype
NULL,
--watertemperatureincelsius
NULL,
--habitat
NULL,
--bottomtype
NULL,
--quantificationtype
'P',
--quantificationvocabulary
NULL,
--quantificationname
'CPUE',
--quantificationvalue
"CPUE",
--quantificationunit
'hectare^-1',
--quantificationuncertainty
NULL,
--quantificationdetermineddate
NULL,
--quantificationdeterminedby
'SEFSC',
--quantificationmethod
'refer to metadata',
--waterbody
"Water_Body",
--islandgroup
NULL,
--island
NULL,
--locality
"Site_Name",
--country
'USA',
--stateprovince
'Louisiana',
--county
NULL,
--municipality
NULL,
--kingdom
NULL,
--phylum
NULL,
--class
NULL,
--order
NULL,
--family
NULL
FROM "CAGES_Louisiana_Lengths_CPUE_Join_20130822"
;
-----

A +select+ after this shows:

-----
CAGES_Louisiana=> select * from "CAGES_Louisiana_Lengths_CPUE_IOOS_Standard_20130822" limit 1;
  modified  | verbatimModified |               datasetID               |              datasetName              |           higherInstitutionCode            | institutionCode
 | ownerInstitutionCode | collectionCode  | catalogNumber |    eventDate     | eventDateTimeZone | eventDateRemarks | verbatimEventDate |         time         | timeUncertai
nty | latitude | longitude | footprintWKT | coordinateUncertaintyInMeters | verbatimCoordinates | verbatimCoordinateSystem | verbatimSRS |  geodeticDatum  | georeferencedBy 
|                            georeferenceProtocol                             | depth |   basisOfRecord   | recordedBy |  vernacularName   |    scientificName    | taxonRank
 | aphiaID | tsn |   genus   | subgenus |  species  | infraspecificEpithet | scientificNameAuthorship | identifiedBy | identificationDate | identificationQualifier | identif
icationRemarks | individualCount | weightInKg | sex | lifeStage | dynamicProperties | observedIndividualLengthInCm | observedMeanLengthInCm | observedMaxLengthInCm | observe
dMinLengthInCm |  lengthType  | occurrenceRemarks | surveyEventID | sampleID | subsampleID |                            samplingProtocol                            |    samp
lingEffort     | samplingConditions | totalInSample | sampleShape | sampleLengthInMeters | sampleWidthInMeters | sampleHeightInMeters | sampleRadiusInMeters | sampleAreaInSq
uareMeters | sampleVolumeInCubicMeters | visibilityInMeters | visibilityType | waterTemperatureInCelsius | habitat | bottomType | quantificationType | quantificationVocabula
ry | quantificationName | quantificationValue | quantificationUnit | quantificationUncertainty | quantificationDeterminedDate | quantificationDeterminedBy | quantificationMe
thod |  waterBody  | islandGroup | island |    locality     | country | stateProvince | county | municipality | kingdom | phylum | class | order | family 
------------+------------------+---------------------------------------+---------------------------------------+--------------------------------------------+----------------
-+----------------------+-----------------+---------------+------------------+-------------------+------------------+-------------------+----------------------+-------------
----+----------+-----------+--------------+-------------------------------+---------------------+--------------------------+-------------+-----------------+-----------------
+-----------------------------------------------------------------------------+-------+-------------------+------------+-------------------+----------------------+----------
-+---------+-----+-----------+----------+-----------+----------------------+--------------------------+--------------+--------------------+-------------------------+--------
---------------+-----------------+------------+-----+-----------+-------------------+------------------------------+------------------------+-----------------------+--------
---------------+--------------+-------------------+---------------+----------+-------------+------------------------------------------------------------------------+--------
---------------+--------------------+---------------+-------------+----------------------+---------------------+----------------------+----------------------+---------------
-----------+---------------------------+--------------------+----------------+---------------------------+---------+------------+--------------------+-----------------------
---+--------------------+---------------------+--------------------+---------------------------+------------------------------+----------------------------+-----------------
-----+-------------+-------------+--------+-----------------+---------+---------------+--------+--------------+---------+--------+-------+-------+--------
 2013-08-14 |                  | CAGES_Louisiana_Lengths_IOOS_Standard | CAGES Louisiana Length Data with CPUE | DOC;NOAA;NMFS;SEFSC;Fishery Ecology Branch | NMFS           
 | NMFS                 | CAGES Louisiana |               | 1990-05-10T10:40 | -05:00            |                  |                   | 1990-05-10T05:40:00Z |             
360 | 30.00    | -89.87    |              |                          1250 |                     |                          |             | EPSG:4326 WGS84 | LDWF            
| Coordinate uncertainty is based on starting point plus travel during trawl. |       | Human Observation | LDWF       | Spotted seatrout  | Cynoscion nebulosus  | Species  
 |         |     | Cynoscion |          | nebulosus |                      |                          | LDWF         | 1990-05-10         |                         |        
               |               1 |            |     |           |                   | 20.2                         |                        |                       |        
               | Total_Length |                   |               | 1077     | 1077-2038   | Each sample was subsampled by species and individual lengths recorded. | Duratio
n = 10 minutes |                    | Count = 2     |             |                      |                     |                      |                      |               
           |                           |                    |                |                           |         |            | P                  |                       
   | CPUE               | 6.4                 | hectare^-1         |                           |                              | SEFSC                      | refer to metadat
a    | Lake Borgne |             |        | Bayou Bienvenue | USA     | Louisiana     |        |              |         |        |       |       | 
(1 row)
-----

Converting Table Name to Lowercase
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Various downstream problems have caused us to change the
name of the table to lowercase via:

-----
alter table CAGES_Louisiana_Lengths_CPUE_IOOS_Standard_20130822 rename to cages_louisiana_lengths_cpue_ioos_standard_20130822;
-----

Granting Privileges to the Table
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

If a table is going to be accessed via a user other than +postgres+, then
table privileges must be granted by +postgres+ to that user.  For
the table we just created that would be via:

-----
grant all privileges on table cages_louisiana_lengths_cpue_ioos_standard_20130822 to baum;
-----

Testing the Database Availability with Python Psycopg2
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Obtain the Psycopg2 program from:

http://initd.org/psycopg/[+http://initd.org/psycopg/+]

or install it in the usual Pythonic way(s).

*Useful Tutorials and Bits of Advice*

http://stackoverflow.com/questions/2209169/using-postgres-in-a-web-app-transaction-aborted-errors[+http://stackoverflow.com/questions/2209169/using-postgres-in-a-web-app-transaction-aborted-errors+]

http://wiki.postgresql.org/wiki/Psycopg2_Tutorial[+http://wiki.postgresql.org/wiki/Psycopg2_Tutorial+]

Sample Program to Test the Database
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

-----
import psycopg2
try:
      conn = psycopg2.connect("dbname='CAGES_Louisiana' user='baum' host='localhost' password='password'")
except:
      print " Can't connect to server."
cur = conn.cursor()
conn.rollback()
cur.execute('SELECT * from "CAGES_Louisiana_CPUEImage"')
rows = cur.fetchall()
print rows[0]
('1                               ', '2000                            ', '7                               ', '6                               ', None, '2004                            ', 'ANCHOA MITCHILLI                ', '60.87                           ')
-----

The database can be reached, so we proceed to the JDBC attempt.

Obtaining the JDBC Driver for PostgreSQL
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

The driver can be found at:

http://jdbc.postgresql.org/download.html[+http://jdbc.postgresql.org/download.html+]

with the most recent version (as of this writing) being:

-----
postgresql-9.2-1003.jdbc4.jar
-----

This must then be copied to the appropriate location in the ERDDAP
server directories:

-----
cp postgresql-9.2-1003.jdbc4.jar /opt/tomcat6/webapps/erddap/WEB-INF/lib
-----

Connecting to the JDBC Postgresql Server
~~~~~~~~~~~~~~~~~~~~~~`````~~~~~~~~~~~~~

Useful sources were:

http://www.cyberciti.biz/faq/postgresql-remote-access-or-connection/[+http://www.cyberciti.biz/faq/postgresql-remote-access-or-connection/+]

http://jdbc.postgresql.org/documentation/80/connect.html[+http://jdbc.postgresql.org/documentation/80/connect.html+]

The final bits that worked when the ERDDAP and PostgreSQL servers are on the
same machine are:

-----
    <sourceUrl>jdbc:postgresql://localhost:5432/CAGES_Louisiana</sourceUrl>
    <driverName>org.postgresql.Driver</driverName>
    <connectionProperty name="user">baum</connectionProperty>
    <connectionProperty name="password">pword</connectionProperty>
    <tableName>CAGES_Louisiana_Lengths_CPUE_IOOS_Standard_20130822</tableName>
-----

We still haven't successfully connected to the PostgreSQL server from a remote
machine.


Creating the Skeleton XML File Using +GenerateDatasetsXml+
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Run the program via:

-----
/opt/tomcat6/webapps/erddap/WEB-INF/GenerateDatasetsXml
-----

and obtain:

-----
*** GenerateDatasetsXml ***
Results are shown on the screen, put on the clipboard and put in
/raid/erddap/logs/log.txt
Press ^C to exit at any time.
Type "" to change from a non-nothing default back to nothing.
DISCLAIMER:
  The chunk of datasets.xml made by GenerateDatasetsXml isn't perfect.
  YOU MUST READ AND EDIT THE XML BEFORE USING IT IN A PUBLIC ERDDAP.
  GenerateDatasetsXml relies on a lot of rules-of-thumb which aren't always
  correct.  *YOU* ARE RESPONSIBLE FOR ENSURING THE CORRECTNESS OF THE XML
  THAT YOU ADD TO ERDDAP'S datasets.xml FILE.
For detailed information, see
http://coastwatch.pfeg.noaa.gov/erddap/download/setupDatasetsXml.html

The EDDType options are:
  EDDGridAggregateExistingDimension
  EDDGridFromDap
  EDDGridFromErddap
  EDDGridFromNcFiles
  EDDGridFromThreddsCatalog
  EDDTableFromAsciiFiles
  EDDTableFromAwsXmlFiles
  EDDTableFromDapSequence
  EDDTableFromDatabase
  EDDTableFromErddap
  EDDTableFromIoosSOS
  EDDTableFromNcFiles
  EDDTableFromNcCFFiles
  EDDTableFromOBIS
  EDDTableFromSOS
  EDDTableFromThreddsFiles
Which EDDType (default="EDDGridFromDap")
? EDDTableFromDatabase
URL (default="")
? jdbc:postgresql://localhost:5432/CAGES_Louisiana
Driver name (default="")
? org.postgresql.Driver
Connection properties (format: name1|value1|name2|value2) (default="")
? user|baum|password|pword
Catalog name (default="")
? 
Schema name (default="")
? 
Table name (default="")
? CAGES_Louisiana_Lengths_CPUE_IOOS_Standard_20130822
OrderBy (CSV list of sourceNames) (default="")
? 
ReloadEveryNMinutes (e.g., 10080) (default="")
? 
infoUrl (default="")
? 
institution (default="")
? 
summary (default="")
? 
title (default="")
? 
-----

The Initial Skeleton File for CAGES_Louisiana_Lengths_CPUE_IOOS_Standard_20130822
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

*Error*:

The variable +time+ had no +units+ attribute.

*Fix:*

Added  +<att name="units">seconds since 1970-01-01T00:00:00Z</att>+ to the
attributes for the variable +datetime_epoch+.

*Error*:

When a variable's destinationName is "depth", the sourceAttributes or
addAttributes "units" MUST be "m" (not "null").
If needed, use "scale_factor" to convert the source values to meters
(positive=down),
use a different destinationName for this variable.

*Fix*:

Added +<att name="units">m</att>+ to the list of attributes for the variable +depth+.

*Problem*:

The title was the unenlightening +Data from a local source.+

*Fix:*

Changed it to +CAGES Louisiana Lengths CPUE IOOS Standard+.

-----
<dataset type="EDDTableFromDatabase" datasetID="CAGES_Louisiana_Lengths_CPUE_IOOS_Standard_20130822" active="true">
    <sourceUrl>jdbc:postgresql://localhost:5432/CAGES_Louisiana</sourceUrl>
    <driverName>org.postgresql.Driver</driverName>
    <connectionProperty name="user">baum</connectionProperty>
    <connectionProperty name="password">pword</connectionProperty>
    <catalogName></catalogName>
    <schemaName></schemaName>
    <tableName>CAGES_Louisiana_Lengths_CPUE_IOOS_Standard_20130822</tableName>
    <reloadEveryNMinutes>10080</reloadEveryNMinutes>
    <!-- sourceAttributes>
    </sourceAttributes -->
    <!-- Please specify the actual cdm_data_type (TimeSeries?) and related info below, for example...
        <att name="cdm_timeseries_variables">station, longitude, latitude</att>
        <att name="subsetVariables">station, longitude, latitude</att>
    -->
    <addAttributes>
        <att name="cdm_data_type">Point</att>
        <att name="Conventions">COARDS, CF-1.6, Unidata Dataset Discovery v1.0</att>
        <att name="infoUrl">???</att>
        <att name="institution">???</att>
        <att name="keywords">air, aphia, area, atmosphere,
Atmosphere &gt; Air Quality &gt; Visibility,
authorship, basis, biology, body, bottom, catalog, celsius, class, code, collection, conditions, coordinate, coordinates, count, country, county, cubic, data, dataset, date, datum, depth, determined, dynamic, effort, epithet, event, family, footprint, genus, geodetic, georeference, georeferenced, group, habitat, height, higher, identification, identified, identifier, individual, infraspecific, institution, island, kingdom, length, life, local, locality, max, mean, meteorology, meters, method, min, modified, municipality, name, number, observed, occurrence, order, owner, phylum, properties, protocol, province, qualifier, quality, quantification, radius, rank, record, recorded, remarks, sample, sampling, scientific, sex, shape, source., species, square, srs, stage, state, statistics, subgenus, subsample, survey, system, taxon, taxonomy, temperature, time, total, tsn, type, uncertainty, unit, value, verbatim, vernacular, visibility, visibility_in_air, vocabulary, volume, water, weight, width, wkt, zone</att>
        <att name="keywords_vocabulary">GCMD Science Keywords</att>
        <att name="license">[standard]</att>
        <att name="Metadata_Conventions">COARDS, CF-1.6, Unidata Dataset Discovery v1.0</att>
        <att name="sourceUrl">(local database)</att>
        <att name="standard_name_vocabulary">CF-12</att>
        <att name="summary">Data from a local source.</att>
        <att name="title">Data from a local source.</att>
    </addAttributes>
    <dataVariable>
        <sourceName>modified</sourceName>
        <destinationName>modified</destinationName>
        <dataType>String</dataType>
        <!-- sourceAttributes>
        </sourceAttributes -->
        <addAttributes>
            <att name="ioos_category">Unknown</att>
            <att name="long_name">Modified</att>
        </addAttributes>
    </dataVariable>
    <dataVariable>
        <sourceName>verbatimmodified</sourceName>
        <destinationName>verbatimmodified</destinationName>
        <dataType>String</dataType>
        <!-- sourceAttributes>
        </sourceAttributes -->
        <addAttributes>
            <att name="ioos_category">Unknown</att>
            <att name="long_name">Verbatim Modified</att>
        </addAttributes>
    </dataVariable>
    <dataVariable>
        <sourceName>datasetid</sourceName>
        <destinationName>datasetid</destinationName>
        <dataType>String</dataType>
        <!-- sourceAttributes>
        </sourceAttributes -->
        <addAttributes>
            <att name="ioos_category">Identifier</att>
            <att name="long_name">Dataset ID</att>
        </addAttributes>
    </dataVariable>
    <dataVariable>
        <sourceName>datasetname</sourceName>
        <destinationName>datasetname</destinationName>
        <dataType>String</dataType>
        <!-- sourceAttributes>
        </sourceAttributes -->
        <addAttributes>
            <att name="ioos_category">Unknown</att>
            <att name="long_name">Dataset Name</att>
        </addAttributes>
    </dataVariable>
    <dataVariable>
        <sourceName>higherinstitutioncode</sourceName>
        <destinationName>higherinstitutioncode</destinationName>
        <dataType>String</dataType>
        <!-- sourceAttributes>
        </sourceAttributes -->
        <addAttributes>
            <att name="ioos_category">Unknown</att>
            <att name="long_name">Higher Institution Code</att>
        </addAttributes>
    </dataVariable>
    <dataVariable>
        <sourceName>institutioncode</sourceName>
        <destinationName>institutioncode</destinationName>
        <dataType>String</dataType>
        <!-- sourceAttributes>
        </sourceAttributes -->
        <addAttributes>
            <att name="ioos_category">Unknown</att>
            <att name="long_name">Institution Code</att>
        </addAttributes>
    </dataVariable>
    <dataVariable>
        <sourceName>ownerinstitutioncode</sourceName>
        <destinationName>ownerinstitutioncode</destinationName>
        <dataType>String</dataType>
        <!-- sourceAttributes>
        </sourceAttributes -->
        <addAttributes>
            <att name="ioos_category">Unknown</att>
            <att name="long_name">Owner Institution Code</att>
        </addAttributes>
    </dataVariable>
    <dataVariable>
        <sourceName>collectioncode</sourceName>
        <destinationName>collectioncode</destinationName>
        <dataType>String</dataType>
        <!-- sourceAttributes>
        </sourceAttributes -->
        <addAttributes>
            <att name="ioos_category">Unknown</att>
            <att name="long_name">Collection Code</att>
        </addAttributes>
    </dataVariable>
    <dataVariable>
        <sourceName>catalognumber</sourceName>
        <destinationName>catalognumber</destinationName>
        <dataType>String</dataType>
        <!-- sourceAttributes>
        </sourceAttributes -->
        <addAttributes>
            <att name="colorBarMaximum" type="double">100.0</att>
            <att name="colorBarMinimum" type="double">0.0</att>
            <att name="ioos_category">Statistics</att>
            <att name="long_name">Catalog Number</att>
        </addAttributes>
    </dataVariable>
    <dataVariable>
        <sourceName>eventdate</sourceName>
        <destinationName>eventdate</destinationName>
        <dataType>String</dataType>
        <!-- sourceAttributes>
        </sourceAttributes -->
        <addAttributes>
            <att name="ioos_category">Time</att>
            <att name="long_name">Event Date</att>
        </addAttributes>
    </dataVariable>
    <dataVariable>
        <sourceName>eventdatetimezone</sourceName>
        <destinationName>eventdatetimezone</destinationName>
        <dataType>String</dataType>
        <!-- sourceAttributes>
        </sourceAttributes -->
        <addAttributes>
            <att name="ioos_category">Time</att>
            <att name="long_name">Event Date Time Zone</att>
        </addAttributes>
    </dataVariable>
    <dataVariable>
        <sourceName>eventdateremarks</sourceName>
        <destinationName>eventdateremarks</destinationName>
        <dataType>String</dataType>
        <!-- sourceAttributes>
        </sourceAttributes -->
        <addAttributes>
            <att name="ioos_category">Time</att>
            <att name="long_name">Event Date Remarks</att>
        </addAttributes>
    </dataVariable>
    <dataVariable>
        <sourceName>verbatimeventdate</sourceName>
        <destinationName>verbatimeventdate</destinationName>
        <dataType>String</dataType>
        <!-- sourceAttributes>
        </sourceAttributes -->
        <addAttributes>
            <att name="ioos_category">Time</att>
            <att name="long_name">Verbatim Event Date</att>
        </addAttributes>
    </dataVariable>
    <dataVariable>
        <sourceName>time</sourceName>
        <destinationName>time</destinationName>
        <dataType>String</dataType>
        <!-- sourceAttributes>
        </sourceAttributes -->
        <addAttributes>
            <att name="ioos_category">Time</att>
            <att name="long_name">Time</att>
            <att name="standard_name">time</att>
        </addAttributes>
    </dataVariable>
    <dataVariable>
        <sourceName>timeuncertainty</sourceName>
        <destinationName>timeuncertainty</destinationName>
        <dataType>int</dataType>
        <!-- sourceAttributes>
        </sourceAttributes -->
        <addAttributes>
            <att name="ioos_category">Quality</att>
            <att name="long_name">Time Uncertainty</att>
        </addAttributes>
    </dataVariable>
    <dataVariable>
        <sourceName>latitude</sourceName>
        <destinationName>latitude</destinationName>
        <dataType>String</dataType>
        <!-- sourceAttributes>
        </sourceAttributes -->
        <addAttributes>
            <att name="colorBarMaximum" type="double">90.0</att>
            <att name="colorBarMinimum" type="double">-90.0</att>
            <att name="ioos_category">Location</att>
            <att name="long_name">Latitude</att>
            <att name="standard_name">latitude</att>
            <att name="units">degrees_north</att>
        </addAttributes>
    </dataVariable>
    <dataVariable>
        <sourceName>longitude</sourceName>
        <destinationName>longitude</destinationName>
        <dataType>String</dataType>
        <!-- sourceAttributes>
        </sourceAttributes -->
        <addAttributes>
            <att name="colorBarMaximum" type="double">180.0</att>
            <att name="colorBarMinimum" type="double">-180.0</att>
            <att name="ioos_category">Location</att>
            <att name="long_name">Longitude</att>
            <att name="standard_name">longitude</att>
            <att name="units">degrees_east</att>
        </addAttributes>
    </dataVariable>
    <dataVariable>
        <sourceName>footprintwkt</sourceName>
        <destinationName>footprintwkt</destinationName>
        <dataType>String</dataType>
        <!-- sourceAttributes>
        </sourceAttributes -->
        <addAttributes>
            <att name="ioos_category">Unknown</att>
            <att name="long_name">Footprint WKT</att>
        </addAttributes>
    </dataVariable>
    <dataVariable>
        <sourceName>coordinateuncertaintyinmeters</sourceName>
        <destinationName>coordinateuncertaintyinmeters</destinationName>
        <dataType>double</dataType>
        <!-- sourceAttributes>
        </sourceAttributes -->
        <addAttributes>
            <att name="ioos_category">Quality</att>
            <att name="long_name">Coordinate Uncertainty In Meters</att>
        </addAttributes>
    </dataVariable>
    <dataVariable>
        <sourceName>verbatimcoordinates</sourceName>
        <destinationName>verbatimcoordinates</destinationName>
        <dataType>String</dataType>
        <!-- sourceAttributes>
        </sourceAttributes -->
        <addAttributes>
            <att name="ioos_category">Unknown</att>
            <att name="long_name">Verbatim Coordinates</att>
        </addAttributes>
    </dataVariable>
    <dataVariable>
        <sourceName>verbatimcoordinatesystem</sourceName>
        <destinationName>verbatimcoordinatesystem</destinationName>
        <dataType>String</dataType>
        <!-- sourceAttributes>
        </sourceAttributes -->
        <addAttributes>
            <att name="ioos_category">Unknown</att>
            <att name="long_name">Verbatim Coordinate System</att>
        </addAttributes>
    </dataVariable>
    <dataVariable>
        <sourceName>verbatimsrs</sourceName>
        <destinationName>verbatimsrs</destinationName>
        <dataType>String</dataType>
        <!-- sourceAttributes>
        </sourceAttributes -->
        <addAttributes>
            <att name="ioos_category">Location</att>
            <att name="long_name">Verbatim SRS</att>
        </addAttributes>
    </dataVariable>
    <dataVariable>
        <sourceName>geodeticdatum</sourceName>
        <destinationName>geodeticdatum</destinationName>
        <dataType>String</dataType>
        <!-- sourceAttributes>
        </sourceAttributes -->
        <addAttributes>
            <att name="ioos_category">Location</att>
            <att name="long_name">Geodetic Datum</att>
        </addAttributes>
    </dataVariable>
    <dataVariable>
        <sourceName>georeferencedby</sourceName>
        <destinationName>georeferencedby</destinationName>
        <dataType>String</dataType>
        <!-- sourceAttributes>
        </sourceAttributes -->
        <addAttributes>
            <att name="ioos_category">Unknown</att>
            <att name="long_name">Georeferenced By</att>
        </addAttributes>
    </dataVariable>
    <dataVariable>
        <sourceName>georeferenceprotocol</sourceName>
        <destinationName>georeferenceprotocol</destinationName>
        <dataType>String</dataType>
        <!-- sourceAttributes>
        </sourceAttributes -->
        <addAttributes>
            <att name="ioos_category">Unknown</att>
            <att name="long_name">Georeference Protocol</att>
        </addAttributes>
    </dataVariable>
    <dataVariable>
        <sourceName>depth</sourceName>
        <destinationName>depth</destinationName>
        <dataType>double</dataType>
        <!-- sourceAttributes>
        </sourceAttributes -->
        <addAttributes>
            <att name="colorBarMaximum" type="double">8000.0</att>
            <att name="colorBarMinimum" type="double">0.0</att>
            <att name="colorBarPalette">OceanDepth</att>
            <att name="ioos_category">Location</att>
            <att name="long_name">Depth</att>
            <att name="standard_name">depth</att>
        </addAttributes>
    </dataVariable>
    <dataVariable>
        <sourceName>basisofrecord</sourceName>
        <destinationName>basisofrecord</destinationName>
        <dataType>String</dataType>
        <!-- sourceAttributes>
        </sourceAttributes -->
        <addAttributes>
            <att name="ioos_category">Unknown</att>
            <att name="long_name">Basis Of Record</att>
        </addAttributes>
    </dataVariable>
    <dataVariable>
        <sourceName>recordedby</sourceName>
        <destinationName>recordedby</destinationName>
        <dataType>String</dataType>
        <!-- sourceAttributes>
        </sourceAttributes -->
        <addAttributes>
            <att name="ioos_category">Unknown</att>
            <att name="long_name">Recorded By</att>
        </addAttributes>
    </dataVariable>
    <dataVariable>
        <sourceName>vernacularname</sourceName>
        <destinationName>vernacularname</destinationName>
        <dataType>String</dataType>
        <!-- sourceAttributes>
        </sourceAttributes -->
        <addAttributes>
            <att name="ioos_category">Taxonomy</att>
            <att name="long_name">Vernacular Name</att>
        </addAttributes>
    </dataVariable>
    <dataVariable>
        <sourceName>scientificname</sourceName>
        <destinationName>scientificname</destinationName>
        <dataType>String</dataType>
        <!-- sourceAttributes>
        </sourceAttributes -->
        <addAttributes>
            <att name="ioos_category">Taxonomy</att>
            <att name="long_name">Scientific Name</att>
        </addAttributes>
    </dataVariable>
    <dataVariable>
        <sourceName>taxonrank</sourceName>
        <destinationName>taxonrank</destinationName>
        <dataType>String</dataType>
        <!-- sourceAttributes>
        </sourceAttributes -->
        <addAttributes>
            <att name="ioos_category">Unknown</att>
            <att name="long_name">Taxon Rank</att>
        </addAttributes>
    </dataVariable>
    <dataVariable>
        <sourceName>aphiaid</sourceName>
        <destinationName>aphiaid</destinationName>
        <dataType>String</dataType>
        <!-- sourceAttributes>
        </sourceAttributes -->
        <addAttributes>
            <att name="ioos_category">Identifier</att>
            <att name="long_name">Aphia ID</att>
        </addAttributes>
    </dataVariable>
    <dataVariable>
        <sourceName>tsn</sourceName>
        <destinationName>tsn</destinationName>
        <dataType>String</dataType>
        <!-- sourceAttributes>
        </sourceAttributes -->
        <addAttributes>
            <att name="ioos_category">Unknown</att>
            <att name="long_name">TSN</att>
        </addAttributes>
    </dataVariable>
    <dataVariable>
        <sourceName>genus</sourceName>
        <destinationName>genus</destinationName>
        <dataType>String</dataType>
        <!-- sourceAttributes>
        </sourceAttributes -->
        <addAttributes>
            <att name="ioos_category">Taxonomy</att>
            <att name="long_name">Genus</att>
        </addAttributes>
    </dataVariable>
    <dataVariable>
        <sourceName>subgenus</sourceName>
        <destinationName>subgenus</destinationName>
        <dataType>String</dataType>
        <!-- sourceAttributes>
        </sourceAttributes -->
        <addAttributes>
            <att name="ioos_category">Taxonomy</att>
            <att name="long_name">Subgenus</att>
        </addAttributes>
    </dataVariable>
    <dataVariable>
        <sourceName>species</sourceName>
        <destinationName>species</destinationName>
        <dataType>String</dataType>
        <!-- sourceAttributes>
        </sourceAttributes -->
        <addAttributes>
            <att name="ioos_category">Taxonomy</att>
            <att name="long_name">Species</att>
        </addAttributes>
    </dataVariable>
    <dataVariable>
        <sourceName>infraspecificepithet</sourceName>
        <destinationName>infraspecificepithet</destinationName>
        <dataType>String</dataType>
        <!-- sourceAttributes>
        </sourceAttributes -->
        <addAttributes>
            <att name="ioos_category">Unknown</att>
            <att name="long_name">Infraspecific Epithet</att>
        </addAttributes>
    </dataVariable>
    <dataVariable>
        <sourceName>scientificnameauthorship</sourceName>
        <destinationName>scientificnameauthorship</destinationName>
        <dataType>String</dataType>
        <!-- sourceAttributes>
        </sourceAttributes -->
        <addAttributes>
            <att name="ioos_category">Taxonomy</att>
            <att name="long_name">Scientific Name Authorship</att>
        </addAttributes>
    </dataVariable>
    <dataVariable>
        <sourceName>identifiedby</sourceName>
        <destinationName>identifiedby</destinationName>
        <dataType>String</dataType>
        <!-- sourceAttributes>
        </sourceAttributes -->
        <addAttributes>
            <att name="ioos_category">Unknown</att>
            <att name="long_name">Identified By</att>
        </addAttributes>
    </dataVariable>
    <dataVariable>
        <sourceName>identificationdate</sourceName>
        <destinationName>identificationdate</destinationName>
        <dataType>String</dataType>
        <!-- sourceAttributes>
        </sourceAttributes -->
        <addAttributes>
            <att name="ioos_category">Time</att>
            <att name="long_name">Identification Date</att>
        </addAttributes>
    </dataVariable>
    <dataVariable>
        <sourceName>identificationqualifier</sourceName>
        <destinationName>identificationqualifier</destinationName>
        <dataType>String</dataType>
        <!-- sourceAttributes>
        </sourceAttributes -->
        <addAttributes>
            <att name="ioos_category">Unknown</att>
            <att name="long_name">Identification Qualifier</att>
        </addAttributes>
    </dataVariable>
    <dataVariable>
        <sourceName>identificationremarks</sourceName>
        <destinationName>identificationremarks</destinationName>
        <dataType>String</dataType>
        <!-- sourceAttributes>
        </sourceAttributes -->
        <addAttributes>
            <att name="ioos_category">Unknown</att>
            <att name="long_name">Identification Remarks</att>
        </addAttributes>
    </dataVariable>
    <dataVariable>
        <sourceName>individualcount</sourceName>
        <destinationName>individualcount</destinationName>
        <dataType>int</dataType>
        <!-- sourceAttributes>
        </sourceAttributes -->
        <addAttributes>
            <att name="colorBarMaximum" type="double">100.0</att>
            <att name="colorBarMinimum" type="double">0.0</att>
            <att name="ioos_category">Statistics</att>
            <att name="long_name">Individual Count</att>
        </addAttributes>
    </dataVariable>
    <dataVariable>
        <sourceName>weightinkg</sourceName>
        <destinationName>weightinkg</destinationName>
        <dataType>String</dataType>
        <!-- sourceAttributes>
        </sourceAttributes -->
        <addAttributes>
            <att name="ioos_category">Unknown</att>
            <att name="long_name">Weight In Kg</att>
        </addAttributes>
    </dataVariable>
    <dataVariable>
        <sourceName>sex</sourceName>
        <destinationName>sex</destinationName>
        <dataType>String</dataType>
        <!-- sourceAttributes>
        </sourceAttributes -->
        <addAttributes>
            <att name="ioos_category">Biology</att>
            <att name="long_name">Sex</att>
        </addAttributes>
    </dataVariable>
    <dataVariable>
        <sourceName>lifestage</sourceName>
        <destinationName>lifestage</destinationName>
        <dataType>String</dataType>
        <!-- sourceAttributes>
        </sourceAttributes -->
        <addAttributes>
            <att name="ioos_category">Biology</att>
            <att name="long_name">Life Stage</att>
        </addAttributes>
    </dataVariable>
    <dataVariable>
        <sourceName>dynamicproperties</sourceName>
        <destinationName>dynamicproperties</destinationName>
        <dataType>String</dataType>
        <!-- sourceAttributes>
        </sourceAttributes -->
        <addAttributes>
            <att name="ioos_category">Unknown</att>
            <att name="long_name">Dynamic Properties</att>
        </addAttributes>
    </dataVariable>
    <dataVariable>
        <sourceName>observedindividuallengthincm</sourceName>
        <destinationName>observedindividuallengthincm</destinationName>
        <dataType>String</dataType>
        <!-- sourceAttributes>
        </sourceAttributes -->
        <addAttributes>
            <att name="ioos_category">Unknown</att>
            <att name="long_name">Observed Individual Length In Cm</att>
        </addAttributes>
    </dataVariable>
    <dataVariable>
        <sourceName>observedmeanlengthincm</sourceName>
        <destinationName>observedmeanlengthincm</destinationName>
        <dataType>String</dataType>
        <!-- sourceAttributes>
        </sourceAttributes -->
        <addAttributes>
            <att name="ioos_category">Statistics</att>
            <att name="long_name">Observed Mean Length In Cm</att>
        </addAttributes>
    </dataVariable>
    <dataVariable>
        <sourceName>observedmaxlengthincm</sourceName>
        <destinationName>observedmaxlengthincm</destinationName>
        <dataType>String</dataType>
        <!-- sourceAttributes>
        </sourceAttributes -->
        <addAttributes>
            <att name="ioos_category">Unknown</att>
            <att name="long_name">Observed Max Length In Cm</att>
        </addAttributes>
    </dataVariable>
    <dataVariable>
        <sourceName>observedminlengthincm</sourceName>
        <destinationName>observedminlengthincm</destinationName>
        <dataType>String</dataType>
        <!-- sourceAttributes>
        </sourceAttributes -->
        <addAttributes>
            <att name="ioos_category">Unknown</att>
            <att name="long_name">Observed Min Length In Cm</att>
        </addAttributes>
    </dataVariable>
    <dataVariable>
        <sourceName>lengthtype</sourceName>
        <destinationName>lengthtype</destinationName>
        <dataType>String</dataType>
        <!-- sourceAttributes>
        </sourceAttributes -->
        <addAttributes>
            <att name="ioos_category">Unknown</att>
            <att name="long_name">Length Type</att>
        </addAttributes>
    </dataVariable>
    <dataVariable>
        <sourceName>occurrenceremarks</sourceName>
        <destinationName>occurrenceremarks</destinationName>
        <dataType>String</dataType>
        <!-- sourceAttributes>
        </sourceAttributes -->
        <addAttributes>
            <att name="ioos_category">Unknown</att>
            <att name="long_name">Occurrence Remarks</att>
        </addAttributes>
    </dataVariable>
    <dataVariable>
        <sourceName>surveyeventid</sourceName>
        <destinationName>surveyeventid</destinationName>
        <dataType>String</dataType>
        <!-- sourceAttributes>
        </sourceAttributes -->
        <addAttributes>
            <att name="ioos_category">Identifier</att>
            <att name="long_name">Survey Event ID</att>
        </addAttributes>
    </dataVariable>
    <dataVariable>
        <sourceName>sampleid</sourceName>
        <destinationName>sampleid</destinationName>
        <dataType>String</dataType>
        <!-- sourceAttributes>
        </sourceAttributes -->
        <addAttributes>
            <att name="ioos_category">Identifier</att>
            <att name="long_name">Sample ID</att>
        </addAttributes>
    </dataVariable>
    <dataVariable>
        <sourceName>subsampleid</sourceName>
        <destinationName>subsampleid</destinationName>
        <dataType>String</dataType>
        <!-- sourceAttributes>
        </sourceAttributes -->
        <addAttributes>
            <att name="ioos_category">Identifier</att>
            <att name="long_name">Subsample ID</att>
        </addAttributes>
    </dataVariable>
    <dataVariable>
        <sourceName>samplingprotocol</sourceName>
        <destinationName>samplingprotocol</destinationName>
        <dataType>String</dataType>
        <!-- sourceAttributes>
        </sourceAttributes -->
        <addAttributes>
            <att name="ioos_category">Unknown</att>
            <att name="long_name">Sampling Protocol</att>
        </addAttributes>
    </dataVariable>
    <dataVariable>
        <sourceName>samplingeffort</sourceName>
        <destinationName>samplingeffort</destinationName>
        <dataType>String</dataType>
        <!-- sourceAttributes>
        </sourceAttributes -->
        <addAttributes>
            <att name="ioos_category">Unknown</att>
            <att name="long_name">Sampling Effort</att>
        </addAttributes>
    </dataVariable>
    <dataVariable>
        <sourceName>samplingconditions</sourceName>
        <destinationName>samplingconditions</destinationName>
        <dataType>String</dataType>
        <!-- sourceAttributes>
        </sourceAttributes -->
        <addAttributes>
            <att name="ioos_category">Unknown</att>
            <att name="long_name">Sampling Conditions</att>
        </addAttributes>
    </dataVariable>
    <dataVariable>
        <sourceName>totalinsample</sourceName>
        <destinationName>totalinsample</destinationName>
        <dataType>String</dataType>
        <!-- sourceAttributes>
        </sourceAttributes -->
        <addAttributes>
            <att name="ioos_category">Unknown</att>
            <att name="long_name">Total In Sample</att>
        </addAttributes>
    </dataVariable>
    <dataVariable>
        <sourceName>sampleshape</sourceName>
        <destinationName>sampleshape</destinationName>
        <dataType>String</dataType>
        <!-- sourceAttributes>
        </sourceAttributes -->
        <addAttributes>
            <att name="ioos_category">Unknown</att>
            <att name="long_name">Sample Shape</att>
        </addAttributes>
    </dataVariable>
    <dataVariable>
        <sourceName>samplelengthinmeters</sourceName>
        <destinationName>samplelengthinmeters</destinationName>
        <dataType>String</dataType>
        <!-- sourceAttributes>
        </sourceAttributes -->
        <addAttributes>
            <att name="ioos_category">Unknown</att>
            <att name="long_name">Sample Length In Meters</att>
        </addAttributes>
    </dataVariable>
    <dataVariable>
        <sourceName>samplewidthinmeters</sourceName>
        <destinationName>samplewidthinmeters</destinationName>
        <dataType>String</dataType>
        <!-- sourceAttributes>
        </sourceAttributes -->
        <addAttributes>
            <att name="ioos_category">Unknown</att>
            <att name="long_name">Sample Width In Meters</att>
        </addAttributes>
    </dataVariable>
    <dataVariable>
        <sourceName>sampleheightinmeters</sourceName>
        <destinationName>sampleheightinmeters</destinationName>
        <dataType>String</dataType>
        <!-- sourceAttributes>
        </sourceAttributes -->
        <addAttributes>
            <att name="ioos_category">Unknown</att>
            <att name="long_name">Sample Height In Meters</att>
        </addAttributes>
    </dataVariable>
    <dataVariable>
        <sourceName>sampleradiusinmeters</sourceName>
        <destinationName>sampleradiusinmeters</destinationName>
        <dataType>String</dataType>
        <!-- sourceAttributes>
        </sourceAttributes -->
        <addAttributes>
            <att name="ioos_category">Unknown</att>
            <att name="long_name">Sample Radius In Meters</att>
        </addAttributes>
    </dataVariable>
    <dataVariable>
        <sourceName>sampleareainsquaremeters</sourceName>
        <destinationName>sampleareainsquaremeters</destinationName>
        <dataType>String</dataType>
        <!-- sourceAttributes>
        </sourceAttributes -->
        <addAttributes>
            <att name="ioos_category">Unknown</att>
            <att name="long_name">Sample Area In Square Meters</att>
        </addAttributes>
    </dataVariable>
    <dataVariable>
        <sourceName>samplevolumeincubicmeters</sourceName>
        <destinationName>samplevolumeincubicmeters</destinationName>
        <dataType>String</dataType>
        <!-- sourceAttributes>
        </sourceAttributes -->
        <addAttributes>
            <att name="ioos_category">Unknown</att>
            <att name="long_name">Sample Volume In Cubic Meters</att>
        </addAttributes>
    </dataVariable>
    <dataVariable>
        <sourceName>visibilityinmeters</sourceName>
        <destinationName>visibilityinmeters</destinationName>
        <dataType>String</dataType>
        <!-- sourceAttributes>
        </sourceAttributes -->
        <addAttributes>
            <att name="colorBarMaximum" type="double">100.0</att>
            <att name="colorBarMinimum" type="double">0.0</att>
            <att name="ioos_category">Meteorology</att>
            <att name="long_name">Visibility In Air</att>
            <att name="standard_name">visibility_in_air</att>
        </addAttributes>
    </dataVariable>
    <dataVariable>
        <sourceName>visibilitytype</sourceName>
        <destinationName>visibilitytype</destinationName>
        <dataType>String</dataType>
        <!-- sourceAttributes>
        </sourceAttributes -->
        <addAttributes>
            <att name="colorBarMaximum" type="double">100.0</att>
            <att name="colorBarMinimum" type="double">0.0</att>
            <att name="ioos_category">Meteorology</att>
            <att name="long_name">Visibility In Air</att>
            <att name="standard_name">visibility_in_air</att>
        </addAttributes>
    </dataVariable>
    <dataVariable>
        <sourceName>watertemperatureincelsius</sourceName>
        <destinationName>watertemperatureincelsius</destinationName>
        <dataType>String</dataType>
        <!-- sourceAttributes>
        </sourceAttributes -->
        <addAttributes>
            <att name="ioos_category">Temperature</att>
            <att name="long_name">Water Temperature In Celsius</att>
        </addAttributes>
    </dataVariable>
    <dataVariable>
        <sourceName>habitat</sourceName>
        <destinationName>habitat</destinationName>
        <dataType>String</dataType>
        <!-- sourceAttributes>
        </sourceAttributes -->
        <addAttributes>
            <att name="ioos_category">Unknown</att>
            <att name="long_name">Habitat</att>
        </addAttributes>
    </dataVariable>
    <dataVariable>
        <sourceName>bottomtype</sourceName>
        <destinationName>bottomtype</destinationName>
        <dataType>String</dataType>
        <!-- sourceAttributes>
        </sourceAttributes -->
        <addAttributes>
            <att name="ioos_category">Unknown</att>
            <att name="long_name">Bottom Type</att>
        </addAttributes>
    </dataVariable>
    <dataVariable>
        <sourceName>quantificationtype</sourceName>
        <destinationName>quantificationtype</destinationName>
        <dataType>String</dataType>
        <!-- sourceAttributes>
        </sourceAttributes -->
        <addAttributes>
            <att name="ioos_category">Unknown</att>
            <att name="long_name">Quantification Type</att>
        </addAttributes>
    </dataVariable>
    <dataVariable>
        <sourceName>quantificationvocabulary</sourceName>
        <destinationName>quantificationvocabulary</destinationName>
        <dataType>String</dataType>
        <!-- sourceAttributes>
        </sourceAttributes -->
        <addAttributes>
            <att name="ioos_category">Unknown</att>
            <att name="long_name">Quantification Vocabulary</att>
        </addAttributes>
    </dataVariable>
    <dataVariable>
        <sourceName>quantificationname</sourceName>
        <destinationName>quantificationname</destinationName>
        <dataType>String</dataType>
        <!-- sourceAttributes>
        </sourceAttributes -->
        <addAttributes>
            <att name="ioos_category">Unknown</att>
            <att name="long_name">Quantification Name</att>
        </addAttributes>
    </dataVariable>
    <dataVariable>
        <sourceName>quantificationvalue</sourceName>
        <destinationName>quantificationvalue</destinationName>
        <dataType>String</dataType>
        <!-- sourceAttributes>
        </sourceAttributes -->
        <addAttributes>
            <att name="ioos_category">Unknown</att>
            <att name="long_name">Quantification Value</att>
        </addAttributes>
    </dataVariable>
    <dataVariable>
        <sourceName>quantificationunit</sourceName>
        <destinationName>quantificationunit</destinationName>
        <dataType>String</dataType>
        <!-- sourceAttributes>
        </sourceAttributes -->
        <addAttributes>
            <att name="ioos_category">Unknown</att>
            <att name="long_name">Quantification Unit</att>
        </addAttributes>
    </dataVariable>
    <dataVariable>
        <sourceName>quantificationuncertainty</sourceName>
        <destinationName>quantificationuncertainty</destinationName>
        <dataType>String</dataType>
        <!-- sourceAttributes>
        </sourceAttributes -->
        <addAttributes>
            <att name="ioos_category">Quality</att>
            <att name="long_name">Quantification Uncertainty</att>
        </addAttributes>
    </dataVariable>
    <dataVariable>
        <sourceName>quantificationdetermineddate</sourceName>
        <destinationName>quantificationdetermineddate</destinationName>
        <dataType>String</dataType>
        <!-- sourceAttributes>
        </sourceAttributes -->
        <addAttributes>
            <att name="ioos_category">Time</att>
            <att name="long_name">Quantification Determined Date</att>
        </addAttributes>
    </dataVariable>
    <dataVariable>
        <sourceName>quantificationdeterminedby</sourceName>
        <destinationName>quantificationdeterminedby</destinationName>
        <dataType>String</dataType>
        <!-- sourceAttributes>
        </sourceAttributes -->
        <addAttributes>
            <att name="ioos_category">Unknown</att>
            <att name="long_name">Quantification Determined By</att>
        </addAttributes>
    </dataVariable>
    <dataVariable>
        <sourceName>quantificationmethod</sourceName>
        <destinationName>quantificationmethod</destinationName>
        <dataType>String</dataType>
        <!-- sourceAttributes>
        </sourceAttributes -->
        <addAttributes>
            <att name="ioos_category">Unknown</att>
            <att name="long_name">Quantification Method</att>
        </addAttributes>
    </dataVariable>
    <dataVariable>
        <sourceName>waterbody</sourceName>
        <destinationName>waterbody</destinationName>
        <dataType>String</dataType>
        <!-- sourceAttributes>
        </sourceAttributes -->
        <addAttributes>
            <att name="ioos_category">Unknown</att>
            <att name="long_name">Water Body</att>
        </addAttributes>
    </dataVariable>
    <dataVariable>
        <sourceName>islandgroup</sourceName>
        <destinationName>islandgroup</destinationName>
        <dataType>String</dataType>
        <!-- sourceAttributes>
        </sourceAttributes -->
        <addAttributes>
            <att name="ioos_category">Unknown</att>
            <att name="long_name">Island Group</att>
        </addAttributes>
    </dataVariable>
    <dataVariable>
        <sourceName>island</sourceName>
        <destinationName>island</destinationName>
        <dataType>String</dataType>
        <!-- sourceAttributes>
        </sourceAttributes -->
        <addAttributes>
            <att name="ioos_category">Unknown</att>
            <att name="long_name">Island</att>
        </addAttributes>
    </dataVariable>
    <dataVariable>
        <sourceName>locality</sourceName>
        <destinationName>locality</destinationName>
        <dataType>String</dataType>
        <!-- sourceAttributes>
        </sourceAttributes -->
        <addAttributes>
            <att name="ioos_category">Location</att>
            <att name="long_name">Locality</att>
        </addAttributes>
    </dataVariable>
    <dataVariable>
        <sourceName>country</sourceName>
        <destinationName>country</destinationName>
        <dataType>String</dataType>
        <!-- sourceAttributes>
        </sourceAttributes -->
        <addAttributes>
            <att name="colorBarMaximum" type="double">100.0</att>
            <att name="colorBarMinimum" type="double">0.0</att>
            <att name="ioos_category">Statistics</att>
            <att name="long_name">Country</att>
        </addAttributes>
    </dataVariable>
    <dataVariable>
        <sourceName>stateprovince</sourceName>
        <destinationName>stateprovince</destinationName>
        <dataType>String</dataType>
        <!-- sourceAttributes>
        </sourceAttributes -->
        <addAttributes>
            <att name="ioos_category">Location</att>
            <att name="long_name">State Province</att>
        </addAttributes>
    </dataVariable>
    <dataVariable>
        <sourceName>county</sourceName>
        <destinationName>county</destinationName>
        <dataType>String</dataType>
        <!-- sourceAttributes>
        </sourceAttributes -->
        <addAttributes>
            <att name="colorBarMaximum" type="double">100.0</att>
            <att name="colorBarMinimum" type="double">0.0</att>
            <att name="ioos_category">Statistics</att>
            <att name="long_name">County</att>
        </addAttributes>
    </dataVariable>
    <dataVariable>
        <sourceName>municipality</sourceName>
        <destinationName>municipality</destinationName>
        <dataType>String</dataType>
        <!-- sourceAttributes>
        </sourceAttributes -->
        <addAttributes>
            <att name="ioos_category">Unknown</att>
            <att name="long_name">Municipality</att>
        </addAttributes>
    </dataVariable>
    <dataVariable>
        <sourceName>kingdom</sourceName>
        <destinationName>kingdom</destinationName>
        <dataType>String</dataType>
        <!-- sourceAttributes>
        </sourceAttributes -->
        <addAttributes>
            <att name="ioos_category">Unknown</att>
            <att name="long_name">Kingdom</att>
        </addAttributes>
    </dataVariable>
    <dataVariable>
        <sourceName>phylum</sourceName>
        <destinationName>phylum</destinationName>
        <dataType>String</dataType>
        <!-- sourceAttributes>
        </sourceAttributes -->
        <addAttributes>
            <att name="ioos_category">Taxonomy</att>
            <att name="long_name">Phylum</att>
        </addAttributes>
    </dataVariable>
    <dataVariable>
        <sourceName>class</sourceName>
        <destinationName>class</destinationName>
        <dataType>String</dataType>
        <!-- sourceAttributes>
        </sourceAttributes -->
        <addAttributes>
            <att name="ioos_category">Unknown</att>
            <att name="long_name">Class</att>
        </addAttributes>
    </dataVariable>
    <dataVariable>
        <sourceName>order</sourceName>
        <destinationName>order</destinationName>
        <dataType>String</dataType>
        <!-- sourceAttributes>
        </sourceAttributes -->
        <addAttributes>
            <att name="ioos_category">Taxonomy</att>
            <att name="long_name">Order</att>
        </addAttributes>
    </dataVariable>
    <dataVariable>
        <sourceName>family</sourceName>
        <destinationName>family</destinationName>
        <dataType>String</dataType>
        <!-- sourceAttributes>
        </sourceAttributes -->
        <addAttributes>
            <att name="ioos_category">Taxonomy</att>
            <att name="long_name">Family</att>
        </addAttributes>
    </dataVariable>
</dataset>
-----

The Debugging Cycle
~~~~~~~~~~~~~~~~~~~

Changing the Datatype of the Time Variables
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

Errors related to the time variables led to converting all the time-related
variables in the database from type +string+ to type +timestamp with time
zone+.  The appropriate PostgreSQL commands are:

-----
update "cages_louisiana_lengths_cpue_ioos_standard_20130822" set time=to_timestamp(time,'YYYY-MM-DDTHH:MI:SSZ');
update "cages_louisiana_lengths_cpue_ioos_standard_20130822" set modified=to_timestamp(modified,'YYYY-MM-DD');
update "cages_louisiana_lengths_cpue_ioos_standard_20130822" set "eventDate"=to_timestamp("eventDate",'YYYY-MM-DDTHH:MI');
-----

Converting Variable Names to Lowercase in ERDDAP Configuration File
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

It was thought a good idea to work with all lowercase variable names given
various problems encountered with the errors in ERDDAP with mixed-case
variable names.  An initial attempt - the easiest way out - was to simply
convert all the +sourceName+ variable names in the +datasets.xml+ file
to lowercase, with the +destinationName+ variable names retaining the
mixed-case naming scheme so the end user would see the variable names in their
officially approved mixed-case format evem of they were stored in all
lowercase.

An example of the change from the +datasets.xml+ file for the officially approved
variable name +verbatimModified+ is:

-----
        <sourceName>verbatimmodified</sourceName>
        <destinationName>verbatimModified</destinationName>
-----

With this attempt, the variable names in the database table would still be
stored in their mixed-case formats, but the assumption was made - based
on various evidence - that they'd be converted to lowercase in the transition
from PostgreSQL to ERDDAP and that the +sourceName+ variable names would
in fact work.

This did not work.  We still obtained the *Make a Graph*
page, i.e.

http://gcoos1.tamu.edu:8080/erddap/tabledap/CAGES_Louisiana_Lengths_CPUE_IOOS_Standard_20130822.graph[+http://gcoos1.tamu.edu:8080/erddap/tabledap/CAGES_Louisiana_Lengths_CPUE_IOOS_Standard_20130822.graph+]

error:

-----
Query error: variable=modified is listed twice in the results variables list
-----

So we're going to have to go the further distance of actually converting
the variable names in the database table to lowercase.


Converting Variable Names to Lowercase in Database Table
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

It was decided to convert all of the variable names in the database to
lowercase.  The first attempt was via the +alter+ commmand, an example being:

-----
alter table "cages_louisiana_lengths_cpue_ioos_standard_20130822" rename column "stateProvince" to "stateprovince";
-----

It turns out that such commands essentially rewrite the entire database and take forever, so another
route was taken.

. re-create the xref:create_louisiana_table[standard IOOS table] with the variables all in
xref:create_louisiana_table_lowercase[lowercase], changing the name from
+cages_louisiana_lengths_cpue_ioos_standard_20130822+ to
+cages_louisiana_lengths_cpue_ioos_standard_lc+ so as to at least keep what's
partially working;

. populate the table structure created, swapping out the
xref:populate_louisiana_table[original mixed case variables] for
xref:populate_louisiana_table_lowercase[lowercase variables];

. change the type of the time variables again:

-----
update "cages_louisiana_lengths_cpue_ioos_standard_lc" set time=to_timestamp(time,'YYYY-MM-DDTHH:MI:SSZ');
update "cages_louisiana_lengths_cpue_ioos_standard_lc" set modified=to_timestamp(modified,'YYYY-MM-DD');
update "cages_louisiana_lengths_cpue_ioos_standard_lc" set "eventDate"=to_timestamp("eventDate",'YYYY-MM-DDTHH:MI');
-----

The +datasets.xml+ was modified, changing:

-----
<tableName>cages_louisiana_lengths_cpue_ioos_standard_20130822</tableName>
-----

to 

-----
<tableName>cages_louisiana_lengths_cpue_ioos_standard_lc</tableName>
-----

Upon making these changes and restarting the Tomcat server, we get the
following error message when attempting to retrieve the values of +scientificName+ - i.e. everything
is unchecked except for +scientificName+ - from
the *Data Access Form* page:

-----
HTTP Status 500 - There was a (temporary?) problem. Wait a minute, then try
again. (In a browser, click the Reload button.) (ERROR from data source:
java.lang.Throwable: ERROR from data source: com.cohort.util.SimpleException:
Your query produced no matching results.) (Cause: java.lang.Throwable: ERROR
from data source: com.cohort.util.SimpleException: Your query produced no
matching results.)

type Status report

message There was a (temporary?) problem. Wait a minute, then try again. (In a
browser, click the Reload button.) (ERROR from data source:
java.lang.Throwable: ERROR from data source: com.cohort.util.SimpleException:
Your query produced no matching results.) (Cause: java.lang.Throwable: ERROR
from data source: com.cohort.util.SimpleException: Your query produced no
matching results.)

description The server encountered an internal error that prevented it from
fulfilling this request.
-----

The +log.txt+ file bits pertaining to this are:

-----
{{{{#21 2013-12-16T13:00:38 (notLoggedIn) (unknownIPAddress) /erddap/tabledap/CAGES_Louisiana_Lengths_CPUE_IOOS_Standard_20130822.htmlTable?scientificName&time%3E=2013-12-09T00:00:00Z
EDDTableFromDatabase.getConnection via DriverManager + datasets.xml info
  Success! time=4
  statement=SELECT "scientificname", "time" FROM cages_louisiana_lengths_cpue_ioos_standard_lc WHERE "time" >= '2013-12-08 19:00:00.000000 -05:00:00'
 statement~=SELECT "scientificname", "time" FROM cages_louisiana_lengths_cpue_ioos_standard_lc WHERE "time" >= '1.3865472E9'
ERROR for /erddap/tabledap/CAGES_Louisiana_Lengths_CPUE_IOOS_Standard_20130822.htmlTable?scientificName&time%3E=2013-12-09T00:00:00Z
gov.noaa.pfel.erddap.dataset.WaitThenTryAgainException: There was a (temporary?) problem.  Wait a minute, then try again.  (In a browser, click the Reload button.)
(ERROR from data source: java.lang.Throwable: ERROR from data source: com.cohort.util.SimpleException: Your query produced no matching results.)
(Cause: java.lang.Throwable: ERROR from data source: com.cohort.util.SimpleException: Your query produced no matching results.)
 at gov.noaa.pfel.erddap.dataset.EDDTableFromDatabase.getDataForDapQuery(EDDTableFromDatabase.java:826)
 at gov.noaa.pfel.erddap.dataset.EDDTable.respondToDapQuery(EDDTable.java:2390)
 at gov.noaa.pfel.erddap.Erddap.doDap(Erddap.java:2250)
 at gov.noaa.pfel.erddap.Erddap.doGet(Erddap.java:449)
Caused by: java.lang.Throwable: ERROR from data source: com.cohort.util.SimpleException: Your query produced no matching results.
 at gov.noaa.pfel.erddap.dataset.EDDTableFromDatabase.getDataForDapQuery(EDDTableFromDatabase.java:817)
 ... 21 more
Caused by: com.cohort.util.SimpleException: Your query produced no matching results.
 at gov.noaa.pfel.erddap.dataset.EDDTable.standardizeResultsTable(EDDTable.java:1211)
 at gov.noaa.pfel.erddap.dataset.EDDTableFromDatabase.getDataForDapQuery(EDDTableFromDatabase.java:805)
 ... 21 more
-----

Testing the PostgreSQL Server
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

The PostgreSQL server is tested using the Python *psycopg* package.
The following Python lines are run:

-----
conn = psycopg2.connect("dbname='cages_louisiana' user='baum' host='localhost' password='p1pster'")
cur = conn.cursor()
conn.rollback()
cur.execute('SELECT "scientificname" from "cages_louisiana_lengths_cpue_ioos_standard_lc"')
rows = cur.fetchall()
print rows[0]
('Arius felis ',)
-----

If we run the following PostgreSQL command:

-----
select "scientificname" from "cages_louisiana_lengths_cpue_ioos_standard_lc" limit 1;
-----

we obtain:

-----
  scientificname   

 Arius felis
(1 row)
-----

Thus we know the PostgreSQL server is working properly.

Also, the *Subset* page at:

http://gcoos1.tamu.edu:8080/erddap/tabledap/CAGES_Louisiana_Lengths_CPUE_IOOS_Standard_20130822.subset[+http://gcoos1.tamu.edu:8080/erddap/tabledap/CAGES_Louisiana_Lengths_CPUE_IOOS_Standard_20130822.subset+]

successfully retrieves the +longitude+ and +latitude+ values from the PostgreSQL database.

PostgreSQL Datatypes
~~~~~~~~~~~~~~~~~~~~

http://www.postgresql.org/docs/8.4/static/datatype.html[+http://www.postgresql.org/docs/8.4/static/datatype.html+]

Numeric Types
^^^^^^^^^^^^^

-----
Name 	        	Storage Size 			Description 				Range
smallint 		2 bytes 			small-range integer 			-32768 to +32767
integer 		4 bytes 			typical choice for integer 		-2147483648 to +2147483647
bigint 			8 bytes 			large-range integer 			-9223372036854775808 to 9223372036854775807
decimal 		variable 			user-specified precision, exact 	no limit
numeric 		variable 			user-specified precision, exact 	no limit
real 			4 bytes 			variable-precision, inexact 		6 decimal digits precision
double precision 	8 bytes 			variable-precision, inexact 		15 decimal digits precision
serial 	4 bytes 	autoincrementing integer 	1 to 2147483647
bigserial 		8 bytes 			large autoincrementing integer 		1 to 9223372036854775807
-----

Date/Time Types
^^^^^^^^^^^^^^^

http://www.postgresql.org/docs/8.4/static/datatype-datetime.html[+http://www.postgresql.org/docs/8.4/static/datatype-datetime.html+]

Character Types
^^^^^^^^^^^^^^^

http://www.postgresql.org/docs/8.4/static/datatype-character.html[+http://www.postgresql.org/docs/8.4/static/datatype-character.html+]




Old Version
~~~~~~~~~~~

+Louisiana_Biological+

-----
+-----------------------+---------+------+-----+---------+-------+
| Field                 | Type    | Null | Key | Default | Extra |
+-----------------------+---------+------+-----+---------+-------+
| Sample_Code           | int(11) | YES  |     | NULL    |       |
| Gear_Observation_Code | int(11) | YES  |     | NULL    |       |
| Duration              | int(11) | YES  |     | NULL    |       |
| Duration_Units        | int(11) | YES  |     | NULL    |       |
| Species_Code          | int(11) | YES  |     | NULL    |       |
| Total_Number          | int(11) | YES  |     | NULL    |       |
| Total_Number_Method   | int(11) | YES  |     | NULL    |       |
| Number_Measured       | int(11) | YES  |     | NULL    |       |
| Species_Observations  | int(11) | YES  |     | NULL    |       |
+-----------------------+---------+------+-----+---------+-------+
-----

+Louisiana_CPUE+

-----
+--------------+---------+------+-----+---------+-------+
| Field        | Type    | Null | Key | Default | Extra |
+--------------+---------+------+-----+---------+-------+
| Sample_Code  | int(11) | YES  |     | NULL    |       |
| CSA          | int(11) | YES  |     | NULL    |       |
| Station      | int(11) | YES  |     | NULL    |       |
| YYYY         | int(11) | YES  |     | NULL    |       |
| MM           | int(11) | YES  |     | NULL    |       |
| DD           | int(11) | YES  |     | NULL    |       |
| Species_Code | int(11) | YES  |     | NULL    |       |
| cpue         | double  | YES  |     | NULL    |       |
+--------------+---------+------+-----+---------+-------+
-----

+Louisiana_Duration+

-----
+---------------+--------------+------+-----+---------+-------+
| Field         | Type         | Null | Key | Default | Extra |
+---------------+--------------+------+-----+---------+-------+
| Duration_Code | int(11)      | YES  |     | NULL    |       |
| Units         | varchar(255) | YES  |     | NULL    |       |
+---------------+--------------+------+-----+---------+-------+
-----

+Louisiana_Gear+

-----
+-----------+--------------+------+-----+---------+-------+
| Field     | Type         | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| Gear_Code | int(11)      | YES  |     | NULL    |       |
| Gear      | varchar(255) | YES  |     | NULL    |       |
+-----------+--------------+------+-----+---------+-------+
-----

+Louisiana_Hydrological+

-----
+---------------------------+---------+------+-----+---------+-------+
| Field                     | Type    | Null | Key | Default | Extra |
+---------------------------+---------+------+-----+---------+-------+
| Sample_Code               | int(11) | YES  |     | NULL    |       |
| Salinity_Method           | int(11) | YES  |     | NULL    |       |
| Surface_Salinity          | double  | YES  |     | NULL    |       |
| Bottom_Salinity           | double  | YES  |     | NULL    |       |
| Average_Salinity          | double  | YES  |     | NULL    |       |
| Air_Temperature_Method    | int(11) | YES  |     | NULL    |       |
| Air_Temperature           | double  | YES  |     | NULL    |       |
| Water_Temperature_Method  | int(11) | YES  |     | NULL    |       |
| Surface_Water_Temperature | double  | YES  |     | NULL    |       |
| Bottom_Water_Temperature  | double  | YES  |     | NULL    |       |
| Average_Temperature       | double  | YES  |     | NULL    |       |
| Turbidity_Method          | int(11) | YES  |     | NULL    |       |
| Turbidity                 | double  | YES  |     | NULL    |       |
+---------------------------+---------+------+-----+---------+-------+
-----

+Louisiana_Length_Units+

-----
+-------------------+--------------+------+-----+---------+-------+
| Field             | Type         | Null | Key | Default | Extra |
+-------------------+--------------+------+-----+---------+-------+
| Length_Units_Code | int(11)      | YES  |     | NULL    |       |
| Units             | varchar(255) | YES  |     | NULL    |       |
+-------------------+--------------+------+-----+---------+-------+
-----

+Louisiana_Lengths+

-----
+-------------------------+---------+------+-----+---------+-------+
| Field                   | Type    | Null | Key | Default | Extra |
+-------------------------+---------+------+-----+---------+-------+
| Sample_Code             | int(11) | YES  |     | NULL    |       |
| Gear_Observation_Code   | int(11) | YES  |     | NULL    |       |
| Species_Code            | int(11) | YES  |     | NULL    |       |
| Length_Interval         | int(11) | YES  |     | NULL    |       |
| Length_Units            | int(11) | YES  |     | NULL    |       |
| Individual_Weight_Units | int(11) | YES  |     | NULL    |       |
| Stage                   | int(11) | YES  |     | NULL    |       |
| Species_Observation     | int(11) | YES  |     | NULL    |       |
| Length_Measurement      | int(11) | YES  |     | NULL    |       |
| Length_Group            | int(11) | YES  |     | NULL    |       |
+-------------------------+---------+------+-----+---------+-------+
-----

+Louisiana_Numbering_Methods+

-----
+--------------------+--------------+------+-----+---------+-------+
| Field              | Type         | Null | Key | Default | Extra |
+--------------------+--------------+------+-----+---------+-------+
| Number_Method_Code | int(11)      | YES  |     | NULL    |       |
| Method             | varchar(255) | YES  |     | NULL    |       |
+--------------------+--------------+------+-----+---------+-------+
-----

+Louisiana_Observations+

-----
+-------------+--------------+------+-----+---------+-------+
| Field       | Type         | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| Obs_Code    | int(11)      | YES  |     | NULL    |       |
| Observation | varchar(255) | YES  |     | NULL    |       |
+-------------+--------------+------+-----+---------+-------+
-----

+Louisiana_Physical_Methods+

-----
+-------------+--------------+------+-----+---------+-------+
| Field       | Type         | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| Method_Code | int(11)      | YES  |     | NULL    |       |
| Method      | varchar(255) | YES  |     | NULL    |       |
+-------------+--------------+------+-----+---------+-------+
-----

+Louisiana_Samples+

-----
+--------------+---------+------+-----+---------+-------+
| Field        | Type    | Null | Key | Default | Extra |
+--------------+---------+------+-----+---------+-------+
| Sample_Code  | int(11) | YES  |     | NULL    |       |
| CSA          | int(11) | YES  |     | NULL    |       |
| Station_Code | int(11) | YES  |     | NULL    |       |
| YYYY         | int(11) | YES  |     | NULL    |       |
| MM           | int(11) | YES  |     | NULL    |       |
| DD           | int(11) | YES  |     | NULL    |       |
| Time         | int(11) | YES  |     | NULL    |       |
| Gear_Code    | int(11) | YES  |     | NULL    |       |
+--------------+---------+------+-----+---------+-------+
-----

+Louisiana_Species+

-----
+-----------------+--------------+------+-----+---------+-------+
| Field           | Type         | Null | Key | Default | Extra |
+-----------------+--------------+------+-----+---------+-------+
| Species_Code    | int(11)      | YES  |     | NULL    |       |
| Scientific_Name | varchar(255) | YES  |     | NULL    |       |
| Common_Name     | varchar(255) | YES  |     | NULL    |       |
+-----------------+--------------+------+-----+---------+-------+
-----

+Louisiana_Stage+

-----
+----------------+--------------+------+-----+---------+-------+
| Field          | Type         | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+-------+
| Stage_Code     | int(11)      | YES  |     | NULL    |       |
| Maturity_Stage | varchar(255) | YES  |     | NULL    |       |
+----------------+--------------+------+-----+---------+-------+
-----

+Louisiana_Stations+

-----
+--------------+--------------+------+-----+---------+-------+
| Field        | Type         | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| Station_Code | int(11)      | YES  |     | NULL    |       |
| CSA          | int(11)      | YES  |     | NULL    |       |
| Station      | int(11)      | YES  |     | NULL    |       |
| Latitude     | double       | YES  |     | NULL    |       |
| Longitude    | double       | YES  |     | NULL    |       |
| Site_Name    | varchar(255) | YES  |     | NULL    |       |
| SubBay       | varchar(255) | YES  |     | NULL    |       |
+--------------+--------------+------+-----+---------+-------+
-----

+Louisiana_Weight_Measures+

-----
+-------------+--------------+------+-----+---------+-------+
| Field       | Type         | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| Weight_Code | int(11)      | YES  |     | NULL    |       |
| Descriptor  | varchar(255) | YES  |     | NULL    |       |
+-------------+--------------+------+-----+---------+-------+
-----

CAGES Mississippi Database
--------------------------

Disconnect from Louisiana database and create a new one for
Mississippi:

-----
\q
psql
create database "CAGES_Mississippi";
\c "CAGES_Mississippi";
-----

Creating the Tables
~~~~~~~~~~~~~~~~~~~

The statements used to create the tables:

-----
CREATE TABLE "CAGES_Mississippi_CPUEImage"("Sample_Code" CHAR(32),"Station_Code" CHAR(32),"YYYY" CHAR(32),"MM" CHAR(32),"DD" CHAR(32),"Species Code" CHAR(32),"CPUE" CHAR(32));

CREATE TABLE "CAGES_Mississippi_HydrologicalImage"("Sample_Code" CHAR(32),"Depth" CHAR(32),"Surface_Salinity" CHAR(32),"Bottom_Salinity" CHAR(32),"Average_Salinity" CHAR(32),"Surface_Temperature" CHAR(32),"Bottom_Temperature" CHAR(32),"Average_Temperature" CHAR(32),"Surface_DO" CHAR(32),"Bottom_DO"
CHAR(32),"Average_DO" CHAR(32));

CREATE TABLE "CAGES_Mississippi_LengthsImage"("Sample_Code" CHAR(32),"Species_Code" CHAR(32),"Length" CHAR(32),"Weight" CHAR(32));

CREATE TABLE "CAGES_Mississippi_SamplesImage"("Sample_Code" CHAR(32),"Station_Code" CHAR(32),"YYYY" CHAR(32),"MM" CHAR(32),"DD" CHAR(32));

CREATE TABLE "CAGES_Mississippi_SpeciesImage"("Species_Code" CHAR(32),"Phylum" CHAR(32),"Class" CHAR(32),"Order" CHAR(32),"Family" CHAR(32), "Scientific_Name" CHAR(32));

CREATE TABLE "CAGES_Mississippi_StationsImage"("Station_Code" CHAR(32),"Latitude" CHAR(32),"Longitude" CHAR(32),"Description" CHAR(32),"Bay" CHAR(32));

CREATE TABLE "CAGES_Mississippi_TrawlsImage"("Sample_Code" CHAR(32),"Species_Code" CHAR(32),"Measured" CHAR(32),"Total_Number" CHAR(32),"Total_Weight" CHAR(32));
-----

To obtain:

-----
CAGES_Mississippi=# \dt;
                        List of relations
 Schema |                Name                 | Type  |  Owner   
--------+-------------------------------------+-------+----------
 public | CAGES_Mississippi_CPUEImage         | table | postgres
 public | CAGES_Mississippi_HydrologicalImage | table | postgres
 public | CAGES_Mississippi_LengthsImage      | table | postgres
 public | CAGES_Mississippi_SamplesImage      | table | postgres
 public | CAGES_Mississippi_SpeciesImage      | table | postgres
 public | CAGES_Mississippi_StationsImage     | table | postgres
 public | CAGES_Mississippi_TrawlsImage       | table | postgres
 public | CdtCstReferenceTable                | table | postgres
(8 rows)
-----

Populating the Tables
~~~~~~~~~~~~~~~~~~~~~

-----
\copy "CAGES_Mississippi_CPUEImage" from '/home/postgres/Mississippi_CPUE.txt'

\copy "CAGES_Mississippi_HydrologicalImage" from '/home/postgres/Mississippi_Hydrological.txt';

\copy "CAGES_Mississippi_LengthsImage" from '/home/postgres/Mississippi_Lengths.txt';

\copy "CAGES_Mississippi_SamplesImage" from '/home/postgres/Mississippi_Samples.txt';

\copy "CAGES_Mississippi_SpeciesImage" from '/home/postgres/Mississippi_Species.txt';

\copy "CAGES_Mississippi_StationsImage" from '/home/postgres/Mississippi_Stations.txt';

\copy "CAGES_Mississippi_TrawlsImage" from '/home/postgres/Mississippi_Trawls.txt';

\copy "CdtCstReferenceTable" from '/home/postgres/CdtCstReferenceTable_20130827.txt';
-----

Make sure I have privileges on the database:

-----
grant all privileges on database "CAGES_Louisiana" to newuser;
-----

Join Source Tables Into Combined Table
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

-----
SELECT
"CAGES_Mississippi_LengthsImage"."Sample_Code",
"CAGES_Mississippi_LengthsImage"."Species_Code",
"CAGES_Mississippi_LengthsImage"."Length",
"CAGES_Mississippi_LengthsImage"."Weight",
"CAGES_Mississippi_SpeciesImage"."Phylum",
"CAGES_Mississippi_SpeciesImage"."Class",
"CAGES_Mississippi_SpeciesImage"."Order",
"CAGES_Mississippi_SpeciesImage"."Family",
"CAGES_Mississippi_SpeciesImage"."Scientific_Name",
"CAGES_Mississippi_SamplesImage"."Station_Code",
"CAGES_Mississippi_SamplesImage"."YYYY",
"CAGES_Mississippi_SamplesImage"."MM",
"CAGES_Mississippi_SamplesImage"."DD",
"CdtCstReferenceTable"."Time_Zone",
"CAGES_Mississippi_StationsImage"."Latitude",
"CAGES_Mississippi_StationsImage"."Longitude",
"CAGES_Mississippi_StationsImage"."Bay",
"CAGES_Mississippi_StationsImage"."Description",
"CAGES_Mississippi_HydrologicalImage"."Depth",
"CAGES_Mississippi_TrawlsImage"."Measured",
"CAGES_Mississippi_TrawlsImage"."Total_Number",
"CAGES_Mississippi_TrawlsImage"."Total_Weight",
"CAGES_Mississippi_CPUEImage"."CPUE"
INTO "CAGES_Mississippi_Lengths_CPUE_Join_20130827"
FROM
"CAGES_Mississippi_LengthsImage"
LEFT JOIN "CAGES_Mississippi_SpeciesImage" ON
"CAGES_Mississippi_SpeciesImage"."Species_Code" =
"CAGES_Mississippi_LengthsImage"."Species_Code"
LEFT JOIN "CAGES_Mississippi_SamplesImage" ON
"CAGES_Mississippi_SamplesImage"."Sample_Code" =
"CAGES_Mississippi_LengthsImage"."Sample_Code"
LEFT JOIN "CAGES_Mississippi_StationsImage" ON
"CAGES_Mississippi_StationsImage"."Station_Code" =
"CAGES_Mississippi_SamplesImage"."Station_Code"
LEFT JOIN "CAGES_Mississippi_HydrologicalImage" ON
"CAGES_Mississippi_HydrologicalImage"."Sample_Code" =
"CAGES_Mississippi_LengthsImage"."Sample_Code"
LEFT Join "CAGES_Mississippi_TrawlsImage" ON
"CAGES_Mississippi_TrawlsImage"."Sample_Code" =
"CAGES_Mississippi_LengthsImage"."Sample_Code" AND
"CAGES_Mississippi_TrawlsImage"."Species_Code" =
"CAGES_Mississippi_LengthsImage"."Species_Code"
LEFT Join "CAGES_Mississippi_CPUEImage" ON
"CAGES_Mississippi_CPUEImage"."Sample_Code" =
"CAGES_Mississippi_SamplesImage"."Sample_Code" AND
"CAGES_Mississippi_CPUEImage"."Species_Code" =
"CAGES_Mississippi_SpeciesImage"."Species_Code"
LEFT JOIN "CdtCstReferenceTable" ON
"CdtCstReferenceTable"."YYYY" = "CAGES_Mississippi_SamplesImage"."YYYY"
AND
"CdtCstReferenceTable"."MM" = "CAGES_Mississippi_SamplesImage"."MM"
AND
"CdtCstReferenceTable"."DD" = "CAGES_Mississippi_SamplesImage"."DD"
;
-----

Create Table for IOOS Standard Data
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Mixed Case Version
^^^^^^^^^^^^^^^^^^

-----
CREATE TABLE "CAGES_Mississippi_Lengths_CPUE_IOOS_Standard_20130827" (
"modified" character varying,
"verbatimModified" character varying,
"datasetID" character varying,
"datasetName" character varying,
"higherInstitutionCode" character varying,
"institutionCode" character varying,
"ownerInstitutionCode" character varying,
"collectionCode" character varying,
"catalogNumber" character varying,
"eventDate" character varying,
"eventDateTimeZone" character varying,
"eventDateRemarks" character varying,
"verbatimEventDate" character varying,
"time" character varying,
"timeUncertainty" integer,
"latitude" character varying,
"longitude" character varying,
"footprintWKT" character varying,
"coordinateUncertaintyInMeters" decimal,
"verbatimCoordinates" character varying,
"verbatimCoordinateSystem" character varying,
"verbatimSRS" character varying,
"geodeticDatum" character varying,
"georeferencedBy" character varying,
"georeferenceProtocol" character varying,
"depth" decimal,
"basisOfRecord" character varying,
"recordedBy" character varying,
"vernacularName" character varying,
"scientificName" character varying,
"taxonRank" character varying,
"aphiaID" character varying,
"tsn" character varying,
"genus" character varying,
"subgenus" character varying,
"species" character varying,
"infraspecificEpithet" character varying,
"scientificNameAuthorship" character varying,
"identifiedBy" character varying,
"identificationDate" character varying,
"identificationQualifier" character varying,
"identificationRemarks" character varying,
"individualCount" integer,
"weightInKg" character varying,
"sex" character varying,
"lifeStage" character varying,
"dynamicProperties" character varying,
"observedIndividualLengthInCm" character varying,
"observedMeanLengthInCm" character varying,
"observedMaxLengthInCm" character varying,
"observedMinLengthInCm" character varying,
"lengthType" character varying,
"occurrenceRemarks" text,
"surveyEventID" character varying,
"sampleID" character varying,
"subsampleID" character varying,
"samplingProtocol" character varying,
"samplingEffort" character varying,
"samplingConditions" character varying,
"totalInSample" character varying,
"sampleShape" character varying,
"sampleLengthInMeters" character varying,
"sampleWidthInMeters" character varying,
"sampleHeightInMeters" character varying,
"sampleRadiusInMeters" character varying,
"sampleAreaInSquareMeters" character varying,
"sampleVolumeInCubicMeters" character varying,
"visibilityInMeters" character varying,
"visibilityType" character varying,
"waterTemperatureInCelsius" character varying,
"habitat" character varying,
"bottomType" character varying,
"quantificationType" character varying,
"quantificationVocabulary" character varying,
"quantificationName" character varying,
"quantificationValue" character varying,
"quantificationUnit" character varying,
"quantificationUncertainty" character varying,
"quantificationDeterminedDate" character varying,
"quantificationDeterminedBy" character varying,
"quantificationMethod" character varying,
"waterBody" character varying,
"islandGroup" character varying,
"island" character varying,
"locality" character varying,
"country" character varying,
"stateProvince" character varying,
"county" character varying,
"municipality" character varying,
"kingdom" character varying,
"phylum" character varying,
"class" character varying,
"order" character varying,
"family" character varying
)
WITH OIDS;
-----

Lower Case Version
^^^^^^^^^^^^^^^^^^

Create a lowercase version:

-----
CREATE TABLE "cages_mississippi_lengths_cpue_ioos_standard_lc" (
"modified" character varying,
"verbatimmodified" character varying,
"datasetid" character varying,
"datasetname" character varying,
"higherinstitutioncode" character varying,
"institutioncode" character varying,
"ownerinstitutioncode" character varying,
"collectioncode" character varying,
"catalognumber" character varying,
"eventdate" character varying,
"eventdatetimezone" character varying,
"eventdateremarks" character varying,
"verbatimeventdate" character varying,
"time" character varying,
"timeuncertainty" integer,
"latitude" character varying,
"longitude" character varying,
"footprintwkt" character varying,
"coordinateuncertaintyinmeters" decimal,
"verbatimcoordinates" character varying,
"verbatimcoordinatesystem" character varying,
"verbatimsrs" character varying,
"geodeticdatum" character varying,
"georeferencedby" character varying,
"georeferenceprotocol" character varying,
"depth" decimal,
"basisofrecord" character varying,
"recordedby" character varying,
"vernacularname" character varying,
"scientificname" character varying,
"taxonrank" character varying,
"aphiaid" character varying,
"tsn" character varying,
"genus" character varying,
"subgenus" character varying,
"species" character varying,
"infraspecificepithet" character varying,
"scientificnameauthorship" character varying,
"identifiedby" character varying,
"identificationdate" character varying,
"identificationqualifier" character varying,
"identificationremarks" character varying,
"individualcount" integer,
"weightinkg" character varying,
"sex" character varying,
"lifestage" character varying,
"dynamicproperties" character varying,
"observedindividuallengthincm" character varying,
"observedmeanlengthincm" character varying,
"observedmaxlengthincm" character varying,
"observedminlengthincm" character varying,
"lengthtype" character varying,
"occurrenceremarks" text,
"surveyeventid" character varying,
"sampleid" character varying,
"subsampleid" character varying,
"samplingprotocol" character varying,
"samplingeffort" character varying,
"samplingconditions" character varying,
"totalinsample" character varying,
"sampleshape" character varying,
"samplelengthinmeters" character varying,
"samplewidthinmeters" character varying,
"sampleheightinmeters" character varying,
"sampleradiusinmeters" character varying,
"sampleareainsquaremeters" character varying,
"samplevolumeincubicmeters" character varying,
"visibilityinmeters" character varying,
"visibilitytype" character varying,
"watertemperatureincelsius" character varying,
"habitat" character varying,
"bottomtype" character varying,
"quantificationtype" character varying,
"quantificationvocabulary" character varying,
"quantificationname" character varying,
"quantificationvalue" character varying,
"quantificationunit" character varying,
"quantificationuncertainty" character varying,
"quantificationdetermineddate" character varying,
"quantificationdeterminedby" character varying,
"quantificationmethod" character varying,
"waterbody" character varying,
"islandgroup" character varying,
"island" character varying,
"locality" character varying,
"country" character varying,
"stateprovince" character varying,
"county" character varying,
"municipality" character varying,
"kingdom" character varying,
"phylum" character varying,
"class" character varying,
"order" character varying,
"family" character varying
)
WITH OIDS;
-----

CAGES Mississippi Insert Into IOOS Standard
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Mixed Case Version
^^^^^^^^^^^^^^^^^^

-----
INSERT INTO "CAGES_Mississippi_Lengths_CPUE_IOOS_Standard_20130827"
SELECT
--modified
'2012-08-01',
--verbatimModified
NULL,
--datasetID
'CAGES_Mississippi_Lengths_IOOS_Standard',
--datasetName
'CAGES Mississippi Length Data with CPUE',
--higherInstitutionCode
'DOC;NOAA;NMFS;SEFSC;Fishery Ecology Branch',
--institutionCode
'NMFS',
--ownerInstitutionCode
'NMFS',
--collectionCode
'CAGES Mississippi',
--catalogNumber
NULL,
--eventDate
"YYYY" || '-' || lpad("MM",2,'0') || '-' || lpad("DD",2,'0'),
--eventDateTimeZone
"Time_Zone",
--eventDateRemarks
NULL,
--verbatimEventDate
NULL,
--time
replace((("YYYY" || '-' || lpad("MM",2,'0') || '-' || lpad("DD",2,'0') || ' ' || '12:00:00')::timestamp + "Time_Zone"::interval)::character varying,' ','T') || 'Z',
--timeUncertainty
43200,
--latitude
"Latitude",
--longitude
"Longitude",
--footprintWKT
NULL,
--coordinateUncertaintyInMeters
370,
--verbatimCoordinates
NULL,
--verbatimCoordinateSystem
NULL,
--verbatimSRS
NULL,
--geodeticDatum
'EPSG:4326 WGS84',
--georeferencedBy
'MDMR/GCRL',
--georeferenceProtocol
'Coordinate uncertainty is based on starting point plus travel during trawl.',
--depth
rtrim("Depth",'.00')::numeric,
--basisOfRecord
'Human Observation',
--recordedBy
'MDMR/GCRL',
--vernacularName
NULL,
--scientificName
initcap(split_part("Scientific_Name",' ',1)) || ' ' || lower(split_part("Scientific_Name",' ',2)) || ' ' || lower(split_part("Scientific_Name",' ',3)),
--taxonRank
CASE
-- no taxon rank if no sci name
WHEN "Scientific_Name" IS NULL THEN NULL
-- taxon rank = Genus for the following list
WHEN "Species_Code" IN ('3030020200','4010220100','4010240200','4010280100','4010350100','4030400100','6020010100','14030130500','14030150401','14030210100','14030550200','14030580100','14030610101','14040170100','14050100101','18020040200','18020250400','18020330100','29040010100','29040010200','29040030201','29110090201','29130060100','29130061001','29320020400','29330050200','29330050301','29350010600','29350250700','29360370100','30040060100','34020010100','34030010100') THEN 'Genus'
-- taxon rank = Species for the rest
ELSE 'Species'
END,
--aphiaID
NULL,
--tsn
NULL,
--genus
initcap(split_part("Scientific_Name",' ',1)),
--subgenus
NULL,
--species
CASE
WHEN "Species_Code" IN ('3030020200','4010220100','4010240200','4010280100','4010350100','4030400100','6020010100','14030130500','14030150401','14030210100','14030550200','14030580100','14030610101','14040170100','14050100101','18020040200','18020250400','18020330100','29040010100','29040010200','29040030201','29110090201','29130060100','29130061001','29320020400','29330050200','29330050301','29350010600','29350250700','29360370100','30040060100','34020010100','34030010100') THEN NULL
ELSE lower(split_part("Scientific_Name",' ',2))
END,
--infraspecificEpithet
NULL,
--scientificNameAuthorship
NULL,
--identifiedBy
'MDMR/GCRL',
--identificationDate
"YYYY" || '-' || lpad("MM",2,'0') || '-' || lpad("DD",2,'0'),
--identificationQualifier
NULL,
--identificationRemarks
NULL,
--individualCount
1,
--weightInKg
to_char("Weight"::numeric/1000, 'FM999.9999')::numeric,
--sex
NULL,
--lifeStage
NULL,
--dynamicProperties
NULL,
--observedIndividualLengthInCm
to_char("Length"::numeric/10, 'FM999.9')::numeric,
--observedMeanLengthInCm
NULL,
--observedMaxLengthInCm
NULL,
--observedMinLengthInCm
NULL,
--lengthType
CASE
WHEN "Species_Code" IN ('29350140102','29350140103','29350140201','29350170101','29350180102','29350180103','29350190101','29350190201','29350190202','29350190301','29350190901','29350200102','29350200201','29350200205','29350200206','29350200207','29350200208','29350210301','29350210302','29350220201','29350220203','29350220204','29350220205','29350220206','29350230101','29350230201','29350230202','29350230203','29350230302','29350230401','29350230402','29350230403','29350230404','29350250101','29350250201','29350250302','29350250304','29350250402','29350250501','29350250601','29350250700','29350250801','29350270101','29350290101','29350290201','29350320101','29350320102','29350340101','29350340401','29350340501','29350340503','29350340504','29350340505','29350340509','29350340602','29350340801','29350350101','29350350201','29350350301','29350350401','29350350402') THEN 'Carapace Width'
ELSE 'Standard Length'
END,
--occurrenceRemarks
NULL,
--surveyEventID
NULL,
--sampleID
"Sample_Code",
--subsampleID
"Sample_Code" || '-' || "Species_Code",
--samplingProtocol
'Each sample was subsampled by species; total count and weight in subsample were recorded; and individual length and weight were recorded.',
--samplingEffort
'Duration = 10 minutes',
--samplingConditions
NULL,
--totalInSample
'For this species subsample, total count = ' || "Total_Number" || ' and total weight = ' || to_char("Total_Weight"::numeric/1000, 'FM999.999')::numeric || ' kg',
--sampleShape
NULL,
--sampleLengthInMeters
NULL,
--sampleWidthInMeters
NULL,
--sampleHeightInMeters
NULL,
--sampleRadiusInMeters
NULL,
--sampleAreaInSquareMeters
NULL,
--sampleVolumeInCubicMeters
NULL,
--visibilityInMeters
NULL,
--visibilityType
NULL,
--waterTemperatureInCelsius
NULL,
--habitat
NULL,
--bottomType
NULL,
--quantificationType
'P',
--quantificationVocabulary
'verbatim',
--quantificationName
'CPUE',
--quantificationValue
"CPUE",
--quantificationUnit
'hectare^-1',
--quantificationUncertainty
NULL,
--quantificationDeterminedDate
NULL,
--quantificationDeterminedBy
'SEFSC',
--quantificationMethod
'refer to metadata',
--waterBody
"Bay",
--islandGroup
NULL,
--island
NULL,
--locality
"Description",
--country
'USA',
--stateProvince
'Mississippi',
--county
NULL,
--municipality
NULL,
--kingdom
'Animalia',
--phylum
initcap("Phylum"),
--class
initcap("Class"),
--order
initcap("Order"),
--family
initcap("Family")
FROM "CAGES_Mississippi_Lengths_CPUE_Join_20130827"
;
-----

Lower Case Version
^^^^^^^^^^^^^^^^^^

Create a lowercase version:

-----
INSERT INTO "cages_mississippi_lengths_cpue_ioos_standard_lc"
SELECT
--modified
'2012-08-01',
--verbatimmodified
NULL,
--datasetid
'CAGES_Mississippi_Lengths_IOOS_Standard',
--datasetname
'CAGES Mississippi Length Data with CPUE',
--higherinstitutioncode
'DOC;NOAA;NMFS;SEFSC;Fishery Ecology Branch',
--institutioncode
'NMFS',
--ownerinstitutioncode
'NMFS',
--collectioncode
'CAGES Mississippi',
--catalognumber
NULL,
--eventdate
"YYYY" || '-' || lpad("MM",2,'0') || '-' || lpad("DD",2,'0'),
--eventdatetimezone
"Time_Zone",
--eventdateremarks
NULL,
--verbatimeventdate
NULL,
--time
replace((("YYYY" || '-' || lpad("MM",2,'0') || '-' || lpad("DD",2,'0') || ' ' || '12:00:00')::timestamp + "Time_Zone"::interval)::character varying,' ','T') || 'Z',
--timeuncertainty
43200,
--latitude
"Latitude",
--longitude
"Longitude",
--footprintwkt
NULL,
--coordinateuncertaintyinmeters
370,
--verbatimcoordinates
NULL,
--verbatimcoordinatesystem
NULL,
--verbatimsrs
NULL,
--geodeticdatum
'EPSG:4326 WGS84',
--georeferencedby
'MDMR/GCRL',
--georeferenceprotocol
'Coordinate uncertainty is based on starting point plus travel during trawl.',
--depth
rtrim("Depth",'.00')::numeric,
--basisofrecord
'Human Observation',
--recordedby
'MDMR/GCRL',
--vernacularname
NULL,
--scientificname
initcap(split_part("Scientific_Name",' ',1)) || ' ' || lower(split_part("Scientific_Name",' ',2)) || ' ' || lower(split_part("Scientific_Name",' ',3)),
--taxonrank
CASE
-- no taxon rank if no sci name
WHEN "Scientific_Name" IS NULL THEN NULL
-- taxon rank = Genus for the following list
WHEN "Species_Code" IN ('3030020200','4010220100','4010240200','4010280100','4010350100','4030400100','6020010100','14030130500','14030150401','14030210100','14030550200','14030580100','14030610101','14040170100','14050100101','18020040200','18020250400','18020330100','29040010100','29040010200','29040030201','29110090201','29130060100','29130061001','29320020400','29330050200','29330050301','29350010600','29350250700','29360370100','30040060100','34020010100','34030010100') THEN 'Genus'
-- taxon rank = Species for the rest
ELSE 'Species'
END,
--aphiaid
NULL,
--tsn
NULL,
--genus
initcap(split_part("Scientific_Name",' ',1)),
--subgenus
NULL,
--species
CASE
WHEN "Species_Code" IN ('3030020200','4010220100','4010240200','4010280100','4010350100','4030400100','6020010100','14030130500','14030150401','14030210100','14030550200','14030580100','14030610101','14040170100','14050100101','18020040200','18020250400','18020330100','29040010100','29040010200','29040030201','29110090201','29130060100','29130061001','29320020400','29330050200','29330050301','29350010600','29350250700','29360370100','30040060100','34020010100','34030010100') THEN NULL
ELSE lower(split_part("Scientific_Name",' ',2))
END,
--infraspecificepithet
NULL,
--scientificnameauthorship
NULL,
--identifiedby
'MDMR/GCRL',
--identificationdate
"YYYY" || '-' || lpad("MM",2,'0') || '-' || lpad("DD",2,'0'),
--identificationqualifier
NULL,
--identificationremarks
NULL,
--individualcount
1,
--weightinkg
to_char("Weight"::numeric/1000, 'FM999.9999')::numeric,
--sex
NULL,
--lifestage
NULL,
--dynamicproperties
NULL,
--observedindividuallengthincm
to_char("Length"::numeric/10, 'FM999.9')::numeric,
--observedmeanlengthincm
NULL,
--observedmaxlengthincm
NULL,
--observedminlengthincm
NULL,
--lengthtype
CASE
WHEN "Species_Code" IN ('29350140102','29350140103','29350140201','29350170101','29350180102','29350180103','29350190101','29350190201','29350190202','29350190301','29350190901','29350200102','29350200201','29350200205','29350200206','29350200207','29350200208','29350210301','29350210302','29350220201','29350220203','29350220204','29350220205','29350220206','29350230101','29350230201','29350230202','29350230203','29350230302','29350230401','29350230402','29350230403','29350230404','29350250101','29350250201','29350250302','29350250304','29350250402','29350250501','29350250601','29350250700','29350250801','29350270101','29350290101','29350290201','29350320101','29350320102','29350340101','29350340401','29350340501','29350340503','29350340504','29350340505','29350340509','29350340602','29350340801','29350350101','29350350201','29350350301','29350350401','29350350402') THEN 'Carapace Width'
ELSE 'Standard Length'
END,
--occurrenceremarks
NULL,
--surveyeventid
NULL,
--sampleid
"Sample_Code",
--subsampleid
"Sample_Code" || '-' || "Species_Code",
--samplingprotocol
'Each sample was subsampled by species; total count and weight in subsample were recorded; and individual length and weight were recorded.',
--samplingeffort
'Duration = 10 minutes',
--samplingconditions
NULL,
--totalinsample
'For this species subsample, total count = ' || "Total_Number" || ' and total weight = ' || to_char("Total_Weight"::numeric/1000, 'FM999.999')::numeric || ' kg',
--sampleshape
NULL,
--samplelengthinmeters
NULL,
--samplewidthinmeters
NULL,
--sampleheightinmeters
NULL,
--sampleradiusinmeters
NULL,
--sampleareainsquaremeters
NULL,
--samplevolumeincubicmeters
NULL,
--visibilityinmeters
NULL,
--visibilitytype
NULL,
--watertemperatureincelsius
NULL,
--habitat
NULL,
--bottomtype
NULL,
--quantificationtype
'P',
--quantificationvocabulary
'verbatim',
--quantificationname
'CPUE',
--quantificationvalue
"CPUE",
--quantificationunit
'hectare^-1',
--quantificationuncertainty
NULL,
--quantificationdetermineddate
NULL,
--quantificationdeterminedby
'SEFSC',
--quantificationmethod
'refer to metadata',
--waterbody
"Bay",
--islandgroup
NULL,
--island
NULL,
--locality
"Description",
--country
'USA',
--stateprovince
'Mississippi',
--county
NULL,
--municipality
NULL,
--kingdom
'Animalia',
--phylum
initcap("Phylum"),
--class
initcap("Class"),
--order
initcap("Order"),
--family
initcap("Family")
FROM "CAGES_Mississippi_Lengths_CPUE_Join_20130827"
;
-----

Change Time Variables from String to Date Format
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

-----
update "cages_mississippi_lengths_cpue_ioos_standard_lc" set time=to_timestamp(time,'YYYY-MM-DDTHH:MI:SSZ');
update "cages_mississippi_lengths_cpue_ioos_standard_lc" set modified=to_timestamp(modified,'YYYY-MM-DD');
update "cages_mississippi_lengths_cpue_ioos_standard_lc" set "eventdate"=to_timestamp("eventdate",'YYYY-MM-DDTHH:MI');
-----


CAGES Alabama Database
----------------------

How to Get There
~~~~~~~~~~~~~~~~

Become user +postgres+:

-----
su
su - postgres
-----

Obtain database list:

-----
psql -l
                                       List of databases
        Name         |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
---------------------+----------+----------+-------------+-------------+-----------------------
 cages_alabama       | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres         +
                     |          |          |             |             | postgres=CTc/postgres+
                     |          |          |             |             | baum=CTc/postgres
 cages_louisiana     | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 cages_mississippi   | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres         +
                     |          |          |             |             | postgres=CTc/postgres+
                     |          |          |             |             | baum=CTc/postgres
-----

Connect to Alabama database and get list of tables:

-----
psql cages_alabama
\dt
                               List of relations
 Schema |                       Name                        | Type  |  Owner   
--------+---------------------------------------------------+-------+----------
 public | CAGES_Alabama_CPUEImage                           | table | postgres
 public | CAGES_Alabama_LengthsImage                        | table | postgres
 public | CAGES_Alabama_Lengths_CPUE_Join_20130829          | table | postgres
 public | CAGES_Alabama_Lengths_CPUE_Join_20140115          | table | postgres
 public | CAGES_Alabama_Lengths_CPUE_Join_20140203          | table | postgres
 public | CAGES_Alabama_Lengths_CPUE_Join_Check             | table | postgres
 public | CAGES_Alabama_SamplesImage                        | table | postgres
 public | CAGES_Alabama_SpeciesImage                        | table | postgres
 public | CAGES_Alabama_StationsImage                       | table | postgres
 public | CAGES_Alabama_StationsImage_Original              | table | postgres
 public | CAGES_Alabama_TrawlsImage                         | table | postgres
 public | CdtCstReferenceTable                              | table | postgres
 public | cages_alabama_lengths_cpue_ioos_standard_20130829 | table | postgres
 public | cages_alabama_lengths_cpue_ioos_standard_lc       | table | postgres
-----

Initial Creation of Database
~~~~~~~~~~~~~~~~~~~~~~~~~~~~

-----
psql
create database cages_alabama;
-----


Creating the Tables
~~~~~~~~~~~~~~~~~~~

First, go to the database:

-----
psql cages_alabama;
-----

The statements used to create the tables:

-----
CREATE TABLE "CAGES_Alabama_CPUEImage"("Sample_Code" CHAR(32),"Station_Code" CHAR(32),"YYYY" CHAR(32),"MM" CHAR(32),"Species Code" CHAR(32),"CPUE" CHAR(32));

CREATE TABLE "CAGES_Alabama_LengthsImage"("Sample_Code" CHAR(32),"Species_Code" CHAR(32),"Length" CHAR(32));

CREATE TABLE "CAGES_Alabama_SamplesImage"("Sample_Code" CHAR(32),"YYYY" CHAR(32),"MM" CHAR(32),"DD" CHAR(32),"Station_Code" CHAR(32));

CREATE TABLE "CAGES_Alabama_SpeciesImage"("Species_Code" CHAR(32),"Phylum" CHAR(32),"Class" CHAR(32),"Family" CHAR(32),"Scientific_Name" CHAR(64),"Common_Name" CHAR(64));

CREATE TABLE "CAGES_Alabama_StationsImage"("Station_Code" CHAR(32),"Station" CHAR(32),"Description" CHAR(32),"Latitude" CHAR(32),"Longitude" CHAR(32), "Water_Body" CHAR(32));

CREATE TABLE "CAGES_Alabama_TrawlsImage"("Sample_Code" CHAR(32),"Species_Code" CHAR(32),"Measured" CHAR(32),"Total_Number" CHAR(32));

CREATE TABLE "CdtCstReferenceTable"("YYYY" CHAR(32),"MM" CHAR(32),"DD" CHAR(32),"Date8" CHAR(32),"TimeZone" CHAR(43));
-----


Populating the Tables
~~~~~~~~~~~~~~~~~~~~~

*Note*:  Had to remove several +<96>+ metacharacters from +Alabama_Stations.txt+.

-----
\copy "CAGES_Alabama_CPUEImage" from '/home/postgres/Alabama_CPUE.txt'

\copy "CAGES_Alabama_LengthsImage" from '/home/postgres/Alabama_Lengths.txt';

\copy "CAGES_Alabama_SamplesImage" from '/home/postgres/Alabama_Samples.txt';

\copy "CAGES_Alabama_SpeciesImage" from '/home/postgres/Alabama_Species.txt';

\copy "CAGES_Alabama_StationsImage" from '/home/postgres/Alabama_Stations.txt';

\copy "CAGES_Alabama_TrawlsImage" from '/home/postgres/Alabama_Trawls.txt';

\copy "CdtCstReferenceTable" from '/home/postgres/CdtCstReferenceTable_20130829.txt';
-----

[[alabama_join_table]]
Create Alabama Join Table
~~~~~~~~~~~~~~~~~~~~~~~~~

-----
SELECT
"CAGES_Alabama_LengthsImage"."Sample_Code",
"CAGES_Alabama_LengthsImage"."Species_Code",
"CAGES_Alabama_LengthsImage"."Length",
"CAGES_Alabama_SpeciesImage"."Scientific_Name",
"CAGES_Alabama_SpeciesImage"."Common_Name",
"CAGES_Alabama_SpeciesImage"."Phylum",
"CAGES_Alabama_SpeciesImage"."Class",
"CAGES_Alabama_SpeciesImage"."Family",
"CAGES_Alabama_SamplesImage"."Station_Code",
"CAGES_Alabama_SamplesImage"."YYYY",
"CAGES_Alabama_SamplesImage"."MM",
"CAGES_Alabama_SamplesImage"."DD",
"CdtCstReferenceTable"."TimeZone",
"CAGES_Alabama_StationsImage"."Latitude",
"CAGES_Alabama_StationsImage"."Longitude",
"CAGES_Alabama_StationsImage"."Water_Body",
"CAGES_Alabama_StationsImage"."Description",
"CAGES_Alabama_TrawlsImage"."Measured",
"CAGES_Alabama_TrawlsImage"."Total_Number",
"CAGES_Alabama_CPUEImage"."CPUE"
INTO "CAGES_Alabama_Lengths_CPUE_Join_20140203"
FROM
"CAGES_Alabama_LengthsImage"
LEFT JOIN "CAGES_Alabama_SamplesImage" ON
"CAGES_Alabama_SamplesImage"."Sample_Code" =
"CAGES_Alabama_LengthsImage"."Sample_Code"
LEFT JOIN "CAGES_Alabama_StationsImage" ON
"CAGES_Alabama_StationsImage"."Station_Code" =
 "CAGES_Alabama_SamplesImage"."Station_Code"
LEFT JOIN "CAGES_Alabama_SpeciesImage" ON
"CAGES_Alabama_SpeciesImage"."Species_Code" =
 "CAGES_Alabama_LengthsImage"."Species_Code"
LEFT Join "CAGES_Alabama_TrawlsImage" ON
"CAGES_Alabama_TrawlsImage"."Sample_Code" =
"CAGES_Alabama_LengthsImage"."Sample_Code" AND
"CAGES_Alabama_TrawlsImage"."Species_Code" =
"CAGES_Alabama_LengthsImage"."Species_Code"
LEFT Join "CAGES_Alabama_CPUEImage" ON
"CAGES_Alabama_CPUEImage"."Sample_Code" =
"CAGES_Alabama_LengthsImage"."Sample_Code" AND
"CAGES_Alabama_CPUEImage"."Species_Code" =
"CAGES_Alabama_LengthsImage"."Species_Code"
LEFT JOIN "CdtCstReferenceTable" ON
"CdtCstReferenceTable"."YYYY" = "CAGES_Alabama_SamplesImage"."YYYY"
AND
"CdtCstReferenceTable"."MM" = "CAGES_Alabama_SamplesImage"."MM"
AND
"CdtCstReferenceTable"."DD" = "CAGES_Alabama_SamplesImage"."DD"
;
-----

Repair a Bad Date
~~~~~~~~~~~~~~~~~

-----
UPDATE "CAGES_Alabama_Lengths_CPUE_Join_20130829"
SET "DD" = '29'
WHERE "YYYY" = '2004' AND "MM" = '2' AND "DD" = '30'
;
-----

[[create_alabama_ioos_table]]
Create IOOS Standard Table for Alabama
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Mixed Case Version
^^^^^^^^^^^^^^^^^^

-----
CREATE TABLE "CAGES_Alabama_Lengths_CPUE_IOOS_Standard_20130829" (
"modified" character varying,
"verbatimModified" character varying,
"datasetID" character varying,
"datasetName" character varying,
"higherInstitutionCode" character varying,
"institutionCode" character varying,
"ownerInstitutionCode" character varying,
"collectionCode" character varying,
"catalogNumber" character varying,
"eventDate" character varying,
"eventDateTimeZone" character varying,
"eventDateRemarks" character varying,
"verbatimEventDate" character varying,
"time" character varying,
"timeUncertainty" integer,
"latitude" character varying,
"longitude" character varying,
"footprintWKT" character varying,
"coordinateUncertaintyInMeters" decimal,
"verbatimCoordinates" character varying,
"verbatimCoordinateSystem" character varying,
"verbatimSRS" character varying,
"geodeticDatum" character varying,
"georeferencedBy" character varying,
"georeferenceProtocol" character varying,
"depth" decimal,
"basisOfRecord" character varying,
"recordedBy" character varying,
"vernacularName" character varying,
"scientificName" character varying,
"taxonRank" character varying,
"aphiaID" character varying,
"tsn" character varying,
"genus" character varying,
"subgenus" character varying,
"species" character varying,
"infraspecificEpithet" character varying,
"scientificNameAuthorship" character varying,
"identifiedBy" character varying,
"identificationDate" character varying,
"identificationQualifier" character varying,
"identificationRemarks" character varying,
"individualCount" integer,
"weightInKg" character varying,
"sex" character varying,
"lifeStage" character varying,
"dynamicProperties" character varying,
"observedIndividualLengthInCm" character varying,
"observedMeanLengthInCm" character varying,
"observedMaxLengthInCm" character varying,
"observedMinLengthInCm" character varying,
"lengthType" character varying,
"occurrenceRemarks" text,
"surveyEventID" character varying,
"sampleID" character varying,
"subsampleID" character varying,
"samplingProtocol" character varying,
"samplingEffort" character varying,
"samplingConditions" character varying,
"totalInSample" character varying,
"sampleShape" character varying,
"sampleLengthInMeters" character varying,
"sampleWidthInMeters" character varying,
"sampleHeightInMeters" character varying,
"sampleRadiusInMeters" character varying,
"sampleAreaInSquareMeters" character varying,
"sampleVolumeInCubicMeters" character varying,
"visibilityInMeters" character varying,
"visibilityType" character varying,
"waterTemperatureInCelsius" character varying,
"habitat" character varying,
"bottomType" character varying,
"quantificationType" character varying,
"quantificationVocabulary" character varying,
"quantificationName" character varying,
"quantificationValue" character varying,
"quantificationUnit" character varying,
"quantificationUncertainty" character varying,
"quantificationDeterminedDate" character varying,
"quantificationDeterminedBy" character varying,
"quantificationMethod" character varying,
"waterBody" character varying,
"islandGroup" character varying,
"island" character varying,
"locality" character varying,
"country" character varying,
"stateProvince" character varying,
"county" character varying,
"municipality" character varying,
"kingdom" character varying,
"phylum" character varying,
"class" character varying,
"order" character varying,
"family" character varying
)
WITH OIDS;
-----

Lower Case Version
^^^^^^^^^^^^^^^^^^

-----
CREATE TABLE "cages_alabama_lengths_cpue_ioos_standard_lc" (
"modified" character varying,
"verbatimmodified" character varying,
"datasetid" character varying,
"datasetname" character varying,
"higherinstitutioncode" character varying,
"institutioncode" character varying,
"ownerinstitutioncode" character varying,
"collectioncode" character varying,
"catalognumber" character varying,
"eventdate" character varying,
"eventdatetimezone" character varying,
"eventdateremarks" character varying,
"verbatimeventdate" character varying,
"time" character varying,
"timeuncertainty" integer,
"latitude" character varying,
"longitude" character varying,
"footprintwkt" character varying,
"coordinateuncertaintyinmeters" decimal,
"verbatimcoordinates" character varying,
"verbatimcoordinatesystem" character varying,
"verbatimsrs" character varying,
"geodeticdatum" character varying,
"georeferencedby" character varying,
"georeferenceprotocol" character varying,
"depth" decimal,
"basisofrecord" character varying,
"recordedby" character varying,
"vernacularname" character varying,
"scientificname" character varying,
"taxonrank" character varying,
"aphiaid" character varying,
"tsn" character varying,
"genus" character varying,
"subgenus" character varying,
"species" character varying,
"infraspecificepithet" character varying,
"scientificnameauthorship" character varying,
"identifiedby" character varying,
"identificationdate" character varying,
"identificationqualifier" character varying,
"identificationremarks" character varying,
"individualcount" integer,
"weightinkg" character varying,
"sex" character varying,
"lifestage" character varying,
"dynamicproperties" character varying,
"observedindividuallengthincm" character varying,
"observedmeanlengthincm" character varying,
"observedmaxlengthincm" character varying,
"observedminlengthincm" character varying,
"lengthtype" character varying,
"occurrenceremarks" text,
"surveyeventid" character varying,
"sampleid" character varying,
"subsampleid" character varying,
"samplingprotocol" character varying,
"samplingeffort" character varying,
"samplingconditions" character varying,
"totalinsample" character varying,
"sampleshape" character varying,
"samplelengthinmeters" character varying,
"samplewidthinmeters" character varying,
"sampleheightinmeters" character varying,
"sampleradiusinmeters" character varying,
"sampleareainsquaremeters" character varying,
"samplevolumeincubicmeters" character varying,
"visibilityinmeters" character varying,
"visibilitytype" character varying,
"watertemperatureincelsius" character varying,
"habitat" character varying,
"bottomtype" character varying,
"quantificationtype" character varying,
"quantificationvocabulary" character varying,
"quantificationname" character varying,
"quantificationvalue" character varying,
"quantificationunit" character varying,
"quantificationuncertainty" character varying,
"quantificationdetermineddate" character varying,
"quantificationdeterminedby" character varying,
"quantificationmethod" character varying,
"waterbody" character varying,
"islandgroup" character varying,
"island" character varying,
"locality" character varying,
"country" character varying,
"stateprovince" character varying,
"county" character varying,
"municipality" character varying,
"kingdom" character varying,
"phylum" character varying,
"class" character varying,
"order" character varying,
"family" character varying
)
WITH OIDS;
-----

[[populate_alabama_ioos_table]]
Populate the IOOS Standard Table for Alabama
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Mixed Case Version
^^^^^^^^^^^^^^^^^^

-----
INSERT INTO "CAGES_Alabama_Lengths_CPUE_IOOS_Standard_20130829"
SELECT
--modified
'2013-08-29',
--verbatimModified
NULL,
--datasetID
'CAGES_Alabama_Lengths_IOOS_Standard',
--datasetName
'CAGES Alabama Length Data with CPUE',
--higherInstitutionCode
'DOC;NOAA;NMFS;SEFSC;Fishery Ecology Branch',
--institutionCode
'NMFS',
--ownerInstitutionCode
'NMFS',
--collectionCode
'CAGES Alabama',
--catalogNumber
NULL,
--eventDate
"YYYY" || '-' || lpad("MM",2,'0') || '-' || lpad("DD",2,'0'),
--eventDateTimeZone
"TimeZone",
--eventDateRemarks
NULL,
--verbatimEventDate
NULL,
--time
replace((("YYYY" || '-' || lpad("MM",2,'0') || '-' || lpad("DD",2,'0') || ' ' || '12:00:00')::timestamp + "TimeZone"::interval)::character varying,' ','T') || 'Z',
--timeUncertainty
43200,
--latitude
"Latitude",
--longitude
"Longitude",
--footprintWKT
NULL,
--coordinateUncertaintyInMeters
370,
--verbatimCoordinates
NULL,
--verbatimCoordinateSystem
NULL,
--verbatimSRS
NULL,
--geodeticDatum
'EPSG:4326 WGS84',
--georeferencedBy
'ADCNR/GCRL',
--georeferenceProtocol
'Coordinate uncertainty is based on starting point plus travel during trawl.',
--depth
NULL,
--basisOfRecord
'Human Observation',
--recordedBy
'ADCNR/GCRL',
--vernacularName
initcap(split_part("Common_Name",' ',1)) || ' ' || lower(split_part("Common_Name",' ',2)) || ' ' || lower(split_part("Common_Name",' ',3)),
--scientificName
initcap(split_part("Scientific_Name",' ',1)) || ' ' || lower(split_part("Scientific_Name",' ',2)),
--taxonRank
CASE
-- no taxon rank for NULL sci name
WHEN "Scientific_Name" IS NULL THEN NULL
-- no taxon rank for any in this list of exceptions
WHEN "Scientific_Name" IN ('882002010000','883544010000') THEN 'Genus'
ELSE 'Species'
END,
--aphiaID
NULL,
--tsn
NULL,
--genus
initcap(split_part("Scientific_Name",' ',1)),
--subgenus
NULL,
--species
CASE
WHEN "Species_Code" IN ('882002010000','883544010000') THEN NULL
ELSE lower(split_part("Scientific_Name",' ',2))
END,
--infraspecificEpithet
NULL,
--scientificNameAuthorship
NULL,
--identifiedBy
'ADCNR/GCRL',
--identificationDate
"YYYY" || '-' || lpad("MM",2,'0') || '-' || lpad("DD",2,'0'),
--identificationQualifier
NULL,
--identificationRemarks
NULL,
--individualCount
1,
--weightInKg
NULL,
--sex
NULL,
--lifeStage
NULL,
--dynamicProperties
NULL,
--observedIndividualLengthInCm
to_char("Length"::numeric/10, 'FM999.9')::numeric,
--observedMeanLengthInCm
NULL,
--observedMaxLengthInCm
NULL,
--observedMinLengthInCm
NULL,
--lengthType
CASE
WHEN "Species_Code" IN ('618306023000','618306023200','618312010300','618314010100','618316020100','618603010200','618603010400','618901030100','618901030200','618901050100','618901060100','618902050100','618902090100','618902130100','618902130200','618902420200') THEN 'Carapace Width'
ELSE 'Standard Length'
END,
--OccurrenceRemarks
NULL,
--surveyEventID
NULL,
--sampleID
"Sample_Code",
--subsampleID
"Sample_Code" || '-' || "Species_Code",
--samplingProtocol
'Species subsampled and individual lengths recorded',
--samplingEffort
'Duration = 10 minutes',
--samplingConditions
NULL,
--totalInSample
'For this species subsample, total count = ' || "Total_Number",
--sampleShape
'R',
--sampleLengthInMeters
NULL,
--sampleWidthInMeters
NULL,
--sampleHeightInMeters
NULL,
--sampleRadiusInMeters
NULL,
--sampleAreaInSquareMeters
NULL,
--sampleVolumeInCubicMeters
NULL,
--visibilityInMeters
NULL,
--visibilityType
NULL,
--waterTemperatureInCelsius
NULL,
--habitat
NULL,
--bottomType
NULL,
--quantificationType
'P',
--quantificationVocabulary
'verbatim',
--quantificationName
'CPUE',
--quantificationValue
"CPUE",
--quantificationUnit
'hectare^-1',
--quantificationUncertainty
NULL,
--quantificationDeterminedDate
NULL,
--quantificationDeterminedBy
NULL,
--quantificationMethod
'refer to metadata',
--waterBody
"Water_Body",
--islandGroup
NULL,
--island
NULL,
--locality
"Description",
--country
'USA',
--stateProvince
'Alabama',
--county
NULL,
--municipality
NULL,
--kingdom
'Animalia',
--phylum
initcap("Phylum"),
--class
initcap("Class"),
--order
NULL,
--family
initcap("Family")
FROM "CAGES_Alabama_Lengths_CPUE_Join_20130829"
;
-----

Lower Case Version
^^^^^^^^^^^^^^^^^^

-----
INSERT INTO "cages_alabama_lengths_cpue_ioos_standard_lc"
SELECT
--modified
'2013-08-29',
--verbatimmodified
NULL,
--datasetid
'CAGES_Alabama_Lengths_IOOS_Standard',
--datasetname
'CAGES Alabama Length Data with CPUE',
--higherinstitutioncode
'DOC;NOAA;NMFS;SEFSC;Fishery Ecology Branch',
--institutioncode
'NMFS',
--ownerinstitutioncode
'NMFS',
--collectioncode
'CAGES Alabama',
--catalognumber
NULL,
--eventdate
"YYYY" || '-' || lpad("MM",2,'0') || '-' || lpad("DD",2,'0'),
--eventdatetimezone
"TimeZone",
--eventdateremarks
NULL,
--verbatimeventdate
NULL,
--time
replace((("YYYY" || '-' || lpad("MM",2,'0') || '-' || lpad("DD",2,'0') || ' '
|| '12:00:00')::timestamp + "TimeZone"::interval)::character varying,' ','T')
|| 'Z',
--timeuncertainty
43200,
--latitude
"Latitude",
--longitude
"Longitude",
--footprintwkt
NULL,
--coordinateuncertaintyinmeters
370,
--verbatimcoordinates
NULL,
--verbatimcoordinatesystem
NULL,
--verbatimsrs
NULL,
--geodeticdatum
'EPSG:4326 WGS84',
--georeferencedby
'ADCNR/GCRL',
--georeferenceprotocol
'Coordinate uncertainty is based on starting point plus travel during trawl.',
--depth
NULL,
--basisofrecord
'Human Observation',
--recordedby
'ADCNR/GCRL',
--vernacularname
initcap(split_part("Common_Name",' ',1)) || ' ' ||
lower(split_part("Common_Name",' ',2)) || ' ' ||
lower(split_part("Common_Name",' ',3)),
--scientificname
initcap(split_part("Scientific_Name",' ',1)) || ' ' ||
lower(split_part("Scientific_Name",' ',2)),
--taxonrank
CASE
-- no taxon rank for NULL sci name
WHEN "Scientific_Name" IS NULL THEN NULL
-- no taxon rank for any in this list of exceptions
WHEN "Scientific_Name" IN ('882002010000','883544010000') THEN 'Genus'
ELSE 'Species'
END,
--aphiaid
NULL,
--tsn
NULL,
--genus
initcap(split_part("Scientific_Name",' ',1)),
--subgenus
NULL,
--species
CASE
WHEN "Species_Code" IN ('882002010000','883544010000') THEN NULL
ELSE lower(split_part("Scientific_Name",' ',2))
END,
--infraspecificepithet
NULL,
--scientificnameauthorship
NULL,
--identifiedby
'ADCNR/GCRL',
--identificationdate
"YYYY" || '-' || lpad("MM",2,'0') || '-' || lpad("DD",2,'0'),
--identificationqualifier
NULL,
--identificationremarks
NULL,
--individualcount
1,
--weightinkg
NULL,
--sex
NULL,
--lifestage
NULL,
--dynamicproperties
NULL,
--observedindividuallengthincm
to_char("Length"::numeric/10, 'FM999.9')::numeric,
--observedmeanlengthincm
NULL,
--observedmaxlengthincm
NULL,
--observedminlengthincm
NULL,
--lengthtype
CASE
WHEN "Species_Code" IN
('618306023000','618306023200','618312010300','618314010100','618316020100','618603010200','618603010400','618901030100','618901030200','618901050100','618901060100','618902050100','618902090100','618902130100','618902130200','618902420200')
THEN 'Carapace Width'
ELSE 'Standard Length'
END,
--occurrenceremarks
NULL,
--surveyeventid
NULL,
--sampleid
"Sample_Code",
--subsampleid
"Sample_Code" || '-' || "Species_Code",
--samplingprotocol
'Species subsampled and individual lengths recorded',
--samplingeffort
'Duration = 10 minutes',
--samplingconditions
NULL,
--totalinsample
'For this species subsample, total count = ' || "Total_Number",
--sampleshape
'R',
--samplelengthinmeters
NULL,
--samplewidthinmeters
NULL,
--sampleheightinmeters
NULL,
--sampleradiusinmeters
NULL,
--sampleareainsquaremeters
NULL,
--samplevolumeincubicmeters
NULL,
--visibilityinmeters
NULL,
--visibilitytype
NULL,
--watertemperatureincelsius
NULL,
--habitat
NULL,
--bottomtype
NULL,
--quantificationtype
'P',
--quantificationvocabulary
'verbatim',
--quantificationname
'CPUE',
--quantificationvalue
"CPUE",
--quantificationunit
'hectare^-1',
--quantificationuncertainty
NULL,
--quantificationdeterminedDate
NULL,
--quantificationdeterminedBy
NULL,
--quantificationmethod
'refer to metadata',
--waterbody
"Water_Body",
--islandgroup
NULL,
--island
NULL,
--locality
"Description",
--country
'USA',
--stateprovince
'Alabama',
--county
NULL,
--municipality
NULL,
--kingdom
'Animalia',
--phylum
initcap("Phylum"),
--class
initcap("Class"),
--order
NULL,
--family
initcap("Family")
FROM "CAGES_Alabama_Lengths_CPUE_Join_20140203"
;
-----

Change Time Variables from String to Date Format
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

-----
update "cages_alabama_lengths_cpue_ioos_standard_lc" set time=to_timestamp(time,'YYYY-MM-DDTHH:MI:SSZ');
update "cages_alabama_lengths_cpue_ioos_standard_lc" set modified=to_timestamp(modified,'YYYY-MM-DD');
update "cages_alabama_lengths_cpue_ioos_standard_lc" set "eventdate"=to_timestamp("eventdate",'YYYY-MM-DDTHH:MI');
-----


Problems
~~~~~~~~

ERDDAP choked on this with the error:

-----
datasets.xml error on line #4044
While trying to load datasetID=CAGES_Alabama_Lengths_CPUE_IOOS_Standard_20130829 (after 845 ms)
java.lang.RuntimeException: datasets.xml error on or before line #4044: There was a (temporary?) problem.  Wait a minute, then try again.  (In a browser, click the Reload button.)
(ERROR from data source: java.lang.Throwable: ERROR from data source: org.postgresql.util.PSQLException: Bad value for type float : )
(Cause: java.lang.Throwable: ERROR from data source: org.postgresql.util.PSQLException: Bad value for type float : )
 at gov.noaa.pfel.erddap.dataset.EDD.fromXml(EDD.java:371)
 at gov.noaa.pfel.erddap.LoadDatasets.run(LoadDatasets.java:300)
Caused by: gov.noaa.pfel.erddap.dataset.WaitThenTryAgainException: There was a (temporary?) problem.  Wait a minute, then try again.  (In a browser, click the Reload button.)
(ERROR from data source: java.lang.Throwable: ERROR from data source: org.postgresql.util.PSQLException: Bad value for type float : )
(Cause: java.lang.Throwable: ERROR from data source: org.postgresql.util.PSQLException: Bad value for type float : )
 at gov.noaa.pfel.erddap.dataset.EDDTableFromDatabase.getDataForDapQuery(EDDTableFromDatabase.java:826)
 at gov.noaa.pfel.erddap.dataset.EDDTable.subsetVariablesDataTable(EDDTable.java:9935)
 at gov.noaa.pfel.erddap.dataset.EDDTable.distinctSubsetVariablesDataTable(EDDTable.java:9999)
 at gov.noaa.pfel.erddap.dataset.EDDTable.ensureValid(EDDTable.java:531)
 at gov.noaa.pfel.erddap.dataset.EDDTableFromDatabase.<init>(EDDTableFromDatabase.java:527)
 at gov.noaa.pfel.erddap.dataset.EDDTableFromDatabase.lowFromXml(EDDTableFromDatabase.java:197)
 at gov.noaa.pfel.erddap.dataset.EDDTableFromDatabase.fromXml(EDDTableFromDatabase.java:85)
 at gov.noaa.pfel.erddap.dataset.EDD.fromXml(EDD.java:352)
-----

The only variables of type +float+ are the +latitude+ and +longitude+, so we check them.

Upon checking the table +cages_alabama_lengths_cpue_ioos_standard_lc+ via:

-----
select latitude from "cages_alabama_lengths_cpue_ioos_standard_lc";
-----

and holding the enter key down while zillions of values flew by, I noticed some blanks far down
the list.  So we go back one and check +CAGES_Alabama_Lengths_CPUE_Join_20130829+ via:

-----
select "Latitude" from "CAGES_Alabama_Lengths_CPUE_Join_20130829";
-----

and also find blanks instead of values therein, so the problem is here and just
transferred down the line.

So we recreate the original xref:alabama_join_table[Alabama join table], substituting
+CAGES_Alabama_Lengths_CPUE_Join_Check+ for +CAGES_Alabama_Lengths_CPUE_Join_20130829+,
and run the command:

-----
select "Latitude" from "CAGES_Alabama_Lengths_CPUE_Join_Check";
-----

to see if the problem is persistent, i.e. we see blanks again.
They persist, so we check where they came from.

They came from the +CAGES_Alabama_StationsImage+ table, and if we check in
there we discover that the lines with +Station_Code+s 114, 115 and 116
do not have lon/lat values.

First, we duplicate the table via:

-----
create table "CAGES_Alabama_StationsImage_Mod" as table "CAGES_Alabama_StationsImage";
-----

Then, we delete those three rows:

-----
DELETE FROM "CAGES_Alabama_StationsImage_Mod" WHERE "Station" = '766';
DELETE FROM "CAGES_Alabama_StationsImage_Mod" WHERE "Station" = '738';
DELETE FROM "CAGES_Alabama_StationsImage_Mod" WHERE "Station" = '135';
-----

Then we start over with:

* creating a new xref:alabama_join_table[join table] called
+CAGES_Alabama_Lengths_CPUE_Join_20140203+

* fixing the bad date via:
-----
UPDATE "CAGES_Alabama_Lengths_CPUE_Join_20140203"
SET "DD" = '29'
WHERE "YYYY" = '2004' AND "MM" = '2' AND "DD" = '30'
;
-----

* dropping and then xref:create_alabama_ioos_table[creating] another
IOOS standard data table for Alabama
called +cages_alabama_lengths_cpue_ioos_standard_lc+
----
drop table "cages_alabama_lengths_cpue_ioos_standard_lc";
CREATE TABLE "cages_alabama_lengths_cpue_ioos_standard_lc" (
"modified" character varying,
"verbatimmodified" character varying,
...
"order" character varying,
"family" character varying
)
WITH OIDS;
----

* xref:populate_alabama_ioos_table[populating] the Alabama IOOS
standard table using the update/modified join table
+CAGES_Alabama_Lengths_CPUE_Join_20140203+

-----
INSERT INTO "cages_alabama_lengths_cpue_ioos_standard_lc"
SELECT
--modified
'2013-08-29',
--verbatimmodified
NULL,
...
--order
NULL,
--family
initcap("Family")
FROM "CAGES_Alabama_Lengths_CPUE_Join_20140203"
;
-----

Grant privileges to user baum:

-----
cages_alabama=# grant all privileges on database "cages_alabama" to baum;
-----

since the +connectionProperty+ attributes in the ERDDAP configuration
file +datasets.xml+ specify accessing with username +baum+.

Moving from gcoos1 to gcoos2
----------------------------

Copy the ERDDAP Configuration Files
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Here's the list:

* Tar the contents of +/opt/tomcat7/content+ on gcoos1 via:

-----
cd /opt/tomcat7/content
tar cvf erddap_content_03_26_14.tar
-----

* Copy this to gcoos2 and untar it in
+/usr/local/apache-tomcat-7.0.25/content+

-----
cd /usr/local/apache-tomcat-7.0.25
mkdir content
tar xvf erddap_content_03_26_14.tar
-----

Copy the ERDDAP Software
~~~~~~~~~~~~~~~~~~~~~~~~

On gcoos1:

-----
cd /opt/tomcat7/webapps
scp erddap.war baum@gcoos2.tamu.edu:/home/baum
-----

On gcoos2:

-----
cd /usr/local/apache-tomcat-7.0.25/webapps
cp /home/baum/erddap.war ./
-----

Edit the setup.xml File
~~~~~~~~~~~~~~~~~~~~~~~

Here's the list:

* Change +gcoos1+ to +gcoos2+
* Do it again.

Debugging the New ERDDAP 1.62 Version (July 2015)
-------------------------------------------------

Checking the Existence and Validity of the MySQL Database
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Log in to the MySQL database on +gcoos1.tamu.edu+:

-----
mysql -u root -p
-----

Show the available databases:

-----
show databases;
-----

to obtain:

-----
+--------------------+
| Database           |
+--------------------+
| information_schema | 
| CAGES              | 
| fred               | 
| mysql              | 
| performance_schema | 
| piwikDB            | 
| test               | 
+--------------------+
-----

Select the +CAGES+ database:

-----
use CAGES;
-----

and list the tables in it:

-----
show tables;
-----

to obtain:

-----
+------------------------------------------------+                                                         
| Tables_in_CAGES                                |                                                         
+------------------------------------------------+                                                         
| Alabama_CPUE                                   |                                                         
| Alabama_Gear                                   |                                                         
| Alabama_Hydrological                           |                                                         
| Alabama_Lengths                                |                                                         
| Alabama_Samples                                |                                                         
| Alabama_Species                                |                                                         
| Alabama_Stations                               |                                                         
| Alabama_Trawls                                 |                                                         
| CAGES_Texas_IOOS_Biology                       |                                                         
| CAGES_Texas_IOOS_Biology2                      |                                                         
| CAGES_Texas_IOOS_Biology_10                    |                                                         
| CAGES_Texas_Join1_Image                        |                                                         
| CAGES_Texas_Join1_Image_10                     |                                                         
| CAGES_Texas_Join1_Image_SubBay                 |                                                         
| CAGES_Texas_Join1_Image_Test                   |                                                         
| CAGES_Texas_Join2_Image                        |                                                         
| CAGES_Texas_Join3_Image                        |                                                         
| CAGES_Texas_Join_Trawls_Lengths_Image          |                                                         
| CAGES_Texas_Trawls_Lengths_IOOS_Standard       |                                                         
| CAGES_Texas_Trawls_Lengths_IOOS_Standard_SNAFU |                                                         
| Florida_Bottom_Type                            |                                                         
| Florida_Bottom_Vegetation                      |                                                         
| Florida_Bycatch                                |                                                         
| Florida_CPUE                                   |                                                         
| Florida_Habitat                                |                                                         
| Florida_Hydrological                           |                                                         
| Florida_Lengths                                |                                                         
| Florida_Physical                               |                                                         
| Florida_Shore_Type                             |                                                         
| Florida_Species                                |                                                         
| Florida_Stations                               | 
| Florida_Trawls                                 | 
| Louisiana_Biological                           | 
| Louisiana_CPUE                                 | 
| Louisiana_Duration                             | 
| Louisiana_Gear                                 | 
| Louisiana_Hydrological                         | 
| Louisiana_Length_Units                         | 
| Louisiana_Lengths                              | 
| Louisiana_Numbering_Methods                    | 
| Louisiana_Observations                         | 
| Louisiana_Physical_Methods                     | 
| Louisiana_Samples                              | 
| Louisiana_Species                              | 
| Louisiana_Stage                                | 
| Louisiana_Stations                             | 
| Louisiana_Weight_Measures                      | 
| Mississippi_CPUE                               | 
| Mississippi_Hydrological                       | 
| Mississippi_Lengths                            | 
| Mississippi_Samples                            | 
| Mississippi_Species                            | 
| Mississippi_Stations                           | 
| Mississippi_Trawls                             | 
| TMPTAB                                         | 
| Texas_Bays                                     | 
| Texas_CPUE                                     | 
| Texas_Hydrological                             | 
| Texas_Lengths                                  | 
| Texas_Samples                                  | 
| Texas_Species                                  | 
| Texas_Stations                                 | 
! Texas_SubBays                                  | 
| Texas_Trawls                                   | 
| example                                        | 
+------------------------------------------------+
-----

Grant privileges on +CAGES+ database to +baum+ at
+terrebonne+ to test ERDDAP 1.62 version installed there.

-----
grant all privileges on CAGES.* to baum@165.91.85.132 identified by 'password';
-----

Using +GenerateDatasetsXml+ on a Test Database
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

This dataset XML generation program is located on +terrebonne+ at:

-----
/opt/tomcat7/webapps/erddap/WEB-INF/GenerateDatasetsXml.sh
-----

Solving a Quotation Problem
~~~~~~~~~~~~~~~~~~~~~~~~~~~

The +log.txt+ file shows the +Texas_Bays+ table being constructed for ERDDAP via:

-----
*** constructing EDDTableFromDatabase CAGES_Texas_Bays

dataSourceName wasn't specified, so connection pooling won't be used.

EDDTableFromDatabase.makeConnection via DriverManager + datasets.xml info
  Success! time=9

database name=MySQL version=5.5.27-log
driver name=MySQL Connector Java version=mysql-connector-java-5.1.35 ( Revision: 5fb9c5849535c13917c2cf9baaece6ef9693ef27 )
jdbc majorVersion=4 minorVersion=0
  accessibleViaNcCF=.ncCF/.ncCFMA isn't available for this dataset because cdm_data_type=Other is not a compatible type.
EDDTableFromDatabase.makeConnection via DriverManager + datasets.xml info
  Success! time=8
  statement=com.mysql.jdbc.JDBC4PreparedStatement@22b482fb: SELECT DISTINCT "Bay_Location" FROM Texas_Bays
 statement~=SELECT DISTINCT "Bay_Location" FROM Texas_Bays
Table.saveAsFlatNc /raid/erddap/dataset/ys/CAGES_Texas_Bays/subset.nc
  Table.saveAsFlatNc done. TIME=3
* CAGES_Texas_Bays made subsetVariablesDataTable(null).  time=13
* CAGES_Texas_Bays made distinctSubsetVariablesDataTable(null).  time=15 (includes subsetVariablesDataTable() time)
FGDC isn't available for this dataset because there is no longitude and/or latitude variable.
ISO 19115-2/19139 isn't available for this dataset because there is no longitude and/or latitude variable.

*** EDDTableFromDatabase CAGES_Texas_Bays constructor finished. TIME=252
-----

The problem is the statement:

-----
SELECT DISTINCT "Bay_Location" FROM Texas_Bays
-----

which obtains:

-----
+--------------+
| Bay_Location |
+--------------+
| Bay_Location | 
+--------------+
1 row in set (0.00 sec)
-----

as opposed to the statement:

-----
SELECT DISTINCT Bay_Location FROM Texas_Bays
-----

which obtains:

-----
+-------------------------------------------+
| Bay_Location                              |
+-------------------------------------------+
| ARANSAS BAY                               | 
| CEDAR LAKES                               | 
| CORPUS CHRISTI BAY                        | 
| EAST MATAGORDA BAY                        | 
| GALVESTON BAY                             | 
| LOWER LAGUNA MADRE                        | 
| MATAGORDA BAY                             | 
| off Corpus Christi-Upper Laguna Madre     | 
| off Galveston-Freeport                    | 
| off Matagorda-San Antonio-Aransas         | 
| off Sabine Lake                           | 
| off upper Laguna Madre-lower Laguna Madre | 
| SABINE LAKE                               | 
| SAN ANTONIO BAY                           | 
| UPPER LAGUNA MADRE                        | 
+-------------------------------------------+
15 rows in set (0.00 sec)
-----

The solution can be found in the ERDDAP doc at:

http://coastwatch.pfeg.noaa.gov/erddap/download/setupDatasetsXml.html#databaseQuotes[+http://coastwatch.pfeg.noaa.gov/erddap/download/setupDatasetsXml.html#databaseQuotes+]

*****
By default, EDDTableFromDatabase puts ANSI-SQL-standard double quotes around field/column names in SELECT statements in case you have used a reserved word as a field/column name, or a special character in a field/column name. The double quotes also thwart certain types of SQL injection attacks. You can tell ERDDAP to use ", ', or no quotes via <columnNameQuotes> in datasets.xml for this dataset. 
*****

Thus, the problem is solved by placing:

-----
<columnNameQuotes></columnNameQuotes>
-----

in every +dataset+ section of +datasets.xml+ to eliminate the default quotations placed around
the variable names by ERDDAP.