[Roadster] Converting TIGER data to a MySQL database

[Adam Renner]

Jeff Garrett wrote:
>>    
>
>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.)
>  
Checked out the source; I am somehow on the same wavelength with 
whomever created the database layout. Mine was extremely similar. 4 road 
tables for varying size roads, a table for road names, a table for 
cities, one for states, one for zipcodes. There is some differences in 
how we were handling points of interest.

I noticed in the screenshots that parks and rivers were also displayed 
on some of the maps, but I did not see tables for this data anywhere in 
db.c....   Did I miss something?

Another table that I recommend including is one that I refer too as my 
"Glossary". I use it for lookups, so in case somebody enters something 
like "boulevard", I can determine that this is equivalent to "Blvd" and 
is a road type.
>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...
>
>  
POI data is what I have been working on. Right now I am using 
chefmoz.org data for restaurants. Shapefiles for a lot of things can be 
downloaded from nationalatlas.gov:
http://www.nationalatlas.gov/atlasftp.html

I have also recently discovered the google local for mobile phones site. 
It would be good to build a tool that could retrieve all the chinese 
restaurants within a 20 mile radius of some location:
http://www.google.com/xhtml?site=local&dm=none&latlng=39900000,-83200000&radius=20&q=category:+Restaurant+Chinese
>>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 ...
>
>  
Creating various wizard dialogues for importing various sorts of data 
and setting up the database is the direction I was headed. Then the 
wizard could either be run individually, as part of a separate app, or 
within the mapping application depending on the users needs. My final 
goal, however, was that end users would receive most data, especially 
tiger data from an alternate source such as an SQL dump. Then it lessens 
the headaches and complications of dealing with a large number of 
obscure data sources.
>>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
>  
The tables and indexes can be directly copied from one machine to 
another, at least to a certain extent. I have copied from a celeron 
machine to an athlon xp machine with no problems. I was using the same 
operating system and same MySQL version. If I had tried the copy with a 
new version of MySQL or a 64 bit processor, I have no idea what would 
have happened.

There are some potential solutions to this. Distribute different data 
loads for different processors, OSes and possibly MySQL version. We 
could also try distributing dumps of the database which would possibly 
speed up the process by eliminating the need to parse the tiger files.

I hope that we can find a way to merge our efforts. It appears that I am 
repeating a lot of the work that you have already done on this project.

--AR