Changes between Version 84 and Version 85 of CookBook join ROE_CCM

Timestamp:

May 6, 2010 4:30:44 PM (15 years ago)

Author:

cedric

Comment:

--

CookBook join ROE_CCM

@@ -137 +137 @@
  * gid
  * dam_height
- * dam_score
+ * dam_score (for the moment defaut is zero)
 {{{
 CREATE INDEX indexgeobs ON geobs2010.obstacle_referentiel  USING GIST (ref_position_etrs89 GIST_GEOMETRY_OPS);
 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, 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
+        SELECT distinct on (ref_id) ref_id, gid, min(distance) as distance, height, score,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
+                    WHEN   ref_hauteur_chute=0 then ref_hauteur_chute
+                    ELSE  ref_hauteur_terrain
+                    END  AS height,
+                0 As score
                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, the_geom
-); --53s
-
-
+        GROUP BY ref_id, distance, gid,height,score, the_geom
+); 
 -- mise à jour de la table geometry_columns
 select Probe_Geometry_Columns();