Postgres Pro
Downloads
Home   >   mailing lists

Thread: Syntax for use of point

Syntax for use of point

From
Poul Møller Hansen
Date:
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) ?


Thanks,
  Poul

Re: Syntax for use of point

From
Michael Fuhr
Date:
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

Re: Syntax for use of point

From
Poul Møller Hansen
Date:
> 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