back to first page ..
back to CookBook Eda
back to Limites administratives Europe
go to europe.wso1

Pour la France mettre : France (gid=11)+Andorre (gid=2) +Monaco (gid=27) ?

create schema europe;
drop table  if exists europe.wso;
     CREATE TABLE europe.wso (
     id serial PRIMARY KEY,
     wso_id integer,
     area varchar(12)
     );

----France
    insert into europe.wso(wso_id) 
    select distinct on (wso_id)  wso_id from ccm21.riversegments r
    join (SELECT ST_Union(f.the_geom) as singlegeom
        FROM europe.limiteeurope As f where gid='11'or gid='2'or gid='27' ) as sub
    ON ST_Intersects(sub.singlegeom,r.the_geom);

    UPDATE europe.wso set area='France' where area IS NULL; --680 sea nodes

select * from ccm21.riversegments where wso_id in (select wso_id from europe.wso where area='France');--170734 lines (with france.departement 170703 lines)

----Germany
    insert into europe.wso(wso_id) 
    select distinct on (wso_id)  wso_id from ccm21.riversegments r
    join (SELECT ST_Union(f.the_geom) as singlegeom
        FROM europe.limiteeurope As f where gid='12') as sub
    ON ST_Intersects(sub.singlegeom,r.the_geom);

    UPDATE europe.wso set area='Germany' where area IS NULL;  --234 sea nodes

----Spain (+Gibraltar)
    insert into europe.wso(wso_id) 
    select distinct on (wso_id)  wso_id from ccm21.riversegments r
    join (SELECT ST_Union(f.the_geom) as singlegeom
        FROM europe.limiteeurope As f where gid='37' or gid='13') as sub
    ON ST_Intersects(sub.singlegeom,r.the_geom);

    UPDATE europe.wso set area='Spain' where area IS NULL;  --863 lines

----Italy (+San Marino)
    insert into europe.wso(wso_id) 
    select distinct on (wso_id)  wso_id from ccm21.riversegments r
    join (SELECT ST_Union(f.the_geom) as singlegeom
        FROM europe.limiteeurope As f where gid='19' or gid='33') as sub
    ON ST_Intersects(sub.singlegeom,r.the_geom);

    UPDATE europe.wso set area='Italy' where area IS NULL; --1490 lines


----Ireland
    insert into europe.wso(wso_id) 
    select distinct on (wso_id)  wso_id from ccm21.riversegments r
    join (SELECT ST_Union(f.the_geom) as singlegeom
        FROM europe.limiteeurope As f where gid='17') as sub
    ON ST_Intersects(sub.singlegeom,r.the_geom);

    UPDATE europe.wso set area='Ireland' where area IS NULL; --505 lines

----Sweden
    insert into europe.wso(wso_id) 
    select distinct on (wso_id)  wso_id from ccm21.riversegments r
    join (SELECT ST_Union(f.the_geom) as singlegeom
        FROM europe.limiteeurope As f where gid='38') as sub
    ON ST_Intersects(sub.singlegeom,r.the_geom);

    UPDATE europe.wso set area='Sweden' where area IS NULL;  --998 lines

----UK
    insert into europe.wso(wso_id) 
    select distinct on (wso_id)  wso_id from ccm21.riversegments r
    join (SELECT ST_Union(f.the_geom) as singlegeom
        FROM europe.limiteeurope As f where gid='40') as sub
    ON ST_Intersects(sub.singlegeom,r.the_geom);

    UPDATE europe.wso set area='UK' where area IS NULL;


---- Ajout des UGA
insert into europe.wso (wso_id,area) select wso_id,uga from  uga2010.wso



CREATE INDEX europe_wso_id
  ON europe.wso
  (wso_id);

---Spain-Atl
insert into europe.wso(wso_id) 
(select wso_id from europe.wso where area='Spain'
except (select  wso_id  from europe.wso where area='France')) 
intersect (select wso_id from ccm21.seaoutlets where area_cd='A1'); 
UPDATE europe.wso set area='Spain_Atl' where area IS NULL;  --348 lines

--WRB Western River Basin
    insert into europe.wso(wso_id) 
    select distinct on (wso_id)  wso_id from ccm21.riversegments r
    join (SELECT the_geom 
        FROM european_wise2008.rbd_f1v3 As f where gid=39) as sub
    ON ST_Intersects(sub.the_geom,r.the_geom);
    UPDATE europe.wso set area='Western' where area IS NULL; --118 lines

--Anglian
    insert into europe.wso(wso_id) 
    select distinct on (wso_id)  wso_id from ccm21.riversegments r
    join (SELECT the_geom 
        FROM european_wise2008.rbd_f1v3 As f where gid=200) as sub
    ON ST_Intersects(sub.the_geom,r.the_geom);
    UPDATE europe.wso set area='Anglian' where area IS NULL;  --69 lines

--Sardinia
    insert into europe.wso(wso_id) 
    select distinct on (wso_id)  wso_id from ccm21.riversegments r
    join (SELECT the_geom 
        FROM european_wise2008.rbd_f1v3 As f where gid=85) as sub
    ON ST_Intersects(sub.the_geom,r.the_geom);
    UPDATE europe.wso set area='Sardinia' where area IS NULL; --209 lines

