[OSM-dev] Alternative PostgreSQL Schema

[Martijn van Oosterhout]

On 10/24/07, Tom Hughes <tom at compton.nu> wrote:
> The point is that you don't actually need (and probably don't want) to
> create a single field of type point. You just want to create a 2D index
> over the latitude and longitude columns:
>
>   CREATE INDEX node_latlon ON node USING gist (latitude, longitude)

To do a 2D index you have to do:
CREATE INDEX node_latlon ON node USING gist (point(latitude, longitude))

If you do a 2 column index you just get the same problems as with a 2
column b-tree index.

> That will sufficient to resolve simple queries along the lines of:
>
>   SELECT * FROM node WHERE latitude BETWEEN ... AND longitude BETWEEN ...

You want a single condition, for example:

SELECT * FROM node WHERE point(latitude,longitude) @ 'x,y';

Have a nice day,
-- 
Martijn van Oosterhout <kleptog at gmail.com> http://svana.org/kleptog/