Changes between Version 10 and Version 11 of Meuse dbeel

Timestamp:

Jun 5, 2014 10:20:11 AM (11 years ago)

Author:

cedric

Comment:

--

Meuse dbeel

@@ -561 +561 @@
 ------------------------------------------------------------------------------------
 -- selection des opérations de l'ensemble des données de dbeel:
-DROP TABLE IF EXISTS dbeel.operations_ccm_500;
-CREATE TABLE dbeel.operations_ccm_500 as (
-        SELECT distinct on (op_id) op_id, gid, wso1_id, min(distance) as distance, the_geom FROM (
-               SELECT op_id, gid , wso1_id, CAST(distance(r.the_geom, s.the_geom) as  decimal(15,1)) as distance,s.the_geom 
+DROP TABLE IF EXISTS dbeel.observation_places_ccm_500;
+CREATE TABLE dbeel.observation_places_ccm_500 as (
+        SELECT distinct on (op_id) op_id, gid, wso1_id, min(distance) as distance,op_gis_layername, the_geom FROM (
+               SELECT op_id, gid , wso1_id,op_gis_layername, CAST(distance(r.the_geom, s.the_geom) as  decimal(15,1)) as distance,s.the_geom 
                FROM dbeel.observation_places As s
                INNER JOIN  ccm21.riversegments r ON ST_DWithin(r.the_geom, s.the_geom,500)  --76406 observations sur 92286
                WHERE s.the_geom IS NOT NULL
                ) AS sub 
-        GROUP BY op_id,distance, gid, wso1_id,  the_geom
+        GROUP BY op_id,distance, gid, wso1_id,  the_geom,op_gis_layername
 );
 
-alter table dbeel.operations_ccm_500 add column id serial;
+alter table dbeel.observation_places_ccm_500 add column id serial;
 
 -- 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 '', 'dbeel', 'operations_ccm_500', 'the_geom', ST_CoordDim(the_geom), ST_SRID(the_geom), GeometryType(the_geom)
-FROM dbeel.operations_ccm_500 LIMIT 1;
+SELECT '', 'dbeel', 'observation_places_ccm_500', 'the_geom', ST_CoordDim(the_geom), ST_SRID(the_geom), GeometryType(the_geom)
+FROM dbeel.observation_places_ccm_500 LIMIT 1;
 -- creation d'index, clé primaire, et constraintes qui vont bien
-alter table dbeel.operations_ccm_500 add CONSTRAINT enforce_dims_the_geom CHECK (ndims(the_geom) = 2);
-alter table dbeel.operations_ccm_500 add  CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) = 'POINT'::text OR the_geom IS NULL);
-alter table dbeel.operations_ccm_500 add  CONSTRAINT enforce_srid_the_geom CHECK (srid(the_geom) = 3035);
-alter table dbeel.operations_ccm_500 ADD CONSTRAINT pk_id PRIMARY KEY(id);
-CREATE INDEX indexstation_ccm_500 ON dbeel.operations_ccm_500
+alter table dbeel.observation_places_ccm_500 add CONSTRAINT enforce_dims_the_geom CHECK (ndims(the_geom) = 2);
+alter table dbeel.observation_places_ccm_500 add  CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) = 'POINT'::text OR the_geom IS NULL);
+alter table dbeel.observation_places_ccm_500 add  CONSTRAINT enforce_srid_the_geom CHECK (srid(the_geom) = 3035);
+alter table dbeel.observation_places_ccm_500 ADD CONSTRAINT pk_id PRIMARY KEY(id);
+CREATE INDEX indexstation_ccm_500 ON dbeel.observation_places_ccm_500
   USING GIST ( the_geom GIST_GEOMETRY_OPS );