CookBook join ROE_RHT – EDA

Context Navigation

back to first page..
back to CookBook Eda
back to RHT
see #79

Integrating new ROE_v2 data into RHT

Lambert_Conformal_Conic --> ETRS LAEA srid 3035 with ArcGis

d:
cd D:\CelineJouanin\ROE2011
C:\"Program Files"\PostgreSQL\8.4\bin\shp2pgsql -s 3035 -c -g the_geom -W LATIN1 -I ROE_v2_Project.shp roe_v2 > roe_v2.sql
C:\"Program Files"\PostgreSQL\8.4\bin\psql -d eda2.0_RHT -h localhost -U postgres -p 5432 -f roe_v2.sql

create schema roe_v2;
alter table roe_v2 set schema roe_v2;

---Integrating pre_ice_v2.csv
See in ["CookBook export access"] to integrate the database into postgre from ACCESS (it's usefull when you got lots of variables in the table) This allows us to create the table quickly. 
alter table roe_geo_v2 set schema roe_v2;
ALTER TABLE roe_v2.roe_geo_v2 RENAME COLUMN "Id_ROE" TO Id_ROE;
ALTER TABLE roe_v2.roe_geo_v2 RENAME COLUMN "XCartL93" TO XCartL93;
ALTER TABLE roe_v2.roe_geo_v2 RENAME COLUMN "YCartL93" TO YCartL93;
ALTER TABLE roe_v2.roe_geo_v2 RENAME COLUMN "XCartL2e" TO XCartL2e;
ALTER TABLE roe_v2.roe_geo_v2 RENAME COLUMN "YCartL2e" TO YCartL2e;
ALTER TABLE roe_v2.roe_geo_v2 RENAME COLUMN "IdTrCart" TO IdTrCart;
ALTER TABLE roe_v2.roe_geo_v2 RENAME COLUMN "CHCart" TO CHCart;
ALTER TABLE roe_v2.roe_geo_v2 RENAME COLUMN "CGCart" TO CGCart;
ALTER TABLE roe_v2.roe_geo_v2 RENAME COLUMN "nomCart" TO nomCart;
ALTER TABLE roe_v2.roe_geo_v2 RENAME COLUMN "ZHCart" TO ZHCart;
ALTER TABLE roe_v2.roe_geo_v2 RENAME COLUMN "XTopoL93" TO XTopoL93;
ALTER TABLE roe_v2.roe_geo_v2 RENAME COLUMN "YTopoL93" TO YTopoL93;
ALTER TABLE roe_v2.roe_geo_v2 RENAME COLUMN "XTopoL2e" TO XTopoL2e;
ALTER TABLE roe_v2.roe_geo_v2 RENAME COLUMN "YTopoL2e" TO YTopoL2e;
ALTER TABLE roe_v2.roe_geo_v2 RENAME COLUMN "IdTopo" TO IdTopo;
ALTER TABLE roe_v2.roe_geo_v2 RENAME COLUMN "nomTopo" TO nomTopo;
ALTER TABLE roe_v2.roe_geo_v2 RENAME COLUMN "commCd" TO commCd;
ALTER TABLE roe_v2.roe_geo_v2 RENAME COLUMN "commNom" TO commNom;
ALTER TABLE roe_v2.roe_geo_v2 RENAME COLUMN "deptCd" TO deptCd;
ALTER TABLE roe_v2.roe_geo_v2 RENAME COLUMN "deptNom" TO deptNom;
ALTER TABLE roe_v2.roe_geo_v2 RENAME COLUMN "massdoCd" TO massdoCd;
ALTER TABLE roe_v2.roe_geo_v2 RENAME COLUMN "ID_troncon_syrah" TO ID_troncon_syrah;
ALTER TABLE roe_v2.roe_geo_v2 RENAME COLUMN "PKCart" TO PKCart;
ALTER TABLE roe_v2.roe_geo_v2 RENAME COLUMN "etatNom" TO etatNom;

---Integrating roe_geo_v2.csv
See ["CookBook export access"]
alter table pre_ice_v2 set schema roe_v2;
ALTER TABLE roe_v2.pre_ice_v2 RENAME COLUMN "Id_ROE" TO Id_ROE;
ALTER TABLE roe_v2.pre_ice_v2 RENAME COLUMN "etatNom" TO etatNom;
ALTER TABLE roe_v2.pre_ice_v2 RENAME COLUMN "note_fr_Anguille" TO note_fr_Anguille;
ALTER TABLE roe_v2.pre_ice_v2 RENAME COLUMN "note_fr_Alose" TO note_fr_Alose;
ALTER TABLE roe_v2.pre_ice_v2 RENAME COLUMN "note_fr_Alose_feinte" TO note_fr_Alose_feinte;
ALTER TABLE roe_v2.pre_ice_v2 RENAME COLUMN "note_fr_Apron" TO note_fr_Apron;
ALTER TABLE roe_v2.pre_ice_v2 RENAME COLUMN "note_fr_Brochet" TO note_fr_Brochet;
ALTER TABLE roe_v2.pre_ice_v2 RENAME COLUMN "note_fr_CypEC" TO note_fr_CypEC;
ALTER TABLE roe_v2.pre_ice_v2 RENAME COLUMN "note_fr_CypEV" TO note_fr_CypEV;
ALTER TABLE roe_v2.pre_ice_v2 RENAME COLUMN "note_fr_Grale" TO note_fr_Grale;
ALTER TABLE roe_v2.pre_ice_v2 RENAME COLUMN "note_fr_LamproieM" TO note_fr_LamproieM;
ALTER TABLE roe_v2.pre_ice_v2 RENAME COLUMN "note_fr_OmbreComm" TO note_fr_OmbreComm;
ALTER TABLE roe_v2.pre_ice_v2 RENAME COLUMN "note_fr_PEBenth" TO note_fr_PEBenth;
ALTER TABLE roe_v2.pre_ice_v2 RENAME COLUMN "note_fr_Salmonides" TO note_fr_Salmonides;
ALTER TABLE roe_v2.pre_ice_v2 RENAME COLUMN "note_fr_Saumon" TO note_fr_Saumon;
ALTER TABLE roe_v2.pre_ice_v2 RENAME COLUMN "note_fr_TruiteMer" TO note_fr_TruiteMer;

Integrating ROE data from Hervé into RHT

Changement du système de projection via ArcGis roe_snap50m_rht.shp from Hervé (Lambert II étendu) --> srid 3035 with roe_snap50m_rht_Project.shp

d:
cd D:\CelineJouanin\RHT_Estimkart
C:\"Program Files"\PostgreSQL\8.4\bin\shp2pgsql -s 3035 -c -g the_geom -W LATIN1 -I roe_snap50m_rht_Project.shp rht_roe > rht_roe.sql 
C:\"Program Files"\PostgreSQL\8.4\bin\psql -d eda2.0_RHT -h localhost -U postgres -p 5432 -f rht_roe.sql

alter table rht_roe set schema rht

Problème pas les identifiant roe (id_roe) dans la table

Joining ROE with RHT

DROP TABLE IF EXISTS rht.rht_roev2;
CREATE TABLE rht.rht_roev2 as (
        SELECT distinct on (id_roe) id_roe, id_drain, min(distance) as distance, the_geom FROM (
               SELECT id_roe, id_drain ,CAST(distance(r.the_geom, s.the_geom) as  decimal(15,1)) as distance, s.the_geom 
               FROM roe_v2.roe_v2 As s
               INNER JOIN  rht.rht r ON ST_DWithin(r.the_geom, s.the_geom,300)
               WHERE s.the_geom IS NOT NULL
               ORDER BY id_roe) AS sub 
        GROUP BY id_roe, distance,id_drain, the_geom  ---49269 lines
); 
alter table rht.rht_roev2 add column id serial;
-- mise à jour de la table geometry_columns
INSERT INTO geometry_columns(f_table_catalog, f_table_schema, f_table_name, f_geometry_column, coord_dimension, srid, "type")
SELECT '', 'rht', 'rht_roev2', 'the_geom', ST_CoordDim(the_geom), ST_SRID(the_geom), GeometryType(the_geom)
FROM rht.rht_roev2 LIMIT 1;

-- creation d'index, clé primaire, et constraintes qui vont bien
alter table rht.rht_roev2 add CONSTRAINT enforce_dims_the_geom CHECK (ndims(the_geom) = 2);
alter table rht.rht_roev2 add  CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) = 'POINT'::text OR the_geom IS NULL);
alter table rht.rht_roev2 add  CONSTRAINT enforce_srid_the_geom CHECK (srid(the_geom) = 3035);
alter table rht.rht_roev2 ADD CONSTRAINT pk_idroe PRIMARY KEY(id);
alter table rht.rht_roev2 drop CONSTRAINT pk_idroe;
CREATE INDEX indexroev2_rht ON rht.rht_roev2
  USING GIST ( the_geom GIST_GEOMETRY_OPS );

