[Roadster] sqlite database spec

[Adam Renner]

Does tile.data contain the geometry information for all of the lines in 
the tile? 
 
What about other information such as address ranges and zipcodes?
 
Will this data only be stored in blobs and no longer in tables?
 
It seems that highway objects are going to be stored in tiles that span 
a larger geographic region than neighborhood roads. Will the highways 
also be stored in the smaller tiles?
 
Where is POI data stored? Only in tile.data? or in it's own table?
 
I see the advantages of this design. It should be a much smaller 
database and can be more easily divided to make local databases and 
blobs can be compressed. However, I do think that the previous MySQL 
layout was better, overall.
 
With the new design, you may be best off without using any sql database 
since most of the data is hidden in the blobs. All that you get by using 
sqlite is an indexed list of object names.
 
I suggest that you keep as much data as possible in tables and do away 
with tile.data. The data belonging to a tile can still be quickly 
fetched by searching for a tileID on the following table:
 
CREATE TABLE object{
    ID INT Not NULL,
    nameID INT3 NOT NULL,
    tileID INT4 NOT NULL,
    ...
    //stuff describing the geometry and other data
    ...
    PRIMARY KEY(ID),
    INDEX(nameID)
    INDEX(tileID)
}
 
How about using a hybrid structure that combines tiles with the spatial 
index.
Here is a concept:
*Tiles are arranged in a tree, highest level tile is whole world.
*Level2 branches into one tile per continent
*Level3 branches into one tile per country
*level4 branches into one tile per state/province
*level 5 branches into one tile per first three number of zip (or 
country equivalent)
*level 6 branches into full five number zip
*Tiles can be overlapping
*Major objects reference higher level tiles than minor objects. Major 
Highways at level 4, minor highways at level 6, other roads at level 6.
*Dividing by zip is not necessary but might be an effective strategy 
because rural tiles would cover large geographic areas and urban tiles 
be smaller.

Alternatively, you could use the above concept and instead of dividing 
by geographic features, we just slice everything into arbitrary tiles 
that do not overlap as you have proposed but still keep the tree 
structure as well. Large roads could then still exist at a different 
level and in a tile that is a parent to the tiles containing the small 
roads.
 
CREATE TABLE tile{
    ID INT4 NOT NULL,
    parentTile INT4 NOT NULL,
    minLon INT4 NOT NULL,
    minLat INT4 NOT NULL,
    maxLon INT4 NOT NULL,
    maxLat INT4 NOT NULL,
    PRIMARY KEY(ID),
    INDEX(parentTile)
}