back to wiki main page ..

Download instructions for EDA2.3 (SUDOANG project)

latest run : december - january 2021

Download of shape files will soon be available in in the ​project site but in the meantime here you can download postgres dump. The folder where all backup files for EDA2.3 can be downloaded is ​Drive EPTB. First take a look at the Atlas, you will look at the definitions and see what you need. All tables are in ESPG:3035 (European LAEA) don't forget to reproject if you want ot use it. The project is currently in version final (upload these day 17/05 30/05).

Create an eda2.3 database

note : the current version is postgresSQL 12 Later we will provide some shapefile, for now you have to use postgis.

Initial command

# open a shell with command CMD # Move to the place where you have downloaded the file using the following command cd c:/path/to/my/folder # note psql must be accessible, in windows you can add the path to the postgres #bin folder, otherwise you need to add the full path to the postgres bin folder see link to instructions below createdb -U postgres eda2.3 psql -U postgres eda2.3 # this will open a command with # where you can launch the commands in the next box instructions on how to set path for psql set_path create extension "postgis"; create extension "dblink"; create extension "ltree"; create extension "tablefunc"; create schema dbeel_rivers; create schema france; create schema spain; create schema portugal; -- type \q to quit the psql shell

Now the database is ready to receive the differents dumps. The dump file are large. You might not need the part including unit basins or waterbodies. All the tables are described in the Atlas. You might need to understand what is inheritance in a database. ​https://www.postgresql.org/docs/12/tutorial-inheritance.html

