Changes between Version 85 and Version 86 of CookBook join ROE_CCM

Timestamp:

May 6, 2010 5:11:57 PM (15 years ago)

Author:

cedric

Comment:

--

CookBook join ROE_CCM

@@ -142 +142 @@
 DROP TABLE IF EXISTS geobs2010.roe_ccm_300;
 CREATE TABLE geobs2010.roe_ccm_300 as (
-        SELECT distinct on (ref_id) ref_id, gid, min(distance) as distance, height, score,ref_position_etrs89 as the_geom FROM (
+        SELECT distinct on (ref_id) ref_id, gid, min(distance) as distance, height, score,nbdams,ref_position_etrs89 as the_geom FROM (
                SELECT ref_id, gid ,CAST(distance(r.the_geom, b.ref_position_etrs89) as  decimal(15,1)) as distance ,b.ref_position_etrs89,
                CASE WHEN  ref_hauteur_chute>0 then ref_hauteur_chute
@@ -148 +148 @@
                     ELSE  ref_hauteur_terrain
                     END  AS height,
-                0 As score
+                0 As score,
+                1 AS nbdams -- pour jointure ultérieure
                FROM geobs2010.obstacle_referentiel As b
                INNER JOIN  ccm21.riversegments r ON ST_DWithin(r.the_geom, b.ref_position_etrs89,300)
 --             WHERE b.goodproj IS TRUE -- Attention c'est faux
                ORDER BY ref_id) AS sub 
-        GROUP BY ref_id, distance, gid,height,score, the_geom
+        GROUP BY ref_id, distance, gid,height,score,nbdams the_geom
 ); 
 -- mise à jour de la table geometry_columns