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

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

--Loire
    insert into uga2010.wso(wso_id) 
    select distinct on (wso_id)  wso_id from ccm21.riversegments r
    join (SELECT ST_Union(f.the_geom) as singlegeom
        FROM uga2010.uga As f where gid='1') as sub
    ON ST_Intersects(sub.singlegeom,r.the_geom);

    UPDATE uga2010.wso set uga='Loire' where uga IS NULL --40 sea nodes

--Garonne
    insert into uga2010.wso(wso_id) 
    select distinct on (wso_id)  wso_id from ccm21.riversegments r
    join (SELECT ST_Union(f.the_geom) as singlegeom
        FROM uga2010.uga As f where gid='2') as sub
    ON ST_Intersects(sub.singlegeom,r.the_geom);

    UPDATE uga2010.wso set uga='Garonne' where uga IS NULL --60 sea nodes

--Adour
    insert into uga2010.wso(wso_id) 
    select distinct on (wso_id)  wso_id from ccm21.riversegments r
    join (SELECT ST_Union(f.the_geom) as singlegeom
        FROM uga2010.uga As f where gid='3') as sub
    ON ST_Intersects(sub.singlegeom,r.the_geom);

    UPDATE uga2010.wso set uga='Adour' where uga IS NULL --19 sea nodes

--Artois-Picardie
    insert into uga2010.wso(wso_id) 
    select distinct on (wso_id)  wso_id from ccm21.riversegments r
    join (SELECT ST_Union(f.the_geom) as singlegeom
        FROM uga2010.uga As f where gid='4') as sub
    ON ST_Intersects(sub.singlegeom,r.the_geom);

    UPDATE uga2010.wso set uga='ArtoisPicardie' where uga IS NULL --26 sea nodes

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

    UPDATE uga2010.wso set uga='RhoneMediterranee' where uga IS NULL --133 sea nodes

--Corse
    insert into uga2010.wso(wso_id) 
    select distinct on (wso_id)  wso_id from ccm21.riversegments r
    join (SELECT ST_Union(f.the_geom) as singlegeom
        FROM uga2010.uga As f where gid='6') as sub
    ON ST_Intersects(sub.singlegeom,r.the_geom);

    UPDATE uga2010.wso set uga='Corse' where uga IS NULL --177 sea nodes

--Seine-Normandie
    insert into uga2010.wso(wso_id) 
    select distinct on (wso_id)  wso_id from ccm21.riversegments r
    join (SELECT ST_Union(f.the_geom) as singlegeom
        FROM uga2010.uga As f where gid='7') as sub
    ON ST_Intersects(sub.singlegeom,r.the_geom);

    UPDATE uga2010.wso set uga='SeineNormandie' where uga IS NULL --89 sea nodes

--Bretagne
    insert into uga2010.wso(wso_id) 
    select distinct on (wso_id)  wso_id from ccm21.riversegments r
    join (SELECT ST_Union(f.the_geom) as singlegeom
        FROM uga2010.uga As f where gid='8') as sub
    ON ST_Intersects(sub.singlegeom,r.the_geom);

    UPDATE uga2010.wso set uga='Bretagne' where uga IS NULL --166 sea nodes

--Rhin
    insert into uga2010.wso(wso_id) 
    select distinct on (wso_id)  wso_id from ccm21.riversegments r
    join (SELECT ST_Union(f.the_geom) as singlegeom
        FROM uga2010.uga As f where gid='9') as sub
    ON ST_Intersects(sub.singlegeom,r.the_geom);

    UPDATE uga2010.wso set uga='Rhin' where uga IS NULL --2 sea nodes

--Meuse

    insert into uga2010.wso(wso_id) 
    select distinct on (wso_id)  wso_id from ccm21.riversegments r
    join (SELECT ST_Union(f.the_geom) as singlegeom
        FROM uga2010.uga As f where gid='10') as sub
    ON ST_Intersects(sub.singlegeom,r.the_geom);

    UPDATE uga2010.wso set uga='Meuse' where uga IS NULL --3 sea nodes

J'ai un doute pour la requête entre wso_id et wso1_id lequel choisir ?? Requete uga wso1_id

Problème dans la construction même des UGA
Deux couches d'UGA (Loire et Garonne) se supperposent sous QGis au niveau de la mer

Sous ArcGis il ne semble pas y avoir de suppersposition

Probleme de segment à cheval sur deux couches

Recherche de doublons

select wso_id, count(wso_id) as nombre from uga2010.wso group by wso_id order by nombre;

Problèmes de doubons avec les riversegments qui sont à cheval sur deux UGA En fait c'est plus compliqué que cela '''

wso_id	nb de doublons	UGA donnés	Appartient en réalité à l'UGA (ce que je propose)
291110	3	Rhin, Meuse, RhoneMediterranee	RHIN
291111	4	SeineNormandie, Loire, Garonne et Bretagne	LOIRE
291112	2	RhoneMediterranee, Rhin	RHONEMEDITERRANEE
291115	3	ArtoisPicardie, SeineNormandie, Meuse	SEINENORMANDIE
291125	2	Loire, Garonne	GARONNE
291126	2	Garonne, RhoneMediterranee	GARONNE
291130	3	ArtoisPicardie, Meuse, SeineNormandie	MEUSE
291194	2	Garonne, Adour	ADOUR
291223	2	ArtoisPicardie, SeineNormandie	ARTOISPICARDIE
291249	2	SeineNormandie, Loire	SEINENORMANDIE
291513	2	Garonne, RhoneMediterranee	RHONEMEDITERRANEE
292706	2	Garonne, RhoneMediterranee	RHONEMEDITERRANEE
309731	2	Loire, Garonne	GARONNE
442353	2	Garonne, RhoneMediterranee	RHONEMEDITERRANEE

Pour ces riversegment que faut -il prendre comme UGA ?

Pour ces riversegment on prend l'exutoire.

Pour ce riversegment l'exutoire est dans UGA Loire, l'ensemble des segments amonts se trouvent dans UGA Garonne.

On prendre l'exutoire comme référence, si celui-ci est dans un UGA donné alors tous les segements amons apaprtenant à ce bassin versant sont dans l'UGA considéré.

Exception pour la Meuse, l'exutoire ne se situe pas en France.

Exception pour Rhone Mediterrannee qui prend sa source en espagne mais en Méditerranée.