Spatial SQL -> MS-SQL. Reverse spatial line direction

In this example we have function that rebuilds line (geography type), by reversing order of it's vertices.


IF EXISTS (SELECT name FROM sys.objects
  WHERE name = 'dbo.sfn_reorient_line' AND type IN (N'FN',N'IF', N'TF',N'FS', N'FT')
  DROP FUNCTION dbo.sfn_reorient_line
GO

-- -------------------------------------------------
-- function: sfn_reorient_line
-- changes line vector direction to the opposite
-- input: @Line [type: GEOGRAPHY]
-- @max_vertix [max number of line vertices]
-- output: Line GEOGRAPHY
-- -------------------------------------------------

CREATE FUNCTION dbo.sfn_reorient_line (@Line AS GEOGRAPHY, @max_vertix AS INT)
  RETURNS GEOGRAPHY
AS
BEGIN


DECLARE @Line_Vertices AS Varchar(MAX);
DECLARE @Tmp_String AS Char(80), @OutLine 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 precision will be DECIMAL(12,8)
-- convert from FLOAT to DECIMAL(12,8)

-- --------------------------------------------------------------
-- Extract Input Line vertices
-- --------------------------------------------------------------

SET @Line_Vertices = 'LINESTRING (';

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

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

-- run cursor

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

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

/* release data structures that was allocated by cursor */
CLOSE Line_Cursor;
DEALLOCATE Line_Cursor;

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

-- construct GEOGRAPHY object from String
SET @OutLine = GEOGRAPHY::STGeomFromText(@Line_Vertices,4326);

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

END;


sqlexamples.info