back to first page[..][[BR]]
back to ["RHT"] - ["Download and load RHT"][[BR]]
Go to ["CookBook join RHT-BDMAP"][[BR]]

NTF_France_II_degrees / NTF Lambert zone II (srid 27572) --> ETRS1989-LAEA (srid 3035)

== Integrating topology (rht_vs2_export_cjouanin_sept11.csv) ==

{{{
#!sql
drop table  if exists rht.topologie_rht_sept2011;
CREATE TABLE rht.topologie_rht_sept2011
(
  ID_DRAIN integer,
  Xcdg numeric,
  Ycdg numeric,
  Xstart numeric,
  Ystart numeric,
  Lg numeric,
  Gridid integer,
  Fnode integer,
  Tnode integer,
  Long numeric
);

copy rht.topologie_rht_sept2011 from 'D:/CelineJouanin/RHT_Estimkart/rht_vs2_export_cjouanin_sept11.csv' with csv delimiter as ';' header; 
alter table rht.topologie_rht_sept2011 add constraint  pk_id_drain_topo primary key (id_drain);
set client_encoding to 'latin1';
}}}

id_drain 30000 sans topologie [[BR]]
Create a view rht_topology
{{{
#!sql
Alter table rht.topologie_rht_sept2011 rename column Length to length;
ALTER TABLE rht.topologie_rht_sept2011 RENAME COLUMN id_drain TO id_draintopo;
}}}


== Integrating "noeuds" ==
{{{
--Celine
d:
cd D:\CelineJouanin\RHT_Estimkart
C:\"Program Files"\PostgreSQL\8.4\bin\shp2pgsql -s 27572 -c -g the_geom -W LATIN1 -I noeud_rhtot_3035.shp rht_noeud > rht_noeud.sql 
(I can't convert this version into utf8 with notepad)
C:\"Program Files"\PostgreSQL\8.4\bin\psql -d eda2.0_RHT -h localhost -U postgres -p 5432 -f rht_noeud.sql 

alter table rht_noeud set schema rht;

CREATE INDEX indexnoeud
ON rht.rht_noeud
USING btree (node_id);
}}}

Topology de l'amont vers l'aval.

=== Noeuds mer ===
{{{
#!sql
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';
}}}