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 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy