back to first page ..
back to CookBook Eda
back to Limites administratives Europe
back to Cookbook CCM21_Europe

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

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

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

CREATE INDEX europe_wso1_id
  ON europe.wso1
  (wso1_id);

#TODO A finir la suite...

--WRB Western River Basin
    insert into europe.wso(wso_id, wso1_id) 
    select distinct on (wso_id, wso1_id)  wso_id, wso1_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.wso1 set area='Western' where area IS NULL; --118 lines

--Anglian
    insert into europe.wso1(wso_id, wso1_id) 
    select distinct on (wso_id, wso1_id)  wso_id, wso1_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.wso1 set area='Anglian' where area IS NULL;  --69 lines

--Sardinia
    insert into europe.wso1(wso_id, wso1_id) 
    select distinct on (wso_id, wso1_id)  wso_id, wso1_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.wso1 set area='Sardinia' where area IS NULL; --209 lines

--Swedish
    insert into europe.wso1(wso_id, wso1_id) 
    select distinct on (wso_id, wso1_id)  wso_id, wso1_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.wso1 set area='Swedish' where area IS NULL; --121 lines

--Elbe
    insert into europe.wso1(wso_id, wso1_id) 
    select distinct on (wso_id, wso1_id)  wso_id, wso1_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.wso1 set area='Elbe' where area IS NULL; --18 lines

---- Cas Espagne OK !!! ( avant beaucoup trop de segments retenus dans Spain_Atl --> area/zonegeo="Basque" maintenant)
---Basque
 insert into europe.wso1(wso_id, wso1_id) 
    select distinct on (wso_id, wso1_id)  wso_id, wso1_id from ccm21.riversegments r1 where wso_id in (select  r.wso_id from ccm21.riversegments r
    join (SELECT the_geom 
        FROM european_wise2008.rbd_f1v3 As f where gid=41) as sub
    ON ST_Intersects(sub.the_geom,r.the_geom)) or wso_id in ('291467','297569','297544','420706','319592','297608','319864','319930','319559');
    UPDATE europe.wso1 set area='Basque' where area IS NULL; --2680 lines
drop view if exists europe.spain;
       
CREATE VIEW europe.spain as select r1.* from ccm21.riversegments r1 where wso_id in (select  r.wso_id from ccm21.riversegments r
    join (SELECT the_geom 
        FROM european_wise2008.rbd_f1v3 As f where gid=41) as sub
    ON ST_Intersects(sub.the_geom,r.the_geom)) or wso_id in ('291467','297569','297544','420706','319592','297608','319864','319930','319559');

insert into europe.wso1(wso_id, wso1_id) 
    select distinct on (wso_id, wso1_id)  wso_id, wso1_id from ccm21.riversegments r where wso_id='342310'
        UPDATE europe.wso1 set area='SeineNormandie' where area IS NULL;
insert into europe.wso1(wso_id, wso1_id) 
    select distinct on (wso_id, wso1_id)  wso_id, wso1_id from ccm21.riversegments r where wso_id='353376'
        UPDATE europe.wso1 set area='Bretagne' where area IS NULL;
delete from europe.wso where wso_id='442529'
insert into europe.wso1(wso_id, wso1_id) 
    select distinct on (wso_id, wso1_id)  wso_id, wso1_id from ccm21.riversegments r where wso_id in ('383','2005','8639','38619');
     UPDATE europe.wso1 set area='Elbe' where area IS NULL;
delete from europe.wso1 where wso_id='130439'
delete from europe.wso where wso_id='129487' and area='France'
delete from europe.wso where wso_id='442353' and area='France'
delete from europe.wso1 where wso_id='291110' and area='RhoneMediterranee'

---uga2010
insert into uga2010.wso1(wso_id, wso1_id) 
    select distinct on (wso_id, wso1_id)  wso_id, wso1_id from ccm21.riversegments r where wso_id='353376'
        UPDATE uga2010.wso1 set uga='Bretagne' where uga IS NULL;
insert into uga2010.wso1(wso_id, wso1_id) 
    select distinct on (wso_id, wso1_id)  wso_id, wso1_id from ccm21.riversegments r where wso_id='342310'
        UPDATE uga2010.wso1 set uga='SeineNormandie' where uga IS NULL;
delete from uga2010.wso where wso_id='442529'
delete from uga2010.wso1 where wso_id='130439'
delete from uga2010.wso1 where wso_id='291110' and uga='RhoneMediterranee'

---Western
delete from europe.wso1 where wso_id='84023'  (3wso1_id deleted)
delete from europe.wso where wso_id='84023' and area="Western"

---Anglian
delete from europe.wso where wso_id in ('85180','83795') and area='Anglian'
delete from europe.wso1 where wso_id in ('85180','83795')

---France
insert into europe.wso1(wso_id,wso1_id)
    select wso_id, wso1_id from europe.wso1 where area in ('Corse','Bretagne','SeineNormandie','RhoneMediterranee','Loire','Meuse','Rhin','Adour','Garonne','ArtoisPicardie');
    update europe.wso1 set area='France' where area IS NULL;

--- CREATE A VIEW of europe.wso1
drop view if exists europe.e_wso1;
CREATE VIEW europe.e_wso1 AS select r.*, e.area as area1 from ccm21.riversegments r inner join europe.wso1 e on e.wso1_id=r.wso1_id;