Slide 49
Slide 49 text
routing_sql
31
def routing_sql(dijkstra_condition, text)
grouping_text = ""
text_query = ""
if text
text_query = <<-EOS
with_geom.osm_id as osm_id,
degrees(ST_azimuth(
ST_StartPoint(ST_MakeLine(route_geom)),
ST_EndPoint(ST_MakeLine(route_geom))
)) as heading,
routing.osm_ways.tags as tags,
max(with_geom.seq) as max_seq,
EOS
grouping_text = <<-EOS
JOIN routing.osm_ways
ON (with_geom.osm_id = routing.osm_ways.osm_id)
group by with_geom.osm_id, routing.osm_ways.tags
order by max_seq;
EOS
end
<<-EOS
WITH
dijkstra AS (
SELECT * FROM pgr_dijkstra(
$$#{dijkstra_condition}$$,
(SELECT id FROM routing.ways_vertices_pgr
ORDER BY the_geom <-> ST_SetSRID(ST_Point(#{source_lon},#{source_lat}),4326) LIMIT 1),
(SELECT id FROM routing.ways_vertices_pgr
ORDER BY the_geom <-> ST_SetSRID(ST_Point(#{target_lon},#{target_lat}),4326) LIMIT 1),
false)
),
with_geom AS (
SELECT dijkstra.seq, dijkstra.cost, routing.ways.name, routing.ways.osm_id,
CASE
WHEN dijkstra.node = routing.ways.source THEN the_geom
ELSE ST_Reverse(the_geom)
END AS route_geom
FROM dijkstra JOIN routing.ways
ON (edge = ways.id) ORDER BY seq
)
SELECT
sum(with_geom.cost) as cost,
ST_Length(ST_MakeLine(route_geom)::geography) as length_m,
ST_AsText(ST_MakeLine(route_geom)) as geom_text,
#{text_query}
ST_AsText(ST_StartPoint(ST_MakeLine(route_geom))) as start_position,
ST_AsText(ST_EndPoint(ST_MakeLine(route_geom))) as end_position
FROM with_geom
#{grouping_text}
EOS
end
Making additional query
and grouping
for text builder.
select pgr_dijkstra
with custom query
with source location and
target location
Making result