back to first page [..][[BR]]
back to ["CookBook Eda"][[BR]]
back to ["UGA"][[BR]]


{{{
     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'''[[BR]]
Deux couches d'UGA (Loire et Garonne) se supperposent sous QGis au niveau de la mer[[BR]][[BR]]
[[Image(source:data/Docs/trac/UGA/Supperposition_UGA.jpg,600px)]]
Sous !ArcGis il ne semble pas y avoir de suppersposition[[BR]]
[[Image(source:data/Docs/trac/UGA/Supperposition_UGA_ArcGis.jpg,600px)]]

'''Probleme de segment à cheval sur deux couches'''[[BR]]

[[Image(source:data/Docs/trac/UGA/UGA-CCM.jpg,600px)]] 


'''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 ? [[BR]]
[[Image(source:data/Docs/trac/UGA/riversgement_uga.jpg,600px)]] [[BR]]
Pour ces riversegment on prend l'exutoire. [[BR]]
[[Image(source:data/Docs/trac/UGA/uga a definir.jpg,600px)]] [[BR]]
Pour ce riversegment l'exutoire est dans UGA Loire, l'ensemble des segments amonts se trouvent dans UGA Garonne. [[BR]]

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é. [[BR]]

Exception pour la Meuse, l'exutoire ne se situe pas en France.[[BR]]
[[Image(source:data/Docs/trac/UGA/uga_meuse.jpg,600px)]]  [[BR]]
Exception pour Rhone Mediterrannee qui prend sa source en espagne mais en Méditerranée. [[BR]]
[[Image(source:data/Docs/trac/UGA/exception_uga.jpg,600px)]]  [[BR]]