Changes between Version 39 and Version 40 of CookBook join BDMAP_CCM v2

Timestamp:

Nov 9, 2010 3:31:57 PM (14 years ago)

Author:

cedric

Comment:

--

CookBook join BDMAP_CCM v2

@@ -138 +138 @@
 DROP TABLE IF EXISTS bd_map_bd_carthage;
 CREATE TABLE bd_map_bd_carthage 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 st_codecsp, n.distance_source,n.distance_mer,n.strahler ,a.id_trhyd ,CAST(distance(a.the_geom, s.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)
+               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
                WHERE s.the_geom IS NOT NULL
-               ORDER BY st_codecsp) AS sub 
-        GROUP BY st_codecsp, distance,gid, the_geom
+               ORDER BY st_codecsp
+
 );
--- ajout d'une clé primaire pour Qgis
-alter table bd_map_bd_carthage add column id serial;
+alter table  bd_map_bd_carthage add column id serial;
 -- mise à jour de la table geometry_columns
 INSERT INTO geometry_columns(f_table_catalog, f_table_schema, f_table_name, f_geometry_column, coord_dimension, srid, "type")
-SELECT '', '', 'bd_map_bd_carthage', 'the_geom', ST_CoordDim(the_geom), ST_SRID(the_geom), GeometryType(the_geom)
+SELECT '', 'public', 'bd_map_bd_carthage', 'the_geom', ST_CoordDim(the_geom), ST_SRID(the_geom), GeometryType(the_geom)
 FROM bd_map_bd_carthage LIMIT 1;