The first step to improving routing methods is to mimic current routing methods. To do this, i took the free US Census TIGER/LINE data from the Census website and imported it into a SQL Server 2005 database. The Census Bureau provides map data to the public free of charge. the files are text files and are difficult to use in their current state. We'll import them into a database and work with them a bit. I will provide everything you need to know about what's in the files, however, they have written a great PDF show what each file is for, and what each field does. Their data includes roads, railroads, geographic bodies, like rivers, borders, address and zip code information and much more. This information can be used for geolocation and other things. The data is not perfect, but will work for our purpose.
Download the data you want to use from this website. It is divided by state. I'm using the data from the county I live in only. This keeps processing times lower. Use this web page to find the filename of the county you live in.
There are several files for each county. Basically, the .RT1 file contains all the road segments and a rough start and end of the segments. The segments terminate where the road intersects with another road. The .RT2 file has more data about the segment, such as more points along the way making the segment look more accurate on a map. We are most interested in RT1 and want to figure out what type of road we are using and what roads intersect with each road.
I imported two counties into my database. We will import all fields of both of these files into a database. The tables can be created with these files: RT1 and RT2. To import the data, use the BCP command and these FMT files: TIGER_01, TIGER_02. The BCP command is used like this:
bcp tiger..TIGER_01 in "path_to\TGR51059.RT1" -f"path_to\tiger1.fmt" -Scomputername\SQLEXPRESS -T
bcp tiger..TIGER_01 in "path_to\TGR51600.RT1" -f"path_to\tiger1.fmt" -Scomputername\SQLEXPRESS -T
bcp tiger..TIGER_02 in "path_to\TGR51059.RT2" -f"path_to\tiger2.fmt" -Scomputername\SQLEXPRESS -T
bcp tiger..TIGER_02 in "path_to\TGR51600.RT2" -f"path_to\tiger2.fmt" -Scomputername\SQLEXPRESS -T
tiger is the database name
TIGER_01 is the table name
TGR51059.RT1 is the text file from the census
tiger1.fmt is the format file from this site
computername\SQLEXPRESS - is the name of your computer and the SQL Server instance name
Run this on every file you want to import. I recommend starting with a county to keep the processing time low. We will investigate how to manage larger data sets in the future.