Saving and loading EDA2.0

cedric
pg_dump -U postgres -h 192.168.1.104 --schema public --schema wrbd --file "eda2_public_wrbd.sql" --verbose eda2
pg_dump -U postgres -h 192.168.1.104 --schema ccm21 --file "eda2_ccm21.sql" --verbose eda2 
pg_dump -U postgres -h 192.168.1.104 --schema europe --file "eda2_europe.sql" --verbose eda2 
pg_dump -U postgres -h 192.168.1.104 --table ccm21.riversegments --file "eda2_ccm21_riversegments.sql" --verbose eda2
pg_dump -U postgres -h 192.168.1.104 --table ccm21.model_mod --table ccm21.resultmodel --table ccm21.temp_western --file "eda2_ccm21_othernew.sql" --verbose eda2
pg_dump -U postgres -h 192.168.1.104 --schema public --table ccm21.catchments --file "eda2_ccm21_catchments.sql" --verbose eda2 # corrupted ?
elvira
cd c:\data

psql -U postgres -h localhost --file "eda2_public_wrbd.sql" eda2
restoring an older version of ccm21
psql -U postgres -h localhost --file "eda2_ccm21.sql" eda2  (this is from a previous save to restore catchment)

Now we have a problem with ccm21, we will delete the tables that we do not want to keep from the older version

psql -U postgres -h localhost -c "drop table ccm21.riversegments cascade" eda2
psql -U postgres -h localhost --file "eda2_ccm21_othernew.sql" eda2 (tables model_mod, temp_western, and resultmodel)
psql -U postgres -h localhost --file "eda2_ccm21_riversegments.sql" eda2
psql -U postgres -h localhost --file "eda2_ccm21_europe.sql" eda2
verification that the version is all right
select count(*) from ccm21.riversegments;--690349
select count(*) from ccm21.catchments; --745668
select count(*) from ccm21.riversegments where cum_len_sea>0; --234567

Saving and loading dbeel

restoring the dbeel psql -U postgres -h localhost --file "backup_dbeel_12082011.sql" eda2

saving shp files from ccm21

use psql2shp
pgsql2shp -f "C:\data\riversegments" -p 5432 -u postgres -g the_geom -r -k eda2 "select * from ccm21.riversegments where wso_id in (select wso_id from europe.wso where area='Ireland');"

Inserting wso1 values

/* some checking
SELECT * FROM ccm21.riversegments where wso1_id in (select wso1_id from europe.wso1 where area='Ireland')  ORDER BY gid ;
select distinct on (area) area from europe.wso1 
select * from europe.wso where area='Ireland';
select r.wso_id,r.wso1_id, area from europe.wso e join ccm21.riversegments r on e.wso_id=r.wso_id where area='Ireland' limit 50;
select * from europe.wso1 limit 10;
*/
-- the right request
insert into europe.wso1 (wso_id,wso1_id, area) 
        select r.wso_id,r.wso1_id, area from europe.wso e join ccm21.riversegments r on e.wso_id=r.wso_id where area='Ireland';--8644

wso_id in (select wso_id from europe.wso where area= 'Ireland')

TODO list

1. dams
   11. Import dam data into postgres
   12. Build the table joining dam data
   13. Run R program to calculate the cumulated number of dams from the sea.
2. run the program to calculate the clc numbers
   21. run sql script

Ireland clc

22. run R program

3. run the program to calculate the distance to the source
4. import electrofishing data into the dbeel

Ireland_dams
electrofishing data
Ireland dbeel
Ireland geology