The ## operator is for finding the closest point to one geometric
object from another (e.g., point ## lseg), not for searching an
entire table. In this example you'll have to calculate the distance
to each point and order the results by it:
SELECT position, position <-> '55.4,-6.4'::point AS distance
FROM store_point
ORDER BY distance
LIMIT 1;
If you have a lot of data then you can use a bounding box search
to speed up the query (i.e., first restrict the result set to points
inside a box centered on the target point, then calculate the
distance to only those points). PostGIS adds spatial features to
PostgreSQL that allow such queries to use indexes so they can be
fast even on large data sets.
http://postgis.refractions.net/
Thanks, that explains why it wouldn't work for me.
I was reading on the Postgis, and I'm sure it will be the final
solution, but then I'm short on time now, I'm running through the 1500
positions
and calculate the distance to each of them to find the shortest.
I can do that in 100ms so it's usable till further.
Poul
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
