Changes between Version 21 and Version 22 of CLC Join

Timestamp:

Jun 1, 2010 11:52:58 AM (15 years ago)

Author:

cedric

Comment:

--

CLC Join

@@ -1 +1 @@
 = This page describes the join of ccm21.catchments with the clc =
 [wiki:"CookBook Eda"]
-
---- building a table of clc only for Britany ---
+= First an example for Britany =
+== building a table of clc only for Britany ==
+After several trial neither --- ST_Crosses --- nor  --- ST_Contains --- did work, the example with ST_Intersects provides a surface that is larger than britany, but ST_Crosses missed the area near the coasts as the geom had to be fully within Britany.
 {{{
 #!sql
--- 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;
@@ -12 +12 @@
 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));
+    ON ST_Intersects(sub.the_geom,clc.the_geom));
     
 ALTER TABLE clc.clc00_v2_Bretagne ADD CONSTRAINT c_pk_gid PRIMARY KEY  (gid);
@@ -18 +18 @@
 alter table clc.clc00_v2_Bretagne add  CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) = 'MULTIPOLYGON'::text OR the_geom IS NULL);
 alter table clc.clc00_v2_Bretagne add  CONSTRAINT enforce_srid_the_geom CHECK (srid(the_geom) = 3035);
+CREATE INDEX indexclc00_v2_Bretagne ON clc.clc00_v2_Bretagne
+  USING GIST ( the_geom GIST_GEOMETRY_OPS ); 
 
-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 [[BR]]
-
-{{{
-#!sql
-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);
-
+-- run this only once
 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;
 }}}
+[[Image(source:data/Docs/trac/clc_britany.png,600px)]] 
+
+== Cutting the surface according to the catchments area ==
+Note this one took a long time to run on my computer, one hour for Britany...
+It uses --- ST_Intersects --- to reduce the search list and -- ST_Multi(ST_Intersection()) to cut along the primary catchments borders.
+{{{
+--------------------------------------
+--------------------------------------
+--DECOUPAGE DES SURFACES
+--------------------------------------
+--------------------------------------
+DROP TABLE IF EXISTS clc.clipped_bretagne;
+CREATE TABLE clc.clipped_bretagne 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 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; --1h12 min
+ALTER TABLE clc.clipped_bretagne ADD column id serial PRIMARY KEY;
+alter table clc.clipped_bretagne add CONSTRAINT enforce_dims_the_geom CHECK (ndims(the_geom) = 2);
+alter table clc.clipped_bretagne add  CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) = 'MULTIPOLYGON'::text OR the_geom IS NULL);
+alter table clc.clipped_bretagne add  CONSTRAINT enforce_srid_the_geom CHECK (srid(the_geom) = 3035);
+CREATE INDEX indexclc00clipped_bretagne ON clc.clipped_bretagne
+  USING GIST ( the_geom GIST_GEOMETRY_OPS ); 
+  -- les limites sont bien meilleures
+-- Here to analyse the structure of data in the created table
+SELECT ST_AsText(the_geom) from clc.clipped_bretagne limit 20;
+}}}
+[[Image(source:data/Docs/trac/clipped_bretane.png,600px)]] 
 The final file is effectively cut .... [[BR]]
 [[Image(source:data/Docs/trac/coupure des couvertures anthropisées.png,600px)]]  [[BR]]