problème de distance source pour certaines stations

[cedric]

/*
id_trhyd = 408004159
Pourquoi a t'il au final comme distance source bdcar 0.05 alors qu'il a bien la bonne distance_source_max dans hylcov_arc_dist2 ?
*/

-- the trouble is already there in eda2.0
select * from bd_map.bd_map_bd_carthage where id_trhyd= 408004169 ; --0.05
--in bd_carhtage
select * from bd_map_bd_carthage where id_trhyd= 408004169 ; --0.05
select * from hylcov_arc_dist2 WHERE id_trhyd= 408004169; --0.0568835 ah id som_i 408004083
-- je ne comprends pas c'est pas sur la carte
select * from noeuds

-- Il s'agit en fait d'un problème de projection

--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 ,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 (select * from stationsp2 where st_codecsp='04350152') As s
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 = n.id_bdcarthage -- pour récupérer les strahler

WHERE s.the_geom IS NOT NULL
ORDER BY st_codecsp

-- ) as sub

-- GROUP BY st_codecsp, distance, dist_source,dist_sea,strahler, id_trhyd, the_geom

-- et pas d'un problème de left join... Les distances sont nulles, toutes

--SELECT distinct on (st_codecsp) st_codecsp, dist_source,dist_sea,strahler,id_trhyd, min(distance) as distance, the_geom FROM (

SELECT st_codecsp, 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 (select * from stationsp2 where st_codecsp='04350152') As s
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 = n.id_bdcarthage -- pour récupérer les strahler

WHERE s.the_geom IS NOT NULL
ORDER BY st_codecsp

-- ) as sub

-- GROUP BY st_codecsp, distance, dist_source,dist_sea,strahler, id_trhyd, the_geom

-- pas mieux

--SELECT distinct on (st_codecsp) st_codecsp, dist_source,dist_sea,strahler,id_trhyd, min(distance) as distance, the_geom FROM (

SELECT st_codecsp, d.id_trhyd ,d.dist_source_max as dist_source, distance(d.the_geom, d.the_geom) as distance,s.the_geom
FROM (select * from stationsp2 where st_codecsp='04350152') As s
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 = n.id_bdcarthage -- pour récupérer les strahler

WHERE s.the_geom IS NOT NULL
ORDER BY st_codecsp

-- ) as sub

-- GROUP BY st_codecsp, distance, dist_source,dist_sea,strahler, id_trhyd, the_geom

-- OK trouvé bravo Cédric

SELECT st_codecsp, d.id_trhyd ,d.dist_source_max as dist_source, distance(d.the_geom, s.the_geom) as distance,s.the_geom
FROM (select * from stationsp2 where st_codecsp='04350152') As s
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 = n.id_bdcarthage -- pour récupérer les strahler

WHERE s.the_geom IS NOT NULL
ORDER BY st_codecsp

-- ) as sub

[cedric]

Milestone Data integration deleted