Changes between Initial Version and Version 1 of Calculating CorineLandCover_Anglian


Ignore:
Timestamp:
Feb 28, 2011 1:21:58 PM (14 years ago)
Author:
celine
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • Calculating CorineLandCover_Anglian

    v1 v1  
     1back to first page[..][[BR]] 
     2back to [""][[BR]] 
     3 
     4{{{ 
     5-------------------------------------- 
     6--SURFACE CUT 
     7-------------------------------------- 
     8-------------------------------------- 
     9 
     10DROP TABLE IF EXISTS clc.clipped_anglian; 
     11CREATE TABLE clc.clipped_anglian AS 
     12SELECT intersected.clcgid, intersected.gid, code_00,the_geom 
     13FROM (SELECT clc.gid as clcgid, c.gid,code_00, ST_Multi(ST_Intersection(clc.the_geom, c.the_geom)) the_geom 
     14        FROM  clc.clc00_v2_europe clc INNER JOIN  
     15        (select * from ccm21.catchments c WHERE c.wso_id IN (SELECT wso_id FROM europe.wso WHERE area='Anglian')) as c 
     16        ON  ST_Intersects (c.the_geom,clc.the_geom) 
     17         
     18       -- AND substring(code_00 from 1 for 1)='1' 
     19       )  AS intersected; --2h30 min 
     20ALTER TABLE clc.clipped_anglian ADD column id serial PRIMARY KEY; 
     21alter table clc.clipped_anglian add CONSTRAINT enforce_dims_the_geom CHECK (ndims(the_geom) = 2); 
     22alter table clc.clipped_anglian add  CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) = 'MULTIPOLYGON'::text OR the_geom IS NULL); 
     23alter table clc.clipped_anglian add  CONSTRAINT enforce_srid_the_geom CHECK (srid(the_geom) = 3035); 
     24CREATE INDEX indexclc00clipped_anglian ON clc.clipped_anglian 
     25  USING GIST ( the_geom GIST_GEOMETRY_OPS );  
     26-------------------------------------- 
     27-------------------------------------- 
     28--MERGING 
     29-------------------------------------- 
     30-------------------------------------- 
     31DROP TABLE IF EXISTS clc.clipped_anglian1; 
     32CREATE TABLE clc.clipped_anglian1 AS ( 
     33SELECT gid,code_00, 
     34           ST_Multi(ST_Collect(f.the_geom)) as the_geom 
     35         FROM (SELECT gid, code_00,(ST_Dump(the_geom)).geom As the_geom 
     36                                FROM 
     37                                 clc.clipped_anglian 
     38                                ) As f  
     39GROUP BY gid,code_00);--171 s 
     40ALTER TABLE clc.clipped_anglian1 add column id serial PRIMARY KEY; 
     41alter table clc.clipped_anglian1 add CONSTRAINT enforce_dims_the_geom CHECK (ndims(the_geom) = 2); 
     42alter table clc.clipped_anglian1 add  CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) = 'MULTIPOLYGON'::text OR the_geom IS NULL); 
     43alter table clc.clipped_anglian1 add  CONSTRAINT enforce_srid_the_geom CHECK (srid(the_geom) = 3035); 
     44CREATE INDEX indexclc00clipped_anglian1 ON clc.clipped_anglian1 
     45  USING GIST ( the_geom GIST_GEOMETRY_OPS ); 
     46ALTER TABLE clc.clipped_anglian1 add constraint c_ck_uk_anglian  UNIQUE(gid,code_00); -- contrainte d'unicité 
     47-------------------------------------- 
     48-------------------------------------- 
     49--AREA 
     50-------------------------------------- 
     51-------------------------------------- 
     52ALTER TABLE clc.clipped_anglian1 add column area numeric; 
     53UPDATE clc.clipped_anglian1 set area=ST_Area(the_geom);  
     54-------------------------------------- 
     55-------------------------------------- 
     56--AREA PER COLUMN FOR CLC TYPE (agregation) 
     57-------------------------------------- 
     58-------------------------------------- 
     59SELECT gid,code_00, id,round(area) as area FROM clc.clipped_anglian1 order by gid, code_00 limit 10; 
     60DROP TABLE IF EXISTS clc.surf_area_anglian; 
     61CREATE TABLE clc.surf_area_anglian AS ( 
     62SELECT DISTINCT ON (init.gid) init.gid, 
     63        artificial_surfaces_11_13, 
     64        artificial_vegetated_14, 
     65         arable_land_21, 
     66         permanent_crops_22, 
     67         pastures_23, 
     68         heterogeneous_agricultural_24, 
     69         forest_31, 
     70         natural_32_33, 
     71         wetlands_4, 
     72         inland_waterbodies_51 , 
     73         marine_water_52 
     74        -- SELECT *  
     75         FROM ( 
     76        SELECT  gid from clc.clipped_anglian1    ) as init         
     77        FULL OUTER JOIN (SELECT gid,sum(area) AS artificial_surfaces_11_13 FROM clc.clipped_anglian1 WHERE  
     78                        substring(code_00 from 1 for 2)='11'  
     79                        OR  substring(code_00 from 1 for 2)='12' 
     80                        OR substring(code_00 from 1 for 2)='13'  
     81                        GROUP BY gid) AS artificial_surfaces 
     82                       on (init.gid) =(artificial_surfaces.gid)          
     83        FULL OUTER JOIN (SELECT gid,sum(area) AS artificial_vegetated_14 FROM clc.clipped_anglian1 WHERE  
     84                        substring(code_00 from 1 for 2)='14' 
     85                        GROUP BY gid) AS artificial_vegetated 
     86                        on artificial_vegetated.gid =init.gid 
     87        FULL OUTER JOIN (SELECT gid,sum(area) AS arable_land_21 FROM clc.clipped_anglian1 WHERE  
     88                        substring(code_00 from 1 for 2)='21' 
     89                        GROUP BY gid) AS arable_land 
     90                        on arable_land.gid =init.gid 
     91        FULL OUTER JOIN (SELECT gid, sum(area) AS permanent_crops_22 FROM clc.clipped_anglian1 WHERE  
     92                        substring(code_00 from 1 for 2)='22' 
     93                        GROUP BY gid) AS permanent_crops 
     94                        on permanent_crops.gid =init.gid 
     95        FULL OUTER JOIN (SELECT gid,sum(area) AS pastures_23 FROM clc.clipped_anglian1 WHERE  
     96                        substring(code_00 from 1 for 2)='23' 
     97                        GROUP BY gid) AS pastures 
     98                        on pastures.gid =init.gid 
     99        FULL OUTER JOIN (SELECT gid, sum(area) AS heterogeneous_agricultural_24 FROM clc.clipped_anglian1 WHERE  
     100                        substring(code_00 from 1 for 2)='24' 
     101                        GROUP BY gid) AS heterogeneous_agricultural 
     102                        on heterogeneous_agricultural.gid =init.gid 
     103        FULL OUTER JOIN (SELECT gid,sum(area) AS forest_31 FROM clc.clipped_anglian1 WHERE  
     104                        substring(code_00 from 1 for 2)='31' 
     105                        GROUP BY gid) AS forest 
     106                        ON forest.gid =init.gid 
     107        FULL OUTER JOIN (SELECT gid,sum(area) AS natural_32_33 FROM clc.clipped_anglian1 WHERE  
     108                        substring(code_00 from 1 for 2)='32' 
     109                        OR  substring(code_00 from 1 for 2)='33' 
     110                        GROUP BY gid) AS nature 
     111                        ON nature.gid =init.gid 
     112        FULL OUTER JOIN (SELECT gid, sum(area) AS wetlands_4  FROM clc.clipped_anglian1 WHERE  
     113                        substring(code_00 from 1 for 1)='4' 
     114                        GROUP BY gid) AS wetlands 
     115                        on wetlands.gid =init.gid 
     116        FULL OUTER JOIN (SELECT gid,sum(area) AS inland_waterbodies_51 FROM clc.clipped_anglian1 WHERE  
     117                        substring(code_00 from 1 for 2)='51' 
     118                        GROUP BY gid) AS waterbodies 
     119                        on waterbodies.gid =init.gid 
     120        FULL OUTER JOIN (SELECT gid,sum(area) AS marine_water_52 FROM clc.clipped_anglian1 WHERE  
     121                        substring(code_00 from 1 for 2)='52' 
     122                        GROUP BY gid) AS marine_water 
     123                        on marine_water.gid =init.gid); --375 ms 
     124ALTER TABLE clc.surf_area_anglian ADD CONSTRAINT c_pk_gid_surf_area_anglian PRIMARY KEY (gid); 
     125SELECT * FROM clc.surf_area_anglian; 
     126-------------------------------------- 
     127-------------------------------------- 
     128--REMOVING ZEROS AND JOINING RIVERSEGMENTS AND CATCHMENTS TABLES 
     129-------------------------------------- 
     130-------------------------------------- 
     131-- this table drops the previous one but final calculations are stored in surf_area_final 
     132DROP TABLE IF EXISTS clc.surf_area_anglian_final; 
     133CREATE TABLE clc.surf_area_anglian_final AS(  
     134SELECT  
     135        r.gid, 
     136        C.area/1e6 as catchment_area, 
     137        CASE WHEN p.artificial_surfaces_11_13 IS NOT NULL THEN p.artificial_surfaces_11_13/1e6 
     138        ELSE 0 
     139        END AS artificial_surfaces_11_13, 
     140        CASE WHEN p.artificial_vegetated_14 IS NOT NULL THEN p.artificial_vegetated_14/1e6  
     141        ELSE 0 
     142        END AS artificial_vegetated_14, 
     143        CASE WHEN p.arable_land_21 IS NOT NULL THEN p.arable_land_21/1e6  
     144        ELSE 0 
     145        END AS arable_land_21, 
     146        CASE WHEN p.permanent_crops_22 IS NOT NULL THEN p.permanent_crops_22/1e6  
     147        ELSE 0 
     148        END AS permanent_crops_22, 
     149        CASE WHEN p.pastures_23 IS NOT NULL THEN p.pastures_23/1e6  
     150        ELSE 0 
     151        END AS pastures_23, 
     152        CASE WHEN p.heterogeneous_agricultural_24 IS NOT NULL THEN p.heterogeneous_agricultural_24/1e6 
     153        ELSE 0 
     154        END AS heterogeneous_agricultural_24, 
     155        CASE WHEN p.forest_31 IS NOT NULL THEN p.forest_31/1e6  
     156        ELSE 0 
     157        END AS forest_31, 
     158        CASE WHEN p.natural_32_33 IS NOT NULL THEN p.natural_32_33/1e6  
     159        ELSE 0 
     160        END AS natural_32_33, 
     161        CASE WHEN p.wetlands_4 IS NOT NULL THEN p.wetlands_4/1e6  
     162        ELSE 0 
     163        END AS wetlands_4, 
     164        CASE WHEN p.inland_waterbodies_51 IS NOT NULL THEN p.inland_waterbodies_51 /1e6  
     165        ELSE 0 
     166        END AS inland_waterbodies_51, 
     167        CASE WHEN  p.marine_water_52 IS NOT NULL THEN p.marine_water_52/1e6  
     168        ELSE 0 
     169        END AS marine_water_52, 
     170        c.wso1_id, 
     171        c.the_geom       
     172FROM clc.surf_area_anglian p 
     173JOIN ccm21.catchments c ON c.gid=p.gid 
     174JOIN ccm21.riversegments r on r.wso1_id=c.wso1_id 
     175); 
     176}}}