--Swedish ?
    insert into europe.wso(wso_id) 
    select distinct on (wso_id)  wso_id from ccm21.riversegments r
    join (SELECT the_geom 
        FROM european_wise2008.rbd_f1v3 As f where gid=166) as sub
    ON ST_Intersects(sub.the_geom,r.the_geom);
    UPDATE europe.wso set area='Swedish' where area IS NULL; --121 lines

--Elbe ?
    insert into europe.wso(wso_id) 
    select distinct on (wso_id)  wso_id from ccm21.riversegments r
    join (SELECT the_geom 
        FROM european_wise2008.rbd_f1v3 As f where gid=208) as sub
    ON ST_Intersects(sub.the_geom,r.the_geom);
    UPDATE europe.wso set area='Elbe' where area IS NULL; --18 lines

---Sauvegarde des données
D:
cd D:\CelineJouanin\backup
C:\"Program Files"\PostgreSQL\8.4\bin\pg_dump  -U postgres -p 5433 -t geobs2010.obstacle_referenciel_projccm eda2.0> geobs2010.obstacle_referenciel_projccm.sql 

Comparaison europe.wso et rbd_f1v3.bassin_pose

select cum_len_sea, up_area, shree, scheid, distance_relative, c_area, distance_source, catchment_area from ccm21.riversegments r inner join europe.wso e on e.wso_id=r.wso_id where e.area='Sardinia'
select cum_len_sea, up_area, shree, scheid, distance_relative, c_area, distance_source, catchment_area from ccm21.riversegments r inner join rbd_f1v3.bassin_pose e on e.wso_id=r.wso_id where e.emu='Sardinia'
select * from rbd_f1v3.bassin_pose e where e.emu='Anglian'
select * from europe.wso w where w.area='Sardinia'

select * from europe.wso w left join rbd_f1v3.bassin_pose b on b.wso_id=w.wso_id where w.area='Elbe'
select * from rbd_f1v3.bassin_pose b right join europe.wso w on b.wso_id=w.wso_id where emu='Sardinia'

---63 wso_id qui notés Anglian et UK dans europe.wso --> OK

select * from rbd_f1v3.bassin_pose b right join europe.wso w on b.wso_id=w.wso_id where emu='Anglian' and area='UK'

---6 wso_id qui doivent être rajouté dans europe.wso pour l'Elbe

select * from rbd_f1v3.bassin_pose b right join europe.wso w on b.wso_id=w.wso_id where emu='Elbe'

insert into europe.wso(wso_id) values ('383'),('2005'),('8639'),('38619'),('38481'),('2020');
UPDATE europe.wso set area='Elbe' where area IS NULL;

Problème :

Nb lignes dans	riversegments_uga avec europe.wso	CCM sous R uga2010.wso
Bretagne	2115	2035
Corse	4839	4836
Garonne	13219	13223
RMC	91635	91548
SeineNormandie	7415	7413

select * from uga2010.wso b left join europe.wso w on b.wso_id=w.wso_id where uga='SeineNormandie'
select count(b.wso_id), b.wso_id from uga2010.wso b left join europe.wso w on b.wso_id=w.wso_id where uga='SeineNormandie' group by b.wso_id order by count(b.wso_id)

SeineNormandie

332670, 337665

A mettre dans area France

---SeineNormandie
insert into europe.wso(wso_id) values ('332670'),('337665'),('342310');
UPDATE europe.wso set area='France' where area IS NULL;
---Corse
insert into europe.wso(wso_id) values ('263289'),('261207'),('261251');
UPDATE europe.wso set area='France' where area IS NULL;
---RMC
insert into europe.wso(wso_id) values ('238201'),('434444'),('434159'),('433693'),('432880'),('432326'),('433584'),('434407'),('440607'),('432933');
UPDATE europe.wso set area='France' where area IS NULL;
---Garonne
insert into europe.wso(wso_id) values ('394204');
UPDATE europe.wso set area='France' where area IS NULL;
---Bretagne
insert into europe.wso(wso_id) values ('353376'),('352555');
UPDATE europe.wso set area='France' where area IS NULL;

delete from europe.wso where wso_id='291126' and area='Spain';
delete from europe.wso where wso_id='442494' and area='Spain';
delete from europe.wso where wso_id='445532' and area='Spain';
delete from europe.wso where wso_id='510766' and area='Spain';
delete from europe.wso where wso_id='442508' and area='Spain';
delete from europe.wso where wso_id='129681' and area='Italy';
delete from europe.wso where wso_id='129487' and area='RhoneMediterranee';
delete from europe.wso where wso_id='442353' and area='RhoneMediterranee';
insert into europe.wso(wso_id) values ('442529');
UPDATE europe.wso set area='RhoneMediterranee' where area IS NULL;
insert into uga2010.wso(wso_id) values ('442529');
UPDATE uga2010.wso set uga='RhoneMediterranee' where uga IS NULL;
delete from uga2010.wso where wso_id='129487' and uga='RhoneMediterranee';
delete from europe.wso where wso_id='291112' and area='Italy';
delete from europe.wso where wso_id='442529' and area='Spain';
delete from uga2010.wso where wso_id='130439' and uga='RhoneMediterranee';
delete from europe.wso where wso_id='130439' and area='France';
delete from europe.wso where wso_id='130439' and area='RhoneMediterranee';
insert into europe.wso(wso_id) values ('130439');
UPDATE europe.wso set area='Italy' where area IS NULL;
delete from uga2010.wso where wso_id='442353' and uga='RhoneMediterranee';