Changes between Version 1 and Version 2 of Calculating CorineLandCover_Ireland


Ignore:
Timestamp:
Dec 3, 2010 4:55:06 PM (14 years ago)
Author:
celine
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • Calculating CorineLandCover_Ireland

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