Vérification Calcul distance entre ROE et noeud Bdcarthage (j'ai pas de couche avec les noeuds pour RHT)

A FAIRE ! mais difficile de s'y retrouver

Transborder dams

FID	Shape *	id_trhyd	NATURE	TOPONYME	CANDIDAT	COTE	SITUATION	MER	barrage	Nom	wso_id correspondant	UGA	id_drain RHT
6297	Point	100007688	Source simple	9999			frontiere	358	23	Meuse	291130	MEUSE
493235	Point	150001278	Source simple	9999			frontiere	149	23	Meuse	/	MEUSE
494054	Point	250002088	Source simple	9999			frontiere	331	23	Meuse	291130	MEUSE
495249	Point	250003393	Source simple	9999			frontiere	593	12	Moselle	291110	RHIN
495588	Point	250003736	Source simple	9999			frontiere	586	17	Sarre	291110	RHIN
496464	Point	250004650	Embouchure	9999			frontiere	575	17	Sarre	291110	RHIN

Joining ROE and BDCarthage

select r.*, e.* from bd_carthage2011.troncon_hydrographique r inner join roe_v2.roe_geo_v2 as e on e.idtrcart=CAST("id_bdcarth" AS character varying(25))

Joining ROE and RHT with rht.rht_bdcarthage

drop table if exists rht.rht_bdcarthage_roev2;
create table rht.rht_bdcarthage_roev2 as
select geo.*, bdc.*, roe.the_geom, roe.nom, roe.typecd, roe.typenom, roe.stypecd, roe.stypenom, roe.stanom, roe.etatcd, roe.source 
from roe_v2.roe_geo_v2 as geo
join  roe_v2.roe_v2 as roe on roe.id_roe=geo.id_roe
join rht.rht_bdcarthage2 bdc on bdc.id_bdcarth=cast(geo.idtrcart as integer); ---49212 lines
CREATE INDEX indexrhtbdcroe
ON rht.rht_bdcarthage_roev2
USING btree (id_roe);
alter table rht.rht_bdcarthage_roev2 add CONSTRAINT enforce_dims_the_geom CHECK (ndims(the_geom) = 2);
alter table rht.rht_bdcarthage_roev2 add  CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) = 'POINT'::text OR the_geom IS NULL);
alter table rht.rht_bdcarthage_roev2 add  CONSTRAINT enforce_srid_the_geom CHECK (srid(the_geom) = 3035);
alter table rht.rht_bdcarthage_roev2 ADD CONSTRAINT pk_idroe PRIMARY KEY(id_roe);
CREATE INDEX indexrht_bdc_roev2_geom
  ON rht.rht_bdcarthage_roev2
  USING gist (the_geom GIST_GEOMETRY_OPS);

