Raphaël Jacquot sxpert at esitcom.org
Tue Apr 25 13:31:02 BST 2006

SteveC wrote:
> * @ 25/04/06 01:15:45 PM sxpert at esitcom.org wrote:
>>imho, after reading the code that does the drawing, I'm pretty sure the 
>>problems we face are linked to the sql and the database server.
>>I'll attribute the slowness to
>>1) insane SQL queries ( see 
>>http://trac.openstreetmap.org/browser/ruby/api/wms/streets.pl#L52 for a 
>>good example)
> Actually those queries are quicker than you might think. You're right
> (as we chatted on IRC, IIRC) that having a flag for the latest data or
> having it in a seperate table might speed it up, but they have their own
> complexities like locking.

there's no need for locking when using a database server that is ACID 
I don't know what the fascination with mysql is, but it's way overrated, 
that's for sure :D

>>2) not using a geometric-aware database engine (1 stems partly from this)
> Again, can be faster but has it's own complexities. geomfromtext etc can
> take a lot of time

you don't need to use geomfromtext and friends. you don't even need 
postgis, as postgresql 8.1 includes most of it in a much better 
integrated manner, as I've shown.

>>3) from 2, not using proper indexes on the things that matter most, 
>>specifically indexes that would greatly accelerate requests looking for 
>>stuff that is within a 2D box
> /me nods. Feel free to hack on it.

I already have my own version of the api available (currently a limited 
version of the 'map' call, but I gather it's enough to show the 
potential, I will be adding the rest of the calls later on...)

just replace www.openstreetmap.org with www.navsys.org

for instance, to get the data (currently only nodes and segments) for 
Grenoble, point your browser to


I am using the same postgres db for my car navigation laptop (soon to be 
replaced by the (currently undergoing maintenance) carpc...

see http://www.navsys.org/cvs/pynavsys/ for the python code and sql to 
create the database

> I'd add 4) the db machine is way underspecced.

my own postgresql box is a celeron 600A with only 128M of ram.

> have fun,

I am having lots of fun. it's just that the experience could be much 
more enjoyable :D

> SteveC steve at asklater.com http://www.asklater.com/steve/

Raphaël Jacquot

More information about the talk mailing list