LarsVegas LarsVegas - 2 months ago 7
SQL Question

How to apply a recursive query to whole table?

This post is related to another question of mine. I came up with a recursive query that does basically want I want. As long as the count for the

dist_calc_points
attribute has not exceeded the recursive query is being executed. But this works only for one entry (see the
WHERE v2_channel.id=2
clause). How I can apply this query to the whole table?

WITH RECURSIVE dist(x, the_geom, d) AS (
SELECT
0::double precision,
the_geom,
0::double precision
FROM v2_channel where v2_channel.id=2
UNION ALL
SELECT
x+1,
v2_channel.the_geom AS gm,
d+(1/v2_channel.dist_calc_points) AS dist_calc_pnts
FROM v2_channel, dist
WHERE dist.x<v2_channel.dist_calc_points AND v2_channel.id=2
)
SELECT *, ST_AsText(ST_LineInterpolatePoint(the_geom, d)) FROM dist;

CL. CL.
Answer

To allow the CTE to apply to multiple rows, you have to be able to identify these rows. So just add the ID:

WITH RECURSIVE dist(id, x, the_geom, d) AS (
    SELECT
       id,
       0::double precision,
       the_geom,
       0::double precision
    FROM v2_channel
       UNION ALL
    SELECT
      dist.id,
      x+1,
      v2_channel.the_geom AS gm,
      d+(1/v2_channel.dist_calc_points) AS dist_calc_pnts
    FROM v2_channel JOIN dist
        ON  dist.x  < v2_channel.dist_calc_points
        AND dist.id = v2_channel.id
)
SELECT *, ST_AsText(ST_LineInterpolatePoint(the_geom, d)) FROM dist;