Changes between Version 58 and Version 59 of CLC Join


Ignore:
Timestamp:
Jun 1, 2010 3:17:05 PM (15 years ago)
Author:
cedric
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • CLC Join

    v58 v59  
    150150{{{ 
    151151#!sql 
    152 DROP TABLE IF EXISTS clc.pourc_area; 
    153 CREATE TABLE clc.pourc_area AS ( 
     152SELECT gid,code_00, id,round(area) as area FROM clc.clipped_Bretagne1 order by gid, code_00 limit 10; 
     153-- here I'm keeping the surface so that we will b able to calculate for any basin 
     154-- or group of basins a percentage of surface according to the basin surface 
     155DROP TABLE IF EXISTS clc.surf_area; 
     156CREATE TABLE clc.surf_area AS ( 
    154157SELECT DISTINCT ON (init.gid) init.gid, 
    155158        urban_11_13, 
    … …  
    167170         FROM ( 
    168171        SELECT  gid from clc.clipped_bretagne1  ) as init         
    169         FULL OUTER JOIN (SELECT gid,area,code_00 AS urban_11_13 FROM clc.clipped_bretagne1 WHERE  
     172        FULL OUTER JOIN (SELECT gid,area AS urban_11_13,code_00 FROM clc.clipped_bretagne1 WHERE  
    170173                        substring(code_00 from 1 for 2)='11'  
    171174                        OR  substring(code_00 from 1 for 2)='12' 
    172175                        OR substring(code_00 from 1 for 2)='13' ) AS urban 
    173176                       on (init.gid) =(urban.gid)                
    174         FULL OUTER JOIN (SELECT gid,area,code_00 AS green_urban_14  FROM clc.clipped_bretagne1 WHERE  
     177        FULL OUTER JOIN (SELECT gid,area AS green_urban_14,code_00  FROM clc.clipped_bretagne1 WHERE  
    175178                        substring(code_00 from 1 for 2)='14') AS green_urban 
    176179                        on green_urban.gid =init.gid 
    177         FULL OUTER JOIN (SELECT gid,area,code_00 AS arable_land_21  FROM clc.clipped_bretagne1 WHERE  
     180        FULL OUTER JOIN (SELECT gid,area AS arable_land_21,code_00   FROM clc.clipped_bretagne1 WHERE  
    178181                        substring(code_00 from 1 for 2)='21') AS arable_land 
    179182                        on arable_land.gid =init.gid 
    180         FULL OUTER JOIN (SELECT gid,area,code_00 AS plantations_22  FROM clc.clipped_bretagne1 WHERE  
     183        FULL OUTER JOIN (SELECT gid,area AS plantations_22, code_00  FROM clc.clipped_bretagne1 WHERE  
    181184                        substring(code_00 from 1 for 2)='22') AS plantations 
    182185                        on plantations.gid =init.gid 
    183         FULL OUTER JOIN (SELECT gid,area,code_00 AS pastures_23 FROM clc.clipped_bretagne1 WHERE  
     186        FULL OUTER JOIN (SELECT gid,area AS pastures_23 ,code_00 FROM clc.clipped_bretagne1 WHERE  
    184187                        substring(code_00 from 1 for 2)='23') AS pastures 
    185188                        on pastures.gid =init.gid 
    186         FULL OUTER JOIN (SELECT gid,area,code_00 AS crops_natural_24  FROM clc.clipped_bretagne1 WHERE  
     189        FULL OUTER JOIN (SELECT gid,area AS crops_natural_24 ,code_00  FROM clc.clipped_bretagne1 WHERE  
    187190                        substring(code_00 from 1 for 2)='24') AS crops_natural 
    188191                        on crops_natural.gid =init.gid 
    189         FULL OUTER JOIN (SELECT gid,area AS forest_31  FROM clc.clipped_bretagne1 WHERE  
     192        FULL OUTER JOIN (SELECT gid,area AS forest_31,code_00   FROM clc.clipped_bretagne1 WHERE  
    190193                        substring(code_00 from 1 for 2)='31') AS forest 
    191194                        on forest.gid =init.gid 
    192         FULL OUTER JOIN (SELECT gid,area AS natural_32_33 FROM clc.clipped_bretagne1 WHERE  
     195        FULL OUTER JOIN (SELECT gid,area AS natural_32_33 ,code_00 FROM clc.clipped_bretagne1 WHERE  
    193196                        substring(code_00 from 1 for 2)='32' 
    194197                        OR  substring(code_00 from 1 for 2)='33') AS nature 
    195198                        on nature.gid =init.gid 
    196         FULL OUTER JOIN (SELECT gid,area AS wetlands_4 FROM clc.clipped_bretagne1 WHERE  
     199        FULL OUTER JOIN (SELECT gid,area AS wetlands_4 ,code_00 FROM clc.clipped_bretagne1 WHERE  
    197200                        substring(code_00 from 1 for 1)='4') AS wetlands 
    198201                        on wetlands.gid =init.gid 
    199         FULL OUTER JOIN (SELECT gid,area AS water_51 FROM clc.clipped_bretagne1 WHERE  
     202        FULL OUTER JOIN (SELECT gid,area AS water_51 ,code_00 FROM clc.clipped_bretagne1 WHERE  
    200203                        substring(code_00 from 1 for 2)='51') AS waterbodies 
    201204                        on waterbodies.gid =init.gid 
    202         FULL OUTER JOIN (SELECT gid,area AS seawater_52 FROM clc.clipped_bretagne1 WHERE  
     205        FULL OUTER JOIN (SELECT gid,area AS seawater_52 ,code_00 FROM clc.clipped_bretagne1 WHERE  
    203206                        substring(code_00 from 1 for 2)='52') AS seawater 
    204207                        on seawater.gid =init.gid); --375 ms 
    205 ALTER TABLE clc.pourc_area ADD CONSTRAINT c_pk_gid_pourc_area PRIMARY KEY (gid); 
    206  
    207 }}} 
    208  
    209 {{{ 
    210 #!sql 
     208ALTER TABLE clc.surf_area ADD CONSTRAINT c_pk_gid_surf_area PRIMARY KEY (gid); 
     209}}} 
     210{{{ 
     211SELECT * FROM clc.surf_area; 
     212}}} 
     213 
     214{{{ 
     215#!sql 
     216DROP TABLE IF EXISTS clc.surf_area1; 
     217CREATE TABLE clc.surf_area1 AS(  
    211218SELECT  
    212219        r.gid, 
    … …  
    244251        CASE WHEN  seawater_52 IS NOT NULL THEN seawater_52/1e6  
    245252        ELSE 0 
    246         END AS seawater_52       
    247           FROM clc.pourc_area p 
     253        END AS seawater_52, 
     254        c.wso1_id, 
     255        c.the_geom       
     256FROM clc.surf_area p 
    248257JOIN ccm21.catchments c ON c.gid=p.gid 
    249 JOIN ccm21.riversegments r on r.wso1_id=c.wso1_id; 
    250  
     258JOIN ccm21.riversegments r on r.wso1_id=c.wso1_id 
     259); 
     260}}} 
     261 
     262{{{ 
     263SELECT * FROM clc.surf_area1 
     264}}} 
     265 
     266{{{ 
     267CREATE TABLE clc.surf_area_analyse AS(  
    251268SELECT  
    252269        gid, 
     270        wso1_id, 
    253271        catchment_area, 
    254272        urban_11_13+ 
    … …  
    262280         wetlands_4+ 
    263281         water_51 + 
    264          seawater_52 as sum_clc_area FROM( 
    265 SELECT  
    266         r.gid, 
    267         area/1e6 as catchment_area, 
    268         CASE WHEN urban_11_13 IS NOT NULL THEN urban_11_13/1e6 
    269         ELSE 0 
    270         END AS urban_11_13, 
    271         CASE WHEN green_urban_14 IS NOT NULL THEN green_urban_14/1e6  
    272         ELSE 0 
    273         END AS green_urban_14, 
    274         CASE WHEN arable_land_21 IS NOT NULL THEN arable_land_21/1e6  
    275         ELSE 0 
    276         END AS arable_land_21, 
    277         CASE WHEN plantations_22 IS NOT NULL THEN plantations_22/1e6  
    278         ELSE 0 
    279         END AS plantations_22, 
    280         CASE WHEN pastures_23 IS NOT NULL THEN pastures_23/1e6  
    281         ELSE 0 
    282         END AS pastures_23, 
    283         CASE WHEN crops_natural_24 IS NOT NULL THEN crops_natural_24/1e6 
    284         ELSE 0 
    285         END AS crops_natural_24, 
    286         CASE WHEN forest_31 IS NOT NULL THEN forest_31/1e6  
    287         ELSE 0 
    288         END AS forest_31, 
    289         CASE WHEN natural_32_33 IS NOT NULL THEN natural_32_33/1e6  
    290         ELSE 0 
    291         END AS natural_32_33, 
    292         CASE WHEN wetlands_4 IS NOT NULL THEN wetlands_4/1e6  
    293         ELSE 0 
    294         END AS wetlands_4, 
    295         CASE WHEN water_51 IS NOT NULL THEN water_51 /1e6  
    296         ELSE 0 
    297         END AS water_51, 
    298         CASE WHEN  seawater_52 IS NOT NULL THEN seawater_52/1e6  
    299         ELSE 0 
    300         END AS seawater_52       
    301           FROM clc.pourc_area p 
    302 JOIN ccm21.catchments c ON c.gid=p.gid 
    303 JOIN ccm21.riversegments r on r.wso1_id=c.wso1_id) AS sub; 
     282         seawater_52 as sum_clc_area , 
     283        (urban_11_13+ 
     284         green_urban_14+ 
     285         arable_land_21+ 
     286         plantations_22+ 
     287         pastures_23+ 
     288         crops_natural_24+ 
     289         forest_31+ 
     290         natural_32_33+ 
     291         wetlands_4+ 
     292         water_51 + 
     293         seawater_52)/catchment_area AS pourc_clc, 
     294         the_geom 
     295         FROM clc.surf_area1); 
     296ALTER TABLE clc.surf_area_analyse add CONSTRAINT c_pk_gid_area_analyse PRIMARY KEY (gid); 
     297alter table clc.surf_area_analyse add CONSTRAINT enforce_dims_the_geom CHECK (ndims(the_geom) = 2); 
     298alter table clc.surf_area_analyse add  CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) = 'MULTIPOLYGON'::text OR the_geom IS NULL); 
     299alter table clc.surf_area_analyse add  CONSTRAINT enforce_srid_the_geom CHECK (srid(the_geom) = 3035); 
     300CREATE INDEX indexclc00area_analyse ON clc.surf_area_analyse 
     301  USING GIST ( the_geom GIST_GEOMETRY_OPS ); 
    304302}}} 
    305303