Multiple number values input for pgr_drivingdistance gives an error in QGIS Model designer

Question

I'm building a QGIS model for calculating isochrones with PostGIS. I can input multiple time values in the model by typing something like '5,10,15' (these are minutes for calculating) into a string input, then I add these values to a vector layer column by string_to_array(@time_values, ',') in Field calculator and I set this field type as Integer List.

Then I run this PostGIS query to calculate driving distance with PostGIS:

SELECT * FROM pgr_drivingDistance(
    'SELECT id, source, target, cost, time FROM network', 
    ARRAY(SELECT node_id FROM isochrone_origins), 
    ARRAY(SELECT network.time from network limit 1),
    false
)

But I get an error:

    Error encountered while running CREATE TABLE route walking: Error executing SQL:
    SQL error: SELECT * FROM pgr_drivingDistance( 'SELECT id, source, target, cost, time FROM network', 
ARRAY(SELECT node_id FROM isochrone_origins), 
ARRAY(SELECT network.time from network limit 1), false ) returned 0 [ERROR: function pgr_drivingdistance(unknown, integer[], integer[], boolean) does not exist

Seems like pgr_drivingdistance function does not allow to use arrays for time values, because when there's just one integer value it works fine without array (SELECT network.time from network limit 1).

Is there any way to make this work?

Answer 1

Process your time array sequentially:

SELECT
  arr.time_id,
  dd.*
FROM
  UNNEST(
    ARRAY(SELECT network.time FROM network LIMIT 1)
  ) WITH ORDINALITY arr(time, time_id)
  CROSS JOIN LATERAL
  pgr_drivingDistance(
    'SELECT id, source, target, cost, time FROM network', 
    ARRAY(SELECT node_id FROM isochrone_origins), 
    arr.time::FLOAT,
    false
  ) AS dd
;

or run against the actual table:

SELECT
  n.*,
  dd.*
FROM
  network AS n
  CROSS JOIN LATERAL
  pgr_drivingDistance(
    'SELECT id, source, target, cost, time FROM network', 
    ARRAY(SELECT node_id FROM isochrone_origins), 
    n.time::FLOAT,
    false
  ) AS dd
WHERE
  <filter>
;

Note: time is a reserved word (TIME type) in PostgreSQL and shouldn't be used as identifier!

How to handle that inside the QGIS Model Designer I don't know, but doesn't seem too far off the original attempt.