[OSM-dev] Spatial vs. multi-column indexes for points

[Andreas Kalsch]

All, thanks for your quick responses!

Quad tiles look like a smart way to create an index. So to lookup a single point or a quad tile, this is fine. But for my application I need another lookup - by bounding box with any ratio and size. Is there a way to look up a special bounding box with this index? I think this will be a little more complicated without conventional multi-column indices. Hmm, I think you could take a maximum number of quad lookups which contain the requested box, what do you think?

My current optimization includes:

- using mediumint for lat/lon - enough for ~2 meters resolution
- using a bounding box first for point+radius calculation and then selecting the circle with a Pythagoras approximation, which is exact enough

For the first, I want to use MySQL.

If you are interested in some benchmark data, I can post ...

> Instead of indexing by precise coordinates you could index by virtual
> tiles as it is done in OSM's main DB since a year ago with nice
> performance boost:
> 
> http://wiki.openstreetmap.org/index.php/QuadTiles
> 
> good luck,
> Stefan
> 
> 
> On Thu, Sep 11, 2008 at 1:49 PM, Andreas Kalsch <andreaskalsch at gmx.de>
> wrote:
> > Hey,
> >
> > last week I made some experiments with huge datasets of lat/lon points.
> I use MySQL 5.0, which partially support GIS extensions, including R-trees.
> But it is still not able to make queries based on the GIS features, so I
> have to use the normal way - multi-column indexes on lat/lon columns. It
> works well but probably there is a way to make it even quicker ;)
> >
> > Has anybody used GIS successfully in MySQL or PGSQL and can tell me how
> the performance compares between the two techniques?
> >
> > Thanks,
> >
> > Andi
> > --
> > Psssst! Schon das coole Video vom GMX MultiMessenger gesehen?
> > Der Eine für Alle: http://www.gmx.net/de/go/messenger03
> >
> > _______________________________________________
> > dev mailing list
> > dev at openstreetmap.org
> > http://lists.openstreetmap.org/listinfo/dev
> >

-- 
Psssst! Schon vom neuen GMX MultiMessenger gehört? Der kann`s mit allen: http://www.gmx.net/de/go/multimessenger