Changes between Version 2 and Version 3 of Noeud - parcours RHT

Timestamp:

Jan 4, 2012 2:47:00 PM (13 years ago)

Author:

celine

Comment:

--

Noeud - parcours RHT

@@ -2 +2 @@
 back to ["RHT"][[BR]]
 
+-- INTEGRATION DU RHT et sa TOPOLOGIE --
 Les données ont été préalablement projetées en 3035 sous !ArcGis[[BR]]
 Intégration de la table avec id_drain, fnode, tnode
@@ -12 +13 @@
 ALTER TABLE rhtvs2 SET SCHEMA rht;
 
+ALTER TABLE rht.rhtvs2 RENAME COLUMN fnode_ TO fnode;
+ALTER TABLE rht.rhtvs2 RENAME COLUMN tnode_ TO tnode;
+
 -- Constraints
-ALTER TABLE rht.rhtvs2 DROP CONSTRAINT rht_pkey;
-ALTER TABLE rht.rhtvs2 ADD CONSTRAINT  pk_id_drain PRIMARY KEY (id_drain);
+ALTER TABLE rht.rhtvs2 DROP CONSTRAINT rhtvs2_pkey;
+ALTER TABLE rht.rhtvs2 ADD CONSTRAINT  pkey_id_drain PRIMARY KEY (id_drain);
 
 -- Create an index on rht
@@ -21 +25 @@
 
 DROP INDEX IF EXISTS rht.indexrhtvs2;
-CREATE INDEX indexrht2
+CREATE INDEX indexrhtv2
 ON rht.rhtvs2
 USING btree (id_drain);
@@ -27 +31 @@
 
 
+-- Noeuds mer RHT --
+{{{
+drop table if exists rht.noeudmervs2;
+create table rht.noeudmervs2 as select * from rht.rhtvs2 where tnode not in (select fnode from rht.rhtvs2);
+CREATE INDEX indexmer ON rht.noeudmervs2 USING btree (id_drain);
+CREATE INDEX indexmergeom ON rht.noeudmervs2 USING GIST ( the_geom GIST_GEOMETRY_OPS );
+COMMENT ON table rht.noeudmervs2 IS 'Table test to know the downstream node from the sea';
 
+select count(*) from rht.noeudmervs2; --- 1114 lines
+
+Intégration des noeuds mer dans la table rhtvs2.
+
+alter table rht.rhtvs2 add column noeudmer boolean default FALSE;
+update rht.rhtvs2 set noeudmer=TRUE
+where id_drain in (select id_drain from rht.noeudmervs2 where noeudmer); --1083
+
+Identification des différentes zones, manche, Bretagne, Méditerranée, Golfe de Gascogne, Est France, Pyrénnées, pb topologie centre France.
+
+alter table rht.noeudmervs2 add column zonegeo character varying(30);
+alter table rht.noeudmervs2_polygon add column location character varying(30);
+update rht.noeudmervs2_polygon set location='pb topologie centre france' where location is null;
+update rht.noeudmervs2 n set zonegeo=location from(
+Select location,id_drain from rht.noeudmervs2_polygon p
+join  rht.noeudmervs2 n on st_intersects(p.the_geom,n.the_geom)) s
+where s.id_drain=n.id_drain; -- 1114
+update rht.noeudmervs2 set zonegeo='Golfe de Gascogne' where zonegeo='Biscaye';
+select * from rht.noeudmervs2 where zonegeo is null; -- zero lignes
+
+comment aller chercher les infos sur le nombre de barrages et la distance mer, les données sont dans la ccm ?
+
+installation de dblink (je suis aller chercher le code sql dans contrib), dblink s'installe dans une base de données PostgreSQL: pgsql/contrib/dblink/dblink.sql
+
+/* cross database query*/
+-- je crée une table contenant la ccm.riversegments pour la France avec les colonnes qui m'intéressent.
+
+select dblink_connect('connectionccm','hostaddr=93.20.247.238 port=5432 dbname=eda2 user=postgres password=petromyzon***');
+create schema ccm;
+drop table if exists ccm.riversegments_france;
+create table ccm.riversegments_france as (
+SELECT * FROM dblink('connectionccm','SELECT wso1_id,cs_nbdams,cum_len_sea,the_geom  FROM ccm21.riversegments r join europe.wso w on r.wso_id=w.wso_id where area=''France''')
+ AS  t(wso1_id int, cs_nbdams integer, cum_len_sea numeric,the_geom geometry)
+);
+
+-- je vais chercher dans la ccm les informations sur les distances mer et les cumuls de barrages
+
+select distinct on (zonegeo) zonegeo from rht.noeudmervs2;
+
+alter table rht.noeudmervs2 add column cumnbbar integer;
+alter table rht.noeudmervs2 add column dmer numeric;
+alter table rht.noeudmervs2 add column wso1_id integer;
+
+
+update rht.noeudmervs2 set (cumnbbar,dmer,wso1_id)= (jointure.cs_nbdams,jointure.cum_len_sea,jointure.wso1_id) from
+        -- resultat final de la jointure spatiale entre ccm et noeudfrontiere
+        (select distinct on (id_drain) id_drain, min(distance) as distance, wso1_id,cs_nbdams, cum_len_sea from (
+                -- requete intermédiaire dont on prend le min (sub)
+                select id_drain, ST_distance(noeudfrontiere.the_geom,ccmrs.the_geom) as distance,ccmrs.*
+                FROM ccm.riversegments_france ccmrs join
+                (select id_drain, the_geom from rht.noeudmervs2 where noeudmer and zonegeo in ('Est','pyrénées')) as noeudfrontiere
+                on ST_DWithin(noeudfrontiere.the_geom,ccmrs.the_geom, 500)) as sub
+        group by id_drain, distance, wso1_id,cs_nbdams, cum_len_sea) as jointure
+where noeudmer.id_drain=jointure.id_drain; --100 lignes modifiées
+
+=> sauvegarde pour Céline
+
+e:
+cd E:\IAV\eda\rht
+pg_dump  -U postgres -p 5432 -t rht.rht_topology -t rht.noeudmervs2 -t rht.noeudmervs2_polygon eda2.0_RHT> sauve3tablesnoeudsmer.sql
+
+---Celine
+d:
+cd D:\CelineJouanin\EDA20RHT
+C:\"Program Files"\PostgreSQL\8.4\bin\psql -d eda2.0_RHT -h localhost -U postgres -p 5432 -f sauve3tablesnoeudsmer.sql
+
+Mise à jour de la table rht_topology
+
+alter table rht.rht_topology add column cumnbbar integer;
+alter table rht.rht_topology add column dmer numeric;
+alter table rht.rht_topology drop column wso1_id ;
+
+update  rht.noeudmervs2 set dmer=0 where dmer is null and noeudmer; -- 983 -- a modifier pour prendre peut être la moitié de la distance du tronçon... a voir avec Céline
+select * from rht.noeudmervs2 where dmer>0; -- il n'a pas trouvé de bassin dans les pyrénées... bizarre.... Mais on vivra bien avec !!!
+-- mise à jour des données dans rht_topology
+update rht.rht_topology set (dmer, cumnbbar)=(sub.dmer, sub.cumnbbar)
+from (select id_drain,dmer,cumnbbar from rht.noeudmervs2 where dmer is not null and noeudmer) sub
+where sub.id_drain=rht_topology.id_drain; --1083
+
+