Thursday, 11 February 2016

geoprocessing - PostGIS: Assign ID of point in layer A to closest point in Layer B



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

arcpy - Changing output name when exporting data driven pages to JPG?

Is there a way to save the output JPG, changing the output file name to the page name, instead of page number? I mean changing the script fo...