Recursive query

How can I do a recursive query with these 2 queries:

``````SELECT
ptb.* AS pois
FROM ways way, pois pta, pois ptb
WHERE way.id = 1
AND pta.id = 12
AND ST_DWithin(
ST_LineSubstring(
way.path,
ST_LineLocatePoint(way.path, pta.lonlat::geometry) + 20000 / ST_Length(way.path, false),
ST_LineLocatePoint(way.path, pta.lonlat::geometry) + 40000 / ST_Length(way.path, false)
)::geography,
ptb.lonlat::geography,
4000)
``````

This first query is for find all POIs around a track (between 2 others POIs). I get a list of green POIs and return A & B.

I need to find the distance from a POI, from each POIs from the list. For that, I have write a second query :

``````      SELECT
ST_Distance(way.path::geography, pta.lonlat::geography) +
ST_Distance(way.path::geography, ptb.lonlat::geography) +
ST_Length(ST_LineSubstring(
way.path,
least(ST_LineLocatePoint(way.path, pta.lonlat::geometry), ST_LineLocatePoint(way.path, ptb.lonlat::geometry)),
greatest(ST_LineLocatePoint(way.path, pta.lonlat::geometry), ST_LineLocatePoint(way.path, ptb.lonlat::geometry))),false)  AS dst_line
FROM ways way, pois pta, pois ptb
WHERE way.id = 1
AND pta.id = 12
AND ptb.id = #{poi2.id}
``````

This query return the distance between pta to ptb (A & B) - Not by fly, but along the linestring

pta => ptb(A) = 25 km

pta => ptb(B) = 35 km

How can I do a recursive query ? Is it a good idea

• I doubt it; truly recursive queries are a good idea if explicitly needed, but I have the feeling you are rather looking for iteration here? it also sounds like you want to perform a (K) Nearest Neighbor search? ...could you maybe add a picture describing your intentions? – geozelot Jan 14 '19 at 20:11
• Recursive queries are generally best for things that involve tree or graph like search. It is a little bit hard to understand what your 2nd query is doing, but there doesn't appear to be anything recursive about it. In my experience, they are very slow and to be avoided unless absolutely necessary. – John Powell Jan 15 '19 at 8:59
• @ThingumaBob. Funnily enough, recursive queries are in fact iterative -- it is a complete misnomer, as the note in the docs states. :D – John Powell Jan 15 '19 at 9:02
• @JohnPowell :D ah man, might I actually have been failing recursively in terminology here? ...should indeed have taken care to be more explicit. – geozelot Jan 15 '19 at 10:11
• Thanks for your help, I updated my question and added images. ;) – Ben Jan 15 '19 at 15:13