[OSM-dev] System going offline for several hours
nick at nickhill.co.uk
Sun Apr 29 23:50:20 BST 2007
As advertised on the wiki front page and system stats, I am intending to run
some optimisation on the database.
This involves organising data in key tables in geographical location order as
opposed to the order the data was added to the tables.
After the optimisation, the table schema will remain exactly the same. Temporary
columns and indexes will be made for the process and dropped after, carrying an
integer tile reference which will be used to sequence records.
I have written the following MySQL function, which takes a +/- 180 degree
lat/lon then converts it to a 32 bit tile reference.
drop function if exists maketile//
CREATE FUNCTION maketile (floatlat double, floatlon double) RETURNS int unsigned
DECLARE outtile,counter,lat,lon INT unsigned;
set outtile = 0;
/*normalise float lat/lon to 16 bit integer*/
lat/lon 65535,0 returns 2863311530 or 10101010101010101010101010101010
lat/lon 0,65535 returns 1431655765 or 01010101010101010101010101010101
SET counter = 0;
WHILE counter < 16 DO
SET outtile = outtile + (lat%2)*POW(2,(1+(counter*2))) +
SET lat = lat >> 1;
SET lon = lon >> 1;
SET counter = counter + 1;
I will perform this optimisation on current_nodes. If there is time (before I go
to bed) on current_segments.
I predict the improved data locality will substantially reduce I/O load on the
DB for those tables affected.
More information about the dev