Changes between Version 30 and Version 31 of CookBook join ROE_CCM

Timestamp:

Mar 23, 2010 7:43:43 AM (15 years ago)

Author:

cedric

Comment:

--

CookBook join ROE_CCM

@@ -123 +123 @@
 
 == Joining CCM and Dams ==
-this is ticket #47
+
+-- 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 
+CREATE INDEX indexgeobs ON geobs.obstacle_referentiel  USING GIST (ref_position_etrs89 GIST_GEOMETRY_OPS);
+DROP TABLE IF EXISTS geobs.correspondance;
+CREATE TABLE geobs.correspondance 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
+               FROM geobs.obstacle_referentiel As b
+               INNER JOIN  riversegments r ON ST_DWithin(r.the_geom, b.ref_position_etrs89,300)
+               WHERE b.goodproj IS TRUE
+               ORDER BY ref_id) AS sub 
+        GROUP BY ref_id, gid, the_geom
+); --53s
+alter table geobs.correspondance add column id serial PRIMARY KEY;
+-- mise à jour de la table geometry_columns
+INSERT INTO geometry_columns(f_table_catalog, f_table_schema, f_table_name, f_geometry_column, coord_dimension, srid, "type")
+SELECT '', 'public', 'correspondance', 'the_geom', ST_CoordDim(the_geom), ST_SRID(the_geom), GeometryType(the_geom)
+FROM correspondance LIMIT 1;
+
+-- creation d'index, clé primaire, et constraintes qui vont bien
+alter table correspondance add CONSTRAINT enforce_dims_the_geom CHECK (ndims(the_geom) = 2);
+alter table correspondance add  CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) = 'POINT'::text OR the_geom IS NULL);
+alter table correspondance add  CONSTRAINT enforce_srid_the_geom CHECK (srid(the_geom) = 3035);
+
+alter table correspondance ADD CONSTRAINT pk_id PRIMARY KEY(id);
+CREATE INDEX indexcorrespondance ON correspondance
+  USING GIST ( the_geom GIST_GEOMETRY_OPS );