[OSM-dev] Alternative PostgreSQL Schema

[Tom Hughes]

In message <471F505C.1040207 at bretth.com>
        Brett Henderson <brett at bretth.com> wrote:

> Patrick Weber wrote:
>
>> geo spatial indexing sounds great. how far could one then go with 
>> spatial queries as well?
>
> My knowledge of the geo-spatial stuff is very limited at the moment so 
> no idea :-)  All I know is that for some reason a point can't be indexed 
> directly but some trickery with the "circle" function allows you to work 
> around that.  In theory it should provide something similar to the 
> existing quad tiles in MySQL but I have no idea if that's how it will 
> work out.  It might also be possible to add some geo-spatial information 
> to ways but that might be moving beyond the realms of the current OSM 
> data model.

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)

I think that's the right syntax anyway...

That will sufficient to resolve simple queries along the lines of:

  SELECT * FROM node WHERE latitude BETWEEN ... AND longitude BETWEEN ...

Tom

-- 
Tom Hughes (tom at compton.nu)
http://www.compton.nu/