Hi
Had a bit of time to play around with a multi point line. The following will do a parallel for both sides of an input geometry. I've left all the calculations exploded out to try and make it a bit easier to follow. There is probably better math for this though:-D
DECLARE @LineString Geometry = Geometry::STGeomFromText('LINESTRING (7 5, 10 3, 11 4, 13 4, 13 -2, 7 1, 5 -2)',0);
DECLARE @offset FLOAT = .5;
WITH cteTally AS (
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) N
FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) E1 (N)
,(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) E2 (N)
,(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) E3 (N)
)
,linePoint AS (
SELECT TOP(@LineString.STNumPoints()) N
,X = @LineString.STPointN(N).STX
,Y = @LineString.STPointN(N).STY
,B2Next = CASE WHEN N < @LineString.STNumPoints() THEN
CAST(90 -
DEGREES(
ATN2(
@LineString.STPointN(N + 1).STY - @LineString.STPointN(N).STY,
@LineString.STPointN(N + 1).STX - @LineString.STPointN(N).STX
)
) + 360
AS DECIMAL(38,19)) % 360
END
FROM cteTally
)
,offsetBearings AS (
SELECT b1.N, b1.X, b1.Y, b1.B2Next,
offsetAngleLeft = CASE
WHEN b1.B2Next is NULL THEN b2.B2Next - 90
WHEN b2.B2Next is NULL THEN b1.B2Next - 90
ELSE (360 + b1.B2Next - ((360 - ((b2.B2Next + 180) - b1.B2Next)) / 2)) % 360
END,
offsetAngleRight = CASE
WHEN b1.B2Next is NULL THEN b2.B2Next + 90
WHEN b2.B2Next is NULL THEN b1.B2Next + 90
ELSE (b1.B2Next + ((((b2.B2Next + 180) - b1.B2Next)) / 2)) % 360
END
FROM linePoint b1
LEFT OUTER JOIN linePoint b2 ON b1.N = b2.N + 1
)
,offsetDistance AS (
SELECT *,
offsetDist = CASE
WHEN N = 1 or B2Next is null THEN @offset
ELSE @offset / (SIN(RADIANS(((b2Next - offsetAngleLeft) + 360) % 360)))
END
FROM offsetBearings
)
, parallelCoords AS (
SELECT *
, XL = X + (offsetDist * COS(RADIANS(90 - offsetAngleLeft)))
, YL = Y + (offsetDist * SIN(RADIANS(90 - offsetAngleLeft)))
, XR = X + (offsetDist * COS(RADIANS(90 - offsetAngleRight)))
, YR = Y + (offsetDist * SIN(RADIANS(90 - offsetAngleRight)))
FROM offsetDistance
)
SELECT 'Left' Name, ParallelLineLeft = geometry::Parse (
CONCAT
(
'LINESTRING('
,STUFF((SELECT
CONCAT
(
CHAR(44)
,CHAR(32)
,CAST(XL AS VARCHAR(12))
,CHAR(32)
,CAST(YL AS VARCHAR(12))
) AS [text()]
FROM parallelCoords NM
ORDER BY N
FOR XML PATH(''), TYPE).value('.[1]','VARCHAR(8000)'),1,1,'')
,CHAR(41)
))
UNION ALL
SELECT 'Right' Name, ParallelLineRight = geometry::Parse (
CONCAT
(
'LINESTRING('
,STUFF((SELECT
CONCAT
(
CHAR(44)
,CHAR(32)
,CAST(XR AS VARCHAR(12))
,CHAR(32)
,CAST(YR AS VARCHAR(12))
) AS [text()]
FROM parallelCoords NM
ORDER BY N
FOR XML PATH(''), TYPE).value('.[1]','VARCHAR(8000)'),1,1,'')
,CHAR(41)
))
UNION ALL
SELECT 'Orig' Name, @LineString
Edit Added some ORDER BY clauses in to ensure line vertexes are added in the correct order
Looks like this