back to first page..
back to WRBD

--------------------------------------------
---------------------------------------------
-- Corinne Landcover
---------------------------------------------
---------------------------------------------
--------------------------------------

DROP INDEX IF EXISTS ccm21.indexriversegments_wso_id;

CREATE INDEX indexriversegments_wso_id
ON ccm21.riversegments
USING btree (wso_id);

DROP INDEX IF EXISTS ccm21.indexriversegments_wso1_id;
CREATE INDEX indexriversegments_wso1_id
ON ccm21.riversegments
USING btree (wso1_id);

CREATE INDEX indexriversegments_sp ON ccm21.riversegments
  USING GIST ( the_geom GIST_GEOMETRY_OPS ); 

  
CREATE INDEX indexcatchment_sp ON ccm21.catchments
  USING GIST ( the_geom GIST_GEOMETRY_OPS ); 
--------
--SURFACE CUT
--------------------------------------
--------------------------------------
DROP TABLE IF EXISTS clc.clipped_ireland;
CREATE TABLE clc.clipped_ireland 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='Western')) 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_ireland ADD column id serial PRIMARY KEY;
alter table clc.clipped_ireland add CONSTRAINT enforce_dims_the_geom CHECK (ndims(the_geom) = 2);
alter table clc.clipped_ireland add  CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) = 'MULTIPOLYGON'::text OR the_geom IS NULL);
alter table clc.clipped_ireland add  CONSTRAINT enforce_srid_the_geom CHECK (srid(the_geom) = 3035);
CREATE INDEX indexclc00clipped_ireland ON clc.clipped_ireland
  USING GIST ( the_geom GIST_GEOMETRY_OPS ); 
--------------------------------------
--------------------------------------
--MERGING
--------------------------------------
--------------------------------------
DROP TABLE IF EXISTS clc.clipped_ireland1;
CREATE TABLE clc.clipped_ireland1 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_ireland
                                ) As f 
GROUP BY gid,code_00);--171 s
ALTER TABLE clc.clipped_ireland1 add column id serial PRIMARY KEY;
alter table clc.clipped_ireland1 add CONSTRAINT enforce_dims_the_geom CHECK (ndims(the_geom) = 2);
alter table clc.clipped_ireland1 add  CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) = 'MULTIPOLYGON'::text OR the_geom IS NULL);
alter table clc.clipped_ireland1 add  CONSTRAINT enforce_srid_the_geom CHECK (srid(the_geom) = 3035);
CREATE INDEX indexclc00clipped_ireland1 ON clc.clipped_ireland1
  USING GIST ( the_geom GIST_GEOMETRY_OPS );
ALTER TABLE clc.clipped_ireland1 add constraint c_ck_uk_ireland  UNIQUE(gid,code_00); -- contrainte d'unicité
--------------------------------------
--------------------------------------
--AREA
--------------------------------------
--------------------------------------
ALTER TABLE clc.clipped_ireland1 add column area numeric;
UPDATE clc.clipped_ireland1 set area=ST_Area(the_geom); 
--------------------------------------
--------------------------------------
--AREA PER COLUMN FOR CLC TYPE (agregation)
--------------------------------------
--------------------------------------
SELECT gid,code_00, id,round(area) as area FROM clc.clipped_ireland1 order by gid, code_00 limit 10;
DROP TABLE IF EXISTS clc.surf_area_ireland;
CREATE TABLE clc.surf_area_ireland 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_ireland1    ) as init        
        FULL OUTER JOIN (SELECT gid,sum(area) AS artificial_surfaces_11_13 FROM clc.clipped_ireland1 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_ireland1 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_ireland1 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_ireland1 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_ireland1 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_ireland1 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_ireland1 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_ireland1 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_ireland1 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_ireland1 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_ireland1 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_ireland ADD CONSTRAINT c_pk_gid_surf_area_ireland PRIMARY KEY (gid);
SELECT * FROM clc.surf_area_ireland;
--------------------------------------
--------------------------------------
--REMOVING ZEROS AND JOINING RIVERSEGMENTS AND CATCHMENTS TABLES
--------------------------------------
--------------------------------------
-- this table drops the previous one but final calculations are stored in surf_area_final
DROP TABLE IF EXISTS clc.surf_area_ireland_final;
CREATE TABLE clc.surf_area_ireland_final AS( 
SELECT 
        r.gid,
        C.area/1e6 as catchment_area,
        CASE WHEN p.artificial_surfaces_11_13 IS NOT NULL THEN p.artificial_surfaces_11_13/1e6
        ELSE 0
        END AS artificial_surfaces_11_13,
        CASE WHEN p.artificial_vegetated_14 IS NOT NULL THEN p.artificial_vegetated_14/1e6 
        ELSE 0
        END AS artificial_vegetated_14,
        CASE WHEN p.arable_land_21 IS NOT NULL THEN p.arable_land_21/1e6 
        ELSE 0
        END AS arable_land_21,
        CASE WHEN p.permanent_crops_22 IS NOT NULL THEN p.permanent_crops_22/1e6 
        ELSE 0
        END AS permanent_crops_22,
        CASE WHEN p.pastures_23 IS NOT NULL THEN p.pastures_23/1e6 
        ELSE 0
        END AS pastures_23,
        CASE WHEN p.heterogeneous_agricultural_24 IS NOT NULL THEN p.heterogeneous_agricultural_24/1e6
        ELSE 0
        END AS heterogeneous_agricultural_24,
        CASE WHEN p.forest_31 IS NOT NULL THEN p.forest_31/1e6 
        ELSE 0
        END AS forest_31,
        CASE WHEN p.natural_32_33 IS NOT NULL THEN p.natural_32_33/1e6 
        ELSE 0
        END AS natural_32_33,
        CASE WHEN p.wetlands_4 IS NOT NULL THEN p.wetlands_4/1e6 
        ELSE 0
        END AS wetlands_4,
        CASE WHEN p.inland_waterbodies_51 IS NOT NULL THEN p.inland_waterbodies_51 /1e6 
        ELSE 0
        END AS inland_waterbodies_51,
        CASE WHEN  p.marine_water_52 IS NOT NULL THEN p.marine_water_52/1e6 
        ELSE 0
        END AS marine_water_52,
        c.wso1_id,
        c.the_geom      
FROM clc.surf_area_ireland p
JOIN ccm21.catchments c ON c.gid=p.gid
JOIN ccm21.riversegments r on r.wso1_id=c.wso1_id
);