0

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?

1 Answer 1

0

I've figured out a solution, its not pretty but it works.

DECLARE @splitValues TABLE(pos INT IDENTITY, value VARCHAR(MAX))

INSERT INTO @splitValues (value)
SELECT  value 
FROM    STRING_SPLIT(@geom.STAsText(), ',')

DECLARE @buildingTable TABLE([form] VARCHAR(MAX), OldCoords VARCHAR(MAX))

INSERT INTO @buildingTable ([form], OldCoords)
SELECT value, 
        substring(value, PatIndex('%[-0-9]%', value), len(value))
FROM    @splitValues s
        
DECLARE @buildingTable2 TABLE([form] VARCHAR(MAX), justCoords VARCHAR(MAX), NewX FLOAT, NewY FLOAT)

INSERT INTO @buildingTable2(form,justCoords,NewX,NewY)
SELECT  form, 
        replace(oldcoords,')',''),
        cast(substring(OldCoords,0,charindex(' ',substring(OldCoords, PatIndex('%[-0-9]%', OldCoords), len(OldCoords)))) as float)[email protected],
        cast(replace(substring(OldCoords,charindex(' ',substring(OldCoords, PatIndex('%[-0-9]%', OldCoords), len(OldCoords))), len(OldCoords)), ')','') as float)[email protected] 
FROM @buildingTable


DECLARE @finalBuildTable TABLE (points varchar(max))
INSERT INTO @finalBuildTable (points)
SELECT replace(form, justCoords, concat(cast(NewX as decimal(16,8)), ' ',cast(NewY as decimal(18,8))))

FROM @buildingTable2

DECLARE @NewGeomText varchar(max)

SELECT @NewGeomText = COALESCE(@NewGeomText+', '+points, points ) 
FROM @finalBuildTable

SELECT cast(@NewGeomText as geometry)

I could probably condense it a bit and use a few less declared tables.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.