[postgis-users] distance beetween the 2 nearest pointsMardi

[Kevin Neufeld]

:) Ok, well, THAT wasn't tested.

My last suggestion will not work for you since the subqueries should 
only return one column.

I would recommend that you iterate over customers and use the my first 
suggested query:

This does work :)

CREATE OR REPLACE FUNCTION
  get_closest_stores(
    text,
    OUT store_name text,
    OUT distance double precision)
  RETURNS SETOF record AS
$$

  SELECT b.name AS store_name,
         ST_Distance(a.the_geom, b.the_geom)
  FROM customers a, stores b
  WHERE a.name = $1
  ORDER BY ST_Distance(a.the_geom, b.the_geom)
  LIMIT 2

$$ LANGUAGE SQL;


-- Using some sample random data I generated...
SELECT foo.name,
      (foo.get_closest_stores).store_name,
      (foo.get_closest_stores).distance
FROM (
   SELECT name, get_closest_stores(name)
   FROM customers
   ) AS foo;
   name    |    store_name     |     distance    
-----------+-------------------+------------------
 Mr. Smith | Convenience Store | 3176.53885664189
 Mr. Smith | Large Mall        | 3336.27393902433
 Mr. McKay | Grocery Store     | 2980.08997338128
 Mr. McKay | Video Store       | 5913.29624615758
(4 rows)


Cheers,
Kevin



Kevin Neufeld wrote:
> 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
>>   
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users