Back to top ..

Script to integrate ROE

download shape from ​http://carmen.carmencarto.fr/66/ka_roe_current_metropole.map

F:
cd F:/IAV/eda/ROE/
shp2pgsql -I -s 2154 -g geom ObstEcoul.shp roe_v4.roe>roe.sql
psql -U postgres -p 5432 -W -c "create schema roe_v4" eda2.2
psql -U postgres -f roe.sql eda2.2
psql -U postgres -c "Comment on table roe_v4.roe IS 'Version Mai 2014'"  eda2.2

From the excel table loaded, we create a temporary table corresponding to the new dam. These might not correspond to those already in the database. lb.tempmerge is the merge between the table coming from excel and the new ROE database (see above).

aaouvtemp=data[,c("dep","modif","ouv_rinom","ouv_id","ouv_libelle")]
sqldf(
                "DROP TABLE IF EXISTS lb.aaouvtemp"
                )

sqldf("create table lb.aaouvtemp as select * from aaouvtemp ")
# je vais rechercher les tableaux ROE mais dans l'autre base correspondant à ces ouvrages

roetemp<-sqldf(
                "SELECT * from roe_v4.roe",
                dbname="eda2.2",
                host="localhost",
        password=passwordlocal)
colnames(data)[c(31:35)]<-c("Mortalite.Francis.70.cm","Mortalite.Francis.18.cm",   
                "Mortalite.kaplan.70.cm","Mortalite.kaplan.18.cm","Nb.turbine.entree")
tempmerge<-merge(roetemp,data, by.x="cdobstecou",by.y="ouv_id")
sqldf("DROP TABLE IF EXISTS lb.tempmerge")
sqldf("CREATE table lb.tempmerge as select * from tempmerge")

From taht step, the next is done in a sql script

source:/devalpomi/sql/table_temp_mortalite.sql

In the final table, the dam which do not exist in our current database will not have the id_drain