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

Question

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 1

It seems like if you have a lot more customers than you do stores, then it might be more efficient to create a layer of voronoi polygons for the stores, then do a spatial join of customers against the store polygons.

Answer 2

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

Answer 3

From http://www.bostongis.com/?content_name=postgis_nearest_neighbor:

If you needed to get the nearest neighbor for all records in a table, but you only need the first nearest neighbor for each, then you can use PostgreSQL's distinctive DISTINCT ON syntax. Which would look something like this:

SELECT DISTINCT ON(g1.gid)  g1.gid As gref_gid, 
       g1.description As gref_description, 
       g2.gid As gnn_gid, 
       g2.description As gnn_description  
FROM sometable As g1, sometable As g2   
WHERE g1.gid <> g2.gid 
      AND ST_DWithin(g1.the_geom, g2.the_geom, 300)   
ORDER BY g1.gid, ST_Distance(g1.the_geom,g2.the_geom) 

This will find minimum distances up to 300 units. So, you have to first check your data and find out how big your minimum distances are going to get.

Answer 4

Discussions about some basic nearest neighbor solutions can be found here: http://www.bostongis.com/?content_name=postgis_nearest_neighbor#120

/Nicklas

Answer 5

You have to find the shortest pair in a search box, and if the box has nothing in it, expand it. It's not pretty but it works. There's example PL/PgSQL code here http://trac.osgeo.org/postgis/wiki/UsersWikiNearest

Answer 6

Thanks for everyone's input. I ultimately went with a combination of eprand's and underdark's suggestions. The final code I used was:

CREATE TABLE closest_point as
SELECT DISTINCT ON (A.GID) A.GID AS CUST_ID, 
      (SELECT B.GID FROM "STORES" as B 
       ORDER BY ST_Distance(A.the_geom, B.the_geom) limit 1) as STORE_ID, 
       A.the_geom 
FROM "CUSTOMERS" as A, "STORES" as B;

I then created a voronoi diagram on the stores layer to confirm the results worked correctly, which of course they did. Thanks for the great work all!