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

I use Redshift and recently Amazon Redshift has updated the database to be able to use some spatial functions.

https://docs.aws.amazon.com/redshift/latest/dg/geospatial-functions.html

In a table I have a customer origin and destination (store) coordinates.

In another table, I have all the coordinates of all the stores (including the destination one).

How can I know the points (red) that the client has to go through to reach the destination?

I could simplify the model using the shortest algorithm of the camimo, but I have not seen if redshift currently has this function.

Attached example image

• Do you only have the coordinate in lat / lon or do you have geometries ? Commented Jan 20, 2020 at 14:13
• I have only the coordinates (latitude and longitude) Commented Jan 20, 2020 at 15:09
• ok, to create geometries from your coordinates you should do this first : ST_SetSRID(ST_MakePoint(long::double precision, lat::double precision), 4326) as geom assuming you are using long lat accordingly to EPSG :4326 standard Commented Jan 20, 2020 at 15:33

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 :

``````with road as (
)
select client.id, client.name
``````

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
``````

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),
select St_Makeline(client.geom, competing_store.geom) as geom,
competing_store.id as id_store
from competing_store)
select LISTAGG(competing_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 :

• Thanks so much for the explanation. I am trying to adapt the query to the second example. I think the second example is if I had CLIENT_ID in the store table, but it's not like that. I can only know if the customer has transacted in my store (STORE_ID). In the store table, I have competing stores, so I don't know if this customer has gone through the competition before arriving at my store. I would like to know if with Redshift, I can get the red points (competition) before arriving at the store (black point). Commented Jan 20, 2020 at 16:36
• If I try to rephrase it, you would like to know for each store, from a given client position, how many "other store" he will encounter on the road to your store ? Commented Jan 20, 2020 at 16:54
• Yes, I want to know from the customer, how many stores will pass until the customer arrives at the destination store. Commented Jan 20, 2020 at 16:56
• I updated my answer, tell me if you need more explanation or if something is wrong. Commented Jan 21, 2020 at 10:11