Changes between Version 12 and Version 13 of OriaEda

Timestamp:

Nov 23, 2010 2:56:20 PM (14 years ago)

Author:

cedric

Comment:

--

OriaEda

@@ -139 +139 @@
 copy oria.obstacles  from 'c:/base/obstacles.csv' with csv delimiter as ';' header;
 }}}
+= Corinne landcover =
+{{{
+--------------------------------------------
+---------------------------------------------
+-- Corinne Landcover
+---------------------------------------------
+---------------------------------------------
+--------------------------------------
+--------------------------------------
+--SURFACE CUT
+--------------------------------------
+--------------------------------------
+DROP TABLE IF EXISTS clc.clipped_spain;
+CREATE TABLE clc.clipped_spain AS
+SELECT intersected.clcgid, intersected.gid, code_00,the_geom
+FROM (SELECT clc.gid as clcgid, c.gid,code_00, ST_Multi(ST_Intersection(clc.the_geom, c.the_geom)) the_geom
+        FROM  clc.clc00_v2_europe clc INNER JOIN 
+        (select * from ccm21.catchments c WHERE c.wso_id IN (SELECT wso_id FROM europe.wso WHERE area='Spain_Atl')) as c
+        ON  ST_Intersects (c.the_geom,clc.the_geom)
+        
+       -- AND substring(code_00 from 1 for 1)='1'
+       )  AS intersected; --2h30 min
+ALTER TABLE clc.clipped_spain ADD column id serial PRIMARY KEY;
+alter table clc.clipped_spain add CONSTRAINT enforce_dims_the_geom CHECK (ndims(the_geom) = 2);
+alter table clc.clipped_spain add  CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) = 'MULTIPOLYGON'::text OR the_geom IS NULL);
+alter table clc.clipped_spain add  CONSTRAINT enforce_srid_the_geom CHECK (srid(the_geom) = 3035);
+CREATE INDEX indexclc00clipped_spain ON clc.clipped_spain
+  USING GIST ( the_geom GIST_GEOMETRY_OPS ); 
+--------------------------------------
+--------------------------------------
+--MERGING
+--------------------------------------
+--------------------------------------
+DROP TABLE IF EXISTS clc.clipped_spain1;
+CREATE TABLE clc.clipped_spain1 AS (
+SELECT gid,code_00,
+           ST_Multi(ST_Collect(f.the_geom)) as the_geom
+         FROM (SELECT gid, code_00,(ST_Dump(the_geom)).geom As the_geom
+                                FROM
+                                 clc.clipped_spain
+                                ) As f
+GROUP BY gid,code_00); -- 5s
+ALTER TABLE clc.clipped_spain1 add column id serial PRIMARY KEY;
+alter table clc.clipped_spain1 add CONSTRAINT enforce_dims_the_geom CHECK (ndims(the_geom) = 2);
+alter table clc.clipped_spain1 add  CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) = 'MULTIPOLYGON'::text OR the_geom IS NULL);
+alter table clc.clipped_spain1 add  CONSTRAINT enforce_srid_the_geom CHECK (srid(the_geom) = 3035);
+CREATE INDEX indexclc00clipped_spain1 ON clc.clipped_spain1
+  USING GIST ( the_geom GIST_GEOMETRY_OPS );
+ALTER TABLE clc.clipped_spain1 add constraint c_ck_uk  UNIQUE(gid,code_00); -- contrainte d'unicité
+--------------------------------------
+--------------------------------------
+--AREA
+--------------------------------------
+--------------------------------------
+ALTER TABLE clc.clipped_spain1 add column area numeric;
+UPDATE clc.clipped_spain1 set area=ST_Area(the_geom); -- 9s
+--------------------------------------
+--------------------------------------
+--AREA PER COLUMN FOR CLC TYPE (agregation)
+--------------------------------------
+--------------------------------------
+SELECT gid,code_00, id,round(area) as area FROM clc.clipped_spain1 order by gid, code_00 limit 10;
+DROP TABLE IF EXISTS clc.surf_area;
+CREATE TABLE clc.surf_area AS (
+SELECT DISTINCT ON (init.gid) init.gid,
+        artificial_surfaces_11_13,
+        artificial_vegetated_14,
+         arable_land_21,
+         permanent_crops_22,
+         pastures_23,
+         heterogeneous_agricultural_24,
+         forest_31,
+         natural_32_33,
+         wetlands_4,
+         inland_waterbodies_51 ,
+         marine_water_52
+        -- SELECT * 
+         FROM (
+        SELECT  gid from clc.clipped_spain1    ) as init        
+        FULL OUTER JOIN (SELECT gid,sum(area) AS artificial_surfaces_11_13 FROM clc.clipped_spain1 WHERE 
+                        substring(code_00 from 1 for 2)='11' 
+                        OR  substring(code_00 from 1 for 2)='12'
+                        OR substring(code_00 from 1 for 2)='13' 
+                        GROUP BY gid) AS artificial_surfaces
+                       on (init.gid) =(artificial_surfaces.gid)         
+        FULL OUTER JOIN (SELECT gid,sum(area) AS artificial_vegetated_14 FROM clc.clipped_spain1 WHERE 
+                        substring(code_00 from 1 for 2)='14'
+                        GROUP BY gid) AS artificial_vegetated
+                        on artificial_vegetated.gid =init.gid
+        FULL OUTER JOIN (SELECT gid,sum(area) AS arable_land_21 FROM clc.clipped_spain1 WHERE 
+                        substring(code_00 from 1 for 2)='21'
+                        GROUP BY gid) AS arable_land
+                        on arable_land.gid =init.gid
+        FULL OUTER JOIN (SELECT gid, sum(area) AS permanent_crops_22 FROM clc.clipped_spain1 WHERE 
+                        substring(code_00 from 1 for 2)='22'
+                        GROUP BY gid) AS permanent_crops
+                        on permanent_crops.gid =init.gid
+        FULL OUTER JOIN (SELECT gid,sum(area) AS pastures_23 FROM clc.clipped_spain1 WHERE 
+                        substring(code_00 from 1 for 2)='23'
+                        GROUP BY gid) AS pastures
+                        on pastures.gid =init.gid
+        FULL OUTER JOIN (SELECT gid, sum(area) AS heterogeneous_agricultural_24 FROM clc.clipped_spain1 WHERE 
+                        substring(code_00 from 1 for 2)='24'
+                        GROUP BY gid) AS heterogeneous_agricultural
+                        on heterogeneous_agricultural.gid =init.gid
+        FULL OUTER JOIN (SELECT gid,sum(area) AS forest_31 FROM clc.clipped_spain1 WHERE 
+                        substring(code_00 from 1 for 2)='31'
+                        GROUP BY gid) AS forest
+                        ON forest.gid =init.gid
+        FULL OUTER JOIN (SELECT gid,sum(area) AS natural_32_33 FROM clc.clipped_spain1 WHERE 
+                        substring(code_00 from 1 for 2)='32'
+                        OR  substring(code_00 from 1 for 2)='33'
+                        GROUP BY gid) AS nature
+                        ON nature.gid =init.gid
+        FULL OUTER JOIN (SELECT gid, sum(area) AS wetlands_4  FROM clc.clipped_spain1 WHERE 
+                        substring(code_00 from 1 for 1)='4'
+                        GROUP BY gid) AS wetlands
+                        on wetlands.gid =init.gid
+        FULL OUTER JOIN (SELECT gid,sum(area) AS inland_waterbodies_51 FROM clc.clipped_spain1 WHERE 
+                        substring(code_00 from 1 for 2)='51'
+                        GROUP BY gid) AS waterbodies
+                        on waterbodies.gid =init.gid
+        FULL OUTER JOIN (SELECT gid,sum(area) AS marine_water_52 FROM clc.clipped_spain1 WHERE 
+                        substring(code_00 from 1 for 2)='52'
+                        GROUP BY gid) AS marine_water
+                        on marine_water.gid =init.gid); --375 ms
+ALTER TABLE clc.surf_area ADD CONSTRAINT c_pk_gid_surf_area PRIMARY KEY (gid);
+SELECT * FROM clc.surf_area;
+--------------------------------------
+--------------------------------------
+--REMOVING ZEROS AND JOINING RIVERSEGMENTS AND CATCHMENTS TABLES
+--------------------------------------
+--------------------------------------
+DROP TABLE IF EXISTS clc.surf_area1;
+CREATE TABLE clc.surf_area1 AS( 
+SELECT 
+        r.gid,
+        area/1e6 as catchment_area,
+        CASE WHEN artificial_surfaces_11_13 IS NOT NULL THEN artificial_surfaces_11_13/1e6
+        ELSE 0
+        END AS artificial_surfaces_11_13,
+        CASE WHEN artificial_vegetated_14 IS NOT NULL THEN artificial_vegetated_14/1e6 
+        ELSE 0
+        END AS artificial_vegetated_14,
+        CASE WHEN arable_land_21 IS NOT NULL THEN arable_land_21/1e6 
+        ELSE 0
+        END AS arable_land_21,
+        CASE WHEN permanent_crops_22 IS NOT NULL THEN permanent_crops_22/1e6 
+        ELSE 0
+        END AS permanent_crops_22,
+        CASE WHEN pastures_23 IS NOT NULL THEN pastures_23/1e6 
+        ELSE 0
+        END AS pastures_23,
+        CASE WHEN heterogeneous_agricultural_24 IS NOT NULL THEN heterogeneous_agricultural_24/1e6
+        ELSE 0
+        END AS heterogeneous_agricultural_24,
+        CASE WHEN forest_31 IS NOT NULL THEN forest_31/1e6 
+        ELSE 0
+        END AS forest_31,
+        CASE WHEN natural_32_33 IS NOT NULL THEN natural_32_33/1e6 
+        ELSE 0
+        END AS natural_32_33,
+        CASE WHEN wetlands_4 IS NOT NULL THEN wetlands_4/1e6 
+        ELSE 0
+        END AS wetlands_4,
+        CASE WHEN inland_waterbodies_51 IS NOT NULL THEN inland_waterbodies_51 /1e6 
+        ELSE 0
+        END AS inland_waterbodies_51,
+        CASE WHEN  marine_water_52 IS NOT NULL THEN marine_water_52/1e6 
+        ELSE 0
+        END AS marine_water_52,
+        c.wso1_id,
+        c.the_geom      
+FROM clc.surf_area p
+JOIN ccm21.catchments c ON c.gid=p.gid
+JOIN ccm21.riversegments r on r.wso1_id=c.wso1_id
+);
+--------------------------------------
+--------------------------------------
+--COMPARISON OF SURFACES FROM THE CCM AND CLC
+--------------------------------------
+--------------------------------------
+DROP TABLE IF EXISTS clc.surf_area_analyse;
+CREATE TABLE clc.surf_area_analyse AS( 
+SELECT 
+        gid,
+        wso1_id,
+        catchment_area,
+        artificial_surfaces_11_13+
+         artificial_vegetated_14+
+         arable_land_21+
+         permanent_crops_22+
+         pastures_23+
+         heterogeneous_agricultural_24+
+         forest_31+
+         natural_32_33+
+         wetlands_4+
+         inland_waterbodies_51 +
+         marine_water_52 as sum_clc_area ,
+        (artificial_surfaces_11_13+
+         artificial_vegetated_14+
+         arable_land_21+
+         permanent_crops_22+
+         pastures_23+
+         heterogeneous_agricultural_24+
+         forest_31+
+         natural_32_33+
+         wetlands_4+
+         inland_waterbodies_51 +
+         marine_water_52)/catchment_area AS pourc_clc,
+         the_geom
+         FROM clc.surf_area1);
+ALTER TABLE clc.surf_area_analyse add CONSTRAINT c_pk_gid_area_analyse PRIMARY KEY (gid);
+alter table clc.surf_area_analyse add CONSTRAINT enforce_dims_the_geom CHECK (ndims(the_geom) = 2);
+alter table clc.surf_area_analyse add  CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) = 'MULTIPOLYGON'::text OR the_geom IS NULL);
+alter table clc.surf_area_analyse add  CONSTRAINT enforce_srid_the_geom CHECK (srid(the_geom) = 3035);
+CREATE INDEX indexclc00area_analyse ON clc.surf_area_analyse
+  USING GIST ( the_geom GIST_GEOMETRY_OPS );
+
+}}}