Changes between Version 14 and Version 15 of Cookbook CCM21_France

Timestamp:

May 31, 2010 1:22:46 PM (15 years ago)

Author:

cedric

Comment:

--

Cookbook CCM21_France

@@ -65 +65 @@
 select * from ccm21.riversegments where wso_id in (select wso_id from france.wso where area='Bretagne')--2115 lines
 }}}
+
+Creating local tables for Britany (to enhance speed and allow fast testing and displaying in 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;
+}}}