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 wso1_id"]

'''Problème dans la construction même des UGA'''[[BR]]
Deux couches d'UGA (Loire et Garonne) se supperpose QGis au niveau de la mer[[BR]][[BR]]
[[Image(source:data/Docs/trac/UGA/Supperposition_UGA.jpg,600px)]]

Mais 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'''
||wso_id||nb de doublons||UGA donnés||Appartient en réalité à l'UGA (ce que je propose)||
||291110||3||Rhin, Meuse, !RhoneMediterranee||
||291111||4||!SeineNormandie, Loire, Garonne et Bretagne||'''Loire'''||
||291112||2||!RhonemEditerranee, Rhin||
||291115||3||!ArtoisPicardie, !SeineNormandie, Meuse||
||291125||2||Loire, Garonne||Garonne||
||291126||2||Garonne, !RhoneMediterranee||Garonne||
||291130||3||!ArtoisPicardie, Meuse, !SeineNormandie||
||291194||2||Garonne, Adour||
||291223||2||!ArtoisPicardie, !SeineNormandie||
||291249||2||!SeineNormandie, Loire||
||291513||2||Garonne, !RhoneMediterranee||!RhoneMediterranee||
||292706||2||Garonne, !RhoneMediterranee||Garonne||
||309731||2||Loire, Garonne||Garonne||
||442353||2||Garonne, !RhoneMediterranee|| Aucune idée !!||