sql server recursive query for list of points using

ulmd4ohb 于 2023-03-22 发布在 SQL Server

关注(0)|答案(2)|浏览(141)

I need the 3D points of a polyline object in SQL Server. I was told to check recursive query to achiev it and tried to adapt what I found so far to my case below.

I'm not sure how to write it correctly. The errore message says that either column shape or the applied aggregate 'shape.STPointN' wasn't found. SQL Server Management Studio underlines 'shape.STPointN' in line 11 of the code block.

Thanks!

The result I need is: number, point 0, POINT(X0 Y0 Z0) 1, POINT(X1 Y1 Z1) etc.

with cte_coords (n, point)
as (
    select 1 
          ,shape.STPointN(1).ToString() as point
    from [ourArcGISEnterpriseServer].[Schema].[myTableWithPolylines]
    where OBJECTID = 316101

    union all

    select n + 1
          ,shape.STPointN(n+1).ToString() as point
    from cte_coords
    where n < 130
)
select n, point
from cte_coords;

sql-server

来源：https://stackoverflow.com/questions/75799850/sql-server-recursive-query-for-list-of-points-using

2条答案

按热度按时间

zvokhttg1#

An alternative solution is to use a series function or similar to create the points, it's probably better than recursive CTE:

select  shape.STPointN(y.counter)
,   *
from yourGeoTable g
cross apply (
    select top 130 row_number() over(order by @@spid) AS counter
    from sys.objects so
    cross join sys.objects so2
    cross join sys.columns sc
    ) y
WHERE   y.counter <= shape.STNumPoints()

I use a quick and dirty select from some system tables to generate 130 rows with 1,2,3,4,5,... as counter, and then fetch each counter point. If you use SQL Server 2022 you can also use GENERATE_SERIES function

赞(0）回复(0）举报 2023-03-22

ilmyapht2#

Just to provide the less sophisticated but still working solution from my initial question compared to the more professional solution of @siggemannen. Since there is a default limit of recursion depth, option(maxrecursion n) needs to be adjusted as well.

WITH cte_coords (n, shape, point)
AS (
    SELECT 1
          ,shape
          ,shape.STPointN(1).ToString() as point
    from [ourArcGISEnterpriseServer].[Schema].[myTableWithPolylines]
    where OBJECTID = 316101
    UNION ALL
    SELECT n + 1
          ,shape
          ,shape.STPointN(n+1).ToString() as point
    FROM cte_coords
    WHERE n < 201
)
SELECT n
      ,replace(replace(point, 'POINT (', ''), ')','')
FROM cte_coords
order by n
option (maxrecursion 1000)

赞(0）回复(0）举报 2023-03-22

我来回答

sql server recursive query for list of points using

2条答案

相关问题

热门标签

最新问答