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

== Nouvelle version d'octobre 2011 du RHT et topologie (fnode,tnode) ==
{{{
#!sql
select count(*) from rht.rht ---114601  (version1 avec pb de topologie)
select count(*) from rht.rhtvs2  ---114564 (version octobre)

select id_drain from rht.rht where id_drain not in (select id_drain from rht.rhtvs2)  ---37 id_drain supprimés
}}}
Nouvelle version d'octobre 2011 avec suppression des problèmes de topologie rencontrés (Loire, Seine Normandie) et amélioration du réseau.[[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
{{{
#!sql
D:
cd D:\CelineJouanin\RHT_Estimkart\RHT_October_newversion
C:\"Program Files"\PostgreSQL\8.4\bin\shp2pgsql -s 3035 -c -g the_geom -W LATIN1 -I rhtvs2_3035.shp rhtvs2 > rhtvs2.sql 
C:\"Program Files"\PostgreSQL\8.4\bin\psql -d eda2.0_RHT -h localhost -U postgres -p 5432 -f rhtvs2.sql 

ALTER TABLE rhtvs2 SET SCHEMA rht;

-- Rename column
ALTER TABLE rht.rhtvs2 RENAME COLUMN fnode_ TO fnode;
ALTER TABLE rht.rhtvs2 RENAME COLUMN tnode_ TO tnode;

-- Change type
alter table rht.rhtvs2 alter column id_drain type integer;
alter table rht.rhtvs2 alter column fnode type integer;
alter table rht.rhtvs2 alter column tnode type integer;

-- Constraints
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
CREATE INDEX indexrhtvs2 ON rht.rhtvs2
  USING GIST ( the_geom GIST_GEOMETRY_OPS );

DROP INDEX IF EXISTS rht.indexrhtvs2;
CREATE INDEX indexrhtv2
ON rht.rhtvs2
USING btree (id_drain);
}}}


== Noeuds mer RHT ==
{{{
#!sql
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 noeudmervs2 to know the downstream node from the sea';

select count(*) from rht.noeudmervs2; ---1082 noeudmer  (version 1: 1114)

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); --1082 noeudmer
}}}

== Nextdownid ==
{{{
#!sql
create table rht.fromnodetonodevs2 as (select taval.id_drain as id_drainaval, tamont.id_drain as id_drainamont from rht.rhtvs2 tamont join rht.rhtvs2 taval on tamont.tnode=taval.fnode);
alter table rht.rhtvs2 add column nextdownid integer;
update rht.rhtvs2 SET nextdownid=id_drainaval from rht.fromnodetonodevs2 ft where id_drain=ft.id_drainamont;


-- correction d'un problème par Cédric
select  * from rht.rhtvs2 where id_drain=nextdownid
update rht.rhtvs2 set nextdownid=-9999 where id_drain=nextdownid --505
}}}

== Identification des différentes zones, manche, Bretagne, Méditerranée, Golfe de Gascogne, Est France, Pyrénnées ==
{{{
#!sql
alter table rht.noeudmervs2 add column zonegeo character varying(30);
update rht.noeudmervs2 n set zonegeo=location from(
select location,id_drain from rht.noeudmer_polygon p
join  rht.noeudmervs2 n on st_intersects(p.the_geom,n.the_geom)) s
where s.id_drain=n.id_drain; -- 1082
update rht.noeudmervs2 set zonegeo='Golfe de Gascogne' where zonegeo='Biscaye';  --188 lignes
select * from rht.noeudmervs2 where zonegeo is null; -- zero lignes
}}}

== Joining ROE with RHT ==
Projection spatiale des obstacles (points) sur le RHT (lignes) avec un buffer de 300 m [[BR]]
{{{
#!sql
DROP TABLE IF EXISTS rht.rhtvs2_roev2;
CREATE TABLE rht.rhtvs2_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.rhtvs2 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.rhtvs2_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', 'rhtvs2_roev2', 'the_geom', ST_CoordDim(the_geom), ST_SRID(the_geom), GeometryType(the_geom)
FROM rht.rhtvs2_roev2 LIMIT 1;

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


#A FINIR ci-dessous !! 
== 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