| Version 2 (modified by celine, 14 years ago) (diff) |
|---|
back to first page ..
back to CookBook Eda
back to Limites administratives 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
---Spain-Atl
insert into europe.wso1(wso_id, wso1_id)
(select wso_id, wso1_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.wso1 set area='Spain_Atl' where area IS NULL; --348 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';
