Changes between Version 45 and Version 46 of WRBD


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

--

Legend:

Unmodified
Added
Removed
Modified

  • WRBD

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