December 9, 2016 at 10:08 am
Hi folks,
I'm trying to generate the various points in a linestring using a recursive cte. For those not familiar with this,
this is a simplified view of the sample data for the source table (displaying a row specific to one asset)
ASSET_NUM NUMOFPOINTSSHAPE
978265450x5D68000001070D000000........
The shape value here actually represents 5 points in a line and I'm using the [SHAPE].STPointN().STAsText() function to generate each of the 5 points. So I need to iterate 5 times through this row to generate 5 rows, one representing each point, something like below..
[SHAPE].STPointN(1).STAsText()
[SHAPE].STPointN(2).STAsText()
.........
.........
[SHAPE].STPointN(5).STAsText()
When I do this, I'm running into an infinite loop with the second point repeating infinitely. My code is posted below, would appreciate any help on this, thanks!
Declare @Counter int = 1;
with cte as (
SELECT @Counter as c,ASSET_NUM,[SHAPE].STPointN(@Counter).STAsText() as Points
FROM [dbo].[GEOMETRY_TABLE]
where ASSET_NUM = 9782654
union all
SELECT @Counter+1,t.ASSET_NUM,t.[SHAPE].STPointN(@Counter+1).STAsText()
FROM cte inner join [dbo].[GEOMETRY_TABLE] t
on cte.ASSET_NUM = t.ASSET_NUM and t.ASSET_NUM= 9782654
where @Counter < 5
)
Select * from cte option (maxrecursion 0)
Expected results
CASSET_NUM POINTS
19782654POINT (554179.5433 4488190.8469499999)
29782654POINT (563279.5433 4598180.8469999991)
39782654POINT (574279.5433 4688180.8469999991)
49782654POINT (582279.5433 4888180.8469999991)
59782654POINT (592279.5433 4988180.8469999991)
Actual results
CASSET_NUM POINTS
19782654POINT (554179.5433 4488190.8469499999)
29782654POINT (563279.5433 4598180.8469999991)
29782654POINT (563279.5433 4598180.8469999991)
29782654POINT (563279.5433 4598180.8469999991)
29782654POINT (563279.5433 4598180.8469999991)
29782654POINT (563279.5433 4598180.8469999991)
29782654POINT (563279.5433 4598180.8469999991)
29782654POINT (563279.5433 4598180.8469999991)
2
2
December 9, 2016 at 11:00 am
Offhand, I'm thinking the problem is the use of the @Counter variable in the recursive part of the CTE query. Maybe something like this instead:
with cte as (
SELECT @Counter as c,ASSET_NUM,[SHAPE].STPointN(@Counter).STAsText() as Points
FROM [dbo].[GEOMETRY_TABLE]
where ASSET_NUM = 9782654
union all
SELECT cte.C+1,t.ASSET_NUM,t.[SHAPE].STPointN(cte.C+1).STAsText()
FROM cte inner join [dbo].[GEOMETRY_TABLE] t
on cte.ASSET_NUM = t.ASSET_NUM and t.ASSET_NUM= 9782654
where cte.C < 5
)
Or avoid recursion altogether and use a tally table to pass into STPointN. If you don't have a tally table, you could improvise a small one on the fly here like:
WITH Tally AS
(SELECT N FROM (VALUES (1),(2),(3),(4),(5)) AS list (N))
SELECT t.N AS c, g.ASSET_NUM, g.[SHAPE].STPointN(t.N).STAsText() AS Points
FROM [dbo].[GEOMETRY_TABLE] g
CROSS JOIN Tally t
WHERE g.ASSET_NUM = 9782654
December 9, 2016 at 2:01 pm
bucketlist50 (12/9/2016)
Declare @Counter int = 1;
with cte as (
SELECT @Counter as c,ASSET_NUM,[SHAPE].STPointN(@Counter).STAsText() as Points
FROM [dbo].[GEOMETRY_TABLE]
where ASSET_NUM = 9782654
union all
SELECT @Counter+1,t.ASSET_NUM,t.[SHAPE].STPointN(@Counter+1).STAsText()
FROM cte inner join [dbo].[GEOMETRY_TABLE] t
on cte.ASSET_NUM = t.ASSET_NUM and t.ASSET_NUM= 9782654
where @Counter < 5
)
Select * from cte option (maxrecursion 0)
You aren't changing the value of the counter. You are doing a static expression of 1+1. I don't know how your output is incrementing column C, but you are definitely not assigning a new value to the @Counter expression.
Until you get your statement working with 5 points, you should probably not include "maxrecursion 0". That is why your get an infinite loop rather than a loop maxing at 100. Maxrecursion 0 can cause server performance and stability (e.g. crash) problems.
Wes
(A solid design is always preferable to a creative workaround)
December 12, 2016 at 9:44 am
Thanks, both of you were spot on. It was the problem with the counter not incrementing. After I replaced @Counter +1 with cte.c+1 in the recursive query, it worked correctly
December 12, 2016 at 10:54 am
bucketlist50 (12/12/2016)
Thanks, both of you were spot on. It was the problem with the counter not incrementing. After I replaced @Counter +1 with cte.c+1 in the recursive query, it worked correctly
I'd strongly suggest that you try the tally table approach. It will have a better performance than a recursive cte.
To learn more about tally tables, check the following:
http://dwaincsql.com/2014/03/27/tally-tables-in-t-sql/
http://www.sqlservercentral.com/articles/T-SQL/62867/
Here's also an article on why you should avoid recursive ctes to count.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply