back to first page..
back to RHT

Integrating BDCarthage2011 into eda2.0_RHT

---Céline
D:
cd D:\CelineJouanin\BDCarthage2011
C:\"Program Files"\PostgreSQL\8.4\bin\psql -d eda2.0_RHT -U postgres -p 5432 -f COURS_D_EAU.sql
C:\"Program Files"\PostgreSQL\8.4\bin\psql -d eda2.0_RHT -U postgres -p 5432 -f HYDROGRAPHIE_SURFACIQUE.sql
C:\"Program Files"\PostgreSQL\8.4\bin\psql -d eda2.0_RHT -U postgres -p 5432 -f LAISSE.sql 
C:\"Program Files"\PostgreSQL\8.4\bin\psql -d eda2.0_RHT -U postgres -p 5432 -f NOEUD_HYDROGRAPHIQUE.sql 
C:\"Program Files"\PostgreSQL\8.4\bin\psql -d eda2.0_RHT -U postgres -p 5432 -f POINT_EAU_ISOLE.sql 
C:\"Program Files"\PostgreSQL\8.4\bin\psql -d eda2.0_RHT -U postgres -p 5432 -f REGION_HYDROGRAPHIQUE.sql 
C:\"Program Files"\PostgreSQL\8.4\bin\psql -d eda2.0_RHT -U postgres -p 5432 -f SECTEUR.sql 
C:\"Program Files"\PostgreSQL\8.4\bin\psql -d eda2.0_RHT -U postgres -p 5432 -f SOUS_SECTEUR.sql 
C:\"Program Files"\PostgreSQL\8.4\bin\psql -d eda2.0_RHT -U postgres -p 5432 -f TRONCON_HYDROGRAPHIQUE.sql 
C:\"Program Files"\PostgreSQL\8.4\bin\psql -d eda2.0_RHT -U postgres -p 5432 -f ZONE_HYDROGRAPHIQUE.sql 


create schema bd_carthage2011;
alter table cours_d_eau set schema bd_carthage2011;
alter table hydrographie_surfacique set schema bd_carthage2011;
alter table laisse set schema bd_carthage2011;
alter table noeud_hydrographique set schema bd_carthage2011;
alter table point_eau_isole set schema bd_carthage2011;
alter table region_hydrographique set schema bd_carthage2011;
alter table secteur set schema bd_carthage2011;
alter table sous_secteur set schema bd_carthage2011;
alter table troncon_hydrographique set schema bd_carthage2011;
alter table zone_hydrographique set schema bd_carthage2011;

CREATE INDEX indextroncon
ON bd_carthage2011.troncon_hydrographique
USING btree (id_bdcarth);

Joining BdCarthage with RHT

select ST_IsClosed(the_geom) from rht.rht_200 order by ST_IsClosed(the_geom);
select ST_IsClosed(the_geom), id_bdcarth from bd_carthage2011.troncon_hydrographique order by ST_IsClosed(the_geom);  ---5 fermés

create table rht.rht_200 as select id_drain, gid, st_buffer(the_geom,200) as the_geom from rht.rht;
CREATE INDEX indexrht_200 ON rht.rht_200
  USING GIST ( the_geom GIST_GEOMETRY_OPS );

drop table if exists rht.rht_bdcarthage_200;
create table rht.rht_bdcarthage_200 as select r.*, id_bdcarth as gidbdc from rht.rht_200 r join bd_carthage2011.troncon_hydrographique bdc on ST_contains(r.the_geom,bdc.the_geom) order by id_drain;

Bretagne

see CookBook RHT_UGA and CookBook BDCarthage_UGA

DROP TABLE IF EXISTS rht.rht_Bretagne;   
CREATE TABLE  rht.rht_Bretagne AS
    SELECT * FROM rht.rht
        WHERE id_drain IN (SELECT id_drain FROM uga2010.id_drain WHERE uga='Bretagne');
ALTER TABLE rht.rht_Bretagne ADD CONSTRAINT pk_id_drain_rht_Bretagne PRIMARY KEY  (id_drain);
INSERT INTO geometry_columns(f_table_catalog, f_table_schema, f_table_name, f_geometry_column, coord_dimension, srid, "type")
SELECT '', 'rht', 'rht_Bretagne', 'the_geom', ST_CoordDim(the_geom), ST_SRID(the_geom), GeometryType(the_geom)
FROM rht.rht_Bretagne LIMIT 1;

DROP TABLE IF EXISTS bd_carthage2011.troncon_Bretagne;   
CREATE TABLE  bd_carthage2011.troncon_Bretagne AS
    SELECT * FROM bd_carthage2011.troncon_hydrographique
        WHERE id_bdcarth IN (SELECT id_bdcarth FROM uga2010.id_bdcarthage WHERE uga='Bretagne');
