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;

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_300 as select id_drain, gid, st_buffer(the_geom,200) as the_geom from rht.rht;
CREATE INDEX indexrht_300 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

--- Create table uga2010.id_drain with id_drain from RHT for Bretagne 
drop table if exists uga2010.wso;

drop table  if exists uga2010.id_drain;
     CREATE TABLE uga2010.id_drain (
     id serial PRIMARY KEY,
     id_drain integer,
     uga varchar(25)
     );

 --Bretagne
    insert into uga2010.id_drain(id_drain) 
    select distinct on (id_drain) id_drain from rht.rht r
    join (SELECT ST_Union(f.the_geom) as singlegeom
        FROM uga2010.uga As f where libelle='Bretagne') as sub
    ON ST_Intersects(sub.singlegeom,r.the_geom);

    UPDATE uga2010.id_drain set uga='Bretagne' where uga IS NULL -- 6468 lignes

--- Create table uga2010.id_drain with id_drain from BDCarthage for Bretagne 
drop table  if exists uga2010.id_bdcarthage;
     CREATE TABLE uga2010.id_bdcarthage (
     id serial PRIMARY KEY,
     id_bdcarth integer,
     uga varchar(25)
     );

 --Bretagne
    insert into uga2010.id_bdcarthage(id_bdcarth) 
    select distinct on (id_bdcarth) id_bdcarth from bd_carthage2011.troncon_hydrographique r
    join (SELECT ST_Union(f.the_geom) as singlegeom
        FROM uga2010.uga As f where libelle='Bretagne') as sub
    ON ST_Intersects(sub.singlegeom,r.the_geom);

    UPDATE uga2010.id_bdcarthage set uga='Bretagne' where uga IS NULL -- 27250 lignes

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;

create table rht.rht_bretagne300 as select id_drain, gid, st_buffer(the_geom,300) as the_geom from rht.rht_bretagne;   ---12578 ms
CREATE INDEX indexrht_bretagne300 ON rht.rht_bretagne300
  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