Warnock Inc. Technology Consultation for the Future

When we created the road segments, we didn't fill in the distance and time information. Now that we have a distance function and speed data, we can fill in distance a time. These two values are need to calculate the shortest and fastest route between two points. Because the distance calculation can take a while, we will do the update in two steps. Use this SQL query to create this data:

USE tiger
UPDATE links
SET links.distance = round(dbo.LatLonRadiusDistance( FRLAT/1000000.0, FRLONG/1000000.0, TOLAT/1000000.0, TOLONG/1000000.0 ), 4)
FROM links

To calculate the time it takes to drive on a road, the speed a person drives must be taken into consideration. This is the cornerstone of improvements to the routing technique. While the speed data we use currently may not be 100% accurate, it is a good starting place, and a good baseline to test our improvements.

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]