Spatial SQL -> MS-SQL 2008. Concatenate two lines (Line2 to the end of Line1)

This example shows use of user defined Spatial function that concatenates additional line segment to existing Line. The last parameter @Dir (default=0) says if this segment will be taken according to it's vector direction or in Reverced mode.

Example:
Two lines before concatenation
DECLARE @Line1 AS GEOGRAPHY, @Line2 AS GEOGRAPHY;
SELECT @Line1 = geog FROM dbo.nt_Roads WHERE id=200;
SELECT @Line2 = geog FROM dbo.nt_Roads WHERE id=201;

SELECT dbo.sfn_concat_lines(@Line1, @Line2, 2000, 0).STBuffer(5) AS g
United Line Object:
-- concat Line2 in opposite direction
SELECT dbo.sfn_concat_lines(@Line1, @Line2, 2000, 1).STBuffer(5) AS g
United Line Object:


USE milano
GO

-- ============================================================================
-- function: sfn_concat_lines
-- Concatenates two lines (Line2 to the end of Line1)
-- input: @Line1, @Line2 [type: GEOGRAPHY Lat/Long]
-- @max_vertix [max number of line vertices]
-- @Dir [concatenation direction of Line2. Default=0 means begin concatenation
-- according to Line2 direction. (> 0) means start concatenation in opposite
-- direction, from the end]
-- output: PolyLine GEOGRAPHY
-- ============================================================================

-- drop prev version
IF EXISTS (
  SELECT * FROM sys.objects
  WHERE object_id = OBJECT_ID(N'[dbo].[sfn_concat_lines]')
  AND type IN (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[sfn_concat_lines]
GO


CREATE FUNCTION dbo.sfn_concat_lines
(@Line1 AS GEOGRAPHY, @Line2 AS GEOGRAPHY, @max_vertix AS INT, @Dir AS TINYINT)
RETURNS GEOGRAPHY
AS
BEGIN

DECLARE @Line1_Vertices AS Varchar(MAX), @Line2_Vertices AS Varchar(MAX);
DECLARE @Tmp_String AS Char(80), @PLine AS GEOGRAPHY;

-- -------- create sequential numbers table -------------------
-- number of records will be defined by @max_vertix parameter
DECLARE @num_seq AS TABLE
(n INT NOT NULL);

WITH
  lv0 AS (SELECT 0 AS g UNION ALL SELECT 0),
  lv1 AS (SELECT 0 AS g FROM LV0 AS a CROSS JOIN LV0 AS b),
  lv2 AS (SELECT 0 AS g FROM LV1 AS a CROSS JOIN LV1 AS b),
  lv3 AS (SELECT 0 AS g FROM LV2 AS a CROSS JOIN LV2 AS b),
  lv4 AS (SELECT 0 AS g FROM LV3 AS a CROSS JOIN LV3 AS b), -- up to 65,536 values
Nums AS (SELECT ROW_NUMBER() OVER (ORDER BY (select NULL)) AS n FROM lv4)
INSERT INTO @num_seq (n)
SELECT TOP(@max_vertix) n FROM Nums;
-- --------------------------------------------------------------

-- LL numbers presition will be DECIMAL(12,8)
-- convert from FLOAT to DECIMAL(12,8)

-- --------------------------------------------------------------
-- 1. Extract Line1 vertices
-- --------------------------------------------------------------
SET @Line1_Vertices = 'LINESTRING (';

DECLARE Line1_Cursor CURSOR FAST_FORWARD
FOR
  SELECT CONVERT(Varchar(30), CAST(@Line1.STPointN(n).Long AS DECIMAL(12,8)), 2)
  + ' ' + CONVERT(Varchar(30), CAST(@Line1.STPointN(n).Lat AS DECIMAL(12,8)), 2)
  + ',' AS TheStr
FROM @num_seq
WHERE n <= @Line1.STNumPoints();

OPEN Line1_Cursor; -- open cursor
FETCH NEXT FROM Line1_Cursor INTO @Tmp_String;

-- run cursor

WHILE (@@FETCH_STATUS <> -1)
BEGIN
  SET @Line1_Vertices = @Line1_Vertices + ' ' + LTRIM(RTRIM(@Tmp_String));

  -- move to the next record
  FETCH NEXT FROM Line1_Cursor INTO @Tmp_String;
END

-- release data structures that was allocated by cursor
CLOSE Line1_Cursor;
DEALLOCATE Line1_Cursor;

-- --------------------------------------------------------------
-- 2. Extract Line2 vertices
-- --------------------------------------------------------------
SET @Line2_Vertices = '';

IF @Dir = 0
BEGIN
  DECLARE Line2_Cursor CURSOR FAST_FORWARD
  FOR
  SELECT CONVERT(Varchar(30), CAST(@Line2.STPointN(n).Long AS DECIMAL(12,8)), 2)
  + ' ' + CONVERT(Varchar(30), CAST(@Line2.STPointN(n).Lat AS DECIMAL(12,8)), 2)
  + ',' AS TheStr
  FROM @num_seq
  WHERE n <= @Line2.STNumPoints()
  ORDER BY n;
END
ELSE
BEGIN
  DECLARE Line2_Cursor CURSOR FAST_FORWARD
  FOR
  SELECT CONVERT(Varchar(30), CAST(@Line2.STPointN(n).Long AS DECIMAL(12,8)), 2)
  + ' ' + CONVERT(Varchar(30), CAST(@Line2.STPointN(n).Lat AS DECIMAL(12,8)), 2) + ',' AS TheStr
  FROM @num_seq
  WHERE n <= @Line2.STNumPoints()
  ORDER BY n DESC;
END

OPEN Line2_Cursor; -- open cursor
FETCH NEXT FROM Line2_Cursor INTO @Tmp_String;

-- run cursor

WHILE (@@FETCH_STATUS <> -1)
BEGIN
  SET @Line2_Vertices = @Line2_Vertices + ' ' + LTRIM(RTRIM(@Tmp_String));

  -- move to the next record
  FETCH NEXT FROM Line2_Cursor INTO @Tmp_String;
END

-- release data structures that was allocated by cursor
CLOSE Line2_Cursor;
DEALLOCATE Line2_Cursor;

-- close brakets at the end of last line
SET @Line2_Vertices = SUBSTRING(@Line2_Vertices,1,LEN(@Line2_Vertices)-1) + ')';

-- construct GEOGRAPHY object from String
SET @PLine = GEOGRAPHY::STGeomFromText(@Line1_Vertices + @Line2_Vertices,4326);

-- return result Line
-- with minor Generalization to remove duplicate vertices
RETURN @PLine.Reduce(0.005);

END
GO




sqlexamples.info