back to first page..
back to RHT

Noeuds mer RHT

Noeuds mer

select * from rht.rht r left join rht.topologie_rht_sept2011 t on r.id_drain=t.id_draintopo where id_draintopo is null;    --- 1 lines

drop table if exists rht.rht_topology;
CREATE table rht.rht_topology AS select * from rht.rht r inner join rht.topologie_rht_sept2011 t on t.id_draintopo=r.id_drain;
CREATE INDEX indextopo ON rht.rht_topology USING btree (id_draintopo);
CREATE INDEX indextopogeom ON rht.rht_topology
  USING GIST ( the_geom GIST_GEOMETRY_OPS );

COMMENT ON table rht.rht_topology IS 'Joining between rht and topologie_rht_sept2011 to see it on Qgis';

drop table if exists rht.noeudmer;
create table rht.noeudmer as select * from rht.rht_topology where tnode not in (select fnode from rht.topologie_rht_sept2011);
CREATE INDEX indexmer ON rht.noeudmer USING btree (id_drain);
CREATE INDEX indexmergeom ON rht.noeudmer USING GIST ( the_geom GIST_GEOMETRY_OPS );
COMMENT ON table rht.noeudmer IS 'Table test to know the downstream node from the sea';

select count(*) from rht.noeudmer; --- 1114 lines

Intégration des noeuds mer dans la table rht_topology.

alter table rht.rht_topology add column noeudmer boolean default FALSE;
update rht.rht_topology set noeudmer=TRUE
where id_drain in (select id_drain from rht.noeudmer 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.noeudmer add column zonegeo character varying(30);
alter table rht.noeudmer_polygon add column location character varying(30);
update rht.noeudmer_polygon set location='pb topologie centre france' where location is null;
update rht.noeudmer n set zonegeo=location from(
Select location,id_drain from rht.noeudmer_polygon p
join  rht.noeudmer n on st_intersects(p.the_geom,n.the_geom)) s
where s.id_drain=n.id_drain; -- 1114
update rht.noeudmer set zonegeo='Golfe de Gascogne' where zonegeo='Biscaye';
select * from rht.noeudmer where zonegeo is null; -- zero lines

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.noeudmer;

alter table rht.noeudmer add column cumnbbar integer;
alter table rht.noeudmer add column dmer numeric;
alter table rht.noeudmer add column wso1_id integer;


update rht.noeudmer 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.noeudmer 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 lines modified

=> sauvegarde pour Céline

e:
cd E:\IAV\eda\rht
pg_dump  -U postgres -p 5432 -t rht.rht_topology -t rht.noeudmer -t rht.noeudmer_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.noeudmer 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.noeudmer 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.noeudmer where dmer is not null and noeudmer) sub
where sub.id_drain=rht_topology.id_drain; --1083