6

I have created a geo database using MySQL and the TIGERLine data files available from the US Census. This is actually an MSSQL database I created that I've transfered to MySQL. Everything works beautifully for geocoding and reverse geocoding. The only problem is that data is now almost 6 years old and I am looking for greater accuracy.

I've looked at the 2010 TIGERLine files and noticed that they changed the format back in 2007 to a new Shapefile format. I've had some success using Ogr2Ogr in getting those shapefiles populated into a database but now I'm stuck. I was able to build my street segments table (center line data) easily with the 2006 data and the format made sense from a processing point of view but the new shapefile format doesn't seem as straightforward.

Is there any documentation (and I've scoured the US Census Bureau) that relates the two formats? A side by side comparison of fields would be too much to ask, I'm sure, but that is exactly the kind of data that I'm looking for.

Thanks in advance.

| improve this question | |
5

You need the tables

  • addr,
  • addrfn,
  • and featnames.

And the geography in the edges layers.

addr

These are address ranges, just address ranges. They relate to feature (i.e. road) names via...

addrfn

A table relating address ranges and feature names. addr.ARID -> addrfn -> featnames.LINEARID

featnames

A table of feature names. Each edge (line) can have multiple features (multiple road names). This relates to the edges layer via the TLID column.

edges

These are all of the lines that make up all geographies. It relates to addr or featnames through the TLID column. You're only interested in edges with an MTFCC that start with 'S' (S1400, S1200, S1100 and maybe a couple others).

Alternatively, you may get by with the roads layer which is an extract of edges with SXXXX MTFCC codes and the primary feature name for that edge. It relates back to addr by LINEARID through the addrfn table.

EDIT: When you're really ready to go down the rabbit hole look at the Technical Documentation. As for routing, OSM might be a better choice at this point.

| improve this answer | |
  • +1 Thanks for pointing out the need for addr etc. tables. – whuber♦ May 17 '11 at 20:26
  • Thanks for the great answer! One question though, in the previous format of TIGERLine, RT2 (record type 2) had the road segments. This record had a sequence number that allowed me to build a nice routing table so I can do driving directions. Is there an equivalent in the edges or lines tables? How do I determine the order of the segments in order to create my street_segments table? Thanks again! – SRM May 18 '11 at 2:27
  • @SRM In the edges layer, TNIDF (from node) and TNIDT (to node). – Sean May 18 '11 at 12:42
  • Oh sweet! That's even easier than the old format. I had to cache the end points and build the sequences after I processed the RT1 table. It all seemed hokey to me - it worked but I didn't like it. This is much more straightforward. Thanks again! – SRM May 18 '11 at 15:42
3

I have been using the 2010 TIGER shapefiles extensively and found the transition easy. The basic change is that what used to be separated into different shapefiles is now contained in a single one: rivers, roads, special places, railroads, everything. The [MTFCC] field tells you what's what. All the roads have an "S" prefix; e.g., S1100 is primary roads. You can obtain documentation (pdf format, but readily converted to a database table) in the MAF/TIGER Feature Class Code (MTFCC) Definitions file. I made a table out of this and joined it to the TIGER files to obtain readable descriptions of the features I wanted, but that's not strictly necessary (and inefficient for SQL queries).

After you select the road features based on [MTFCC] you will have essentially the same format you are used to for geocoding.

Edit

The ftp site to download the files is ftp://ftp2.census.gov/geo/tiger/TIGER2010/. The shapefiles are in the EDGES subdirectory, organized by county and coded by state+county FIPS. As @sean points out, the address information is in separate ADDR tables that you join to the shapefiles on the [TLID] field. (In this way the Census doesn't have to supply null address ranges for all the non-street features.) For detailed instructions about what is needed and how it fits together, see http://www.census.gov/geo/www/tiger/rel_file_desc.pdf .

| improve this answer | |
  • @whuber- Any chance you can post that MTFCC join table (DBF?, CSV?) someplace so that others do not have to go through the same work? – RyanKDalton May 17 '11 at 20:00
  • @Ryan Where would you like to see it and in what format? (As a CSV it's 36 KB, a little much to post in a reply. :-) For the nonce, I put a tab-delimited version on my website. I think conversion to .dbf will truncate the description field. – whuber♦ May 17 '11 at 20:04
  • +1 Good overview. Any changes in coordinate accuracy? – Kirk Kuykendall May 17 '11 at 20:12
  • @Kirk I haven't quantified it, but I was using some Google maps for reference, as well as some satellite photos; I was looking out for errors; and yet I found generally better accuracy than I'm used to. Even the interstate ramps nicely overlaid their images in the photos. It could be due to the location (around DC) or it could be overall improvements: I don't know. – whuber♦ May 17 '11 at 20:15
  • 3
    The positional accuracy for roads is really bad in some areas. There was a big project to improve positional accuracy several years ago. A lot was done but in consolidating all the changes into the database, things went a bit haywire in places. Vertexes snapped to the wrong points causing zig-zags, whole sides of interstates missing or poorly attributed. Be careful using it at large scales or for routing. – Sean May 17 '11 at 20:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.