Changes between Version 33 and Version 34 of Noeud - parcours RHT

Timestamp:

Jan 5, 2012 3:17:56 PM (13 years ago)

Author:

celine

Comment:

--

Noeud - parcours RHT

@@ -169 +169 @@
 {{{
 #!sql
--- creation de la table bdmap2009.bdmap_rhtvs2, je rajoute un the_geom a la requete pour pouvoir voir la table dans Qgis  
+-- creation de la table bdmap2009.bdmap_rht, 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
+        SELECT distinct on (st_codecsp) st_codecsp, id_drain, min(distance) as distance, nom_station, source, bien_projetee, id_bdcarthage_troncon, id_bdcarthage_cours_d_eau, insee_comm, 
+                nom_commun, date_maj, a_conserver, commentaire, verifie, the_geom FROM (
+               SELECT st_codecsp, nom_station, source, bien_projetee, id_bdcarthage_troncon, id_bdcarthage_cours_d_eau, insee_comm, 
+                nom_commun, date_maj, a_conserver, commentaire, verifie, r.id_drain ,CAST(distance(r.the_geom, s.the_geom) as  decimal(15,1)) as distance,s.the_geom 
+               FROM bdmap2009.station_geography 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
-);
+        GROUP BY st_codecsp, id_drain, distance, nom_station, source, bien_projetee, id_bdcarthage_troncon, id_bdcarthage_cours_d_eau, insee_comm, 
+                nom_commun, date_maj, a_conserver, commentaire, verifie, the_geom
+);  ---2094 ms
 alter table bdmap2009.bdmap_rhtvs2 add column id serial;
 -- mise à jour de la table geometry_columns
@@ -191 +194 @@
 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
+CREATE INDEX indexbdmap_rhtvs2 ON bdmap2009.bdmap_rhtvs2
   USING GIST ( the_geom GIST_GEOMETRY_OPS );
 }}}