I'm listing a sample beginning table setup here with some supporting perl code to demonstrate how someone might go from a set of datalogger files into a 'pointform'( http://twiki.sura.org/bin/view/Main/DataStandards#Data_Structures_Canonical_forms ) representation of the variable captured in the files.

The zipped file at page bottom below contains the following files:

bio_watertemp_create.sql

This will create all the lookup/support and point tables.

Screen output when running this command should look similar to the following:

[jcothran@nautilus biology]$ psql -U postgres -d biology -f bio_watertemp_create.sql
psql:bio_watertemp_create.sql:8: NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "station_type_lkp_pkey" for table "station_type_lkp"
CREATE TABLE
psql:bio_watertemp_create.sql:13: NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "sensor_type_lkp_pkey" for table "sensor_type_lkp"
CREATE TABLE
psql:bio_watertemp_create.sql:18: NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "elevation_type_lkp_pkey" for table "elevation_type_lkp"
CREATE TABLE
psql:bio_watertemp_create.sql:23: NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "sensor_status_type_lkp_pkey" for table "sensor_status_type_lkp"
CREATE TABLE
psql:bio_watertemp_create.sql:36: NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "station_pkey" for table "station"
CREATE TABLE
psql:bio_watertemp_create.sql:44: NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "sensor_pkey" for table "sensor"
CREATE TABLE
psql:bio_watertemp_create.sql:55: NOTICE:  CREATE TABLE will create implicit sequence "sensor_status_row_id_seq" for "serial" column "sensor_status.row_id"
psql:bio_watertemp_create.sql:55: NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "sensor_status_pkey" for table "sensor_status"
CREATE TABLE
psql:bio_watertemp_create.sql:68: NOTICE:  CREATE TABLE will create implicit sequence "water_temperature_row_id_seq" for "serial" column "water_temperature.row_id"
psql:bio_watertemp_create.sql:68: NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "water_temperature_pkey" for table "water_temperature"
CREATE TABLE
CREATE INDEX
psql:bio_watertemp_create.sql:81: NOTICE:  Invalid schema name - using current_schema()
CONTEXT:  PL/pgSQL function "addgeometrycolumn" line 4 at select into variables
                                         addgeometrycolumn                                         
---------------------------------------------------------------------------------------------------
 Geometry column the_geom added to table public.water_temperature WITH a SRID of -1 and type POINT
(1 row)

CREATE INDEX

bio_watertemp_populate_support.sql

This will populate the lookup/support tables.

bio_watertemp_drop.sql

This will drop(destroy) all the tables and indexes. (Don't run unless you're ready to start over).

jalamaRaw.txt

This is a sample datalogger raw data input file which will be parsed by the perl routines

bioWaterTemp.pl

This perl routine parses the sample input file and creates a set of SQL INSERT statements which can be run against the database to populate it.

bio_watertemp_insert.sql

This is the sql file which will need to be run against the database after the INSERT file is generated.

The content looks like the following:

INSERT INTO water_temperature (row_id,row_entry_date,row_update_date,station_id,sensor_id,measurement_date,water_temperature_obs,latitude,longitude,the_geom) VALUES (nextval('water_temperature_row_id_seq'),now(),now(),3,0,'1/1/03 8:00',13.57,34.5,-120.5,GeometryFromText('POINT(-120.5 34.5)',-1));
INSERT INTO water_temperature (row_id,row_entry_date,row_update_date,station_id,sensor_id,measurement_date,water_temperature_obs,latitude,longitude,the_geom) VALUES (nextval('water_temperature_row_id_seq'),now(),now(),3,1,'1/1/03 8:00',13.61,34.5,-120.5,GeometryFromText('POINT(-120.5 34.5)',-1));
INSERT INTO water_temperature (row_id,row_entry_date,row_update_date,station_id,sensor_id,measurement_date,water_temperature_obs,latitude,longitude,the_geom) VALUES (nextval('water_temperature_row_id_seq'),now(),now(),3,2,'1/1/03 8:00',13.98,34.5,-120.5,GeometryFromText('POINT(-120.5 34.5)',-1));

bioWaterTempCopy.pl

This perl routine parses the sample input file and creates a COPY file which can also be run against the database to populate it, but for large files(in the millions of records), COPY may run several order of magnitudes faster( http://techdocs.postgresql.org/techdocs/usingcopy.php ).

Creating a COPY file is a little more difficult than creating INSERT statements, since you have to manually retrieve and set the table row sequence and convert any database computed values into their literal components. If a single line in the COPY file fails, the whole COPY fails. The index on the table can also be dropped and recreated to further speed the copy process. The files which are included for doing this are:

bio_watertemp_drop_idx.sql
bio_watertemp_create_idx.sql

bio_watertemp.cpy

This is the COPY file which is created when running bioWaterTempCopy.pl

The content looks like the following:

128,'2004-10-13 16:21:05','2004-10-13 16:21:05',3,0,'1/1/03 8:00',13.57,34.5,-120.5,NULL,'POINT(-120.5 34.5)'
129,'2004-10-13 16:21:05','2004-10-13 16:21:05',3,1,'1/1/03 8:00',13.61,34.5,-120.5,NULL,'POINT(-120.5 34.5)'
130,'2004-10-13 16:21:05','2004-10-13 16:21:05',3,2,'1/1/03 8:00',13.98,34.5,-120.5,NULL,'POINT(-120.5 34.5)'

bio_watertemp_copy.sql

This is the sql file which will need to be run against the database after the COPY file is generated.

COPY water_temperature from '/usr2/prod/buoys/perl/bio_watertemp.cpy' with delimiter ',' NULL AS 'NULL'; 

-- JeremyCothran - 13 Oct 2004

• logger2point.zip:

to top