1

I have a query that uses a handful of CTEs to return a generated set of points. The actual query is more complicated, but the following will do as an example:

WITH geom AS (
    SELECT unnest('{
        POINT(-113.3997627 53.5721641), 
        POINT(-113.4037653 53.452761),
        POINT(-113.7066278 53.4571305),
        POINT(-113.7860125 53.5788978)
    }'::TEXT[]) AS point
    ORDER BY 1
),
points AS (
    SELECT (ROW_NUMBER() OVER ())::int AS id, point::geometry AS point
    FROM geom
)
SELECT *
FROM points;

I would like to use these points in the travelling salesperson problem function, but I haven't been able to make a simple query that achieves this.
Using the pgr_tsp function at the end of the query fails with at relation "points" does not exist error:

WITH geom AS (
    SELECT unnest('{
        POINT(-113.3997627 53.5721641), 
        POINT(-113.4037653 53.452761),
        POINT(-113.7066278 53.4571305),
        POINT(-113.7860125 53.5788978)
    }'::TEXT[]) AS point
    ORDER BY 1
),
points AS (
    SELECT (ROW_NUMBER() OVER ())::int AS id, point::geometry AS point
    FROM geom
)
SELECT seq, id1, id2, round(cost::numeric, 2) AS cost
FROM pgr_tsp('SELECT id, ST_X(point) AS x, ST_Y(point) AS y FROM points', 1);

Moving the entire query into the first parameter of the function seems to work, but I would need to either duplicate the CTE expressions outside of the function or store them in a temporary table to join them in the resulting order:

SELECT seq, id1, id2, round(cost::numeric, 2) AS cost
FROM pgr_tsp($$
    WITH geom AS (
        SELECT unnest('{
            POINT(-113.3997627 53.5721641), 
            POINT(-113.4037653 53.452761),
            POINT(-113.7066278 53.4571305),
            POINT(-113.7860125 53.5788978)
        }'::TEXT[]) AS point
        ORDER BY 1
    ),
    points AS (
        SELECT (ROW_NUMBER() OVER ())::int AS id, point::geometry AS point
        FROM geom
    )
    SELECT id, ST_X(point) AS x, ST_Y(point) AS y
    FROM points
$$, 1);

Is there any workaround for what I am trying to achieve, or should I use a transaction with temporary table?

I am using psql9.5, postgis2.2.1, and pgrouting 2.1.0. Yes, I am planning on updating them.

2

A third option (beside duplicating the CTE and using a TEMP table) would be to use a function to generate the content. You can then call this function from pgr_... or from a regular query. If it costly to generate the content, make the function stable and do your join and the call to pgr_... in a single statement.

On a side note, calling ROW_NUMBER() OVER () without an order by clause is wrong if you expect to generate the content (either from a CTE, a function or else) multiple times. There is absolutely no guarantee that the rows will come in the same order each time.

| improve this answer | |
  • In my actual query I have the OVER statement sorting the points by ST_X and ST_Y, thank you for that note though. I should be able to put my CTEs into a function, I will try that and thank you for responding. – GammaGames Sep 29 at 14:07
  • This worked! I made the function IMMUTABLE because it will always return the same result for given inputs, and joined the new function's id column with the id2 column from the pgr_tsp call. – GammaGames Sep 29 at 16:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.