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

uga2010.wso1 pour avoir le wso1_id et le wso

uga2010.wso with only the wso

     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

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');

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
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.

Insertion into ccm21.riversegments

Insert UGA into riversegment

Create a view

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