Changes between Version 4 and Version 5 of WRBD


Ignore:
Timestamp:
Nov 24, 2010 5:14:09 PM (14 years ago)
Author:
cedric
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • WRBD

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