sqlpostgresqlpostgisdijkstrapgrouting

How to select just result value without row id and column name for function in sql?


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?

enter image description here


Solution

  • 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;