This should have been an obvious precursor (that I didn't ask) to my other question: How to create spider diagrams (hub lines) in PostGIS?
If I do not know the relationship between a point in layer A (stores) and a point in layer B (customers), I would like to generally say "Customer 1 is serviced by the nearest store." While I realize this fact may not be true, it can be a decent surrogate.
Using PostGIS, what is the most efficient way to assign the ID of the nearest point in layer A (stores) to each point in layer B (customers). The output I am looking for is something like below.
Customer | Store
1 | A
2 | A
3 | B
4 | C
Answer
likewise:
select A.ID as CUST_ID, (select B.ID from B order by st_distance(A.geom,B.geom) limit 1) as STORE_ID from A
No comments:
Post a Comment