Changes between Version 2 and Version 3 of CookBook join RHT-BDMAP

Timestamp:

May 20, 2011 10:49:18 AM (14 years ago)

Author:

celine

Comment:

--

CookBook join RHT-BDMAP

@@ -9 +9 @@
 
 Problème de projections !!! A vérifier en cours !
+{{{
 -- creation de la table bdmap_rht, je rajoute un the_geom a la requete pour pouvoir voir la table dans Qgis  
 DROP TABLE IF EXISTS rht.bdmap_rht;
 CREATE TABLE rht.bdmap_rht as (
-        SELECT distinct on (st_codecsp) st_codecsp, gid, min(distance) as distance, the_geom FROM (
-               SELECT st_codecsp, gid ,CAST(distance(r.the_geom, s.the_geom) as  decimal(15,1)) as distance,s.the_geom 
+        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 bd_map.stationsp2 As s
-               INNER JOIN  rht.riversegments r ON ST_DWithin(r.the_geom, s.the_geom,300)
+               INNER JOIN  rht.rht 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,gid, the_geom
+        GROUP BY st_codecsp, distance,id_drain, the_geom
 );
 alter table rht.bdmap_rht add column id serial;
@@ -33 +34 @@
 CREATE INDEX indexbdmap_rht ON rht.bdmap_rht
   USING GIST ( the_geom GIST_GEOMETRY_OPS );
-
+}}}