Postgres - Queryresults as function parameters

Question

I have the following problem: I have a data set of latitude and longitude points, where I want to calculate shortest distances between all of them.

While I found a solution, which works when explicitly specifying the parameters, using a query to get these parameters currently fails. My query looks as follows:

  with data as(
   SELECT testlonglat.*,f as id
   FROM  testlonglat, LATERAL get_closest_node( long,lat) f),
  sourced as (
   SELECT array_agg(id) from data where source = true),
  targetd as (
   SELECT array_agg(id) from data where source = false)
  SELECT *
  FROM sourced,
       targetd,
       LATERAL pgr_dijkstra(
                 'SELECT id,source,target,length::float8 AS cost FROM us1'::text,
                 sourced,
                 targetd,
                 false)

The first subquery to the variable data get a node id for every lat/long coordinate. The succeeding sourced and targetd just divide this large dataset into source and target nodes and concatenate all rows into an array. The resulting dataset only contains 1 records with an array.

The main query applies pgr_dijkstra to find shortest routes between all source and all destination nodes. The signature of this algorithm is

pgr_dijkstra(text,anyarray,anyarray,boolean)

The stated query however produces records and tries to call the following function

pgr_dijkstra(text,record,record,boolean)

Is there a way, to solve this issue? Explicitly casting the records variables to any array (e.g.,sourced::bigint[]) only throws another exception, stating that this cast is not possible.

Thank you for your help.

Just for testing try to CREATE TABLE source AS SELECT array_agg and try changing the data type. But you should show us one working example with a literal array and compare with the one you have in the SELECT` — Juan Carlos Oropeza
– Juan Carlos Oropeza, Commented May 11, 2017 at 13:12
Try sourced.array_agg, targetd.array_agg as parameters -- you could also use an explicit column alias too, in multiple places (I.e. WITH sourced(sourced) AS (...) or SELECT array_agg(id) AS sourced). — pozs
– pozs, Commented May 11, 2017 at 13:14
BTW dont know if you are having performance issues here. But I used to calculate route using tsrp with restriction similar like this and was too slow because the time is proportional to the number of routes, So instead of calculating all together I create a C# app with multithread and send multiple calculations at the same time to the db. — Juan Carlos Oropeza
– Juan Carlos Oropeza, Commented May 11, 2017 at 13:15

Clodoaldo Neto · Accepted Answer · 2017-05-11 13:24:25Z

1

with data as(
    select testlonglat.*,f as id
    from  testlonglat, lateral get_closest_node( long,lat) f
), sourced as (
    select array_agg(id) as aid from data where source = true
), targetd as (
    select array_agg(id) as aid from data where source = false
)
select *
from
    sourced,
    targetd,
    lateral pgr_dijkstra (
        'select id,source,target,length::float8 as cost from us1'::text,
        (select aid from sourced),
        (select aid from targeted),
        false
    )

answered May 11, 2017 at 13:24

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgres - Queryresults as function parameters

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related