Changes between Version 65 and Version 66 of CLC Join


Ignore:
Timestamp:
Jun 1, 2010 5:09:27 PM (15 years ago)
Author:
cedric
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • CLC Join

    v65 v66  
    315315Proportion between the surface of the clc basins and the catchments in the ccm, in red 0.45 to 0.9 in purple 1.1 à 2 ... The error is acceptable... 
    316316  [[Image(source:data/Docs/trac/clc/surf_area_analyse.png,600px)]] [[BR]] 
    317 = A whole country = 
     317 
     318______________________________________________________________________________________________________________________________________________________ 
     319= Same but for France = 
     320Some slight change to enhance speed in the first request... 
     321{{{ 
     322-------------------------------------- 
     323-------------------------------------- 
     324--SURFACE CUT 
     325-------------------------------------- 
     326-------------------------------------- 
     327DROP TABLE IF EXISTS clc.clipped_france; 
     328CREATE TABLE clc.clipped_france AS 
     329SELECT intersected.clcgid, intersected.gid, code_00,the_geom 
     330FROM (SELECT clc.gid as clcgid, c.gid,code_00, ST_Multi(ST_Intersection(clc.the_geom, c.the_geom)) the_geom 
     331        FROM  clc.clc00_v2_europe clc INNER JOIN  
     332        (select * from ccm21.catchments c WHERE c.wso_id IN (SELECT wso_id FROM france.wso WHERE area='France')) as c 
     333        ON  ST_Intersects (c.the_geom,clc.the_geom) 
     334         
     335       -- AND substring(code_00 from 1 for 1)='1' 
     336       )  AS intersected; --1h12 min 
     337ALTER TABLE clc.clipped_france ADD column id serial PRIMARY KEY; 
     338alter table clc.clipped_france add CONSTRAINT enforce_dims_the_geom CHECK (ndims(the_geom) = 2); 
     339alter table clc.clipped_france add  CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) = 'MULTIPOLYGON'::text OR the_geom IS NULL); 
     340alter table clc.clipped_france add  CONSTRAINT enforce_srid_the_geom CHECK (srid(the_geom) = 3035); 
     341CREATE INDEX indexclc00clipped_france ON clc.clipped_france 
     342  USING GIST ( the_geom GIST_GEOMETRY_OPS );  
     343-------------------------------------- 
     344-------------------------------------- 
     345--MERGING 
     346-------------------------------------- 
     347-------------------------------------- 
     348DROP TABLE IF EXISTS clc.clipped_france1; 
     349CREATE TABLE clc.clipped_france1 AS ( 
     350SELECT gid,code_00, 
     351           ST_Multi(ST_Collect(f.the_geom)) as the_geom 
     352         FROM (SELECT gid, code_00,(ST_Dump(the_geom)).geom As the_geom 
     353                                FROM 
     354                                 clc.clipped_france 
     355                                ) As f 
     356GROUP BY gid,code_00); -- 5s 
     357ALTER TABLE clc.clipped_france1 add column id serial PRIMARY KEY; 
     358alter table clc.clipped_france1 add CONSTRAINT enforce_dims_the_geom CHECK (ndims(the_geom) = 2); 
     359alter table clc.clipped_france1 add  CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) = 'MULTIPOLYGON'::text OR the_geom IS NULL); 
     360alter table clc.clipped_france1 add  CONSTRAINT enforce_srid_the_geom CHECK (srid(the_geom) = 3035); 
     361CREATE INDEX indexclc00clipped_france1 ON clc.clipped_france1 
     362  USING GIST ( the_geom GIST_GEOMETRY_OPS ); 
     363ALTER TABLE clc.clipped_france1 add constraint c_ck_uk  UNIQUE(gid,code_00); -- contrainte d'unicité 
     364-------------------------------------- 
     365-------------------------------------- 
     366--AREA 
     367-------------------------------------- 
     368-------------------------------------- 
     369ALTER TABLE clc.clipped_france1 add column area numeric; 
     370UPDATE clc.clipped_france1 set area=ST_Area(the_geom); -- 9s 
     371-------------------------------------- 
     372-------------------------------------- 
     373--AREA PER COLUMN FOR CLC TYPE (agregation) 
     374-------------------------------------- 
     375-------------------------------------- 
     376SELECT gid,code_00, id,round(area) as area FROM clc.clipped_france1 order by gid, code_00 limit 10; 
     377DROP TABLE IF EXISTS clc.surf_area; 
     378CREATE TABLE clc.surf_area AS ( 
     379SELECT DISTINCT ON (init.gid) init.gid, 
     380        artificial_surfaces_11_13, 
     381        artificial_vegetated_14, 
     382         arable_land_21, 
     383         permanent_crops_22, 
     384         pastures_23, 
     385         heterogeneous_agricultural_24, 
     386         forest_31, 
     387         natural_32_33, 
     388         wetlands_4, 
     389         inland_waterbodies_51 , 
     390         marine_water_52 
     391        -- SELECT *  
     392         FROM ( 
     393        SELECT  gid from clc.clipped_france1    ) as init         
     394        FULL OUTER JOIN (SELECT gid,sum(area) AS artificial_surfaces_11_13 FROM clc.clipped_france1 WHERE  
     395                        substring(code_00 from 1 for 2)='11'  
     396                        OR  substring(code_00 from 1 for 2)='12' 
     397                        OR substring(code_00 from 1 for 2)='13'  
     398                        GROUP BY gid) AS artificial_surfaces 
     399                       on (init.gid) =(artificial_surfaces.gid)          
     400        FULL OUTER JOIN (SELECT gid,sum(area) AS artificial_vegetated_14 FROM clc.clipped_france1 WHERE  
     401                        substring(code_00 from 1 for 2)='14' 
     402                        GROUP BY gid) AS artificial_vegetated 
     403                        on artificial_vegetated.gid =init.gid 
     404        FULL OUTER JOIN (SELECT gid,sum(area) AS arable_land_21 FROM clc.clipped_france1 WHERE  
     405                        substring(code_00 from 1 for 2)='21' 
     406                        GROUP BY gid) AS arable_land 
     407                        on arable_land.gid =init.gid 
     408        FULL OUTER JOIN (SELECT gid, sum(area) AS permanent_crops_22 FROM clc.clipped_france1 WHERE  
     409                        substring(code_00 from 1 for 2)='22' 
     410                        GROUP BY gid) AS permanent_crops 
     411                        on permanent_crops.gid =init.gid 
     412        FULL OUTER JOIN (SELECT gid,sum(area) AS pastures_23 FROM clc.clipped_france1 WHERE  
     413                        substring(code_00 from 1 for 2)='23' 
     414                        GROUP BY gid) AS pastures 
     415                        on pastures.gid =init.gid 
     416        FULL OUTER JOIN (SELECT gid, sum(area) AS heterogeneous_agricultural_24 FROM clc.clipped_france1 WHERE  
     417                        substring(code_00 from 1 for 2)='24' 
     418                        GROUP BY gid) AS heterogeneous_agricultural 
     419                        on heterogeneous_agricultural.gid =init.gid 
     420        FULL OUTER JOIN (SELECT gid,sum(area) AS forest_31 FROM clc.clipped_france1 WHERE  
     421                        substring(code_00 from 1 for 2)='31' 
     422                        GROUP BY gid) AS forest 
     423                        ON forest.gid =init.gid 
     424        FULL OUTER JOIN (SELECT gid,sum(area) AS natural_32_33 FROM clc.clipped_france1 WHERE  
     425                        substring(code_00 from 1 for 2)='32' 
     426                        OR  substring(code_00 from 1 for 2)='33' 
     427                        GROUP BY gid) AS nature 
     428                        ON nature.gid =init.gid 
     429        FULL OUTER JOIN (SELECT gid, sum(area) AS wetlands_4  FROM clc.clipped_france1 WHERE  
     430                        substring(code_00 from 1 for 1)='4' 
     431                        GROUP BY gid) AS wetlands 
     432                        on wetlands.gid =init.gid 
     433        FULL OUTER JOIN (SELECT gid,sum(area) AS inland_waterbodies_51 FROM clc.clipped_france1 WHERE  
     434                        substring(code_00 from 1 for 2)='51' 
     435                        GROUP BY gid) AS waterbodies 
     436                        on waterbodies.gid =init.gid 
     437        FULL OUTER JOIN (SELECT gid,sum(area) AS marine_water_52 FROM clc.clipped_france1 WHERE  
     438                        substring(code_00 from 1 for 2)='52' 
     439                        GROUP BY gid) AS marine_water 
     440                        on marine_water.gid =init.gid); --375 ms 
     441ALTER TABLE clc.surf_area ADD CONSTRAINT c_pk_gid_surf_area PRIMARY KEY (gid); 
     442SELECT * FROM clc.surf_area; 
     443-------------------------------------- 
     444-------------------------------------- 
     445--REMOVING ZEROS AND JOINING RIVERSEGMENTS AND CATCHMENTS TABLES 
     446-------------------------------------- 
     447-------------------------------------- 
     448DROP TABLE IF EXISTS clc.surf_area1; 
     449CREATE TABLE clc.surf_area1 AS(  
     450SELECT  
     451        r.gid, 
     452        area/1e6 as catchment_area, 
     453        CASE WHEN artificial_surfaces_11_13 IS NOT NULL THEN artificial_surfaces_11_13/1e6 
     454        ELSE 0 
     455        END AS artificial_surfaces_11_13, 
     456        CASE WHEN artificial_vegetated_14 IS NOT NULL THEN artificial_vegetated_14/1e6  
     457        ELSE 0 
     458        END AS artificial_vegetated_14, 
     459        CASE WHEN arable_land_21 IS NOT NULL THEN arable_land_21/1e6  
     460        ELSE 0 
     461        END AS arable_land_21, 
     462        CASE WHEN permanent_crops_22 IS NOT NULL THEN permanent_crops_22/1e6  
     463        ELSE 0 
     464        END AS permanent_crops_22, 
     465        CASE WHEN pastures_23 IS NOT NULL THEN pastures_23/1e6  
     466        ELSE 0 
     467        END AS pastures_23, 
     468        CASE WHEN heterogeneous_agricultural_24 IS NOT NULL THEN heterogeneous_agricultural_24/1e6 
     469        ELSE 0 
     470        END AS heterogeneous_agricultural_24, 
     471        CASE WHEN forest_31 IS NOT NULL THEN forest_31/1e6  
     472        ELSE 0 
     473        END AS forest_31, 
     474        CASE WHEN natural_32_33 IS NOT NULL THEN natural_32_33/1e6  
     475        ELSE 0 
     476        END AS natural_32_33, 
     477        CASE WHEN wetlands_4 IS NOT NULL THEN wetlands_4/1e6  
     478        ELSE 0 
     479        END AS wetlands_4, 
     480        CASE WHEN inland_waterbodies_51 IS NOT NULL THEN inland_waterbodies_51 /1e6  
     481        ELSE 0 
     482        END AS inland_waterbodies_51, 
     483        CASE WHEN  marine_water_52 IS NOT NULL THEN marine_water_52/1e6  
     484        ELSE 0 
     485        END AS marine_water_52, 
     486        c.wso1_id, 
     487        c.the_geom       
     488FROM clc.surf_area p 
     489JOIN ccm21.catchments c ON c.gid=p.gid 
     490JOIN ccm21.riversegments r on r.wso1_id=c.wso1_id 
     491); 
     492-------------------------------------- 
     493-------------------------------------- 
     494--COMPARISON OF SURFACES FROM THE CCM AND CLC 
     495-------------------------------------- 
     496-------------------------------------- 
     497DROP TABLE IF EXISTS clc.surf_area_analyse; 
     498CREATE TABLE clc.surf_area_analyse AS(  
     499SELECT  
     500        gid, 
     501        wso1_id, 
     502        catchment_area, 
     503        artificial_surfaces_11_13+ 
     504         artificial_vegetated_14+ 
     505         arable_land_21+ 
     506         permanent_crops_22+ 
     507         pastures_23+ 
     508         heterogeneous_agricultural_24+ 
     509         forest_31+ 
     510         natural_32_33+ 
     511         wetlands_4+ 
     512         inland_waterbodies_51 + 
     513         marine_water_52 as sum_clc_area , 
     514        (artificial_surfaces_11_13+ 
     515         artificial_vegetated_14+ 
     516         arable_land_21+ 
     517         permanent_crops_22+ 
     518         pastures_23+ 
     519         heterogeneous_agricultural_24+ 
     520         forest_31+ 
     521         natural_32_33+ 
     522         wetlands_4+ 
     523         inland_waterbodies_51 + 
     524         marine_water_52)/catchment_area AS pourc_clc, 
     525         the_geom 
     526         FROM clc.surf_area1); 
     527ALTER TABLE clc.surf_area_analyse add CONSTRAINT c_pk_gid_area_analyse PRIMARY KEY (gid); 
     528alter table clc.surf_area_analyse add CONSTRAINT enforce_dims_the_geom CHECK (ndims(the_geom) = 2); 
     529alter table clc.surf_area_analyse add  CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) = 'MULTIPOLYGON'::text OR the_geom IS NULL); 
     530alter table clc.surf_area_analyse add  CONSTRAINT enforce_srid_the_geom CHECK (srid(the_geom) = 3035); 
     531CREATE INDEX indexclc00area_analyse ON clc.surf_area_analyse 
     532  USING GIST ( the_geom GIST_GEOMETRY_OPS ); 
     533}}} 
    318534{{{ 
    319535#!html