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

[Andreas Kalsch]

> > 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 ...
> 
> As an aside, there is currently a lack of good benchmarks on the different
> optimizations & hacks for spatial querying, in different circumstances,
> so anything contributing towards guidelines for developers would be most
> helpful

OK, some things I have quickly writte down while testing, made on:
- MySQL 5.0.67
- MacBook Intel Core 2 Duo 
- 2 GHz
- Mac OS 10.4

Time in secs

There are several ways to get the points within a circle ...
- I precompute some values which will be constant during the query - this is quicker
- I use mediumints for lat/lon

1) correct, bust most expensive:
SET @latitude=48;
SET @longitude=13;
SET @latitudeM = getMed(@latitude); /* MySQL function */
SET @longitudeM = getMed(@longitude);
SET @latitudeSin=sin(radians(@latitude));
SET @latitudeCos=cos(radians(@latitude));
SELECT BENCHMARK(
	1000000,
	degrees(acos(
	@latitudeSin*sin(radians(2300000)) 
	+ @latitudeCos*cos(radians(2300000))*cos(radians(250000- at longitudeM))
)));
.62 secs

2) approximation - really good results for radius up to 10-20°:
SET @longitudeFactor=cos(radians(@latitude));
SELECT BENCHMARK(
	1000000, 
	sqrt(pow(@latitudeM - 2300000, 2) + pow(@longitudeFactor * (@longitudeM - 250000), 2))
);
.40 secs

3) OK, sqrt can be omitted in the resulting query
SELECT BENCHMARK(
	1000000, 
	pow(@latitudeM - 2300000, 2) + pow(@longitudeFactor * (@longitudeM - 250000), 2)
);
.36 secs


If there are more people interested in concrete computation based on 500.000 rows I can post them, too.

Andi
-- 
Der GMX SmartSurfer hilft bis zu 70% Ihrer Onlinekosten zu sparen! 
Ideal für Modem und ISDN: http://www.gmx.net/de/go/smartsurfer