back to first page..
back to CookBook Eda
back to UGA
back to Cookbook CCM21_UGA

NB : Voir pour le problème avec Rhone Méditerranée ![[BR]] Création de la table uga2010.wso1

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


 --Bretagne
    insert into uga2010.wso1(wso_id, wso1_id) 
    select distinct on (wso1_id, wso_id) wso_id, wso1_id from ccm21.riversegments 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.wso1 set uga='Bretagne' where uga IS NULL --2041 lignes

--Loire
    insert into uga2010.wso1(wso_id, wso1_id) 
    select distinct on (wso1_id, wso_id) wso_id, wso1_id from ccm21.riversegments 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.wso1 set uga='Loire' where uga IS NULL; -- 11760 lignes

--Garonne
    insert into uga2010.wso1(wso_id, wso1_id) 
    select distinct on (wso1_id, wso_id) wso_id, wso1_id from ccm21.riversegments 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.wso1 set uga='Garonne' where uga IS NULL; --12791 lignes

--Adour
    insert into uga2010.wso1(wso_id, wso1_id) 
    select distinct on (wso1_id, wso_id) wso_id, wso1_id from ccm21.riversegments 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.wso1 set uga='Adour' where uga IS NULL; --3978 lignes
    
--Artois-Picardie
    insert into uga2010.wso1(wso_id, wso1_id) 
    select distinct on (wso1_id, wso_id) wso_id, wso1_id from ccm21.riversegments 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.wso1 set uga='ArtoisPicardie' where uga IS NULL; --1406 lignes

--Rhône-Méditerranée
    insert into uga2010.wso1(wso_id, wso1_id) 
    select distinct on (wso1_id, wso_id) wso_id, wso1_id from ccm21.riversegments r
    join (SELECT ST_Union(f.the_geom) as singlegeom
        FROM uga2010.uga As f where libelle='Rhône-Méditerranée') as sub
    ON ST_Intersects(sub.singlegeom,r.the_geom);
    --Rhône-Méditerranée changé en Rhône-Méditerranée

    UPDATE uga2010.wso1 set uga='RhoneMediterranee' where uga IS NULL; --39904 lignes

--Corse
    insert into uga2010.wso1(wso_id, wso1_id) 
    select distinct on (wso1_id, wso_id) wso_id, wso1_id from ccm21.riversegments 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.wso1 set uga='Corse' where uga IS NULL; --4839 lignes

--Seine-Normandie
    insert into uga2010.wso1(wso_id, wso1_id) 
    select distinct on (wso1_id, wso_id) wso_id, wso1_id from ccm21.riversegments 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.wso1 set uga='SeineNormandie' where uga IS NULL;--7412 lignes

--Rhin
    insert into uga2010.wso1(wso_id, wso1_id) 
    select distinct on (wso1_id, wso_id) wso_id, wso1_id from ccm21.riversegments 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.wso1 set uga='Rhin' where uga IS NULL; --3228 lignes

--Meuse
    insert into uga2010.wso1(wso_id, wso1_id) 
    select distinct on (wso1_id, wso_id) wso_id, wso1_id from ccm21.riversegments 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.wso1 set uga='Meuse' where uga IS NULL; --888 lignes