Altering STEndPoint or STStartpoint of a line using SQL Spatial

Question

I have built SQL Spatial triggers so that if users of our GIS move a pit (point feature) the pipes (lines) that end or start at the pit have their endpoint/startpoint altered. I basically replace the entire geometry.

The problem is, some pipes have more than one point. In this case I only want to alter the endpoint or startpoint and leave the other points as is, otherwise the other vertices are lost. Can you set the STEndPoint/STStartPoint or is there another way to only alter these points of a line?

Below code is my attempt so far, after trying the recursive CTE which I gave up on as I couldn't reference a table variable inside this. Instead I have built a temp table which will store the x and y values. It would be good to not have to repeat the script 4 times but it works (pipes will have 2 to 4 points). I now need to alter the geometry x/y for just the pipe endpoints/startpoints after the user moves the pit. I Will look at this tomorrow.

--Create temp table to store X and Y values of pipe vertices
CREATE table #PipeGeom
(
    ID int IDENTITY(1,1) NOT NULL,
    N int,
    X float,
    Y float,
    PipeGMSCKey int 
)

--insert vertices. Most pipes have two points. A few have 4.
DECLARE @VertexCount INT
SET @VertexCount = 1    
INSERT INTO #PipeGeom (N, X, Y, PipeGMSCKey)
SELECT N=@VertexCount, pipe.Geometry_SPA.STPointN(1).STX AS X, pipe.Geometry_SPA.STPointN(1).STY AS Y, pipe.GMSC_Key FROM [Assets_GMSC_Dev].[dbo].[vw_Pipes] pipe
    INNER JOIN [Assets_GMSC_Dev].[dbo].[vw_Pits] pit ON pipe.Geometry_SPA.STDistance(pit.Geometry_SPA) <0.1
    WHERE pit.GMSC_Key = '1481532'
    --UNION ALL
INSERT INTO #PipeGeom (N, X, Y, PipeGMSCKey)    
SELECT N=2, pipe.Geometry_SPA.STPointN(2).STX AS X, pipe.Geometry_SPA.STPointN(2).STY AS Y, pipe.GMSC_Key FROM [Assets_GMSC_Dev].[dbo].[vw_Pipes] pipe
    INNER JOIN [Assets_GMSC_Dev].[dbo].[vw_Pits] pit ON pipe.Geometry_SPA.STDistance(pit.Geometry_SPA) <0.1
    WHERE pit.GMSC_Key = '1481532'
    --UNION ALL
INSERT INTO #PipeGeom (N, X, Y, PipeGMSCKey)    
SELECT N=3, pipe.Geometry_SPA.STPointN(3).STX AS X, pipe.Geometry_SPA.STPointN(3).STY AS Y, pipe.GMSC_Key FROM [Assets_GMSC_Dev].[dbo].[vw_Pipes] pipe
    INNER JOIN [Assets_GMSC_Dev].[dbo].[vw_Pits] pit ON pipe.Geometry_SPA.STDistance(pit.Geometry_SPA) <0.1
    WHERE pit.GMSC_Key = '1481532'
    --UNION ALL
INSERT INTO #PipeGeom (N, X, Y, PipeGMSCKey)    
SELECT N=4, pipe.Geometry_SPA.STPointN(4).STX AS X, pipe.Geometry_SPA.STPointN(4).STY AS Y, pipe.GMSC_Key FROM [Assets_GMSC_Dev].[dbo].[vw_Pipes] pipe
    INNER JOIN [Assets_GMSC_Dev].[dbo].[vw_Pits] pit ON pipe.Geometry_SPA.STDistance(pit.Geometry_SPA) <0.1
    WHERE pit.GMSC_Key = '1481532'

--User moves pit, then get new pit location, which pipe start/endpoint will need to move to.
--New Start and Finish points
DECLARE @PitX FLOAT = (SELECT Geometry_SPA.STX FROM vw_Pits WHERE GMSC_Key = '1481532');
DECLARE @PitY FLOAT = (SELECT Geometry_SPA.STY FROM vw_Pits WHERE GMSC_Key = '1481532');

