back to first page [..][[BR]]
back to ["CookBook Eda"][[BR]]
back to ["UGA"][[BR]]
back to ["Ecological regions"][[BR]]


||ecoregion||eco_code||dmeereea40||
||Southern Temperate Atlantic||11||1027||
||Western European broadleaf forests||55||1037||
||Northeastern Spain & Southern France Mediterranean||162||1164||
||Cantabrian mixed forests||14||1172||
||North Atlantic moist mixed forests||38||883||
||Celtic broadleaf forests||17||894||
||Corsican montane broadleaf and mixed forests||154||1268||
||Appenine deciduous montane forests||10||1266||
||Appenine deciduous montane forests||10||1275||
||Tyrrhenian-Adriatic sclerophyllous and mixed forests||169||1252||
||Pyrenees conifer and mixed forests||43||1209||
||Alps conifer and mixed forests||56||1116||
||Italian sclerophyllous and semi-deciduous forests||161||1171||
||Celtic broadleaf forests||17||801||
||Tyrrhenian-Adriatic sclerophyllous and mixed forests||169||1320||
||English Lowlands beech forests||31||1011||
||Northen Temperate Atlantic||888||912||
||Central European mixed forests||20||878||
||Northen Temperate Atlantic||888||1014||
||Southern Temperate Atlantic||11||1004||

{{{
"dmeereea40" IN ('1037','1027','1164','1172','883','894','1268','1266','1275','1252','1209','1116','1171','801','1320','1011','912','878','1014','1004')
}}}

