Changes between Version 9 and Version 10 of Meuse dbeel

Timestamp:

Jun 5, 2014 9:33:08 AM (11 years ago)

Author:

cedric

Comment:

--

Meuse dbeel

@@ -517 +517 @@
 
 }}}
+= Projection =
+Nous choissions de projeter ensemble les données d'ouvrage et les données de stations de pêche qui sont dans la table observation places.
+{{{
+#!sql
+-----------------------------------------------------------
+--  Jointure géographique entre les observations et la CCM  
+-----------------------------------------------------------  
+
+------
+-- selection des opérations de belgiques:
+------
+DROP TABLE IF EXISTS belge.observation_places;
+create table belge.observation_places as(
+select *  from dbeel.observation_places where op_gis_systemname='SPW' or op_gis_layername='OBSTACLES'); -- 8751 lignes (563 pour stations et 8188 obstacles)
+
+DROP TABLE IF EXISTS belge.meuse_operations_ccm_500;
+CREATE TABLE belge.meuse_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 
+               FROM belge.observation_places As s
+               INNER JOIN  ccm21.riversegments r ON ST_DWithin(r.the_geom, s.the_geom,500) --6443 lignes (beaucoup d'exclus sont des obstacles en dehors du CCM)
+               WHERE s.the_geom IS NOT NULL
+               ) AS sub 
+        GROUP BY op_id,distance, gid, wso1_id,  the_geom
+);
+
+alter table belge.meuse_operations_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 '', 'belge', 'meuse_operations_ccm_500', 'the_geom', ST_CoordDim(the_geom), ST_SRID(the_geom), GeometryType(the_geom)
+FROM belge.meuse_operations_ccm_500 LIMIT 1;
+-- creation d'index, clé primaire, et constraintes qui vont bien
+alter table belge.meuse_operations_ccm_500 add CONSTRAINT enforce_dims_the_geom CHECK (ndims(the_geom) = 2);
+alter table belge.meuse_operations_ccm_500 add  CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) = 'POINT'::text OR the_geom IS NULL);
+alter table belge.meuse_operations_ccm_500 add  CONSTRAINT enforce_srid_the_geom CHECK (srid(the_geom) = 3035);
+alter table belge.meuse_operations_ccm_500 ADD CONSTRAINT pk_id PRIMARY KEY(id);
+CREATE INDEX indexstation_ccm_500 ON belge.meuse_operations_ccm_500
+  USING GIST ( the_geom GIST_GEOMETRY_OPS );
+  --select*from belge.meuse_operations_ccm_500
+
+------------------------------------------------------------------------------------
+------------------------------------------------------------------------------------
+-- 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 
+               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
+);
+
+alter table dbeel.operations_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;
+-- 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
+  USING GIST ( the_geom GIST_GEOMETRY_OPS );
+
+----------------------------------------------------------------------------------------
+-------------------------------------------------------------------------------------------
+}}}
+ [[Image(source:data/Docs/trac/Meuse/projection_op_france.jpg)]] [[BR]]
+ [[Image(source:data/Docs/trac/Meuse/projection_op_france_zoom.jpg)]] [[BR]]
+ [[Image(source:data/Docs/trac/Meuse/projection_op_meuse.jpg)]] [[BR]]
+ [[Image(source:data/Docs/trac/Meuse/projection_op_meuse_zoom.jpg)]] [[BR]]