Changes between Version 6 and Version 7 of CLC Join

Timestamp:

May 31, 2010 1:14:35 PM (15 years ago)

Author:

cedric

Comment:

--

CLC Join

@@ -34 +34 @@
         where substring(code_00 from 1 for 1)='1' )  AS intersected;            
 
+-- script de départ (modifié ensuite)
+CREATE TABLE clc.clipped_bretagne AS
+SELECT intersected.clcgid, intersected.gid, clipped_geom
+FROM (SELECT clc.gid as clcgid, c.gid, ST_Multi(ST_Intersection(clc.the_geom, c.the_geom)) clipped_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;
+
+-3721 lines  
+ALTER TABLE clc.clipped_bretagne add column id serial PRIMARY KEY;
+-- ajout dans la table geometry_columns pour référencement rapide sous Qgis
+INSERT INTO geometry_columns(f_table_catalog, f_table_schema, f_table_name, f_geometry_column, coord_dimension, srid, "type")
+SELECT '', 'clc', 'clipped_bretagne', 'the_geom', ST_CoordDim(clipped_geom), ST_SRID(clipped_geom), MULTIPOLYGON
+FROM clc.clipped_bretagne  LIMIT 1;
+
+-- ci dessous j'essaye d'analyser la structure interne des données pour comprendre quels sont les problèmes
+
+SELECT intersected.clcgid, intersected.gid, intersected.the_geom
+FROM (SELECT clc.gid as clcgid, c.gid, CAST(ST_AsText(ST_Multi(ST_Intersection(clc.the_geom, c.the_geom))) as VARCHAR(150)) as 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;
+
+
+-- Pour comparaison (ressemble beaucoup)
+
+SELECT CAST(ST_AsText(the_geom) as varchar(150)) from clc.clc00_v2_europe clc limit 20;
+
+
+-- Je rééssaye
+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;
+
+-- celui là marche sur Postgis (et je ne comprends pas ce qui ne marchait pas peut être le as the_geom a la place de the_geom, ou alors mon Qgis....)
+
+-- 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;
+
+-- Creating a table for riversegments Bretagne
+
+DROP TABLE IF EXISTS ccm21.riversegments_Bretagne;   
+CREATE TABLE  ccm21.riversegments_Bretagne AS
+    SELECT * FROM ccm21.riversegments
+        WHERE wso_id IN (SELECT wso_id FROM france.wso WHERE area='Bretagne');
+ALTER TABLE ccm21.riversegments_Bretagne ADD CONSTRAINT c_pk_gid_riversegments_Bretagne PRIMARY KEY  (gid);
+INSERT INTO geometry_columns(f_table_catalog, f_table_schema, f_table_name, f_geometry_column, coord_dimension, srid, "type")
+SELECT '', 'ccm21', 'riversegments_Bretagne', 'the_geom', ST_CoordDim(the_geom), ST_SRID(the_geom), GeometryType(the_geom)
+FROM ccm21.riversegments_Bretagne LIMIT 1;
+
+-- creating a table for catchments Bretagne.
+DROP TABLE IF EXISTS ccm21.catchments_Bretagne;   
+CREATE TABLE  ccm21.catchments_Bretagne AS
+    SELECT * FROM ccm21.catchments
+        WHERE wso_id IN (SELECT wso_id FROM france.wso WHERE area='Bretagne');  
+ALTER TABLE ccm21.catchments_Bretagne ADD CONSTRAINT c_pk_gid_catchments_Bretagne PRIMARY KEY  (gid);
+INSERT INTO geometry_columns(f_table_catalog, f_table_schema, f_table_name, f_geometry_column, coord_dimension, srid, "type")
+SELECT '', 'ccm21', 'catchments_Bretagne', 'the_geom', ST_CoordDim(the_geom), ST_SRID(the_geom), GeometryType(the_geom)
+FROM ccm21.catchments_Bretagne LIMIT 1;
 
 }}}