EDA tables Table type table name restore instructions columns riversegments These layers contain the topology (see Atlas for detail) dbeel_rivers.rn france.rn spain.rn portugal.rn # dbeel_rivers.rn ! mandatory => table at the international level from which # the other table inherit # even if you don't want to use other countries # (In many cases you should ... there are transboundary catchments) download this first. # the rn network must be restored firt ! #table rne and rna refer to it by foreign keys. pg_restore -U postgres -d eda2.3 "dbeel_rivers.rn.backup" #france pg_restore -U postgres -d eda2.3 "france.rn.backup" # spain pg_restore -U postgres -d eda2.3 "spain.rn.backup" # portugal pg_restore -U postgres -d eda2.3 "portugal.rn.backup" # with the schema you will probably want to be able to use the functions psql -U postgres -d eda2.3 -f "function_dbeel_rivers.sql" To understand how you can use function see recipes for sudoang database gid idsegment source target lengthm nextdownidsegment path isfrontier issource seaidsegment issea geom isendoreic isinternational country These layers contain the Attributes dbeel_rivers.rna france.rna spain.rna portugal.rna pg_restore -U postgres -d eda2.3 "dbeel_rivers.rna.backup" -- france pg_restore -U postgres -d eda2.3 "france.rna.backup" -- spain pg_restore -U postgres -d eda2.3 "spain.rna.backup" -- portugal pg_restore -U postgres -d eda2.3 "portugal.rna.backup" idsegment altitudem distanceseam distancesourcem cumnbdam medianflowm3ps surfaceunitbvm2 surfacebvm2 strahler shreeve codesea name pfafriver pfafsegment basin riverwidthm temperature temperaturejan temperaturejul wettedsurfacem2 wettedsurfaceotherm2 lengthriverm emu cumheightdam riverwidthmsource slope dis_m3_pyr_riveratlas dis_m3_pmn_riveratlas dis_m3_pmx_riveratlas drought drought_type_calc These layers contain eel data (see Atlas for detail) dbeel_rivers.rne france.rne spain.rne portugal.rne pg_restore -U postgres -d eda2.3 "dbeel_rivers.rne.backup" -- france pg_restore -U postgres -d eda2.3 "france.rne.backup" -- spain pg_restore -U postgres -d eda2.3 "spain.rne.backup" -- portugal pg_restore -U postgres -d eda2.3 "portugal.rne.backup" idsegment surfaceunitbvm2 surfacebvm2 delta gamma density neel beel peel150 peel150300 peel300450 peel450600 peel600750 peel750 nsilver bsilver psilver150300 psilver300450 psilver450600 psilver600750 psilver750 psilver pmale150300 pmale300450 pmale450600 pfemale300450 pfemale450600 pfemale600750 pfemale750 pmale pfemale sex_ratio cnfemale300450 cnfemale450600 cnfemale600750 cnfemale750 cnmale150300 cnmale300450 cnmale450600 cnsilver150300 cnsilver300450 cnsilver450600 cnsilver600750 cnsilver750 cnsilver delta_tr gamma_tr type_fit_delta_tr type_fit_gamma_tr density_tr density_pmax_tr neel_pmax_tr nsilver_pmax_tr density_wd neel_wd beel_wd nsilver_wd bsilver_wd sector_tr year_tr is_current_distribution_area is_pristine_distribution_area_1985 For the next layer Unit basins I'm giving more details because the file will not be described in the Atlas Table type table name restore instructions columns Unitbasins dbeel_rivers.basinunit_bu france.basinunit_bu spain.basinunit_bu portugal.basinunit_bu france.basinunitout_buo spain.basinunitout_buo portugal.basinunitout_buo The unit basins is the simple basin that surrounds a segment. It correspond to the topography unit from which unit segment have been calculated. ESPG 3035. Tables bu_unitbv, and bu_unitbvout inherit from dbeel_rivers.unit_bv. The first table intersects with a segment, the second table does not, it corresponds to basin polygons which do not have a riversegment. dbeel_rivers.basinunit_bu.backup france.basinunit_bu.backup spain.basinunit_bu.backup portugal.basinunit_bu.backup france.basinunitout_buo.backup spain.basinunitout_buo.backup portugal.basinunitout_buo.backup pg_restore -U postgres -d eda2.3 'dbeel_rivers.basinunit_bu.backup' -- france pg_restore -U postgres -d eda2.3 "france.basinunit_bu.backup" pg_restore -U postgres -d eda2.3 "france.basinunitout_buo.backup" -- spain pg_restore -U postgres -d eda2.3 "spain.basinunit_bu.backup" pg_restore -U postgres -d eda2.3 "spain.basinunit_bu.backup" -- portugal pg_restore -U postgres -d eda2.3 "portugal.basinunit_bu.backup" pg_restore -U postgres -d eda2.3 "portugal.basinunitout_buo.backup" Source Portugal ​https://sniambgeoviewer.apambiente.pt/Geodocs/gml/inspire/HY_PhysicalWaters_DrainageBasinGeoCod.zip ​https://sniambgeoviewer.apambiente.pt/Geodocs/gml/inspire/HY_PhysicalWaters_DrainageBasinGeoCod.zip France In france unit bv corresponds to the RHT (Pella et al., 2012) Spain ​http://www.mapama.gob.es/ide/metadatos/index.html?srv=metadata.show&uuid=898f0ff8-f06c-4c14-88f7-43ea90e48233 bu_id identifier (integer) of the unit basin bu_idsegment idsegment of theunit basin bu_country country of the unit basin bu_code original code of the unit basin bu_perc_rn_not_covered percentage of the length of the riversegment which does not overlap with the waterbodies in the basin (see waterbody_unitbv) bu_isrn is there an associated riversegment to this basin. bu_surfacebvunitm2 unit surface of the unit basin Now the waterbodies we have kept the structure from the source table in WISE or from the bd_topage. Table type table name restore instructions files Waterbodies dbeel_rivers.waterbody_unitbv This one is necessary to load the others (thanks Laurent for spotting the mistake) pg_restore -U postgres -d eda2.3 "dbeel_rivers.waterbody_unitbv.backup" dbeel_rivers.waterbody_unitbv.backup portugal.waterbody_unitbv TODO => SOURCE OF DATA pg_restore -U postgres -d eda2.3 "portugal.waterbody_unitbv.backup" portugal.waterbody_unitbv.backup france.waterbody_unitbv This corresponds to the hydrographic surface from bd_topage. \href{​https://bdtopage.eaufrance.fr/page/documents-ressources}. From the orignal columns, the following have been kept id code_hydro code_pays nature persistance salinite commentaire id_p_eau id_c_eau id_ent_tr nom_p_eau nom_c_eau nom_ent_tr idsegment : idsegment code in the sudoang database geom ​https://bdtopage.eaufrance.fr/page/documents-ressources pg_restore -U postgres -d eda2.3 "france.waterbody_unitbv.backup" france.waterbody_unitbv.backup spain.waterbody_unitbv TODO => SOURCE OF DATA pg_restore -U postgres -d eda2.3 "spain.waterbody_unitbv.backup" spain.waterbody_unitbv.backup Additional tables Result per year This table is similar to dbeel_rivers.rne ... only eel results, it has less columns but it does not only contains the predictions for 2015 but for all years. Beware it's huge ! pg_restore -U postgres -d eda2.3 "dbeel_rivers.rne_annual.backup" Cumulated dam metrics These data include an extraction from BDOE, ICE, ROE database in July 2020, and dam data integration from Spain and Portugal in the course of 2020 with the best data available at the time. The frontier segment in North of France include some of the information avalaible about dams height in the Rhine and the Meuse. In rna table cumnbdams the cumulated number of dams correspond to cumnbdamso the sum of the number of dams for which the height is different from zero (this effectively excludes most bridges and culverts) and the cumheightdams corresponds to cs_height_10_n. variable description Variables finishing with _ncorrespond to sum of height from the sea without predictions for dams with missing height. Variables finishing with _p correspond to cumulated dam height which include the prediction of dam height when the value was missing (see article in prep - and EDA report - and ​data integration for details about prediction models (different in France and the Iberian Peninsula) and calculations. cs_height_15 correspond to sum(h^1.5) cs_height_12 sum(h^1.2). We have tested various powers it worked in EDA 2018 but for the sudoang dataset the best is to use sum(h) = cs_height_10 = sum(h^1.0). Variables ending with 10 have no power transformation. The pp variable are similar to p but set height = 0 when there is a pass adapted to eel (long work on what is - and what is not a pass that eel :-o can cross, see ​data integration). The pps do the same when a score from ICE or ROE or local expertise (see details) say that the dam is crossable. In practise too few expertise for this variable to make a difference... Calculations the height of dams only in one country _SP... e.g in the Tejo start counting at the frontier and don't include Portugese dams ... not good in eda model and discarded soon in the analyses but it's there .... idsegment identifier of the segment cumnbdamso number of dams of height different from zero cs_height_10_n No predicted value for missing dams cs_height_08_n cs_height_12_n cs_height_15_n cumnbdamp Number of dams of height different from zero but include prediction when NULL cs_height_10_p Sum including predicted value when height of dam is NULL cs_height_08_p cs_height_12_p cs_height_15_p cs_height_10_pp h=0 if a passable fishway for eel cs_height_08_pp cs_height_12_pp cs_height_15_pp cs_height_10_pps pp + h=0 if score indicate transparent dam cs_height_08_pps cs_height_12_pps cs_height_15_pps cs_height_10_pass0 Sum of height of dams with a fishway for eel cs_height_10_pass1 Sum of height of dams without fishway for eel cs_height_10_score0 Sum of height of dams with a fishway for eel and good score cs_height_10_score1 Sum of height of dams without a fishway for eel or bad score cs_height_10_FR Sum of height of dams located in France cs_height_10_SP Sum of height of dams located only in Spain cs_height_10_PT Sum of height of dams located in Portugal geom geometry of the segment, same as rn pg_restore -U postgres -d eda2.3 'dbeel_rivers.rnadam.backup.backup' Licence License agreement The whole dataset is licensed under a Creative Commons Attribution 4.0 International License ​4.0. Some parts as indicated in the Atlas are governed by their own licence. The eel dataset is the work of the sudoang team, and we permit reuse of the data for any purpose including commercial. In cases where original licenses. By downloading and using the data the user agrees to the terms and conditions of these licenses. Disclaimer of warranty The sudoang database and any related materials contained therein are provided “as is” without warranty of any kind, either express or implied, including, but not limited to, the implied warranties of merchantability, fitness for a particular purpose, noninterference, system integration, or noninfringement. The entire risk of use of the data shall be with the user. The user expressly acknowledges that the data may contain some nonconformities, defects, or errors. The authors do not warrant that the data will meet the user's needs or expectations, that the use of the data will be uninterrupted, or that all nonconformities, defects, or errors can or will be corrected. The authors are not inviting reliance on these data, and the user should always verify actual data. Limitation of liability In no event shall the authors be liable for costs of procurement of substitute goods or services, lost profits, lost sales or business expenditures, investments, or commitments in connection with any business, loss of any goodwill, or for any direct, indirect, special, incidental, exemplary, or consequential damages arising out of the use of the sudoang database and any related materials, however caused, on any theory of liability, and whether or not the authors have been advised of the possibility of such damage. These limitations shall apply notwithstanding any failure of essential purpose of any exclusive remedy commands to create the dump files here the commands we (Maria and Cédric) need to re-run to get the final versions of the files rn cd "C:\Users\cedric.briand\OneDrive - EPTB Vilaine\partage\EDA_download" pg_dump -U postgres -Fc -f "dbeel_rivers.rn.backup" --table dbeel_rivers.rn eda2.3 pg_dump -U postgres -Fc -f "france.rn.backup" --table france.rn eda2.3 pg_dump -U postgres -Fc -f "spain.rn.backup" --table spain.rn eda2.3 pg_dump -U postgres -Fc -f "portugal.rn.backup" --table portugal.rn eda2.3 rna pg_dump -U postgres -Fc -f "dbeel_rivers.rna.backup" --table dbeel_rivers.rna eda2.3 pg_dump -U postgres -Fc -f "france.rna.backup" --table france.rna eda2.3 pg_dump -U postgres -Fc -f "spain.rna.backup" --table spain.rna eda2.3 pg_dump -U postgres -Fc -f "portugal.rna.backup" --table portugal.rna eda2.3 rne pg_dump -U postgres -Fc -f "dbeel_rivers.rne.backup" --table dbeel_rivers.rne eda2.3 pg_dump -U postgres -Fc -f "france.rne.backup" --table france.rne eda2.3 pg_dump -U postgres -Fc -f "spain.rne.backup" --table spain.rne eda2.3 pg_dump -U postgres -Fc -f "portugal.rne.backup" --table portugal.rne eda2.3 rne_annual pg_dump -U postgres -Fc -f "dbeel_rivers.rne_annual.backup" --table dbeel_rivers.rne_annual eda2.3 unit_basins pg_dump -U postgres -Fc -f "dbeel_rivers.basinunit_bu.backup" --table dbeel_rivers.basinunit_bu eda2.3 pg_dump -U postgres -Fc -f "spain.basinunit_bu.backup" --table spain.basinunit_bu eda2.3 pg_dump -U postgres -Fc -f "portugal.basinunit_bu.backup" --table portugal.basinunit_bu eda2.3 pg_dump -U postgres -Fc -f "spain.basinunitout_buo.backup" --table spain.basinunitout_buo eda2.3 pg_dump -U postgres -Fc -f "portugal.basinunitout_buo.backup" --table portugal.basinunitout_buo eda2.3 pg_dump -U postgres -Fc -f "france.basinunitout_buo.backup" --table france.basinunitout_buo eda2.3 Waterbodies pg_dump -U postgres -Fc -f "dbeel_rivers.waterbody_unitbv.backup" --table dbeel_rivers.waterbody_unitbv eda2.3 pg_dump -U postgres -Fc -f "spain.waterbody_unitbv.backup" --table spain.waterbody_unitbv eda2.3 pg_dump -U postgres -Fc -f "portugal.waterbody_unitbv.backup" --table portugal.waterbody_unitbv eda2.3 pg_dump -U postgres -Fc -f "france.waterbody_unitbv.backup" --table france.waterbody_unitbv eda2.3 Cumulated dam metrics pg_dump -U postgres -Fc -f "dbeel_rivers.rnadam.backup" --table dbeel_rivers.rnadam eda2.3 Code for functions SELECT pg_get_functiondef(pgp.oid) FROM pg_catalog.pg_proc pgp INNER JOIN pg_catalog.pg_namespace pgn ON (pgp.pronamespace = pgn.oid) WHERE pgn.nspname = 'dbeel_rivers'; Shape export pgsql2shp -u postgres -g geom -P XXXXX -f "rn_rna" eda2.3 "SELECT idsegment,lengthm,nextdownidsegment,path,isfrontier,issource,seaidsegment,issea,isendoreic, isinternational,country,altitudem,distanceseam,distancesourcem,cumnbdam,medianflowm3ps, surfaceunitbvm2,surfacebvm2,strahler,shreeve,codesea,name,pfafriver,pfafsegment,basin, riverwidthm,riverwidthmsource,temperature,wettedsurfacem2,wettedsurfaceotherm2,lengthriverm, emu,cumheightdam,slope,dis_m3_pyr_riveratlas,dis_m3_pmn_riveratlas,dis_m3_pmx_riveratlas, drought,drought_type_calc, st_transform(geom,4326) as geom FROM spain.rn_rna"