back to first page..
back to RHT
Go to CookBook RHT_UGA, BDCarthage2011 into eda2.0_RHT

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 lines

 --Adour
    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='Adour') as sub
    ON ST_Intersects(sub.singlegeom,r.the_geom);

    UPDATE uga2010.id_bdcarthage set uga='Adour' where uga IS NULL -- 23181 lines

 --Garonne
    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='Garonne') as sub
    ON ST_Intersects(sub.singlegeom,r.the_geom);

    UPDATE uga2010.id_bdcarthage set uga='Garonne' where uga IS NULL -- 92831 lines

 --Loire
    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='Loire') as sub
    ON ST_Intersects(sub.singlegeom,r.the_geom);

    UPDATE uga2010.id_bdcarthage set uga='Loire' where uga IS NULL -- 109437 lines

 --Seine-Normandie
    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='Seine-Normandie') as sub
    ON ST_Intersects(sub.singlegeom,r.the_geom);

    UPDATE uga2010.id_bdcarthage set uga='Seine-Normandie' where uga IS NULL -- 55365 lines


 --Corse
    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='Corse') as sub
    ON ST_Intersects(sub.singlegeom,r.the_geom);

    UPDATE uga2010.id_bdcarthage set uga='Corse' where uga IS NULL -- 12594 lines

 --Artois-Picardie
    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='Artois-Picardie') as sub
    ON ST_Intersects(sub.singlegeom,r.the_geom);

    UPDATE uga2010.id_bdcarthage set uga='Artois-Picardie' where uga IS NULL -- 12040  lines

 --Rhone-Mediterranee
    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='Rhone-Mediterranee') as sub
    ON ST_Intersects(sub.singlegeom,r.the_geom);

    UPDATE uga2010.id_bdcarthage set uga='Rhone-Mediterranee' where uga IS NULL -- 147091 lines

 --Rhin
    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='Rhin') as sub
    ON ST_Intersects(sub.singlegeom,r.the_geom);

    UPDATE uga2010.id_bdcarthage set uga='Rhin' where uga IS NULL -- 24355 lines

--Meuse
    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='Meuse') as sub
    ON ST_Intersects(sub.singlegeom,r.the_geom);

    UPDATE uga2010.id_bdcarthage set uga='Meuse' where uga IS NULL -- 5539 lines

CREATE INDEX indexugaid_bdcarthage
ON uga2010.id_bdcarthage
USING btree (id_bdcarth);