This page describes the join of ccm21.catchments with the clc

CookBook Eda

--- building a table onluy for Britany ---

-- I'm adding some original data tables restricted for an area for Qgis easy use
-- extraction of a clc table for Britany
DROP TABLE IF EXISTS clc.clc00_v2_Bretagne;
CREATE TABLE clc.clc00_v2_Bretagne AS
SELECT * FROM clc.clc00_v2_europe where gid IN (
SELECT gid FROM  clc.clc00_v2_europe clc JOIN
    (SELECT the_geom FROM france.region where code_reg='53') as sub
    ON ST_Contains(sub.the_geom,clc.the_geom));

ALTER TABLE clc.clc00_v2_Bretagne ADD CONSTRAINT c_pk_gid PRIMARY KEY  (gid);

INSERT INTO geometry_columns(f_table_catalog, f_table_schema, f_table_name, f_geometry_column, coord_dimension, srid, "type")
SELECT '', 'clc', 'clc00_v2_Bretagne', 'the_geom', ST_CoordDim(the_geom), ST_SRID(the_geom), GeometryType(the_geom)
FROM clc.clc00_v2_europe LIMIT 1;

Trial in Brittany

DROP TABLE IF EXISTS clc.clipped_bretagne;
CREATE TABLE clc.clipped_bretagne AS
SELECT intersected.clcgid, intersected.gid, the_geom
FROM (SELECT clc.gid as clcgid, c.gid, ST_Multi(ST_Intersection(clc.the_geom, c.the_geom)) the_geom
        FROM  clc.clc00_v2_europe clc INNER JOIN ccm21.catchments c
        ON  ST_Intersects (c.the_geom,clc.the_geom)
        WHERE c.wso_id IN (SELECT wso_id FROM france.wso WHERE area='Bretagne')
        AND substring(code_00 from 1 for 1)='1')  AS intersected;--3721 lines  
ALTER TABLE clc.clipped_bretagne add column id serial PRIMARY KEY;
-- Here to analyse the structure of data in the created table
SELECT ST_AsText(the_geom) from clc.clipped_bretagne limit 20;

ALTER TABLE clc.clc00_v2_Bretagne ADD CONSTRAINT c_pk_gid PRIMARY KEY  (gid);

INSERT INTO geometry_columns(f_table_catalog, f_table_schema, f_table_name, f_geometry_column, coord_dimension, srid, "type")
SELECT '', 'clc', 'clc00_v2_Bretagne', 'the_geom', ST_CoordDim(the_geom), ST_SRID(the_geom), GeometryType(the_geom)
FROM clc.clc00_v2_europe LIMIT 1;

The final file is effectively cut ....