[OSM-dev] More about spatial indexes

[Andreas Kalsch]

Marcus Wolschon schrieb:
> Andreas Kalsch schrieb:
>   
>> ... are nearly 4 times faster than multi.colum indices for lat/lon
>> in MySQL. Extracting 1 column / 17.000 rows out of 1 million takes
>> .39 secs vs. 1.39 - I think all Postgres guys know a similar value.
>> I think this is a pretty impressing result.
>>     
> Does the spatial extension come pre-installed with mysql?
> = can I expect it to be present for a home-user with no
> knowledge of software that has a mysql running?
It is preinstalled even in MySQL 4. It implements the OpenGIS standard 
for MyISAM tables without some rarely used features.
You have to use the special types "Point", "LinearString" ..., a spatial 
index ( "alter table x add spatial index(column)" ) and some functions 
to make operations upon the data - e.g. the prominent example whether 
points are inside a bounding box. This is for what R-Trees are implemented.

See http://dev.mysql.com/doc/refman/5.0/en/spatial-extensions.html

To take a deeper look inside the online doc is a good idea. There are 
some very useful tips about SQL optimization and: Learn to use the 
console if you have not done yet. Most MySQL admin tools don't support 
OpenGIS types.