before i run this code, we need to create a clc schema within eda2 on my postgres. right click on schema and choose new schema

then use the following code in the cmd window to load the clc data from the c drive to the database

c:
---to change from h drive to c drive
cd data  
---to locate thr data folder
psql -U postgres -f "clc00_v2_europe.sql" eda2 
---to get the data from the file eda2.clc into postgres in the eda2 database 
--open pgadmin and run this sql qyery to check its right
select count(*) from clc.clc00_v2_europe
--20709 good
--ctrl c will cancel a command if you know you have messed it up
--if something goes into the wrong schema - for example if you havent set up the clc schema, or the.sql file doenst --
--know where to out it, you can use this command to rest its location

--alter table  mytable set schema myschema
--e.g.
alter table  clc00_v2_europe set schema clc

!#!html !<h2 style="color: orange">Elvira restart there HTML</h2> !

riverbasin districts from EU, download them at my ftp/cedric/elvira
OPEN COMMAND batch cmd
CREATE SCHEMA
psql -U postgres -c "create schema european_wise2008" eda2
RESTORE TABLE
psql -U postgres -f "european_wise2008.rbd_f1v3.sql" eda2

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


CREATE INDEX indexcatchment_sp ON ccm21.catchments
  USING GIST ( the_geom GIST_GEOMETRY_OPS ); 

--------------------------------------
-- Elvira : here I'm renaming the tables labelled something_ireland to something_western
-- so that you can run the code below
alter table clc.clipped_ireland rename to clipped_western;
alter table clc.clipped_ireland1 rename to clipped_western1;
alter table clc.surf_area_ireland rename to surf_area_western;
alter table  clc.surf_area_ireland_final rename to surf_area_western_final;
--------
--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='Ireland')) 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
);