Comment on table rht.rht_bdcarthage_roev2 IS 'Jointure entre id_drain et id_roe à partir de id_bdcarthage - Table à utiliser pour récupérer les barrages du ROE';

d:
cd D:\CelineJouanin\export_schema
C:\"Program Files"\PostgreSQL\8.4\bin\pg_dump  -U postgres -p 5432 -t rht.rht_bdcarthage_roev2 eda2.0_RHT> rht_bdcarthage_roev2.sql

A FINIR

select count(*) from roe_v2.pre_ice_v2 where hauteur_chute='-999'   ---31611 lines

Ci-dessous la requête pour la jointure entre les différentes tables :
SELECT 
  rht_topology.id_drain, 
  roe_geo_v2.idtrcart, 
  roe_geo_v2.id_roe, 
  pre_ice_v2.hauteur_chute, 
  rht_topology.the_geom, 
  rht_topology.fnode, 
  rht_topology.tnode, 
  rht_topology.length, 
  rht_topology.nextdownid, 
  rht_topology.noeudmer, 
  rht_topology.cumnbbar, 
  rht_topology.dmer, 
  rht_topology.id_draintopo, 
  pre_ice_v2.hauteur_terrain, 
  pre_ice_v2.note_fr_anguille
FROM 
  rht.rht_topology, 
  roe_v2.roe_geo_v2, 
  roe_v2.pre_ice_v2, 
  rht.rht_bdcarthage2
