Changes between Version 80 and Version 81 of CookBook join ROE_CCM

Timestamp:

May 4, 2010 2:19:14 PM (15 years ago)

Author:

cedric

Comment:

--

CookBook join ROE_CCM

@@ -131 +131 @@
 -- Création d'un index sur obstacle_referentiel
 
--- Création de la table correspondance, je rajoute un the_geom a la requete pour pouvoir voir la table dans Qgis 
+-- Création de la table correspondance (roe_ccm_300), je rajoute un the_geom a la requete pour pouvoir voir la table dans Qgis 
 '''La distance de projection entre la CCM et la base geobs est choisie à : 300 m (comme pour l'extraction des données BDMAP sur la CCM)'''
 
 {{{
 CREATE INDEX indexgeobs ON geobs2010.obstacle_referentiel  USING GIST (ref_position_etrs89 GIST_GEOMETRY_OPS);
-DROP TABLE IF EXISTS geobs2010.correspondance;
-CREATE TABLE geobs2010.correspondance as (
+DROP TABLE IF EXISTS geobs2010.roe_ccm_300;
+CREATE TABLE geobs2010.roe_ccm_300 as (
         SELECT distinct on (ref_id) ref_id, gid, min(distance) as distance, ref_position_etrs89 as the_geom FROM (
                SELECT ref_id, gid ,CAST(distance(r.the_geom, b.ref_position_etrs89) as  decimal(15,1)) as distance ,b.ref_position_etrs89
@@ -152 +152 @@
 -- si la commande ci dessus ne marche pas ...
 INSERT INTO geometry_columns(f_table_catalog, f_table_schema, f_table_name, f_geometry_column, coord_dimension, srid, "type")
-SELECT '', 'geobs2010', 'correspondance', 'the_geom', ST_CoordDim(the_geom), ST_SRID(the_geom), GeometryType(the_geom)
-FROM geobs2010.correspondance LIMIT 1;
+SELECT '', 'geobs2010', 'roe_ccm_300', 'the_geom', ST_CoordDim(the_geom), ST_SRID(the_geom), GeometryType(the_geom)
+FROM geobs2010.roe_ccm_300 LIMIT 1;
 
 -- creation d'index, clé primaire, et constraintes qui vont bien
-alter table geobs2010.correspondance add column id serial PRIMARY KEY;
-alter table geobs2010.correspondance add CONSTRAINT enforce_dims_the_geom CHECK (ndims(the_geom) = 2);
-alter table geobs2010.correspondance add  CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) = 'POINT'::text OR the_geom IS NULL);
-alter table geobs2010.correspondance add  CONSTRAINT enforce_srid_the_geom CHECK (srid(the_geom) = 3035);
-
-CREATE INDEX indexgeobscorrespondance ON geobs2010.correspondance
+alter table geobs2010.roe_ccm_300 add column id serial PRIMARY KEY;
+alter table geobs2010.roe_ccm_300 add CONSTRAINT enforce_dims_the_geom CHECK (ndims(the_geom) = 2);
+alter table geobs2010.roe_ccm_300 add  CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) = 'POINT'::text OR the_geom IS NULL);
+alter table geobs2010.roe_ccm_300 add  CONSTRAINT enforce_srid_the_geom CHECK (srid(the_geom) = 3035);
+
+CREATE INDEX indexgeobsroe_ccm_300 ON geobs2010.roe_ccm_300
   USING GIST ( the_geom GIST_GEOMETRY_OPS );
 }}}
@@ -169 +169 @@
 UPDATE geobs2010.obstacle_referentiel set goodproj=FALSE ;
 UPDATE geobs2010.obstacle_referentiel set goodproj=TRUE where ref_id IN (
-SELECT  ref_id from geobs2010.correspondance);
+SELECT  ref_id from geobs2010.roe_ccm_300);
 }}}
 We now have to use, and can display the dams left out in qgis