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_bdcarthage bdc on bdc.id_bdcarth=cast(geo.idtrcart as integer); ---49399 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

}}}

}}}