Yes, you can ... Be warned as this requires some work (which, of course, is "free" for you in a GIS, such as ArcGIS or postGIS).
Basically think, when you measure the distance between two points along a series of roads (as opposed to the direct Euclidean distance), you actually cross the graph network.
So you need
- Loading onto your roads (graph edges)
- loading at intersections (graph nodes)
- loading at points A and Z
- Then use the optimal routing algorithm between A and Z, going along the edges, but through the nodes.
There is a detailed message here: Roads in SQL Server 2008 , but you need to connect to the routing algorithm from here Djkstra in SQL .
The code for creating a road network from the Alastaira website (see above) is duplicated if it ever leaves.
DECLARE @Roads TABLE ( RoadId int, RoadName varchar(32) ); INSERT INTO @Roads VALUES (1, 'Britannia Road'), (2, 'Belsize Road'), (3, 'Vincent Road'), (4, 'Plumstead Road'); DECLARE @RoadSegments TABLE ( SegmentId int, RoadId int, SegmentGeometry geography ); INSERT INTO @RoadSegments VALUES (1, 1, 'LINESTRING(1.313772 52.636871, 1.315038 52.635229)'), (2, 1, 'LINESTRING(1.315038 52.635229,1.316052 52.63399,1.316401 52.633518)'), (3, 1, 'LINESTRING(1.316401 52.633518,1.316497 52.632869,1.316642 52.632542)'), (4, 2, 'LINESTRING(1.317538 52.632697,1.317307 52.633448,1.317098 52.633749)'), (5, 3, 'LINESTRING(1.31734 52.633818,1.315982 52.635498,1.315038 52.635229)'), (6, 4, 'LINESTRING(1.314546 52.633479,1.31529 52.633298,1.315902 52.633363,1.316401 52.633518)'), (7, 4, 'LINESTRING(1.316401 52.633518,1.317097 52.633749)'), (8, 4, 'LINESTRING(1.317098 52.633749,1.31734 52.633818)'), (9, 4, 'LINESTRING(1.31734 52.633818,1.318332 52.634119)'); DECLARE @RoadIntersections TABLE ( IntersectionId varchar(32), IntersectionLocation geography ); INSERT INTO @RoadIntersections VALUES ('A', 'POINT(1.315038 52.635229)'), ('B', 'POINT(1.316401 52.633518)'), ('C', 'POINT(1.317097 52.633749)'), ('D', 'POINT(1.31734 52.633818)'); DECLARE @RoadIntersection_Segments TABLE ( IntersectionId varchar(32), SegmentId int ); INSERT INTO @RoadIntersection_Segments VALUES ('A',1), ('A',2), ('A',5), ('B',2), ('B',6), ('B',3), ('B',7), ('C',7), ('C',4), ('C',8), ('D',5), ('D',8), ('D',9);