[postgis-users] distance beetween the 2 nearest pointsMardi

[Obe, Regina]

 Cyril and Kevin,
I've done this kind of thing a lot and came up with an approach for handling it.  Some people have written me with even better approaches of doing this which I haven't had time to try, but was going to document them once I tested them out and did some timings.

Check out this link
http://postgis.refractions.net/support/wiki/index.php?Nearest%20Neighbor%20Search

Hope this helps,
Regina


-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net [mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Kevin Neufeld
Sent: Thursday, May 01, 2008 4:56 PM
To: PostGIS Users Discussion; coeurjolycyril at yahoo.fr
Subject: Re: [postgis-users] distance beetween the 2 nearest pointsMardi

:) 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
_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
-----------------------------------------
The substance of this message, including any attachments, may be
confidential, legally privileged and/or exempt from disclosure
pursuant to Massachusetts law. It is intended
solely for the addressee. If you received this in error, please
contact the sender and delete the material from any computer.