Changes between Version 96 and Version 97 of CookBook join BDMAP_CCM v2

Timestamp:

Nov 12, 2010 2:01:53 PM (14 years ago)

Author:

cedric

Comment:

--

CookBook join BDMAP_CCM v2

@@ -192 +192 @@
 {{{
 #!sql
--- creation de la table bd_map_bd_carthage, je rajoute un the_geom a la requete pour pouvoir voir la table dans Qgis  
+
+
+CREATE INDEX indexhylcov_arc_dist2 ON hylcov_arc_dist2
+  USING GIST ( the_geom GIST_GEOMETRY_OPS );
+  
+  -- creation de la table bd_map_bd_carthage la jointure entre la bd_carthage et la table des distances par Cédric est déjà faite dans un left join qui donne la table hylcov_arc_dist2.
+  -- Ici on projette les stations de pêche sur la bd_carthage
+  
 DROP TABLE IF EXISTS bd_map_bd_carthage;
 CREATE TABLE bd_map_bd_carthage as (
      SELECT distinct on (st_codecsp) st_codecsp, dist_source,dist_sea,strahler,id_trhyd, min(distance) as distance, the_geom FROM (
-               SELECT st_codecsp, n.distance_mer as dist_sea,n.strahler ,a.id_trhyd ,d.dist_source_max as dist_source, CAST(distance(a.the_geom, s.the_geom) as  decimal(15,1)) as distance,s.the_geom
+               SELECT st_codecsp, n.distance_mer as dist_sea,n.strahler ,d.id_trhyd ,d.dist_source_max as dist_source, CAST(distance(d.the_geom, d.the_geom) as  decimal(15,1)) as distance,s.the_geom
                FROM stationsp2 As s
-               INNER JOIN hylcov_arc a ON ST_DWithin(a.the_geom, s.the_geom,300)
-                LEFT JOIN noeud_troncon_final n ON a.id_som_f::numeric = n.id_bdcarthage -- pour récupérer les strahler
-                LEFT JOIN hylcov_arc_dist2 d on a.id_trhyd =d.id_trhyd
+               INNER JOIN hylcov_arc_dist2 d ON ST_DWithin(d.the_geom, s.the_geom,300)
+                LEFT JOIN noeud_troncon_final n ON d.id_som_f::numeric = n.id_bdcarthage -- pour récupérer les strahler
                WHERE s.the_geom IS NOT NULL
                ORDER BY st_codecsp) as sub