= 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 ==
{{{
#!sql
/* 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
}}}
[[Image(source:eda/data/Docs/trac/Ireland/qgis.jpg,600px)]]
{{{
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"][[BR]]
  22. run R program
 3. run the program to calculate the distance to the source
 4. import electrofishing data into the dbeel

["Ireland_dams"] [[BR]]
["electrofishing data"][[BR]]
["Ireland dbeel"][[BR]]
["Ireland geology"][[BR]]