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