[Roadster] sqlite database spec

[Scott Bronson]

I didn't see anything here to handle zooming out.  Right now, viewing
maps from a state or country view becomes unreasonably slow.


One solution would be to simply store another set of tiles at a
different resolution.  Because the information would be so coarse, i
don't think it would greatly increase the storage requirements.

I imagine 3 or more sets of tiles could be stored:

- Full resolution would include all roads, creeks, bridges, city
  boundaries, etc.
- Medium resolution would include large rivers, highways, and county
  boundaries (and some Eastern state boundaries).
- Coarse resolution would only include the biggest highways, rivers,
  and state lines.

Of course, this is US-Centric.  Europe would probably put country
borders at Medium resoultion.

That's just one thought.  Is there a better way of handling this?

    - Scott




On Fri, 2006-08-18 at 14:48 -0300, Ian McIntosh wrote:
> In response to Adam Renner's post, here's one possible database spec
> that has been developed jointly by Jeremy Cole, Ofer Achler, and me.
> 
> It doesn't require any "spatial" features in the database.
> 
> The main problems with our current approach using the MySQL Spatial
> Index are:
> 
> 1) Every point takes 16 bytes.  Storing the roads in the US would take
> several gigabytes this way.
> 
> 2) It doesn't let you select by area AND by type (eg "highway") in any
> optimized way, which is related to 3, below.. 
> 
> 3) For every road segment read in (or just examined), MySQL potentially
> does a 'seek' operation on the harddrive.  Seeks are slow, and hundreds
> of seeks are unacceptably slow.
> 
> Put simply, we need fewer seeks and smaller storage on disk.
> 
> ======================
> 
> Here's the proposal:
> 
> * Split the world up into square tiles.
> * All objects within that part of the world are stored in the tile.
> * Objects that span 2+ tiles are cut.
> * Object coordinates are stored as integer offsets from the tile's
> origin (with 1 unit = ~5 feet).
> * A tile number is derived from its Lan/Lon position in the world,
> making it possible to generate a list of tile IDs that cover any given
> area of Earth.
> 
> CREATE TABLE tile (
>   id         	INT4            NOT NULL,
>   data          MEDIUMBLOB      NOT NULL,
>   PRIMARY KEY (id)
> );
> 
> CREATE TABLE object_name (
>   id		INT3 UNSIGNED   NOT NULL,
>   name		VARCHAR(30)     NOT NULL,
>   tile_ids	MEDIUMBLOB	NOT NULL,
> 
>   PRIMARY KEY (id),
>   INDEX (name(7))
> );
> 
> (The object_name.tile_ids field lists tiles where this roadname shows up
> and is used for search.)
> 
> The size of each tile is chosen so that each point within the tile only
> requires 3 bytes (instead of MySQL's 16) while maintaining 5 foot
> precision.
> 
> We have the format of tile.data spec'd out, but I'd like to hear
> feedback on this approach before diving deeper.
> 
> -Ian
> 
> _______________________________________________
> roadster mailing list
> roadster at cairographics.org
> http://lists.freedesktop.org/mailman/listinfo/roadster