Changes between Version 5 and Version 6 of Anglian

Timestamp:

Jul 15, 2011 9:03:49 PM (14 years ago)

Author:

cedric

Comment:

--

Anglian

@@ -17 +17 @@
 = Projection =
 British national grid reference srid=27700
+-- Sauvegarde de la table station depuis la dbeel vers la ccm
+{{{
+e: 
+cd E:\IAV\eda\anglian
+C:\"Program Files"\PostgreSQL\9.0\bin\pg_dump -h localhost -U postgres --table anglian.station  --file "anglian_station.sql" dbeel
+C:\"Program Files"\PostgreSQL\9.0\bin\psql -h 93.20.247.238 -U postgres  --file "anglian_station.sql" eda2
+}}}
+
+{{{
+create schema anglian;
+}}}
+{{{
+#!sql
+DROP TABLE IF EXISTS anglian.anglian_station_ccm_500;
+CREATE TABLE anglian.anglian_station_ccm_500 as (
+        SELECT distinct on (site_id ) site_id , gid, wso1_id, min(distance) as distance, the_geom FROM (
+               SELECT site_id, gid , wso1_id, CAST(distance(r.the_geom, s.the_geom) as  decimal(15,1)) as distance,s.the_geom 
+               FROM anglian.station As s
+               INNER JOIN  ccm21.riversegments r ON ST_DWithin(r.the_geom, s.the_geom,500)
+               WHERE s.the_geom IS NOT NULL
+               ORDER BY site_id) AS sub 
+        GROUP BY site_id, distance, gid, wso1_id, the_geom
+);
+alter table anglian.anglian_station_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 '', 'anglian', 'anglian_station_ccm_500', 'the_geom', ST_CoordDim(the_geom), ST_SRID(the_geom), GeometryType(the_geom)
+FROM anglian.anglian_station_ccm_500 LIMIT 1;
+-- creation d'index, clé primaire, et constraintes qui vont bien
+alter table anglian.anglian_station_ccm_500 add CONSTRAINT enforce_dims_the_geom CHECK (ndims(the_geom) = 2);
+alter table anglian.anglian_station_ccm_500 add  CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) = 'POINT'::text OR the_geom IS NULL);
+alter table anglian.anglian_station_ccm_500 add  CONSTRAINT enforce_srid_the_geom CHECK (srid(the_geom) = 3035);
+alter table anglian.anglian_station_ccm_500 ADD CONSTRAINT pk_id PRIMARY KEY(id);
+CREATE INDEX indexstation_ccm_500 ON anglian.anglian_station_ccm_500
+  USING GIST ( the_geom GIST_GEOMETRY_OPS );
+}}}
+
+{{{
+C:\"Program Files"\PostgreSQL\9.0\bin\pg_dump -h 93.20.247.238 -U postgres --table anglian.station  --file "anglian_station_ccm_500.sql" eda2 
+}}}
+
+
 
 = Obstacle =