ALRS bulk loading

From PTAGISWiki

Jump to: navigation, search

With the increasing rate of appearance of new sites to be tracked by ALRS, it is no longer feasible to load them one at a time through the ALRS web interface (http://www.ptagis.org/alrs).

This is a first step at a bulk loading process.

Contents

Update

There are now scripted processes to insert interrogation site and tag/release/recovery location definitions into the ALRS tables. Refer to the util_add_new_stream_to_alrs.sh and util_add_new_is-obs_site_to_alrs.sh scripts in ~pittag/bin on blueback.

CSV input

This is the file containing requested new sites that is delivered by Dave: 

obs_site	short_name	long_name	obs_site_type	owner	dec_lat	dec_lon	parent_loc	HUC	km_offset	min_year	max_year
AB3	Lower Abernathy Creek	Instream detectors at Abernathy Creek, rkm 1	Instream Remote Detection System	USFWS 	46.19566	-123.16454	ABERC 	17080003	1	Current	Current
CHL	Lower Chiwawa River	Instream MUX at Chiwawa River rkm 1	Instream Remote Detection System	WDFW  	47.78755	-120.65386	CHIWAR	17020011	1	2008	Current
CHU	Upper Chiwawa River	Instream MUX at Chiwawa River rkm 12, between the FR-62 bridge and Alder Creek	Instream Remote Detection System	WDFW  	47.84380	-120.66544	CHIWAR	17020011	12	2008	Current
ENL	Lower Entiat River	Instream MUX at Entiat River rkm 2, immediately upstream of Entiat, WA.	Instream Remote Detection System	USFWS 	47.66378	-120.24282	ENTIAR	17020010	2	2007	Current
ENM	Middle Entiat River	Instream MUX at Entiat River rkm 26, below the McKenzie Diversion Dam	Instream Remote Detection System	USFWS 	47.79705	-120.40307	ENTIAR	17020010	26	2008	Current
LWN	Little Wenatchee River	Instream MUX at Little Wenatchee River rkm 4, at the old fish weir site	Instream Remote Detection System	WDFW  	47.83420	-120.83888	LWENAT	17020011	4	Current	Current
MAD	Mad River, Entiat River Basin	Instream MUX at Mad River rkm 1, at Ardenvoir, WA.	Instream Remote Detection System	USFWS 	47.73685	-120.36874	MADRVR	17020010	1	2007	Current
NAL	Lower Nason Creek	Instream MUX at Nason Creek rkm 1, located within Lake Wenatchee State Park	Instream Remote Detection System	YINN  	47.80246	-120.71364	NASONC	17020011	1	2008	Current
NAU	Upper Nason Creek	Instream MUX at Nason Creek rkm 19	Instream Remote Detection System	YINN  	47.78598	-120.84855	NASONC	17020011	19	2007	Current
PES	Peshastin Creek	Instream MUX at Peshastin River rkm 3, below the bridge at Smithson's property	Instream Remote Detection System	WDFW  	47.54849	-120.60988	PESHAR	17020011	3	2007	Current
LWE	Lower Wenatchee River	Instream MUX at Wenatchee River rkm 2	Instream Remote Detection System	WDFW  	47.46495	-120.35064	WENATR	17020011	2	Current	Current
MWE	Middle Wenatchee River	Instream MUX at Wenatchee River rkm 50, above Tumwater Dam	Instream Remote Detection System	WDFW  	47.62303	-120.72619	WENATR	17020011	50	Current	Current
UWE	Upper Wenatchee River	Instream MUX at Wenatchee River rkm 86, below the Chiwawa River	Instream Remote Detection System	WDFW  	47.78721	-120.66043	WENATR	17020011	86	Current	Current
WTL	White River, Wenatchee Basin	Instream MUX at White River rkm 4, at the old fish weir site	Instream Remote Detection System	WDFW  	47.84594	-120.83163	WHITER	17020011	4	Current	Current
SFL	Shipherd Falls Ladder	Detectors at Shipherd Falls ladder, Wind River, WA.	Instream Remote Detection System	WDFW  	45.73798	-121.80578	WIND2R	17070105	2	Current	Current

Build individual CSV input files for each of the affected tables

ALRS updates these tables:

  • location
  • river_segment
  • location_in_segment
  • activity_location

I used the ALRS web app to create one new site, then exported that row from the four tables. I used that exported CSV row, to manually create these four CSV files that will be used to copy directly into the tables.

The leading ID key has to be manually generated such that it is a unique, increasing integer.

location

loc_id	location_name	lat_decimal	lon_decimal	lat_lon_source	lat_estimate	lon_estimate	est_lat_lon_source	elevation_msl	y_coord	x_coord	lat_err	lon_err	est_piy	est_pix	y_err	x_err	modification_date	modification_comment	userid
788	Instream MUX at Mad River rkm 1, at Ardenvoir, WA.	47.73685	-120.36874		0	0		0	68	161	0	0	0	0	0	0	2009-06-17 16:59:42	Initial specification of coordinates	rday                                              
789	Instream MUX at Nason Creek rkm 1, located within Lake Wenatchee State Park	47.80246	-120.71364		0	0		0	69	165	0	0	0	0	0	0	2009-06-17 16:59:42	Initial specification of coordinates	rday                                              
790	Instream MUX at Nason Creek rkm 19	47.78598	-120.84855		0	0		0	70	160	0	0	0	0	0	0	2009-06-17 16:59:42	Initial specification of coordinates	rday                                              
791	Instream MUX at Peshastin River rkm 3, below the bridge at Smithson's property	47.54849	-120.60988		0	0		0	83	169	0	0	0	0	0	0	2009-06-17 16:59:42	Initial specification of coordinates	rday                                              
792	Instream MUX at Wenatchee River rkm 2	47.46495	-120.35064		0	0		0	88	179	0	0	0	0	0	0	2009-06-17 16:59:42	Initial specification of coordinates	rday                                              
793	Instream MUX at Wenatchee River rkm 50, above Tumwater Dam	47.62303	-120.72619		0	0		0	79	165	0	0	0	0	0	0	2009-06-17 16:59:42	Initial specification of coordinates	rday                                              
794	Instream MUX at Wenatchee River rkm 86, below the Chiwawa River	47.78721	-120.66043		0	0		0	70	167	0	0	0	0	0	0	2009-06-17 16:59:42	Initial specification of coordinates	rday                                              
795	Instream MUX at Wenatchee River rkm 86, below the Chiwawa River	47.84594	-120.83163		0	0		0	67	161	0	0	0	0	0	0	2009-06-17 16:59:42	Initial specification of coordinates	rday                                              
796	Detectors at Shipherd Falls ladder, Wind River, WA.	45.73798	-121.80578		0	0		0	181	124	0	0	0	0	0	0	2009-06-17 16:59:42	Initial specification of coordinates	rday

river_segment

seg_id	river_name	segment_nbr	river_km	huc_code	segment_description	reach_min_km	reach_max_km	userid	modification_date
367	MAD	1	778.017.001	17020010	Mad River, Entiat River Basin	0	0	rday                                              	2009-06-17 17:00:39
368	NAL	1	754.089.001	17020011	Lower Nason Creek	0	0	rday                                              	2009-06-17 17:00:39
369	NAU	1	754.089.019	17020011	Upper Nason Creek	0	0	rday                                              	2009-06-17 17:00:39
370	PES	1	754.029.003	17020011	Peshastin Creek	0	0	rday                                              	2009-06-17 17:00:39
371	LWE	1	754.002	17020011	Lower Wenatchee River	0	0	rday                                              	2009-06-17 17:00:39
372	MWE	1	754.050	17020011	Middle Wenatchee River	0	0	rday                                              	2009-06-17 17:00:39
373	UWE	1	754.086	17020011	Upper Wenatchee River	0	0	rday                                              	2009-06-17 17:00:39
374	WTL	1	754.090.004	17020011	White River, Wenatchee Basin	0	0	rday                                              	2009-06-17 17:00:39
375	SFL	1	251.002	17070105	Shipherd Falls Ladder	0	0	rday                                              	2009-06-17 17:00:39

location_in_segment

site_id	loc_id	seg_id	site_type	site_description	locality_radius	general_locality_site	km_in_reach	km_total	category	rkm_fixed_site_y_n	fixed_site_y_n	marking_only_y_n	entire_river_y_n	release_act_suffix_y_n	archaic_y_n	userid	modification_date
MAD	788	367	RIVERBED            	natural environment	500		0	0			N	N	N	N	N	rday                                              	2009-06-17 17:02:07
NAL	789	368	RIVERBED            	natural environment	500		0	0			N	N	N	N	N	rday                                              	2009-06-17 17:02:07
NAU	790	369	RIVERBED            	natural environment	500		0	0			N	N	N	N	N	rday                                              	2009-06-17 17:02:07
PES	791	370	RIVERBED            	natural environment	500		0	0			N	N	N	N	N	rday                                              	2009-06-17 17:02:07
LWE	792	371	RIVERBED            	natural environment	500		0	0			N	N	N	N	N	rday                                              	2009-06-17 17:02:07
MWE	793	372	RIVERBED            	natural environment	500		0	0			N	N	N	N	N	rday                                              	2009-06-17 17:02:07
UWE	794	373	RIVERBED            	natural environment	500		0	0			N	N	N	N	N	rday                                              	2009-06-17 17:02:07
WTL	795	374	RIVERBED            	natural environment	500		0	0			N	N	N	N	N	rday                                              	2009-06-17 17:02:07
SFL	796	375	RIVERBED            	natural environment	500		0	0			N	N	N	N	N	rday                                              	2009-06-17 17:02:07

activity_location

site_code	site_id	activity_category	activity_code	activity_suffix	contact_id	activity_loc_description	from_km	to_km	from_date	to_date	userid	modification_date
MAD	MAD	REMOTE         	INTERROGATION  	NON	-1	Instream MUX at Mad River rkm 1, at Ardenvoir, WA.	0	0	01/01/07 12:00 AM	NULL	rday                                              	2009-06-17 17:02:39
NAL	NAL	REMOTE         	INTERROGATION  	NON	-1	Instream MUX at Nason Creek rkm 1, located within Lake Wenatchee State Park	0	0	01/01/08 12:00 AM	NULL	rday                                              	2009-06-17 17:02:39
NAU	NAU	REMOTE         	INTERROGATION  	NON	-1	Instream MUX at Nason Creek rkm 19	0	0	01/01/07 12:00 AM	NULL	rday                                              	2009-06-17 17:02:39
PES	PES	REMOTE         	INTERROGATION  	NON	-1	Instream MUX at Peshastin River rkm 3, below the bridge at Smithson's property	0	0	01/01/07 12:00 AM	NULL	rday                                              	2009-06-17 17:02:39
LWE	LWE	REMOTE         	INTERROGATION  	NON	-1	Instream MUX at Wenatchee River rkm 2	0	0	01/01/09 12:00 AM	NULL	rday                                              	2009-06-17 17:02:39
MWE	MWE	REMOTE         	INTERROGATION  	NON	-1	Instream MUX at Wenatchee River rkm 50, above Tumwater Dam	0	0	01/01/09 12:00 AM	NULL	rday                                              	2009-06-17 17:02:39
UWE	UWE	REMOTE         	INTERROGATION  	NON	-1	Instream MUX at Wenatchee River rkm 86, below the Chiwawa River	0	0	01/01/09 12:00 AM	NULL	rday                                              	2009-06-17 17:02:39
WTL	WTL	REMOTE         	INTERROGATION  	NON	-1	Instream MUX at White River rkm 4, at the old fish weir site	0	0	01/01/09 12:00 AM	NULL	rday                                              	2009-06-17 17:02:39
SFL	SFL	REMOTE         	INTERROGATION  	NON	-1	Detectors at Shipherd Falls ladder, Wind River, WA.	0	0	01/01/09 12:00 AM	NULL	rday                                              	2009-06-17 17:02:39

spreadsheet processing

I removed the header row from each CSV file. I formatted each date column as MM/DD/YYYY HH:MM:SS. Care had to be taken to prevent the spreadsheet from interpretting the RKM values as numbers during import and thus mangling them. I exported each file as CSV, but opted to "Edit filter settings" and thus specify a tab delimited file instead of comma delimited. Also, I removed the default string delimiter (").

import to ingres

The copydb command is not part of the Ingres install on my linux desktop, so I had to do this on sockeye.

I generated a copyin script for each table using the "copydb" command: 

copydb -upittag -c ptagis3 location
copydb -upittag ptagis3 river_segment
copydb -upittag ptagis3 location_in_segment
copydb -upittag ptagis3 activity_location

I extracted the following script fragment from each resulting copy.in: 

copy location(
        loc_id= c0tab,
        location_name= c0tab,
        lat_decimal= c0tab,
        lon_decimal= c0tab,
        lat_lon_source= c0tab,
        lat_estimate= c0tab,
        lon_estimate= c0tab,
        est_lat_lon_source= c0tab,
        elevation_msl= c0tab,
        y_coord= c0tab,
        x_coord= c0tab,
        lat_err= c0tab,
        lon_err= c0tab,
        est_piy= c0tab,
        est_pix= c0tab,
        y_err= c0tab,
        x_err= c0tab,
        modification_date= c0tab,
        modification_comment= c0tab,
        userid= c0nl
        )
from '/home/rday/location-tab.csv'
\g

It assumes the delimiter will be tab. I replaced references to varchar with c0. I removed a dummy column from the end. 

copy river_segment(
        seg_id= c0tab,
        river_name= c0tab,
        segment_nbr= c0tab,
        river_km= c0tab,
        huc_code= c0tab,
        segment_description= c0tab,
        reach_min_km= c0tab,
        reach_max_km= c0tab,
        userid= c0tab,
        modification_date= c0nl
        )
from '/home/rday/river_segment-tab.csv'
\g

copy location_in_segment(
        site_id= c0tab,
        loc_id= c0tab,
        seg_id= c0tab,
        site_type= c0tab,
        site_description= c0tab,
        locality_radius= c0tab,
        general_locality_site= c0tab,
        km_in_reach= c0tab,
        km_total= c0tab,
        category= c0tab,
        rkm_fixed_site_y_n= c0tab,
        fixed_site_y_n= c0tab,
        marking_only_y_n= c0tab,
        entire_river_y_n= c0tab,
        release_act_suffix_y_n= c0tab,
        archaic_y_n= c0tab,
        userid= c0tab,
        modification_date= c0nl
        )
from '/home/rday/location_in_segment-tab.csv'
\g

copy activity_location(
        site_code= c0tab,
        site_id= c0tab,
        activity_category= c0tab,
        activity_code= c0tab,
        activity_suffix= c0tab,
        contact_id= c0tab,
        activity_loc_description= c0tab,
        from_km= c0tab,
        to_km= c0tab,
        from_date= c0tab,
        to_date= c0tab with null('NULL'),
        userid= c0tab,
        modification_date= c0nl
        )
from '/home/rday/activity_location-tab.csv'
\g

I had to edit the import spec for to_date from 

with null('^]NULL^[')

to 

with null('NULL')

I invoked the script like this: 

[rday@snapper ~]$ sql -upittag sockeye::ptagis3 < location.in
INGRES TERMINAL MONITOR Copyright 2007 Ingres Corporation
Ingres 2006 Release 2 Linux Version II 9.1.1 (a64.lnx/103)NPTL login
Thu Jun 18 10:55:34 2009

continue
* * * * * * * * * * * * * * * * * * * * * * * * Executing . . .

(9 rows)
continue
* 
Your SQL statement(s) have been committed.

Ingres 2006 Release 2 Version II 9.1.1 (a64.lnx/103)NPTL logout
Thu Jun 18 10:55:34 2009

generate site xml

After all the tables have been updated, the process to generate the site xml is the same as before. That is, on sockeye, in the sitexml directory, run the script to create the xml files for the flash map. Copy those files to pitblade:/dsk2/ptagis-1.0/web/ptagis/sites/map and check the positions of the dots on the map. Commit the files to CVS. Copy those same files to bay and commit to CVS.

Retrieved from "http://php.ptagis.org/wiki/index.php/ALRS_bulk_loading"
Personal tools