# 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: PostGIS function to create spider diagrams?

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

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

-
This was the best way to accomplish the task. See my note below for actual code that I used. – RyanDalton Nov 19 '10 at 16:41

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.

-
I like this approach! – underdark Nov 6 '10 at 19:36
Which approach would be easiest to create voronoi polys? Are there other options that what is noted here: bostongis.com/… bostongis.com/… – RyanDalton Nov 6 '10 at 22:34
I think the Delaunay Triangulation and Dirichlet Package in the second tutorial would be appropriate, not sure if it is the easiest though. – Kirk Kuykendall Nov 7 '10 at 3:02

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

/Nicklas

-

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.

-

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

-

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!

-