Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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
share|improve this question

6 Answers 6

up vote 3 down vote accepted

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

share|improve this answer
    
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.

share|improve this answer
    
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

share|improve this answer

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

share|improve this answer

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.

share|improve this answer

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!

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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