Syntax for use of point

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

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