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
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
