Cookbook CCM21_EcologicalRegion – EDA

Context Navigation

Version 15 (modified by celine, 14 years ago) (diff)
--

back to first page ..
back to CookBook Eda
back to UGA
back to Ecological regions

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')

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 lignes


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

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


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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Download in other formats:

Plain Text