Changes between Version 16 and Version 17 of Meuse dbeel

Timestamp:

Jun 16, 2014 1:31:57 PM (11 years ago)

Author:

laura

Comment:

--

Meuse dbeel

@@ -1208 +1208 @@
 
 }}}
+
+{{{#!sql
+
+-- On refais la table observation_place_500 du coup avec la nouvelle version BD_MAP ( et IDEM pour la table station_500 avec le même script)
+
+--select * from dbeel.observation_places where op_gis_layername='BDMAP' -- 13059
+--select * from dbeel.observation_places where op_gis_layername='BDMAP' and the_geom is NULL -- 113 (c'est toujours les mêmes répétées par ans.
+select st_srid(the_geom) from dbeel.observation_places group by st_srid(the_geom) -- y a 3035 et lambert 93
+update dbeel.observation_places set the_geom=st_transform(the_geom,3035)
+
+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)  --69931 observations sur 87204 lignes
+               WHERE s.the_geom IS NOT NULL
+               ) AS sub 
+        GROUP BY op_id,distance, gid, wso1_id,  the_geom,op_gis_layername
+);
+
+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', '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.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 );
+
+}}}