How to get the intermediate points between a route origin and destination?

I'll try to give an answer that covers generally your issue.

So let's say you have a table of points called "clients" and another table called "stores" for store.

one of the issue is to know how you get your geometry that symbolized the road, you will need to use some routing here to get a "real" road trajectory based on a road network, and then retrieve these components

For the sake of this example, i will say that you have isolated every linestring that composed this road in a list of id or something, then you can do it this way :

your spatial functions :

with road as (
select roads_segments.geom as geom
where roads_segments.id in {road_list}
)
select client.id, client.name 
from clients, road
where St_intersects(road.geom, clients.geom)

Based on your drawing, you may want to do this simple approximation, that the road is the linestring between your two points, then you don't need the road table, you just create this line with St_MakeLine. you may also want to add a buffer around your linestring to create an area instead of a line, (if you say 5 km around my road, for example) ...

with road as (
select St_MakeLine(store.geom, client.geom) as geom
from store, client
where store.id = origin_id
and client.id = client_id)
select * from client
where St_intersects(client.geom, road.geom)

Edit : let's say we have a client and his coordinates , a table of "competing stores" as geometry points, we will make a road as a Linestring to each store and for each of this store we would like to find the others stores in a radius of 500 meters.

with client as (
select Set_Srid(St_MakePoint({lon}, {lat}),4326) as geom),
roads_to_stores as (
select St_Makeline(client.geom, competing_store.geom) as geom,
competing_store.id as id_store
from competing_store)
select LISTAGG(competing_store.id),
roads_to_store.id as destination_id,
from competing_store, roads_to_stores
where St_DWithin(roads_to_stores.geom, competing_store.geom, 500)
group by roads_to_store.id

(I can't not test this, tell me if it works, it probably has a typo and I can edit it later)

Anyway, you could try to get it step by step.

NB : This is a kind of pseudo code, I am not familiar with redshift and answer on a base of Postgres, I didn't test anything but this should look like this ...

fonctions used :

https://docs.aws.amazon.com/redshift/latest/dg/ST_MakeLine-function.html https://docs.aws.amazon.com/redshift/latest/dg/ST_Intersects-function.html