[postgis-users] distance beetween the 2 nearest pointsMardi

[Kevin Neufeld]

To find the closest store for a particular customer, you might try 
something like this:

SELECT a.name, ST_Distance(a.the_geom, b.the_geom), b.name
FROM customers a, stores b
WHERE a.name = 'Mr Smith'
ORDER BY ST_Distance(a.the_geom, b.the_geom)
LIMIT 2;

Of course, this will have to compute distance between Mr Smith and every 
store in order to determine which one is closest.  You will save a lot 
of computation time if you put an upper bound on the closest stores, ie. 
if at least 2 stores are always going to be within 5km, you could add a 
bounding box filter to your query:
...
WHERE ...
AND ST_Expand(a.the_geom, 5000) && b.the_geom
ORDER BY ...

or alternatively use ST_DWithin()
...
WHERE a.name = 'Mr Smith'
ORDER BY ST_DWithin(a.the_geom, b.the_geom, 5000)
LIMIT 2;


To find the closest store (or second closest store) for every customer 
is an easy matter and can be written in a single SQL statement:
SELECT a.name,
  (SELECT ST_Distance(a.the_geom, b.the_geom), b.name
   FROM stores b
   ORDER BY ST_Distance(a.the_geom, b.the_geom)
   LIMIT 1
   OFFSET 1 -- to find the second closest
  )
FROM customers a;

.. but to find both the closest and second closest in the same query 
without computing distance twice for every name is not so easy.  I think 
you might be left with writing a small PL/PGSQL function to simply loop 
through all your customers and use the first query I mentioned.

Alternatively, a completely inefficient approach would be to simply 
concatenate the two results together (but it'll work for you):

SELECT a.name,
  (SELECT ST_Distance(a.the_geom, b.the_geom), b.name
   FROM stores b
   ORDER BY ST_Distance(a.the_geom, b.the_geom)
   LIMIT 1
  )
FROM customers a
UNION ALL
SELECT a.name,
  (SELECT ST_Distance(a.the_geom, b.the_geom), b.name
   FROM stores b
   ORDER BY ST_Distance(a.the_geom, b.the_geom)
   LIMIT 1
   OFFSET 1 -- to find the second closest
  )
FROM customers a;


Hope that helps,
-- Kevin


cyril coeurjoly wrote:
> Hi, 
>
> I 'm trying to find a way to obtain the distance between two set of geographics datas.
>
> I'm using a Postgis database wich contains my two tables. A table of customers and a table of stores.
>
> For each customer, i would like to know the 2 nearest stores and the distance to theses stores. 
>
> something like :
>
> Mr Smith | 2500 m | Store 3
> Mr Smith | 2501 m | Store 4
>
> Mrs Smith | 240 m | Store 1
> Mrs Smith | 2501 m | Store 54
>
>
> thanks a lot.
>
>
>
> __________________________________________________
> Do You Yahoo!?
> En finir avec le spam? Yahoo! Mail vous offre la meilleure protection possible contre les messages non sollicités 
> http://mail.yahoo.fr Yahoo! Mail 
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>