back to first page [..][[BR]]

= Description =
Pour le projet POSE, une base de données a été développée par Laurent Beaulaton, François Gatel et Erik Baglinière de l’ONEMA (data provider dans le projet POSE). [[BR]]
L'intégration des données dans la DBeel des différents pays européens a été en partie effectuée par Cédric Briand de l’IAV. [[BR]]
Ainsi les données françaises, espagnoles, irlandaises ont déjà été intégrées. Il reste à intégrer les données anglaises. Et il faudrait aussi intégrer les données de l'INRA et le l'IAV sur les migrations, les données de captures. Il reste probablement à programmer quelques vues pour sortir par exemple un tableau avec les toutes les données par passage. [[BR]]
Les infos sur l'intégration des données sont sous le trac http://trac.eptb-vilaine.fr:8066/trac/wiki/dbeel avec francois et francois*** comme code et mot de passe d'accès.[[BR]]

= Scripts de creation ONEMA =
    * source:trunk/EDAcommun/sql/implementation_pose/1-language_schema.sql
    * source:trunk/EDAcommun/sql/implementation_pose/2-function.sql
    * source:trunk/EDAcommun/sql/implementation_pose/3-nomenclatures.sql
    * source:trunk/EDAcommun/sql/implementation_pose/4-Squelette.sql
    * source:trunk/EDAcommun/sql/implementation_pose/5-obs_scientifique.sql
    * source:trunk/EDAcommun/sql/implementation_pose/6-PressionImpact.sql
    * source:trunk/EDAcommun/sql/implementation_pose/7-Biological_Process.sql
    * source:trunk/EDAcommun/sql/implementation_pose/db_feeding.sql
    * source:trunk/EDAcommun/sql/implementation_pose/db_feeding_BDMAP.sql 
    * source:trunk/EDAcommun/sql/implementation_pose/db_feeding_wrbd.sql 
    * source:trunk/EDAcommun/sql/implementation_pose/db_feeding_oria.sql 
= inserting BDMAP =
I already have a dump of the BDMAP somewhere so I'm just saving it and will use it to insert data into the debile.
{{{
sauvegarde
cd C:\eda\backup
C:\"Program Files"\PostgreSQL\8.4\bin\pg_dump  -U postgres -p 5433 --schema=bdmap BDMAP> bdmap.bdmap.sql
restauration
C:\"Program Files"\PostgreSQL\8.4\bin\psql -U postgres -d dbeel -p 5433 -f bdmap.bdmap.sql
}}}

= BDMAP changes by cedric =

changeset:2485 download of base modif from Laurent, the 10 files above [[BR]]
changeset:2486 in the script I first include dbeel.establishment and data.provider, and I'm not copying from .csv files anymore, station finished [[BR]]
changeset:2487 finishing changes in electrofishing, changes brought to operation  [[BR]]
changeset:2488 finishing changes in operations [[BR]]
changeset:2489 included number in optaxon; added stages to batch. [[BR]]
changeset:2490 optaxon, passtaxon batch. [[BR]]
changeset:2491 lotpeche [[BR]]
changeset:2492 lotpeche mensurationindiv[[BR]]

= inserting Western River Basin District (Ireland) =

{{{
REM sauvegarde
cd C:\eda\backup
C:\"Program Files"\PostgreSQL\8.4\bin\pg_dump  -U postgres -p 5433 --schema=wrbd -T wrbd.irish_catchments -T wrbd.irish_lakes -T wrbd.irish_water_bodies -T wrbd.outline_of_ireland -T wrbd.wfd_lakesegment_we -T wrbd.wfd_riversegment_we -T wrbd.wfd_riversubbasins_we eda2.0> eda2.0.wrbd.sql
REM restauration, restoring the whole schema preserves indexes and foreign keys
C:\"Program Files"\PostgreSQL\8.4\bin\psql -U postgres -d dbeel -p 5433 -f eda2.0.wrbd.sql
}}}

changeset:2494 wrbd [[BR]]
changeset:2495 wrbd batch and biological characteristics integrated stopped at migration monitoring [[BR]]
changeset:2496 wrbd finished [[BR]]



= inserting Oria (Spain) =
{{{
REM sauvegarde
cd C:\eda\backup
C:\"Program Files"\PostgreSQL\8.4\bin\pg_dump  -U postgres -p 5433 --schema=oria  eda2.0> eda2.0.oria.sql
REM restauration, restoring the whole schema preserves indexes and foreign keys
C:\"Program Files"\PostgreSQL\8.4\bin\psql -U postgres -d dbeel -p 5433 -f eda2.0.oria.sql
}}}

changeset:2498 oria [[BR]]
there are some other fishes not integrated yet
{{{
sauvegarde
cd C:\eda\backup
C:\"Program Files"\PostgreSQL\8.4\bin\pg_dump  -U postgres -p 5433 dbeel> dbeel_02012010.sql
restauration
C:\"Program Files"\PostgreSQL\8.4\bin\psql -U postgres -d dbeel -p 5433 -f dbeel_02012010.sql
}}}


= inserting ANGLIAN (All data from UK database) =


changeset:3496 anglian [[BR]]
 * Integration of english data for electrofishing 
 * anglian.station all stations[[BR]]
 * anglian.operation contains all operations
 * anglian.electrofishing all electrofishing but not other surveys
 * anglian.batchope all operations
 * anglian.batchfish all fishes
 * anglian.mensurationindiv_biol_charac data for fish length and weight

 * three kind of stages are entered at the batch level. Glass eel, Yellow eel, and glass yellow and silver mixed.
 * The data for gear fishing are still to be integrated at line 1102
 * I've kept the example of western to do that.
{{{
sauvegarde
F:
cd F:\IAV\eda\backup
C:\"Program Files"\PostgreSQL\9.0\bin\pg_dump  -U postgres -p 5432 dbeel> dbeel_13072010.sql
restauration
C:\"Program Files"\PostgreSQL\8.4\bin\psql -U postgres -d dbeel -p 5433 -f dbeel_13072010.sql
}}}


saving for Alan
{{{
C:\"Program Files"\PostgreSQL\8.4\bin\pgsql2shp -f "C:\eda\exports_shape\dbeel.observation_places" -p 5433 -u postgres -P postgres -g the_geom -r -k dbeel "select * from dbeel.observation_places where the_geom is not null";"
}}}

{{{
select count(*) from dbeel.biological_characteristic;--400000
select count(*) from dbeel.observations;--27000
select count(*) from dbeel.observation_places;--11271
select count(*) from dbeel.biological_characteristic;--402000
select count(*) from dbeel.observations;--27000
select count(*) from dbeel.observation_places;--11271
}}}