2

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

enter image description here

3
  • Do you only have the coordinate in lat / lon or do you have geometries ? Jan 20, 2020 at 14:13
  • I have only the coordinates (latitude and longitude)
    – dapdap
    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 Jan 20, 2020 at 15:33

1 Answer 1

0

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

4
  • 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).
    – dapdap
    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 ? 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.
    – dapdap
    Jan 20, 2020 at 16:56
  • I updated my answer, tell me if you need more explanation or if something is wrong. Jan 21, 2020 at 10:11

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.