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