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 [[BR]]
== 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||||