Changes between Version 20 and Version 21 of Noeud - parcours RHT

Timestamp:

Jan 5, 2012 10:20:16 AM (13 years ago)

Author:

celine

Comment:

--

Noeud - parcours RHT

@@ -163 +163 @@
 }}}
 
+== Joining BDMAP with RHT ==
+Projection spatiale des points BDMAP sur la couche station_geography (bdmap2009).
+{{{
+#sql!
+-- creation de la table bdmap2009.bdmap_rhtvs2, je rajoute un the_geom a la requete pour pouvoir voir la table dans Qgis  
+DROP TABLE IF EXISTS bdmap2009.bdmap_rhtvs2;
+CREATE TABLE bdmap2009.bdmap_rhtvs2 as (
+        SELECT distinct on (st_codecsp) st_codecsp, id_drain, min(distance) as distance, the_geom FROM (
+               SELECT st_codecsp, id_drain ,CAST(distance(r.the_geom, s.the_geom) as  decimal(15,1)) as distance,s.the_geom 
+               FROM bdmap2009.stationsp2 As s
+               INNER JOIN  rht.rhtvs2 r ON ST_DWithin(r.the_geom, s.the_geom,300)
+               WHERE s.the_geom IS NOT NULL
+               ORDER BY st_codecsp) AS sub 
+        GROUP BY st_codecsp, distance,id_drain, the_geom
+);
+alter table bdmap2009.bdmap_rhtvs2 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 '', 'bdmap2009', 'bdmap_rhtvs2', 'the_geom', ST_CoordDim(the_geom), ST_SRID(the_geom), GeometryType(the_geom)
+FROM bdmap2009.bdmap_rhtvs2 LIMIT 1;
+
+-- creation d'index, clé primaire, et constraintes qui vont bien
+alter table bdmap2009.bdmap_rhtvs2 add CONSTRAINT enforce_dims_the_geom CHECK (ndims(the_geom) = 2);
+alter table bdmap2009.bdmap_rhtvs2 add  CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) = 'POINT'::text OR the_geom IS NULL);
+alter table bdmap2009.bdmap_rhtvs2 add  CONSTRAINT enforce_srid_the_geom CHECK (srid(the_geom) = 3035);
+alter table bdmap2009.bdmap_rhtvs2 ADD CONSTRAINT pk_id PRIMARY KEY(id);
+CREATE INDEX indexbdmap_rht ON bdmap2009.bdmap_rhtvs2
+  USING GIST ( the_geom GIST_GEOMETRY_OPS );
+}}}
+
 
 #A FINIR ci-dessous !!