[Roadster] Converting TIGER data to a MySQL database

[William Reid]

Jeff Garrett wrote:

>Adam Renner wrote:
>  
>
>
>
---- snip

>>>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
>>    
>>
>
>Nice.  Thanks.
>  
>
Neat.  Not familiar with the data types though.....

>  
>
>>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
>>    
>>
>
>I've had mixed results with Google local, but maybe I just don't know
>how to use it yet.  Personally, I think it'd be nice if I had a list of
>POIs important to me (restaurants, bars, pool halls, ...) near me.
>Since I don't always have wifi access (when out), it'd be nice to crack
>open the laptop and spontaneously choose a restaurant near me.  (This
>lacks some of the flavor of wandering around for miles though...)  Even
>more, it'd be nice if I could get directions to wherever it is I wish to
>go (even disconnected), esp. (for now) on public transportation.  But
>this may be a project for further down the line.  :)
>
>Another advantage of having a Google-free POI set is the possibility of
>sharing POI sets and even implementing some sort of ratings/reviews...
>
>  
>
I like having Maps that don't require the internet......

>>>>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.
>>    
>>
>
>I totally agree.  Users shouldn't have to deal with obscure data
>sources, or know the difference between TIGER/Line files of various
>ages...  What I was thing about was (I'm on a Debian-based distro)
>making packages, like roadster-data-ca, roadster-data-il, etc.  Then a
>simple "apt-get install roadster-data-il" would get you the data.
>(Separate packages probably for POIs.  These could get out of hand
>quickly.)  The way I was thinking about doing this was:
>
>(1) Make Roadster use SQLite instead of MySQL
>(2) Make Roadster get its information from more than one source:
>    e.g. first search for db files in a system directory, and then for
>    local ones (or changes) under ~/.roadster/data
>(3) Import the TIGER/Line data into SQLite databases (with a script :))
>(4) Massage the data (also with script)
>    This could include polygon stitching, maybe other things?
>(5) Make the deb's and rpm's (for those RedHat people :))
>
>What do you think? :)  I've done very little of this so far.
>
>(A wizard is fine, but I think it could be hard to make it
>non-confusing.  And the data import shouldn't so much be a user
>concern.)
>
>  
>
I so much agree with the debian(ish) way of packaging.  By state would 
be awsome.  apt-get done. 


>>>>The Cons to using a MySQL database are: Spatial Index creation is slow, 
>>>>Table sizes can get very large.
>>>>        
>>>>
>
>  
>
Embedded MySQL threw me for a little loop.  Also. I had a little more 
trouble managing/changing the data using an embedded mysql database.  
Perhaps it is my own learning curve or I missed a DOC.

It would be really cool to be able to use gdal to import/export whatever 
storage mechanism is used.  I have been really pleased with the use of 
the gdal libraries and thier ever-more-supporting formats.  The wealth 
of imformation (such as the one listed above at nationalatlas.gov) are 
growing and will require more support and translation.  I am not 
familiar with that format, but would bank that gdal will be soon.  With 
that said,  I think there is also the need to support some (or few) of 
the simpler formats such as a raster (like Geotiff ambedded or TAB) and 
something like shp files since these two formats can be easily used to 
represent such a large amount of usefull data.  They are also one of the 
easier to port to (using gdal or shplib, perl and on and on).  Local bus 
lines, local paths, local hot spots, POI's, and local maps come to mind 
(and the ability to share them in a portable format).  Or the use of 
planned routes in a very portable format (in case routing is from 
another place).  Weather, clouds cover....   All these things can be 
displayed  and shared using two simple formats.  If they could be 
displayed/layered.

The geospatial thing is (in my opinion) is a large learning curve.  
Anyone Having picked apart usgs and Tigerline data knows. The formats 
available are vast.   Getting things into a format that is compatible 
with whatever one is attempting to display is key.  I pretty much use 
gdal for all of that (now).  And I  have pretty much settled on Geotiff 
and shp files for smaller datasets.

Roadster is so pretty that I just want to pack it up and display all my 
data with it.  And gather all my data with it.  This being the key to 
having a usefull portable mapping program.
---snip

>  
>
>>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.
>>    
>>
>
>  
>
I don't want to see any efforts get duped.  That is why I am such a 
terrible lazy programmer ;)

>"you" is not be the right pronoun.  I've contributed nothing (just
>lurked on the list). My understanding is that this is practically all
>Ian's work.  I am interested mainly for the reasons you can infer from
>this e-mail.  I would like a decent mapping/POI/direction thing.  And
>it'd be really cool if I could use this to explore more of this
>city.  ;)
>
>  
>
Thanks Ian.  A bunch.  I still feel like I owe you a bunch for such a 
nice project.

-=bill

>------------------------------------------------------------------------
>
>_______________________________________________
>roadster mailing list
>roadster at cairographics.org
>http://lists.freedesktop.org/mailman/listinfo/roadster
>  
>