back to first page [..] [[BR]]
[[PageOutline]]

= Extraction d'une sous selection des clc pour la meuse =

 [[Image(source:eda/data/Docs/trac/Meuse/clcmeusebelge.jpg)]] [[BR]]
les données ne sont pas utilisées ensuite, on va directement tapper dans la table de la clc européenne... Mais c'est pour voir.
{{{
#!sql
select * from european_wise2008.rbd_f1v3 where name_engl like 'Meuse'


-- selection of the meuse basin
select * from european_wise2008.rbd_f1v3 where name_engl like 'Meuse'
-- 5 lines


select * from europe.wso limit 10;-- id, wso_id, area
-- I just need the two second columns, below a request spatial joining riversegments and the wise layer
select distinct on (wso_id)  wso_id, name_engl as area from ccm21.riversegments r
    join (SELECT the_geom, name_engl FROM european_wise2008.rbd_f1v3 As f where name_engl like 'Meuse' ) as sub
    ON ST_Intersects(sub.the_geom,r.the_geom) limit 10;

    

select * from ccm21.riversegments where wso1_id in 
(291110,
291112,
291115,
291130,
291133,
292843,
292848,
298657,
324473,
324479)
--324473
--324479
--291130 --Meuse
select *  from ccm21.riversegments where wso_id in 
(291110,--rhin
291112,-- Rhone
291115,-- Seine
291130,-- Meuse
291133,--Scheldt
292843,-- Meuse du bas
292848,-- micro pipi près de l'esuaire
298657)-- petit pipi près de l'esuaire


-- i'll insert this in europe.wso

insert into europe.wso(wso_id,area) VALUES (291130,'Meuse');-- Meuse
insert into europe.wso(wso_id,area) VALUES (292848,'Meuse');-- micro pipi près de l'esuaire
insert into europe.wso(wso_id,area) VALUES  (298657,'Meuse');-- petit pipi près de l'esuaire
insert into europe.wso(wso_id,area) VALUES  (292843,'Meuse');--- Meuse du bas
insert into europe.wso1(wso1_id,area) select wso1_id,'Meuse' from ccm21.riversegments where wso_id in (291130,292848,298657,292843);--3022

drop table if exists clc.meusebelge;
create table clc.meusebelge as (
	select distinct on (gid) cl.* from clc.clc00_v2_europe cl
	join
	(select the_geom from ccm21.catchments c join europe.wso e on e.wso_id=c.wso_id where e.area='Meuse' 
	except
	select the_geom from ccm21.catchments c join europe.wso1 e on e.wso1_id=c.wso1_id where e.area='France') as ccm
	on st_intersects(cl.the_geom,ccm.the_geom)
	
);
 alter table clc.meusebelge ADD CONSTRAINT meusebelge_pkey PRIMARY KEY (gid);
  alter table clc.meusebelge ADD  CONSTRAINT enforce_dims_the_geom CHECK (ndims(the_geom) = 2);
  alter table clc.meusebelge ADD  CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) = 'MULTIPOLYGON'::text OR the_geom IS NULL);
  alter table clc.meusebelge ADD  CONSTRAINT enforce_srid_the_geom CHECK (srid(the_geom) = 3035);
  
INSERT INTO geometry_columns(f_table_catalog, f_table_schema, f_table_name, f_geometry_column, coord_dimension, srid, "type")
SELECT '', 'clc', 'meusebelge', 'the_geom', ST_CoordDim(the_geom), ST_SRID(the_geom), GeometryType(the_geom)
FROM clc.meusebelge  LIMIT 1;
}}}

 [[Image(source:eda/data/Docs/trac/Meuse/clc_clipped_meuse1.jpg)]] [[BR]]
{{{
#!sql
-- il faut virer la France

delete from clc.clipped_meuse1 where gid in (select gid from ccm21.catchments where wso1_id in (select wso1_id from europe.wso1 where area='France'));--5124
}}}
 [[Image(source:eda/data/Docs/trac/Meuse/clc_clipped_meuse1_ssfrance.jpg)]] [[BR]]


{{{
#!sql
--------------------------------------
--------------------------------------
--AREA
--------------------------------------
--------------------------------------

ALTER TABLE clc.clipped_meuse1 add column area numeric;
UPDATE clc.clipped_meuse1 set area=ST_Area(the_geom); 

--------------------------------------
--AREA PER COLUMN FOR CLC TYPE (agregation)
--------------------------------------
--------------------------------------
SELECT gid,code_00, round(area) as area FROM clc.clipped_meuse1 order by gid, code_00 limit 10;
DROP TABLE IF EXISTS clc.surf_area_meuse;

CREATE TABLE clc.surf_area_meuse 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_meuse1    ) as init        
        FULL OUTER JOIN (SELECT gid,sum(area) AS artificial_surfaces_11_13 FROM clc.clipped_meuse1 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_meuse1 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_meuse1 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_meuse1 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_meuse1 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_meuse1 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_meuse1 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_meuse1 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_meuse1 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_meuse1 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_meuse1 WHERE 
                        substring(code_00 from 1 for 2)='52'
                        GROUP BY gid) AS marine_water
                        on marine_water.gid =init.gid); 

ALTER TABLE clc.surf_area_meuse ADD CONSTRAINT c_pk_gid_surf_area_meuse PRIMARY KEY (gid);
SELECT * FROM clc.surf_area_meuse;
--------------------------------------
--------------------------------------
--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_meuse_final;
CREATE TABLE clc.surf_area_meuse_final AS( 
SELECT 
        c.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_meuse p
JOIN ccm21.catchments c ON c.gid=p.gid
JOIN ccm21.riversegments r on r.wso1_id=c.wso1_id
);
}}}

{{{
#!sql
-- recopie les données depuis la France (données manquantes)
select count(*) from clc.surf_area_final where wso1_id in (select wso1_id from europe.wso1 where area='Meuse');--2975
select count(*) from europe.wso1 where area='Meuse';--3022
select count(*) from clc.surf_area_meuse;--2011

insert into clc.surf_area_meuse_final 
select * from clc.surf_area_final where wso1_id in (
select wso1_id from  clc.surf_area_final where wso1_id in (select wso1_id from europe.wso1 where area='Meuse')
and wso1_id not in (select wso1_id from clc.surf_area_meuse_final));--1011
}}}