[Roadster] sqlite database spec

[Ian McIntosh]

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

Good catch.  The solution we started using[1] is similar (identical?) to
what you have in mind.

Instead of just one tile table, we have several, each "higher" one has:

- a bigger tile size (and thus coarser resolution)
- fewer types of objects stored (minor roads only in tile0, for example)
- roads stitched together
- fewer points per object (road, lake, etc.)

This reduces load time, memory usage, draw time, and increases output
quality (drawing a tiny polygon with 1000 points sometimes looks bad,
depending on opacity and other settings).  So I agree it's the way to
go.

-Ian

[1]
http://linuxadvocate.org/projects/roadster/gallery/sp_index.php?file=./screenshots/roadster-0.3.8-1.png

> 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
> 
> _______________________________________________
> roadster mailing list
> roadster at cairographics.org
> http://lists.freedesktop.org/mailman/listinfo/roadster
>