I've got a table with polygon geometries, all with their origins @ (0,0). I want to recreate these polygons with the same dimensions, but at different origin points(x,y). Because all the original polygons have an origin of (0,0), I'm trying to add the new origin point value to each coordinate point in the polygon (0+x,0+y).
I'm trying to do this at a database level, using a user defined scalar function that will create the new geometry in a View Table.
I'm able to create a new table, with the new coordinates and the STPointN()
of each point in the polygon.
DECLARE @max INT
SET @max = MAX(@geom.STNumPoints())
DECLARE @points TABLE (pos int,OgX float,OgY float, NewX decimal(16,10),NewY decimal(18,10))
;WITH Sequence(Number) AS
(
SELECT 1 AS Number
UNION ALL
SELECT Number + 1
FROM Sequence
WHERE Number < @max
)
INSERT INTO @points
SELECT
Number
,@geom.STPointN(nums.number).STX AS OgX
,@geom.STPointN(nums.number).STY AS OgY
,@geom.STPointN(nums.number)[email protected] AS NewX
,@geom.STPointN(nums.number)[email protected] AS NewY
FROM Sequence nums
WHERE nums.number <= @geom.STNumPoints()
At this point I'm stuck trying to find a way to get the NewX
and NewY
columns to replace the points in the original geometry. I've tried:
DECLARE @geomtext VARCHAR(MAX) [email protected]()
DECLARE @idColumn INT = MIN(pos) FROM @points
WHILE @idColumn IS NOT NULL
BEGIN
SET @fromX = (SELECT OgX FROM @points WHERE pos=@idColumn)
SET @toX = (SELECT NewX FROM @points WHERE pos=@idColumn)
SET @geomtext= REPALCE(@geomtext,@fromX,@toX)
SELECT @idColumn = MIN(pos) FROM @points WHERE pos>@idColumn
END
but this obviously isn't accurate and replaces the wrong values.
Is there a way to change the coordinates of a polygon using the position number you get from STPointN()
? Maybe something like SET @geom.STPointN(i)=NewXValue
?
Or is there a better way to do this that I have missed?