using STDistance to find shortest path in graph

Category: sql server spatial


gsiewruk on Sat, 31 May 2014 12:36:02

I have a table which contain information about edges of the graph in form of geometry linestring. Spatial result of query select * from edge look like this 

EACH linestring is created always from two geometry points with insert statement like:

INSERT INTO edge VALUES( geometry::Parse('LINESTRING(1 1 ,1 2)'))

In order to finding shortest path between two points I have implemented Dijkstra algorith according toDijkstra in c#, However I have found out about STDistance() function which is ment to do the same thing just by executing simple query. Could anyone give me a hint how could I use STDistance with objects created like I described? Every example I find use linestrings created from 3 points.

I have difficulty using example in the situation I have lets say 3 linestrings as bellow:

INSERT INTO edge VALUES( geometry::Parse('LINESTRING(1 1 ,1 2)'))
INSERT INTO edge VALUES( geometry::Parse('LINESTRING(1 2 ,1 3)'))
INSERT INTO edge VALUES( geometry::Parse('LINESTRING(1 3 ,1 4)'))

and finding shortest path from 1 1 to 1 4

Edit: I have suceeded with combining all linestrings into one shape by:

SELECT geometry::UnionAggregate(linestring) FROM edge

i get shape :


Now I use STDistance as follows:

SELECT (geometry::UnionAggregate(linestring)).STDistance(geometry::STGeomFromText('POINT(0 0)', 0)) FROM edge

However the return value is about distance between point (0,0) and presented shape, when my intend is to count edges length from one point to the other, any clues?


gsiewruk on Sat, 31 May 2014 14:50:08

edited question

tracycai on Mon, 02 Jun 2014 03:23:14


To calculate the distance between two points, use the query like:

DECLARE @g geometry;

DECLARE @h geometry;

SET @g = geometry::STGeomFromText('POINT(1 1)', 0);

SET @h = geometry::STGeomFromText('POINT(1 2)', 0);

SELECT @g.STDistance(@h);

STDistance (geometry Data Type)