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
JOIN TIGER_01 ON TIGER_01.TLID = links.tlid
and
USE tiger
UPDATE links
SET links.time = (links.distance / speeds.speed)*3600
FROM links
JOIN TIGER_01 ON TIGER_01.TLID = links.tlid
JOIN speeds ON TIGER_01.CFCC = speeds.cfcc
Notice that we divide the lat/long by 1000000 because the census data in given as an integer but the function requires a float. The time column is in seconds.
Clean-up
Lastly, clean-up your data by removing links where the start and end intersection is the same:
USE tiger
DELETE FROM links WHERE intersectionId1 = intersectionId2
We now have two tables, intersections that looks like this:
intersectionId lat lon
1 38912765 -77269961
2 38769655 -77189577
3 38758430 -77306988
4 38839553 -77259187
5 38854679 -77341000
6 38809585 -77280469
7 38753025 -77052907
8 38758290 -77213884
9 38789802 -77139534
10 38951528 -77277795
11 38826723 -77239421
12 38764074 -77190912
and intersections:
tlid intersectionId1 intersectionId2 distance time
215935151 43754 29459 0.0219 2
215935190 45694 6706 0.0154 1
215935191 41623 44046 0.0073 0
215971963 44351 32575 0.274 15
215935360 42403 45519 0.0022 0
215935361 38581 16564 0.0081 0
215935371 37397 43804 0.0745 7
215935471 36775 33727 0.2082 21
215935473 45519 31847 0.0062 0
215935475 44108 36235 0.2227 12
215935483 38483 7982 0.2191 22
215972001 39392 27942 0.0608 6
Comments
Post new comment