wiki:download instructions

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).

source:eda/data/Docs/trac/sudoang/density_v2.3.2.png

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

source:eda/data/Docs/trac/sudoang/idsegment.png

   # 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

source:eda/data/Docs/trac/sudoang/wettedsurfaceotherm2.png

   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

source:eda/data/Docs/trac/sudoang/silver_upstream.png

   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

source:eda/data/Docs/trac/sudoang/dbeel_rivers.basinunit_bu.png

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


source:eda/data/Docs/trac/sudoang/unit_basins.png

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_score0Sum of height of dams with a fishway for eel and good score
cs_height_10_score1Sum 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_PTSum 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"
Last modified 2 months ago Last modified on Aug 29, 2021, 4:20:28 PM
Note: See TracWiki for help on using the wiki.