Changes between Initial Version and Version 5 of Ticket #56


Ignore:
Timestamp:
May 31, 2010 1:15:56 PM (15 years ago)
Author:
cedric
Comment:

Legend:

Unmodified
Added
Removed
Modified

  • Ticket #56

    • Property Status changed from new to accepted
    • Property Component changed from Documentation to SIG-data
    • Property Summary changed from Add Corine Land Cover into Postgre to Integrating Corine Land Cover data into Postgre
    • Property Version changed from to 2.0
    • Property Milestone changed from to Data integration
    • Property Type changed from defect to task

  • Ticket #56 – Description

    initial v5  
     1{{{ 
     2#!sql 
     3-- Un essai sur la Bretagne pour voir 
     4DROP TABLE IF EXISTS clc.clipped_bretagne; 
     5CREATE TABLE clc.clipped_bretagne AS 
     6SELECT intersected.clcgid, intersected.gid, clipped_geom 
     7FROM (SELECT clc.gid as clcgid, c.gid, ST_Multi(ST_Intersection(clc.the_geom, c.the_geom)) AS clipped_geom 
     8        FROM  clc.clc00_v2_europe clc INNER JOIN ccm21.catchments c 
     9        ON  ST_Intersects (c.the_geom,clc.the_geom) 
     10        WHERE c.wso_id IN (SELECT wso_id FROM france.wso WHERE area='Bretagne') 
     11        AND substring(code_00 from 1 for 1)='1')  AS intersected;--3721 lines    
     12ALTER TABLE clc.clipped_bretagne add column id serial PRIMARY KEY; 
     13-- ajout dans la table geometry_columns pour référencement rapide sous Qgis 
     14INSERT INTO geometry_columns(f_table_catalog, f_table_schema, f_table_name, f_geometry_column, coord_dimension, srid, "type") 
     15SELECT '', 'clc', 'clipped_bretagne', 'the_geom', ST_CoordDim(clipped_geom), ST_SRID(clipped_geom), MULTIPOLYGON 
     16FROM clc.clipped_bretagne  LIMIT 1; 
     17 
     18-- il ne s'affiche pas... essai réduit OK 
     19DROP TABLE IF EXISTS clc.clipped_bretagne; 
     20CREATE TABLE clc.clipped_bretagne AS 
     21SELECT clc.gid as clcgid,c.gid, clc.the_geom FROM clc.clc00_v2_europe clc INNER JOIN ccm21.catchments c 
     22        ON  ST_Intersects (c.the_geom,clc.the_geom) 
     23        WHERE c.wso_id IN (SELECT wso_id FROM france.wso WHERE area='Bretagne') 
     24        AND substring(code_00 from 1 for 1)='1'; 
     25ALTER TABLE clc.clipped_bretagne add column id serial PRIMARY KEY; 
     26 
     27-- la requete globale 
     28SELECT intersected.clcgid, intersected.gid, clipped_geom 
     29FROM (SELECT clc.gid as clcgid, c.gid, ST_Intersection(clc.the_geom, c.the_geom) AS clipped_geom 
     30        FROM  clc.clc00_v2_europe clc INNER JOIN ccm21.catchments c 
     31        ON  ST_Intersects (c.the_geom,clc.the_geom) 
     32        where substring(code_00 from 1 for 1)='1' )  AS intersected;             
     33 
     34-- script de départ (modifié ensuite) 
     35CREATE TABLE clc.clipped_bretagne AS 
     36SELECT intersected.clcgid, intersected.gid, clipped_geom 
     37FROM (SELECT clc.gid as clcgid, c.gid, ST_Multi(ST_Intersection(clc.the_geom, c.the_geom)) clipped_geom 
     38        FROM  clc.clc00_v2_europe clc INNER JOIN ccm21.catchments c 
     39        ON  ST_Intersects (c.the_geom,clc.the_geom) 
     40        WHERE c.wso_id IN (SELECT wso_id FROM france.wso WHERE area='Bretagne') 
     41        AND substring(code_00 from 1 for 1)='1')  AS intersected;--3721 lines   
     42ALTER TABLE clc.clipped_bretagne add column id serial PRIMARY KEY; 
     43 
     44-3721 lines   
     45ALTER TABLE clc.clipped_bretagne add column id serial PRIMARY KEY; 
     46-- ajout dans la table geometry_columns pour référencement rapide sous Qgis 
     47INSERT INTO geometry_columns(f_table_catalog, f_table_schema, f_table_name, f_geometry_column, coord_dimension, srid, "type") 
     48SELECT '', 'clc', 'clipped_bretagne', 'the_geom', ST_CoordDim(clipped_geom), ST_SRID(clipped_geom), MULTIPOLYGON 
     49FROM clc.clipped_bretagne  LIMIT 1; 
     50 
     51-- ci dessous j'essaye d'analyser la structure interne des données pour comprendre quels sont les problèmes 
     52 
     53SELECT intersected.clcgid, intersected.gid, intersected.the_geom 
     54FROM (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 
     55        FROM  clc.clc00_v2_europe clc INNER JOIN ccm21.catchments c 
     56        ON  ST_Intersects (c.the_geom,clc.the_geom) 
     57        WHERE c.wso_id IN (SELECT wso_id FROM france.wso WHERE area='Bretagne') 
     58        AND substring(code_00 from 1 for 1)='1')  AS intersected; 
     59 
     60 
     61-- Pour comparaison (ressemble beaucoup) 
     62 
     63SELECT CAST(ST_AsText(the_geom) as varchar(150)) from clc.clc00_v2_europe clc limit 20; 
     64 
     65 
     66-- Je rééssaye 
     67DROP TABLE IF EXISTS clc.clipped_bretagne; 
     68CREATE TABLE clc.clipped_bretagne AS 
     69SELECT intersected.clcgid, intersected.gid, the_geom 
     70FROM (SELECT clc.gid as clcgid, c.gid, ST_Multi(ST_Intersection(clc.the_geom, c.the_geom)) the_geom 
     71        FROM  clc.clc00_v2_europe clc INNER JOIN ccm21.catchments c 
     72        ON  ST_Intersects (c.the_geom,clc.the_geom) 
     73        WHERE c.wso_id IN (SELECT wso_id FROM france.wso WHERE area='Bretagne') 
     74        AND substring(code_00 from 1 for 1)='1')  AS intersected;--3721 lines   
     75ALTER TABLE clc.clipped_bretagne add column id serial PRIMARY KEY; 
     76-- Here to analyse the structure of data in the created table 
     77SELECT ST_AsText(the_geom) from clc.clipped_bretagne limit 20; 
     78 
     79-- 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....) 
     80 
     81-- I'm adding some original data tables restricted for an area for Qgis easy use 
     82-- extraction of a clc table for Britany 
     83DROP TABLE IF EXISTS clc.clc00_v2_Bretagne; 
     84CREATE TABLE clc.clc00_v2_Bretagne AS 
     85SELECT * FROM clc.clc00_v2_europe where gid IN ( 
     86SELECT gid FROM  clc.clc00_v2_europe clc JOIN 
     87    (SELECT the_geom FROM france.region where code_reg='53') as sub 
     88    ON ST_Contains(sub.the_geom,clc.the_geom)); 
     89ALTER TABLE clc.clc00_v2_Bretagne ADD CONSTRAINT c_pk_gid PRIMARY KEY  (gid); 
     90INSERT INTO geometry_columns(f_table_catalog, f_table_schema, f_table_name, f_geometry_column, coord_dimension, srid, "type") 
     91SELECT '', 'clc', 'clc00_v2_Bretagne', 'the_geom', ST_CoordDim(the_geom), ST_SRID(the_geom), GeometryType(the_geom) 
     92FROM clc.clc00_v2_europe LIMIT 1; 
     93 
     94-- Creating a table for riversegments Bretagne 
     95 
     96DROP TABLE IF EXISTS ccm21.riversegments_Bretagne;    
     97CREATE TABLE  ccm21.riversegments_Bretagne AS 
     98    SELECT * FROM ccm21.riversegments 
     99        WHERE wso_id IN (SELECT wso_id FROM france.wso WHERE area='Bretagne'); 
     100ALTER TABLE ccm21.riversegments_Bretagne ADD CONSTRAINT c_pk_gid_riversegments_Bretagne PRIMARY KEY  (gid); 
     101INSERT INTO geometry_columns(f_table_catalog, f_table_schema, f_table_name, f_geometry_column, coord_dimension, srid, "type") 
     102SELECT '', 'ccm21', 'riversegments_Bretagne', 'the_geom', ST_CoordDim(the_geom), ST_SRID(the_geom), GeometryType(the_geom) 
     103FROM ccm21.riversegments_Bretagne LIMIT 1; 
     104 
     105-- creating a table for catchments Bretagne. 
     106DROP TABLE IF EXISTS ccm21.catchments_Bretagne;    
     107CREATE TABLE  ccm21.catchments_Bretagne AS 
     108    SELECT * FROM ccm21.catchments 
     109        WHERE wso_id IN (SELECT wso_id FROM france.wso WHERE area='Bretagne');   
     110ALTER TABLE ccm21.catchments_Bretagne ADD CONSTRAINT c_pk_gid_catchments_Bretagne PRIMARY KEY  (gid); 
     111INSERT INTO geometry_columns(f_table_catalog, f_table_schema, f_table_name, f_geometry_column, coord_dimension, srid, "type") 
     112SELECT '', 'ccm21', 'catchments_Bretagne', 'the_geom', ST_CoordDim(the_geom), ST_SRID(the_geom), GeometryType(the_geom) 
     113FROM ccm21.catchments_Bretagne LIMIT 1; 
     114}}}