DataloggerToPointform < Main

Main
Main.DataloggerToPointformr1.2 - 15 Oct 2025 - 19:53 - JeremyCothrantopic end
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:
<verbatim>
[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
</verbatim>
*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:
<verbatim>
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));
</verbatim>
*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* <br>
*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:
<verbatim>
128,'2025-10-13 16:21:05','2025-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,'2025-10-13 16:21:05','2025-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,'2025-10-13 16:21:05','2025-10-13 16:21:05',3,2,'1/1/03 8:00',13.98,34.5,-120.5,NULL,'POINT(-120.5 34.5)'
</verbatim>
*bio_watertemp_copy.sql*
This is the sql file which will need to be run against the database after the COPY file is generated.
<verbatim>
COPY water_temperature from '/usr2/prod/buoys/perl/bio_watertemp.cpy' with delimiter ',' NULL AS 'NULL'; 
</verbatim>
-- Main.JeremyCothran - 13 Oct 2025
   * [[%ATTACHURL%/logger2point.zip][logger2point.zip]]:
to top
End of topic
Skip to action links | Back to top
You are here: Main > DataloggerToPointform

to top