Extending existing linestrings
There are many cases when you might want to extend an existing LineString - specifying another point to add onto the end of the current route. The following code demonstrates how you can do this using T-SQL:
The result is as follows:
LINESTRING (-0.175 51.515, -0.591 51.512, -0.72199999667220527 51.518000000446158, -0.973 51.459)
-- 1.) Define a linestring (The railway line from Paddington - Slough - Maidenhead) DECLARE @Route geography SET @Route = geography::STLineFromText('LINESTRING(-0.175 51.515, -0.591 51.512, -0.722 51.518)',4326) -- 2.) Define a new point to add to the end of the route (Reading) DECLARE @NewPoint geography SET @NewPoint = geography::STPointFromText('POINT(-0.973 51.459)', 4326) -- 3.) Create a new linestring joining the endpoint of the original route to the new point DECLARE @RouteExtension geography SET @RouteExtension = geography::STLineFromText( 'LINESTRING(' + CAST(@Route.STEndPoint().Long AS VARCHAR(32))+ ' ' + CAST(@Route.STEndPoint().Lat AS VARCHAR(32)) + ',' + CAST(@NewPoint.Long AS VARCHAR(32))+ ' ' + CAST(@NewPoint.Lat AS VARCHAR(32)) + ')' , 4326) -- 4.) Use STUnion() to join the extension to the existing route SET @Route = @Route.STUnion(@RouteExtension) -- 5.) Check the new route SELECT @Route.STAsText()
