Warnock Inc. Technology Consultation for the Future

We will be calculated distances between points in lat/long and will need a function in SQL to help us with this. We will input lat1, lon1, lat2, lon2 and return a float with the distance between the points. This command will add the function to your database:

CREATE FUNCTION [dbo].[LatLonRadiusDistance]
(
@lat1Degrees float,
@lon1Degrees float,
@lat2Degrees float,
@lon2Degrees float
)
RETURNS float
AS
BEGIN

DECLARE @earthSphereRadiusNauticalMiles as float
DECLARE @nauticalMileConversionToMilesFactor as float
SELECT @earthSphereRadiusNauticalMiles = 6366.707019

Once we have a set of intersections, we find the connections between these intersections. This should be the data in the TIGER_01 table, but we are going to create out own data set in case there are any errors in the TIGER data. We need these segments and intersections to calculate routes using graphs, nodes and edges. The link table can be created with this:

CREATE TABLE [dbo].[links](
[tlid] [int] NOT NULL,
[intersectionId1] [int] NULL,
[intersectionId2] [int] NULL,
[distance] [float] NULL,
[time] [int] NULL
) ON [PRIMARY]

Once we have the road segment data added, we can move on to processing the data. While road segments are important, we also need to know where these segments intersect. To find this, we take all the start and end points and assume these are intersections.

The intersection table can be created with this command:

CREATE TABLE [dbo].[intersections](
[intersectionId] [int] IDENTITY(1,1) NOT NULL,
[lat] [int] NULL,
[long] [int] NULL
) ON [PRIMARY]

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.

Updates
4/1/2008 - Calculate Route, Priority Queues
3/31/2008 - Importing TIGER data, find intersections, find road segments, distance stored procedure, speeds, distance/speed data

TrafficMan is a project to incorporate real and real-time traffic data into the algorithms used to calculate routes and travel times. Often I wonder if taking the highway or a back-road is the fastest route; now I will in corporate real traffic and travel times into the equations