| 57 | | Pour la France mettre : France (gid=11)+Andorre (gid=2) +Monaco (gid=27) ? |
| 58 | | {{{ |
| 59 | | drop table if exists europe.wso; |
| 60 | | CREATE TABLE europe.wso ( |
| 61 | | id serial PRIMARY KEY, |
| 62 | | wso_id integer, |
| 63 | | area varchar(12) |
| 64 | | ) |
| 65 | | |
| 66 | | ----France |
| 67 | | insert into europe.wso(wso_id) |
| 68 | | select distinct on (wso_id) wso_id from ccm21.riversegments r |
| 69 | | join (SELECT ST_Union(f.the_geom) as singlegeom |
| 70 | | FROM europe.limiteeurope As f where gid='11'or gid='2'or gid='27' ) as sub |
| 71 | | ON ST_Intersects(sub.singlegeom,r.the_geom); |
| 72 | | |
| 73 | | UPDATE europe.wso set area='France' where area IS NULL --680 sea nodes |
| 74 | | |
| 75 | | select * from ccm21.riversegments where wso_id in (select wso_id from europe.wso where area='France')--170734 lines (with france.departement 170703 lines) |
| 76 | | |
| 77 | | ----Germany |
| 78 | | insert into europe.wso(wso_id) |
| 79 | | select distinct on (wso_id) wso_id from ccm21.riversegments r |
| 80 | | join (SELECT ST_Union(f.the_geom) as singlegeom |
| 81 | | FROM europe.limiteeurope As f where gid='12') as sub |
| 82 | | ON ST_Intersects(sub.singlegeom,r.the_geom); |
| 83 | | |
| 84 | | UPDATE europe.wso set area='Germany' where area IS NULL |
| 85 | | |
| 86 | | ----Spain (+Gibraltar) |
| 87 | | insert into europe.wso(wso_id) |
| 88 | | select distinct on (wso_id) wso_id from ccm21.riversegments r |
| 89 | | join (SELECT ST_Union(f.the_geom) as singlegeom |
| 90 | | FROM europe.limiteeurope As f where gid='37' or gid='13') as sub |
| 91 | | ON ST_Intersects(sub.singlegeom,r.the_geom); |
| 92 | | |
| 93 | | UPDATE europe.wso set area='Spain' where area IS NULL |
| 94 | | |
| 95 | | ----Italy (+San Marino) |
| 96 | | insert into europe.wso(wso_id) |
| 97 | | select distinct on (wso_id) wso_id from ccm21.riversegments r |
| 98 | | join (SELECT ST_Union(f.the_geom) as singlegeom |
| 99 | | FROM europe.limiteeurope As f where gid='19' or gid='33') as sub |
| 100 | | ON ST_Intersects(sub.singlegeom,r.the_geom); |
| 101 | | |
| 102 | | UPDATE europe.wso set area='Spain' where area IS NULL |
| 103 | | |
| 104 | | |
| 105 | | CREATE INDEX europe_wso_id |
| 106 | | ON france.wso |
| 107 | | (wso_id); |
| 108 | | }}} |
| | 57 | ["Cookbook CCM21_Europe"] |
