[OSM-talk] System going offline for several hours

[Nick Hill]

Hi All

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 
DETERMINISTIC
BEGIN
DECLARE outtile,counter,lat,lon INT unsigned;
set outtile = 0;
/*normalise float lat/lon to 16 bit integer*/
set lat=floor((floatlat+180)*182);
set lon=floor((floatlon+180)*182);
/*
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))) + 
(lon%2)*POW(2,(counter*2));
     SET lat = lat >> 1;
     SET lon = lon >> 1;
     SET counter = counter + 1;
  END WHILE;
RETURN outtile;
END


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.