ALTER TABLE bd_carthage2011.troncon_Bretagne ADD CONSTRAINT pk_id_bdcarth_Bret PRIMARY KEY  (id_bdcarth);
--- en attendant utilise gid
alter table bd_carthage2011.troncon_Bretagne drop constraint pk_id_bdcarth_Bret
ALTER TABLE bd_carthage2011.troncon_Bretagne ADD CONSTRAINT pk_id_bdcarth_Bret PRIMARY KEY  (gid);

INSERT INTO geometry_columns(f_table_catalog, f_table_schema, f_table_name, f_geometry_column, coord_dimension, srid, "type")
SELECT '', 'bd_carthage2011', 'troncon_Bretagne', 'the_geom', ST_CoordDim(the_geom), ST_SRID(the_geom), GeometryType(the_geom)
FROM bd_carthage2011.troncon_Bretagne LIMIT 1;

drop table rht.rht_bretagne150;
create table rht.rht_bretagne150 as select id_drain, gid, st_buffer(the_geom,150) as the_geom from rht.rht_bretagne;   ---12578 ms
CREATE INDEX indexrht_bretagne150 ON rht.rht_bretagne150
  USING GIST ( the_geom GIST_GEOMETRY_OPS );

---essai1
select r.*, id_bdcarth as gidbdc,
ST_Distance(r.the_geom,bdc.the_geom) as distance, ST_HausdorffDistance(r.the_geom,bdc.the_geom) as st_hausdorffdistance 
from rht.rht_bretagne r, bd_carthage2011.troncon_bretagne bdc;

---essai2
select r.*, id_bdcarth as gidbdc,
ST_Distance(r.the_geom,bdc.the_geom) as distance, ST_HausdorffDistance(r.the_geom,bdc.the_geom) as st_hausdorffdistance 
from rht.rht_bretagne r, bd_carthage2011.troncon_bretagne bdc, rht.rht_bretagne300 as r300 where r.the_geom && bdc.the_geom AND ST_Intersects(bdc.the_geom,r300.the_geom);  ---96226 lines 152105ms

Create a layer with a polygon to select one river basin
See CookBook Quantum Gis Create polygon

d:
cd D:\CelineJouanin\EDA20RHT
C:\"Program Files"\PostgreSQL\8.4\bin\shp2pgsql -s 3035 -c -g the_geom -W LATIN1 -I clip_troncon.shp clip_troncon > clip_troncon.sql 
C:\"Program Files"\PostgreSQL\8.4\bin\psql -d eda2.0_RHT -h localhost -U postgres -p 5432 -f clip_troncon.sql 

Alter table clip_troncon set schema rht;
DROP TABLE IF EXISTS bd_carthage2011.troncon_bv;   
CREATE TABLE  bd_carthage2011.troncon_bv AS
    select bdc.* from bd_carthage2011.troncon_hydrographique bdc, rht.clip_troncon as clip where st_contains(clip.the_geom,bdc.the_geom)=true;
ALTER TABLE bd_carthage2011.troncon_bv ADD CONSTRAINT pk_id_bdcarth_bv PRIMARY KEY  (gid);
INSERT INTO geometry_columns(f_table_catalog, f_table_schema, f_table_name, f_geometry_column, coord_dimension, srid, "type")
SELECT '', 'bd_carthage2011', 'troncon_bv', 'the_geom', ST_CoordDim(the_geom), ST_SRID(the_geom), GeometryType(the_geom)
FROM bd_carthage2011.troncon_bv LIMIT 1;

DROP TABLE IF EXISTS rht.rht_bv;   
CREATE TABLE  rht.rht_bv AS
    SELECT r.* FROM rht.rht r, rht.clip_troncon as clip where st_contains(clip.the_geom,r.the_geom)=true;
ALTER TABLE rht.rht_bv ADD CONSTRAINT pk_id_drain_rht_bv PRIMARY KEY  (id_drain);
INSERT INTO geometry_columns(f_table_catalog, f_table_schema, f_table_name, f_geometry_column, coord_dimension, srid, "type")
SELECT '', 'rht', 'rht_bv', 'the_geom', ST_CoordDim(the_geom), ST_SRID(the_geom), GeometryType(the_geom)
FROM rht.rht_bv LIMIT 1;

---essai3
select r.*, id_bdcarth as gidbdc,
ST_Distance(r.the_geom,bdc.the_geom) as distance, ST_HausdorffDistance(r.the_geom,bdc.the_geom) as st_hausdorffdistance 
from rht.rht_bv r, bd_carthage2011.troncon_bv bdc, rht.rht_bretagne300 as r300 where r.the_geom && bdc.the_geom AND ST_Intersects(bdc.the_geom,r300.the_geom) order by id_bdcarth;  ---30 lines 359ms