WHERE 
  roe_geo_v2.id_roe = pre_ice_v2.id_roe AND
  rht_bdcarthage2.id_drain = rht_topology.id_drain AND
  rht_bdcarthage2.id_bdcarth = cast(roe_geo_v2.idtrcart as bigint) ;  ---49212 lines

Données pre_ice_2 --- problème avec les hauteurs de chute non renseignées.

select count(*) from roe_v2.pre_ice_v2 where hauteur_chute='-999';  ---31611 lines non renseignées
select * from roe_v2.pre_ice_v2 where hauteur_chute='-999' and hauteur_terrain>0;   ---663 lines renseignés

New version de rht_bdcarthage_roev2 avec données pre_ice_v2 et rht_topology

drop table if exists rht.rht_bdcarthage_roev2;
create table rht.rht_bdcarthage_roev2 as
select geo.*, bdc.*, topo.fnode, topo.tnode, topo.length, topo.nextdownid, topo.noeudmer, topo.cumnbbar, topo.dmer, topo.id_draintopo,
roe.the_geom, roe.nom, roe.typecd, roe.typenom, roe.stypecd, roe.stypenom, roe.stanom, roe.etatcd, roe.source,
ice.etatnom as etatnomice, ice.hauteur_terrain, ice.hauteur_chute, ice.note_fr_anguille, ice.usage1, ice.fpi1 from roe_v2.roe_geo_v2 as geo
join  roe_v2.roe_v2 as roe on roe.id_roe=geo.id_roe
join rht.rht_bdcarthage2 bdc on bdc.id_bdcarth=cast(geo.idtrcart as integer)
join roe_v2.pre_ice_v2 as ice on roe.id_roe=ice.id_roe
join rht.rht_topology as topo on bdc.id_drain=topo.id_drain; ---49212 lines

alter table rht.rht_bdcarthage_roev2 add column nbdams integer;
update rht.rht_bdcarthage_roev2 set nbdams=1;

---Clause group by par id_bdcarthage
select id_drain, count(nbdams) as nbdams from rht.rht_bdcarthage_roev2 group by id_drain;

drop table if exists rht.rht_bdcarthage_roev2_nbdams;
create table rht.rht_bdcarthage_roev2_nbdams as
select id_drain, count(nbdams) as nbdams from rht.rht_bdcarthage_roev2 as r group by id_drain;

alter table rht.rht_bdcarthage_roev2_nbdams add column height integer;
alter table rht.rht_bdcarthage_roev2_nbdams add column score integer;
update rht.rht_bdcarthage_roev2_nbdams set height=1;
update rht.rht_bdcarthage_roev2_nbdams set score=1;

2625 obstacles du ROE non projetés car ils ne possèdent pas d'identifiant bdcarthage dans roe_geo_v2.

-- calcul du nb de barrages cumulé sous R et réimport

create table rht.cumnbbar(
        id_drain integer primary key,
        cumnbbar integer);
copy rht.cumnbbar from 'E:/workspace/EDAdata/dataEDArht/cumnbbar.csv' with csv header delimiter as ';' NULL AS 'NA';--114600


-- mise à jour des cumuls barrage

update rht.rht_topology t set cumnbbar=sub.cumnbbar from (
select id_drain, cumnbbar from rht.cumnbbar) as sub
where t.id_drain=sub.id_drain

-- Vérification --

drop table if exists rht.rht_roe_test;
create table rht.rht_roe_test as (
select distinct on (id_drain) id_drain, iddrain, id_roe, idroe from (
select bd.id_drain, ro.id_drain as iddrain, bd.id_roe, ro.id_roe as idroe from rht.rht_bdcarthage_roev2 bd join rht.rht_roev2 ro on bd.id_roe=ro.id_roe) as sub where iddrain!=id_drain);

alter table rht.rht_roe_test add column the_geom geometry;
update rht.rht_roe_test set the_geom=roe_v2.the_geom from roe_v2.roe_v2
 where roe_v2.id_roe=rht_roe_test.idroe;

Il y a 5695 barrages qui sont potentiellement mal ou pas projetés. Hum ; difficile de les vérifier à la main un par un.

Last modified 13 years ago Last modified on Oct 3, 2012 4:57:32 PM

Download in other formats:

Plain Text