[Roadster] Converting TIGER data to a MySQL database

[Jeff Garrett]

(Sorry.  I intended to reply on the list.)

> I have been working to convert the TIGER data into a MYSQL database that 
> uses spatial indexing. The advantage of this is the speed that spatial 
> indexing gives you when searching. I was hoping that someone developing 
> a mapping application would like to use this instead of the raw TIGER 
> files.  I am just trying to gauge if there would be any interest in 
> having Roadster use MySQL to store TIGER data. If so, I would be 
> interested in working with you to  develop this database. If not, I am 
> thinking about dropping the project to work on something that might be 
> more useful, like finding a way to gather more point-of-interest data 
> for use in mapping applications.

Well, Roadster already uses MySQL to store the TIGER data.  The
application imports the TIGER files to a MySQL database, and does take
advantage of spatial indexing.  (The importing happens in import_tiger.c
and the database description can be found near the bottom of db.c.)

Personally I think it'd be nice to have more POI data (esp. for
Chicago :-)).  Also, having a way to get decent driving (or travel)
directions would be good.  To start, Roadster has no way to tell which
streets are one-way.  And there is little weighting information: no
knowledge of speed limits or road conditions or traffic.  The BTS has
some data (e.g. from the Highway Performance Monitoring System) that
could be useful...

> The Pros to using a MySQL database are: spatial indexing is very fast, 
> MySQL is crossplatform, SQL is easy to work with and lots of developers 
> have experience, MySQL can be used with many programming languages, if 
> foreign country data ever becomes available, it will not be in TIGER 
> format and will need converted anyhow.

The last point is salient, but has little to do with MySQL.  The
importation is rather simple, and there's indications (see README) that
it may move to a separate program.  There might be interest in importing
ArcView shapefiles or Arc/Info files or ...

> The Cons to using a MySQL database are: Spatial Index creation is slow, 
> Table sizes can get very large.

You miss one important point: MySQL databases are not (as far as i know)
portable.  Correct me if I'm wrong, but taking a database to a different
machine (possibly with different type sizes and byte order) may  not
work.  This would be a boon for packagers who could run the import on
the TIGER or other datasets, and publish the resulting databases.
SQLite doesn't have this problem, and there is a note in TODO suggesting
moving to SQLite.  (It doesn't have spatial indexing, but this is
probably not be such a slowdown.)

-- 
Jeff
JID: jeff at jgarrett.org
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: application/pgp-signature
Size: 189 bytes
Desc: This is a digitally signed message part
Url : http://lists.freedesktop.org/archives/roadster/attachments/20060213/02ccd443/attachment.pgp