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 lines

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

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

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

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

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

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

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

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

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

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

insert into uga2010.wso1(wso_id,wso1_id,uga) values ('292665','427314','Adour');

--BV à supprimer car l'information sur les barrages à l'aval n'est pas disponible
DELETE FROM uga2010.wso1 where wso_id='129681';  --BV 549 tronçons -519 tronçons supprimés (Italy)
DELETE FROM uga2010.wso1 where wso_id='129487';  --BV 29647 tronçons -104 tronçons supprimés (Italy)
DELETE FROM uga2010.wso1 where wso_id='442353'; --237 tronçons français supprimés
DELETE FROM uga2010.wso1 where wso_id='291133'; --432 tronçons français supprimés
DELETE FROM uga2010.wso1 where wso_id='291549'; --16 tronçons français supprimés

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

DELETE FROM uga2010.wso1 where wso_id='292665';  
insert into uga2010.wso1(wso_id, wso1_id) 
    select distinct on (wso1_id, wso_id) wso_id, wso1_id from ccm21.riversegments r where wso_id='292665'; -- BV 83 tronçons
UPDATE uga2010.wso1 set uga='Adour' where uga IS NULL;

DELETE FROM uga2010.wso1 where wso_id='291126';  
insert into uga2010.wso1(wso_id, wso1_id) 
    select distinct on (wso1_id, wso_id) wso_id, wso1_id from ccm21.riversegments r where wso_id='291126'; -- BV 9636 tronçons
UPDATE uga2010.wso1 set uga='Garonne' where uga IS NULL;

DELETE FROM uga2010.wso1 where wso_id='291112';  
insert into uga2010.wso1(wso_id, wso1_id) 
    select distinct on (wso1_id, wso_id) wso_id, wso1_id from ccm21.riversegments r where wso_id='291112'; -- BV 34564 tronçons
UPDATE uga2010.wso1 set uga='RhoneMediterranee' where uga IS NULL;

drop view if exists uga2010.ugawso1;
CREATE VIEW uga2010.ugawso1 AS select r.*, u.wso_id as wso_idd, u.wso1_id as wso1_idd, u.uga as uga1 from ccm21.riversegments r inner join uga2010.wso1 u on u.wso1_id=r.wso1_id

Backup

C:\"Program Files"\PostgreSQL\8.4\bin\pg_dump  -U postgres -p 5432 -t uga2010.wso1 eda2.0> uga2010_wso1_backup.sql

Rapport ONEMA

select count(uga1), uga1 from uga2010.ugawso1 group by uga1 order by uga1;
select sum(shape_leng)/1000, uga1 from uga2010.ugawso1 group by uga1 order by uga1;
select sum(catchment_)/1000000, uga1 from uga2010.ugawso1 group by uga1 order by uga1