Does SQL Server support measuring distance via routes?

Question

I've been experimenting with SQL Server spatial functions and seen that the STDistance method allows you to determine the distance between two geometries. Is there any functionality that allows you to measure distance between two points via roads, like Google Maps?

Answer 1

Yes you can... Be forewarned as it requires some work (which is of course "free" to you in GIS such as ArcGIS or postGIS).

Basically think of when you measure distance between two points along a series of roads (as opposed to direct euclidean distance) you are actually traversing a network graph.

So you need to

1. load in your roads (graph edges)
2. load in the intersections (graph nodes)
3. load in Point A and Z
4. Then use an optimal routing algorithm between A and Z going along the edges but through the nodes.

There is an in depth post here explaining it: Roads in SQL Server 2008 but you need to hook in the routing algorithm from here Djkstra in SQL.

The code to generate the roads network from the Alastaira's website (above) is duplicated in case it ever goes away.

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);