--need to grab just the endpoint/startpoint of line here and build Geometry String

drop table #PipeGeom

Thanks

I can't figure out how to tag. I want to tag it with SQL Server 2008 but doesn't seem to be working. — user3882865, Feb 24 '16 at 22:04

score 0 · Answer 1 · answered Feb 25 '16 at 10:55

0

This might not be an optimum solution but hope it's of use. Also, I'm on 2014 /2016 so it's untested on 2008R2.

--Initial (Existing) Line
DECLARE @g GEOMETRY =  GEOMETRY::STGeomFromText('LINESTRING(5 5, 20 10, 30 20, 0 50)', 0);

--New Start and Finish points
DECLARE @X1 VARCHAR(2) = '10'
DECLARE @Y1 VARCHAR(2) = '10'
DECLARE @X2 VARCHAR(2) = '20'
DECLARE @Y2 VARCHAR(2) = '20'

DECLARE @Coords NVARCHAR(MAX)
;WITH Points(N, X, Y) AS  
( 
   SELECT 2,  @g.STPointN(2).STX, @g.STPointN(2).STY
   UNION ALL
   SELECT N + 1, @g.STPointN(N + 1).STX, @g.STPointN(N + 1).STY 
   FROM Points GP
   WHERE N < @g.STNumPoints() - 1 
)
SELECT @Coords = COALESCE(@Coords + ', ', '') + CAST(X AS NVARCHAR(50)) + ' ' + CAST(Y AS NVARCHAR(50)) FROM Points


SELECT  GEOMETRY::STGeomFromText('LINESTRING('+@X1+' '+@Y1+', '+@Coords+', '+@X2+' '+@Y2+' '+')', 0) NewGeom

This uses a recursive CTE to parse out the co-ords except the first and last for the Geometry @G into a Nvarchar. The new first and last points are concatenated and a new Geometry returned. You could probably wrap this up in a SP or function.

answered Feb 25 '16 at 10:55

Liesel

2,929
2
12
18

Thanks. Testing this out now. Instead of declaring one geometry at the top (@g), I have a variable table with the geometry of the pipes that intersect with the pit, as there will be more than one result. I want to reference the results from this table further down in Points(N, X, Y), where you have @g.STPoint....but I am unable to as the column name is ambiguous. Perhaps you can't reference a value in a variable table from another variable?? – user3882865 Feb 26 '16 at 02:33
Update the question with some example data and I'll try to alter the code to fit – Liesel Feb 27 '16 at 10:32
I can't seem to send code. I get the "Only one additional @user...." message. I've been trying for an hour. – user3882865 Feb 29 '16 at 22:39
Click edit under the question and add your example code/data there? – Liesel Mar 01 '16 at 01:40
I almost have this working, with temp tables holding the vertices of a line, where the last point or first point is the vertex of the moved point. One question, I have a variable holding the geometry string that concatentates the vertices (minus the last or first point), and the point vertex. But when using that variable to update the line geometry, SQL Server doesn't recognise it and says it needs at least two arguments. It works when I create the geometry with the underlying value in the variable. How can I get around this? SET Geometry_SPA = Geometry::STGeomFromText(@LINE) – user3882865 May 20 '16 at 06:53
Two args required to STGeomFromText?- the second is the srid, https://msdn.microsoft.com/en-us/library/bb933823.aspx In the example code I used 0 - `GEOMETRY::STGeomFromText(@Line, 0)` but you should probably use an appropriate one http://stackoverflow.com/questions/25188020/what-is-srid-0-for-geometry-columns – Liesel May 30 '16 at 10:23
Thanks Les. I got it working in the end. Quite a bit of SQL involved in my case but it has worked without fail yet...and I've tested and tested. A bit of declaring variables beforehand also takes it from 10 seconds to 1 second to run. – user3882865 Jul 25 '16 at 06:29

Altering STEndPoint or STStartpoint of a line using SQL Spatial

1 Answers1