Changes between Initial Version and Version 1 of saving and loading the database

Timestamp:

Jun 1, 2018 7:56:34 PM (7 years ago)

Author:

cedric

Comment:

--

saving and loading the database

@@ +1 @@
+= 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]]