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

NB : Voir pour le problème avec Rhone Méditerranée
Création de la table uga2010.wso1

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


 --Bretagne
    insert into uga2010.wso1(wso_id, wso1_id) 
    select distinct on (wso1_id, wso_id) wso_id, wso1_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.wso1 set uga='Bretagne' where uga IS NULL --2041 lignes

--Loire
    insert into uga2010.wso1(wso_id, wso1_id) 
    select distinct on (wso1_id, wso_id) wso_id, wso1_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.wso1 set uga='Loire' where uga IS NULL; -- 11760 lignes

--Garonne
    insert into uga2010.wso1(wso_id, wso1_id) 
    select distinct on (wso1_id, wso_id) wso_id, wso1_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.wso1 set uga='Garonne' where uga IS NULL; --12791 lignes

--Adour
    insert into uga2010.wso1(wso_id, wso1_id) 
    select distinct on (wso1_id, wso_id) wso_id, wso1_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.wso1 set uga='Adour' where uga IS NULL; --3978 lignes
    
--Artois-Picardie
    insert into uga2010.wso1(wso_id, wso1_id) 
    select distinct on (wso1_id, wso_id) wso_id, wso1_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.wso1 set uga='ArtoisPicardie' where uga IS NULL; --1406 lignes

--Rhône-Méditerranée
    insert into uga2010.wso1(wso_id, wso1_id) 
    select distinct on (wso1_id, wso_id) wso_id, wso1_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);
    --Rhône-Méditerranée changé en Rhône-Méditerranée

    UPDATE uga2010.wso1 set uga='RhoneMediterranee' where uga IS NULL; --39904 lignes

--Corse
    insert into uga2010.wso1(wso_id, wso1_id) 
    select distinct on (wso1_id, wso_id) wso_id, wso1_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.wso1 set uga='Corse' where uga IS NULL; --4839 lignes

--Seine-Normandie
    insert into uga2010.wso1(wso_id, wso1_id) 
    select distinct on (wso1_id, wso_id) wso_id, wso1_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.wso1 set uga='SeineNormandie' where uga IS NULL;--7412 lignes

--Rhin
    insert into uga2010.wso1(wso_id, wso1_id) 
    select distinct on (wso1_id, wso_id) wso_id, wso1_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.wso1 set uga='Rhin' where uga IS NULL; --3228 lignes

--Meuse
    insert into uga2010.wso1(wso_id, wso1_id) 
    select distinct on (wso1_id, wso_id) wso_id, wso1_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.wso1 set uga='Meuse' where uga IS NULL; --888 lignes   

ALTER TABLE uga2010.wso1 RENAME wso1_id TO wso1_idd
ALTER TABLE uga2010.wso1 RENAME wso_id TO wso_idd
ALTER TABLE uga2010.wso1 RENAME uga TO uga1

DELETE FROM uga2010.wso1 where wso1_idd in ('369830','360673');
DELETE FROM uga2010.wso1 where wso1_idd='340390' and uga1='Meuse';
DELETE FROM uga2010.wso1 where wso1_idd='333806' and uga1='Meuse';
DELETE FROM uga2010.wso1 where wso1_idd='360909' and uga1='SeineNormandie';
DELETE FROM uga2010.wso1 where wso1_idd='399668' and uga1='Garonne';
DELETE FROM uga2010.wso1 where wso1_idd in ('430200','431231','438387','504505') and uga1='Garonne'
DELETE FROM uga2010.wso1 where wso1_idd='433170' and uga1='Garonne';
DELETE FROM uga2010.wso1 where wso1_idd IN ('332276','335893') and uga1='SeineNormandie';
DELETE FROM uga2010.wso1 where wso1_idd IN ('335470') and uga1='ArtoisPicardie';
DELETE FROM uga2010.wso1 where wso1_idd in ('338952','339347') and uga1='SeineNormandie'
DELETE FROM uga2010.wso1 where wso1_idd IN ('354400','438158','438509') and uga1='RhoneMediterranee';
DELETE FROM uga2010.wso1 where wso1_idd in ('392090','344744') and uga1='Loire'
DELETE FROM uga2010.wso1 where wso1_idd in ('346909') and uga1='Bretagne'

UPDATE uga2010.wso1 set uga1='Meuse' where wso1_idd IN ('339078','339322');
UPDATE uga2010.wso1 set uga1='Loire' where wso1_idd IN ('347151','347152','347247','347286');
UPDATE uga2010.wso1 set uga1='Adour' where wso1_idd IN ('433778','433792','433793','433807');

insert into uga2010.wso1(wso_idd,wso1_idd,uga1) values ('292665','427314','Adour');

--BV à supprimer car l'information sur les barrages à l'aval n'est pas disponible
DELETE FROM uga2010.wso1 where wso_idd='129681';  --BV 549 tronçons -519 tronçons supprimés (Italy)
DELETE FROM uga2010.wso1 where wso_idd='129487';  --BV 29647 tronçons -104 tronçons supprimés (Italy)

--Ensemble du BV à ajouter dans uga2010.wso1
DELETE FROM uga2010.wso1 where wso_idd='291194';  
insert into uga2010.wso1(wso_idd, wso1_idd) 
    select distinct on (wso1_id, wso_id) wso_id, wso1_id from ccm21.riversegments r where wso_id='291194'; -- BV 3826 tronçons

DELETE FROM uga2010.wso1 where wso_idd='292665';  
insert into uga2010.wso1(wso_idd, wso1_idd) 
    select distinct on (wso1_id, wso_id) wso_id, wso1_id from ccm21.riversegments r where wso_id='292665'; -- BV 83 tronçons

DELETE FROM uga2010.wso1 where wso_idd='291126';  
insert into uga2010.wso1(wso_idd, wso1_idd) 
    select distinct on (wso1_id, wso_id) wso_id, wso1_id from ccm21.riversegments r where wso_id='291126'; -- BV 9636 tronçons

drop view if exists uga2010.ugawso1
CREATE VIEW uga2010.ugawso1 AS select * from ccm21.riversegments r inner join uga2010.wso1 u on u.wso1_idd=r.wso1_id