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

["uga2010.wso1"] pour avoir le wso1_id et le wso

= uga2010.wso with only the wso =
{{{
#!sql
     drop table  if exists uga2010.wso;
     CREATE TABLE uga2010.wso (
     id serial PRIMARY KEY,
     wso_id integer,
     uga varchar(25)
     )

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

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

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

    UPDATE uga2010.wso set uga='Loire' where uga IS NULL; -- 39 lines

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

    UPDATE uga2010.wso set uga='Garonne' where uga IS NULL; --60 lines

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

    UPDATE uga2010.wso set uga='Adour' where uga IS NULL; --19 lines
    
--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 libelle='Artois-Picardie') as sub
    ON ST_Intersects(sub.singlegeom,r.the_geom);

    UPDATE uga2010.wso set uga='ArtoisPicardie' where uga IS NULL; --26 lines

--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 libelle='Rhône-Méditerranée') as sub
    ON ST_Intersects(sub.singlegeom,r.the_geom);

    UPDATE uga2010.wso set uga='RhoneMediterranee' where uga IS NULL; --133 lines

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

    UPDATE uga2010.wso set uga='Corse' where uga IS NULL; --177 lines

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

    UPDATE uga2010.wso set uga='SeineNormandie' where uga IS NULL;--89 lines

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

    UPDATE uga2010.wso set uga='Rhin' where uga IS NULL; --2 lines

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

    UPDATE uga2010.wso set uga='Meuse' where uga IS NULL; --3 lines    
}}}

Suppression des lignes de doublons
{{{
#!sql
delete from uga2010.wso where wso_id in (select wso_id from uga2010.wso group by wso_id having count(wso_id)>1);
-- Réinsertion des wso_id supprimés (sans doublon)
INSERT INTO uga2010.wso (uga,wso_id) VALUES ('ArtoisPicardie','291223');
INSERT INTO uga2010.wso (uga,wso_id) VALUES ('Adour','291194');
INSERT INTO uga2010.wso (uga,wso_id) VALUES ('Meuse','291130');
INSERT INTO uga2010.wso (uga,wso_id) VALUES ('Loire','291111');
INSERT INTO uga2010.wso (uga,wso_id) VALUES ('Rhin','291110');
INSERT INTO uga2010.wso (uga,wso_id) VALUES ('Garonne','291125'),('Garonne','291126'),('Garonne','309731');
INSERT INTO uga2010.wso (uga,wso_id) VALUES ('SeineNormandie','291115'),('SeineNormandie','291249');
INSERT INTO uga2010.wso (uga,wso_id) VALUES ('RhoneMediterranee','291112'),('RhoneMediterranee','291513'),('RhoneMediterranee','292706'),('RhoneMediterranee','442353');

--Insertion d'un wso_id non pris en compte dans l'UGA Bretagne car se situant sur une île
INSERT INTO uga2010.wso (uga,wso_id) VALUES ('Bretagne','353376');

--Insertion d'un wso_id non pris en compte dans l'UGA Seine Normandie car si situant en dehors des limites de la carte des UGA
INSERT INTO uga2010.wso (uga,wso_id) VALUES ('SeineNormandie','342310');

}}}


{{{
#!sql
alter table uga2010.wso add constraint uk_wso unique(wso_id)--marche pas

select * from uga2010.wso except(
    select distinct on (wso_id) * from uga2010.wso );
}}}

'''J'ai un doute pour la requête entre wso_id et wso1_id  lequel choisir ??''' 
["Requete uga wso1_id"] PAS BESOIN

'''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'''
{{{
#!sql
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]]

= Insertion into ccm21.riversegments =
["Insert UGA into riversegment"]

== Create a view ==
{{{
#!sql
drop view if exists uga2010.ugawso
create view uga2010.ugawso as select r.*, u.wso_id as wso_idd, u.uga as ugawso from ccm21.riversegments r inner join uga2010.wso u on u.wso_id=r.wso_id
}}}