{{{
#!sql
drop table  if exists ecoregion2003.wso1;
     CREATE TABLE ecoregion2003.wso1 (
     id serial PRIMARY KEY,
     wso_id integer,
     wso1_id integer,
     ecoregion varchar(99)
     );

 --Southern Temperate Atlantic
insert into ecoregion2003.wso1(wso_id, wso1_id) 
    select distinct on (wso1_id, wso_id) wso_id, wso1_id from ccm21.riversegments r
    join (SELECT ST_Union(f.the_geom) as singlegeom
        FROM ecoregion2003.ecologicalregion_polygon As f where dmeereea40='1027') as sub
    ON ST_Intersects(sub.singlegeom,r.the_geom);

    UPDATE ecoregion2003.wso1 set ecoregion='Southern Temperate Atlantic' where ecoregion IS NULL -- 20485 lines


 --Western European broadleaf forests
insert into ecoregion2003.wso1(wso_id, wso1_id) 
    select distinct on (wso1_id, wso_id) wso_id, wso1_id from ccm21.riversegments r
    join (SELECT ST_Union(f.the_geom) as singlegeom
        FROM ecoregion2003.ecologicalregion_polygon As f where dmeereea40='1037') as sub
    ON ST_Intersects(sub.singlegeom,r.the_geom);

    UPDATE ecoregion2003.wso1 set ecoregion='Western European broadleaf forests' where ecoregion IS NULL -- 58387 lines

 --Northeastern Spain & Southern France Mediterranean
insert into ecoregion2003.wso1(wso_id, wso1_id) 
    select distinct on (wso1_id, wso_id) wso_id, wso1_id from ccm21.riversegments r
    join (SELECT ST_Union(f.the_geom) as singlegeom
        FROM ecoregion2003.ecologicalregion_polygon As f where dmeereea40='1164') as sub
    ON ST_Intersects(sub.singlegeom,r.the_geom);

    UPDATE ecoregion2003.wso1 set ecoregion='Northeastern Spain & Southern France Mediterranean' where ecoregion IS NULL -- 20813 lines


 --Cantabrian mixed forests
insert into ecoregion2003.wso1(wso_id, wso1_id) 
    select distinct on (wso1_id, wso_id) wso_id, wso1_id from ccm21.riversegments r
    join (SELECT ST_Union(f.the_geom) as singlegeom
        FROM ecoregion2003.ecologicalregion_polygon As f where dmeereea40='1172') as sub
    ON ST_Intersects(sub.singlegeom,r.the_geom);

    UPDATE ecoregion2003.wso1 set ecoregion='Cantabrian mixed forests' where ecoregion IS NULL -- 26327 lines

 --North Atlantic moist mixed forests
insert into ecoregion2003.wso1(wso_id, wso1_id) 
    select distinct on (wso1_id, wso_id) wso_id, wso1_id from ccm21.riversegments r
    join (SELECT ST_Union(f.the_geom) as singlegeom
        FROM ecoregion2003.ecologicalregion_polygon As f where dmeereea40='883') as sub
    ON ST_Intersects(sub.singlegeom,r.the_geom);

    UPDATE ecoregion2003.wso1 set ecoregion='North Atlantic moist mixed forests' where ecoregion IS NULL -- 1649 lines

 --Celtic broadleaf forests
insert into ecoregion2003.wso1(wso_id, wso1_id) 
    select distinct on (wso1_id, wso_id) wso_id, wso1_id from ccm21.riversegments r
    join (SELECT ST_Union(f.the_geom) as singlegeom
        FROM ecoregion2003.ecologicalregion_polygon As f where dmeereea40='894') as sub
    ON ST_Intersects(sub.singlegeom,r.the_geom);

    UPDATE ecoregion2003.wso1 set ecoregion='Celtic broadleaf forests' where ecoregion IS NULL -- 7186 lines

 --Corsican montane broadleaf and mixed forests
insert into ecoregion2003.wso1(wso_id, wso1_id) 
    select distinct on (wso1_id, wso_id) wso_id, wso1_id from ccm21.riversegments r
    join (SELECT ST_Union(f.the_geom) as singlegeom
        FROM ecoregion2003.ecologicalregion_polygon As f where dmeereea40='1268') as sub
    ON ST_Intersects(sub.singlegeom,r.the_geom);

    UPDATE ecoregion2003.wso1 set ecoregion='Corsican montane broadleaf and mixed forests' where ecoregion IS NULL -- 2848 lines

 --Appenine deciduous montane forests
insert into ecoregion2003.wso1(wso_id, wso1_id) 
    select distinct on (wso1_id, wso_id) wso_id, wso1_id from ccm21.riversegments r
    join (SELECT ST_Union(f.the_geom) as singlegeom
        FROM ecoregion2003.ecologicalregion_polygon As f where dmeereea40='1266') as sub
    ON ST_Intersects(sub.singlegeom,r.the_geom);

    UPDATE ecoregion2003.wso1 set ecoregion='Appenine deciduous montane forests' where ecoregion IS NULL -- 27 lines

 --Appenine deciduous montane forests
insert into ecoregion2003.wso1(wso_id, wso1_id) 
    select distinct on (wso1_id, wso_id) wso_id, wso1_id from ccm21.riversegments r
    join (SELECT ST_Union(f.the_geom) as singlegeom
        FROM ecoregion2003.ecologicalregion_polygon As f where dmeereea40='1275') as sub
    ON ST_Intersects(sub.singlegeom,r.the_geom);

    UPDATE ecoregion2003.wso1 set ecoregion='Appenine deciduous montane forests' where ecoregion IS NULL -- 46 lines

 --Tyrrhenian-Adriatic sclerophyllous and mixed forests
insert into ecoregion2003.wso1(wso_id, wso1_id) 
    select distinct on (wso1_id, wso_id) wso_id, wso1_id from ccm21.riversegments r
    join (SELECT ST_Union(f.the_geom) as singlegeom
        FROM ecoregion2003.ecologicalregion_polygon As f where dmeereea40='1252') as sub
    ON ST_Intersects(sub.singlegeom,r.the_geom);

    UPDATE ecoregion2003.wso1 set ecoregion='Tyrrhenian-Adriatic sclerophyllous and mixed forests' where ecoregion IS NULL -- 2079 lines

 --Pyrenees conifer and mixed forests
insert into ecoregion2003.wso1(wso_id, wso1_id) 
    select distinct on (wso1_id, wso_id) wso_id, wso1_id from ccm21.riversegments r
    join (SELECT ST_Union(f.the_geom) as singlegeom
        FROM ecoregion2003.ecologicalregion_polygon As f where dmeereea40='1209') as sub
    ON ST_Intersects(sub.singlegeom,r.the_geom);

    UPDATE ecoregion2003.wso1 set ecoregion='Pyrenees conifer and mixed forests' where ecoregion IS NULL -- 15222 lines

 --Alps conifer and mixed forests
insert into ecoregion2003.wso1(wso_id, wso1_id) 
    select distinct on (wso1_id, wso_id) wso_id, wso1_id from ccm21.riversegments r
    join (SELECT ST_Union(f.the_geom) as singlegeom
        FROM ecoregion2003.ecologicalregion_polygon As f where dmeereea40='1116') as sub
    ON ST_Intersects(sub.singlegeom,r.the_geom);

    UPDATE ecoregion2003.wso1 set ecoregion='Alps conifer and mixed forests' where ecoregion IS NULL -- 69641 lines

 --Italian sclerophyllous and semi-deciduous forests
insert into ecoregion2003.wso1(wso_id, wso1_id) 
    select distinct on (wso1_id, wso_id) wso_id, wso1_id from ccm21.riversegments r
    join (SELECT ST_Union(f.the_geom) as singlegeom
        FROM ecoregion2003.ecologicalregion_polygon As f where dmeereea40='1171') as sub
    ON ST_Intersects(sub.singlegeom,r.the_geom);

    UPDATE ecoregion2003.wso1 set ecoregion='Italian sclerophyllous and semi-deciduous forests' where ecoregion IS NULL -- 27451 lines

 --Celtic broadleaf forests
insert into ecoregion2003.wso1(wso_id, wso1_id) 
    select distinct on (wso1_id, wso_id) wso_id, wso1_id from ccm21.riversegments r
    join (SELECT ST_Union(f.the_geom) as singlegeom
        FROM ecoregion2003.ecologicalregion_polygon As f where dmeereea40='801') as sub
    ON ST_Intersects(sub.singlegeom,r.the_geom);

    UPDATE ecoregion2003.wso1 set ecoregion='Celtic broadleaf forests' where ecoregion IS NULL -- 11407 lines

 --Tyrrhenian-Adriatic sclerophyllous and mixed forests
insert into ecoregion2003.wso1(wso_id, wso1_id) 
    select distinct on (wso1_id, wso_id) wso_id, wso1_id from ccm21.riversegments r
    join (SELECT ST_Union(f.the_geom) as singlegeom
        FROM ecoregion2003.ecologicalregion_polygon As f where dmeereea40='1320') as sub
    ON ST_Intersects(sub.singlegeom,r.the_geom);

    UPDATE ecoregion2003.wso1 set ecoregion='Tyrrhenian-Adriatic sclerophyllous and mixed forests' where ecoregion IS NULL -- 3797 lines

 --English Lowlands beech forests
insert into ecoregion2003.wso1(wso_id, wso1_id) 
    select distinct on (wso1_id, wso_id) wso_id, wso1_id from ccm21.riversegments r
    join (SELECT ST_Union(f.the_geom) as singlegeom
        FROM ecoregion2003.ecologicalregion_polygon As f where dmeereea40='1011') as sub
    ON ST_Intersects(sub.singlegeom,r.the_geom);

    UPDATE ecoregion2003.wso1 set ecoregion='English Lowlands beech forests' where ecoregion IS NULL -- 2822 lines

 --Northen Temperate Atlantic
insert into ecoregion2003.wso1(wso_id, wso1_id) 
    select distinct on (wso1_id, wso_id) wso_id, wso1_id from ccm21.riversegments r
    join (SELECT ST_Union(f.the_geom) as singlegeom
        FROM ecoregion2003.ecologicalregion_polygon As f where dmeereea40='912') as sub
    ON ST_Intersects(sub.singlegeom,r.the_geom);

    UPDATE ecoregion2003.wso1 set ecoregion='Northen Temperate Atlantic' where ecoregion IS NULL -- 3878 lines

 --Central European mixed forests
insert into ecoregion2003.wso1(wso_id, wso1_id) 
    select distinct on (wso1_id, wso_id) wso_id, wso1_id from ccm21.riversegments r
    join (SELECT ST_Union(f.the_geom) as singlegeom
        FROM ecoregion2003.ecologicalregion_polygon As f where dmeereea40='878') as sub
    ON ST_Intersects(sub.singlegeom,r.the_geom);

    UPDATE ecoregion2003.wso1 set ecoregion='Central European mixed forests' where ecoregion IS NULL -- 26863 lines

 --Northen Temperate Atlantic
insert into ecoregion2003.wso1(wso_id, wso1_id) 
    select distinct on (wso1_id, wso_id) wso_id, wso1_id from ccm21.riversegments r
    join (SELECT ST_Union(f.the_geom) as singlegeom
        FROM ecoregion2003.ecologicalregion_polygon As f where dmeereea40='1014') as sub
    ON ST_Intersects(sub.singlegeom,r.the_geom);

    UPDATE ecoregion2003.wso1 set ecoregion='Northen Temperate Atlantic' where ecoregion IS NULL -- 63 lines

 --Southern Temperate Atlantic
insert into ecoregion2003.wso1(wso_id, wso1_id) 
    select distinct on (wso1_id, wso_id) wso_id, wso1_id from ccm21.riversegments r
    join (SELECT ST_Union(f.the_geom) as singlegeom
        FROM ecoregion2003.ecologicalregion_polygon As f where dmeereea40='1004') as sub
    ON ST_Intersects(sub.singlegeom,r.the_geom);

    UPDATE ecoregion2003.wso1 set ecoregion='Southern Temperate Atlantic' where ecoregion IS NULL -- 265 lines

}}}