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

6 Answers 6


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.

  • 1
    I like this approach!
    – underdark
    Nov 6, 2010 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/… Nov 6, 2010 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. Nov 7, 2010 at 3:02


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

  • 1
    This was the best way to accomplish the task. See my note below for actual code that I used. Nov 19, 2010 at 16:41

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.


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



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
       ORDER BY ST_Distance(A.the_geom, B.the_geom) limit 1) as STORE_ID, 

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!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.