I do pgRouting and I need to insert integer result to dijkstra algorithm from my SELECT
.
SELECT ST_AsGeoJSON(ST_Transform(way, 4326)) AS geometry
FROM pgr_dijkstra(
'SELECT osm_id AS id, source, target, st_length(way) as cost FROM planet_osm_roads',
34, 3000, false
) as di
JOIN planet_osm_roads pt
ON di.edge = pt.osm_id ;
This is working, but if I want replace for example node 34 in dijkstra function with node number of my street with this query:
SELECT pl.source::integer
FROM planet_osm_roads pl
WHERE pl.name LIKE ''street_name''
LIMIT 1
And together:
SELECT ST_AsGeoJSON(ST_Transform(way, 4326)) AS geometry
FROM pgr_dijkstra(
'SELECT osm_id AS id, source, target, st_length(way) as cost FROM planet_osm_roads',
'SELECT pl.source::integer FROM planet_osm_roads pl WHERE pl.name LIKE ''street_name'' LIMIT 1',
3000, false
) as di
JOIN planet_osm_roads pt
ON di.edge = pt.osm_id ;
It will fail with error:
ERROR: function pgr_dijkstra(unknown, unknown, integer, boolean) is not unique LINE 93: FROM pgr_dijkstra( ^ HINT: Could not choose a best candidate function. You might need to add explicit type casts.
I think, it is because that my select query gives back sql result with row ID, and column name. But maybe there is another problem.
How to output it only as single integer number?
Don't pass the query as a string:
SELECT ST_AsGeoJSON(ST_Transform(way, 4326)) AS geometry
FROM pgr_dijkstra(
'SELECT osm_id AS id, source, target, st_length(way) as cost FROM planet_osm_roads',
(SELECT pl.source::integer FROM planet_osm_roads pl WHERE pl.name LIKE 'street_name' LIMIT 1),
3000, false
) as di
JOIN planet_osm_roads pt
ON di.edge = pt.osm_id;
Or alternatively use a derived table
SELECT ST_AsGeoJSON(ST_Transform(way, 4326)) AS geometry
FROM (
SELECT pl.source::integer as source
FROM planet_osm_roads pl
WHERE pl.name
LIKE 'street_name' LIMIT 1
) pl
join lateral pgr_dijkstra(
'SELECT osm_id AS id, source, target, st_length(way) as cost FROM planet_osm_roads',
pl.source,
3000, false
) as di on true
JOIN planet_osm_roads pt ON di.edge = pt.osm_id;