---essai4
select r.*, id_bdcarth as gidbdc,
ST_Distance(r.the_geom,bdc.the_geom) as distance, ST_HausdorffDistance(r.the_geom,bdc.the_geom) as st_hausdorffdistance 
from rht.rht_bv r, bd_carthage2011.troncon_bv bdc, rht.rht_bretagne300 as r300 where r.the_geom && bdc.the_geom AND ST_Intersects(bdc.the_geom,r300.the_geom)
AND nature !='Aqueduc, conduite forcée' and num_superp !=1 order by id_bdcarth;  ---400 lines 359ms

-- essai5

select distinct on (id_bdcarth) id_bdcarth, min(distance) as distance, id_drain from (
        select r.id_drain,
        id_bdcarth,
        ST_Distance(r.the_geom,bdc.the_geom) as distance
from rht.rht_bv r join rht.rht_bretagne150 rht150 on r.id_drain=rht150.id_drain
                  join  bd_carthage2011.troncon_bv bdc on st_intersects(bdc.the_geom,rht150.the_geom)
where nature !='Aqueduc, conduite forcée' and num_superp !=1 ) as toto
group by id_bdcarth, distance, id_drain
order by id_bdcarth ;  ---400 lines 359ms

For France

drop table if exists rht.rht_150;
create table rht.rht_150 as select id_drain, gid, st_buffer(the_geom,150) as the_geom from rht.rht;   ---12578 ms
CREATE INDEX indexrht_150 ON rht.rht_150
  USING GIST ( the_geom GIST_GEOMETRY_OPS );
CREATE INDEX indexrht150
ON rht.rht_150
USING btree (id_drain);

drop table if exists rht.rht_bdcarthage;
create table rht.rht_bdcarthage as
        select distinct on (id_bdcarth) id_bdcarth, min(distance) as distance, id_drain from (
                select r.id_drain,
                id_bdcarth,
                ST_Distance(r.the_geom,bdc.the_geom) as distance
        from rht.rht r join rht.rht_150 rht150 on r.id_drain=rht150.id_drain
                        join    bd_carthage2011.troncon_hydrographique bdc on st_intersects(bdc.the_geom,rht150.the_geom)
        where nature !='Aqueduc, conduite forcée' and num_superp !=1 ) as toto
        group by id_bdcarth, distance, id_drain
        order by id_bdcarth ;  ---393737 lines
CREATE INDEX indexrhtbdc
ON rht.rht_bdcarthage
USING btree (id_bdcarth);
CREATE INDEX indexrhtbdc2
 ON rht.rht_bdcarthage
 USING btree
 (id_drain);

select count(*) from bd_carthage2011.troncon_hydrographique  ---528422 lines
select count(*) from rht.rht  ---114601 lines
select count(*) from rht.rht_bdcarthage --- 393737 lines

select id_drain from rht.rht_bdcarthage where id_drain not in select id_drain from rht.rht_bdcarthage;  ---

---Changement de buffer  (en cours)
drop table if exists rht.rht_150b;
create table rht.rht_150b as select id_drain, gid, st_buffer(the_geom,150,'endcap=flat join=round') as the_geom from rht.rht;   ---104717 ms

CREATE INDEX indexrht_150b ON rht.rht_150b
  USING GIST ( the_geom GIST_GEOMETRY_OPS );
CREATE INDEX indexrht150b
ON rht.rht_150b
USING btree (id_drain);

drop table if exists rht.rht_bdcarthage2;
create table rht.rht_bdcarthage2 as
        select distinct on (id_bdcarth) id_bdcarth, min(distance) as distance, id_drain from (
                select r.id_drain,
                id_bdcarth,
                ST_Distance(r.the_geom,bdc.the_geom) as distance
        from rht.rht r join rht.rht_150b rht150b on r.id_drain=rht150b.id_drain
                        join    bd_carthage2011.troncon_hydrographique bdc on st_intersects(bdc.the_geom,rht150b.the_geom)
        where nature !='Aqueduc, conduite forcée' and num_superp !=1 ) as toto
        group by id_bdcarth, distance, id_drain
        order by id_bdcarth ;  ---386254 lines
alter table rht.rht_bdcarthage add constraint c_pk_id_bdcarth primary key(id_bdcarth);

CREATE INDEX indexrhtbdcb
ON rht.rht_bdcarthage2
USING btree (id_bdcarth);
CREATE INDEX indexrhtbdcgeom22
 ON rht.rht_bdcarthage2
 USING btree
 (id_drain);


/------Requête effectuée avec Cédric le 7/10/2011
 select distinct on (id_bdcarth,id_drain) id_bdcarth, min(distance) as distance, id_drain from (
                select r.id_drain,
                id_bdcarth,
                ST_Distance(r.the_geom,bdc.the_geom) as distance
        from (select * from rht.rht where id_drain in('309179','309116','309047','309048')) as r join rht.rht_150b rht150 on r.id_drain=rht150.id_drain
                        join    bd_carthage2011.troncon_hydrographique bdc on st_intersects(bdc.the_geom,rht150.the_geom)
        where nature !='Aqueduc, conduite forcée' and num_superp !=1 ) as toto
        group by id_bdcarth, distance, id_drain
        order by id_bdcarth ;  ---9 lines

