On Wed, Oct 05, 2005 at 06:46:16PM +0200, Poul Møller Hansen wrote:
> I have made the table shown below and inserted two points,
> but I can't find the proper syntax for finding the nearest point
>
> create table store_point(position point);
> insert into store_point values ('55.512345, -6.55555');
> insert into store_point values ('55.123456, -6.11111');
> select * from store_point;
> position
> ----------------------
> (55.512345,-6.55555)
> (55.123456,-6.11111)
>
> I suppose that I should use the ## operator,
> but how does it work when I want to find the nearest
> row for the points (55.4,-6.4) ?
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/
--
Michael Fuhr