back to first page [..][[BR]]
back to ["CookBook Eda"][[BR]]
back to ["Limites administratives Europe"][[BR]]

Pour la France mettre : France (gid=11)+Andorre (gid=2) +Monaco (gid=27) ?[[BR]]
{{{
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;

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

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


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

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

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


CREATE INDEX europe_wso_id
  ON france.wso
  (wso_id);
}}}