--- Même requête mais en ne conservant que les id_drain qui n'ont pas id_bdcarth associé dans rht.rht_bdcarthage
 select distinct on (id_bdcarth,id_drain) id_bdcarth, min(distance) as distance, id_drain from (
                select r.id_drain,
                id_bdcarth,
                ST_Distance(r.the_geom,bdc.the_geom) as distance
        from (select * from rht.rht where id_drain in(select id_drain from rht.rht except (select id_drain from rht.rht_bdcarthage))) as r join rht.rht_150b rht150 on r.id_drain=rht150.id_drain
                        join    bd_carthage2011.troncon_hydrographique bdc on st_intersects(bdc.the_geom,rht150.the_geom)
        where nature !='Aqueduc, conduite forcée' and num_superp !=1 ) as toto
        group by id_bdcarth, distance, id_drain
        order by id_bdcarth ;  ---49610 lines


--- Comparaison des longeurs des segments
select length(r.the_geom)-length(bd.the_geom)as diff, length(r.the_geom), length(bd.the_geom), rbd.id_drain, rbd.id_bdcarth from rht.rht_bdcarthage rbd join rht.rht r on rbd.id_drain=r.id_drain
 join bd_carthage2011.troncon_hydrographique bd on bd.id_bdcarth=rbd.id_bdcarth order by diff;
---Comparaison des longueurs id_drain et de la somme des longeurs des id_bdcarth associés

Export the tables

C:\"Program Files"\PostgreSQL\8.4\bin\pg_dump  -U postgres -p 5432 -t rht.rht_bdcarthage eda2.0_RHT> rht_bdcarthage.sql
C:\"Program Files"\PostgreSQL\8.4\bin\pg_dump  -U postgres -p 5432 -t rht.rht_150 eda2.0_RHT> rht_150.sql

Nouvel essai pour récupérer les id_bdcarth sur le RHT

create table rht.table1 as (
select id_drain, id_bdcarth from rht.rhtvs2 r join bd_carthage2011.troncon_hydrographique bdc 
on st_intersects(bdc.the_geom,st_buffer(r.the_geom,150)));

create table rht.table2 as (
select id_drain, id_bdcarth from rht.rhtvs2 r 
join bd_carthage2011.troncon_hydrographique bdc on st_intersects(bdc.the_geom,st_exteriorring(st_buffer(r.the_geom,150))) where id_bdcarth in ('440000482','440000488','440000466','440000465','440000505','440000456','440000455','440000430'));

create table rht.rhtvs2_bdcarth as (
select id_drain, id_bdcarth from rht.table1 t1 join rht.table2 t2 on t1.id_bdcarth=t2.id_bdcarth and t1.id_drain=t2.id_drain);

---------------------------------------
create table rht.proj1 as (
select id_drain, st_buffer(the_geom,150) as the_geom from rht.rhtvs2);

create table rht.proj2 as (
select id_drain, st_exteriorring(st_buffer(the_geom,150)) as the_geom from rht.rhtvs2);  ---105044 ms

select * from bd_carthage2011.troncon_hydrographique limit 2

drop table if exists rht.proj3;
create table rht.proj3 as (
select r.id_drain, id_bdcarth, id_bdcarth as idbdc, bdc.the_geom from rht.rhtvs2 r inner join rht.proj1 p on r.id_drain=p.id_drain join bd_carthage2011.troncon_hydrographique bdc 
on st_intersects(bdc.the_geom,p.the_geom) where nature !='Aqueduc, conduite forcée' and num_superp !=1); ----176730 ms
comment on table rht.proj3 is 'Selection des id_bdcarthage à l''intérieur du buffer de 150m (proj1)';


drop table if exists rht.proj4;
create table rht.proj4 as (
select r.id_drain, id_bdcarth, id_bdcarth as idbdc, bdc.the_geom from rht.rhtvs2 r inner join rht.proj2 p on r.id_drain=p.id_drain join bd_carthage2011.troncon_hydrographique bdc 
on st_intersects(bdc.the_geom,p.the_geom) where nature !='Aqueduc, conduite forcée' and num_superp !=1);
comment on table rht.proj4 is 'Selection des id_bdcarthage à l''extérieur du buffer de 150m (proj2) qui intersect avec st_exteriorring';

create table rht.proj5 as (
select t1.id_drain, t1.id_bdcarth, t1.the_geom from rht.proj3 t1 join rht.proj4 t2 on t1.id_bdcarth=t2.id_bdcarth and t1.id_drain=t2.id_drain);