Разделите координаты на координаты X и Y из таблицы, имеющей данные геометрии в SQL Server 2012
У меня есть таблица с колонкой Shape из geometry тип данных.
Это данные в Shape:
POLYGON ((565542.98375 2127263.4997410, 565538.48450 2127261.3187302, 565541.96658 2127254.1162, 565546.465835 2127256.297297, 565542.9837 2127263.49974102))
POLYGON ((565547.281621307 2127097.9410014, 565549.457915 2127093.43948425, 565553.577449391 2127084.9189882, 565568.882475 2127092.31709055, 565562.586805441 2127105.3404182, 565547.2816807 2127097.94105044))
и так далее....
Мне нужен вывод как
ID | X | Y
---+-----------------+-----------------
1 | 565542.98375 | 2127263.4997410
1 | 565538.48450 | 2127261.3187302
1 | 565541.96658 | 2127254.1162
1 | 565546.465835 | 2127256.297297
1 | 565542.9837 | 2127263.49974102
2 | 565547.281627 | 2127097.9410014
2 | 565549.457915 | 2127093.43948425
2 | 565553.5774391 | 2127084.9189882
и так далее в табличном формате
4 ответа
Решение
Вот вариант, который будет работать с 2012 года. Обратите внимание, что мы поддерживаем последовательность (RetSeq)
пример
SELECT A.ID
,B.RetSeq
,X = left(C.RetVal,charindex(' ',C.RetVal)-1)
,Y = substring(C.RetVal,charindex(' ',C.RetVal)+1,50)
FROM YourTable A
Cross Apply (
Select RetSeq = row_number() over (Order By 1/0)
,RetVal = B2.i.value('(./text())[1]', 'varchar(100)')
From (Select x = Cast('<x>' + replace(A.Shape.STAsText(),',','</x><x>')+'</x>' as xml)) as B1
Cross Apply x.nodes('x') AS B2(i)
) B
Cross Apply ( values (ltrim(rtrim(replace(replace(replace(B.RetVal,'POLYGON',''),'(',''),')','')))) ) C(RetVal)
Возвращает
ID RetSeq X Y
1 1 565542.98375 2127263.499741
1 2 565538.4845 2127261.3187302
1 3 565541.96658 2127254.1162
1 4 565546.465835 2127256.297297
1 5 565542.98375 2127263.499741
2 1 565547.281621307 2127097.9410014
2 2 565549.457915 2127093.43948425
2 3 565553.577449391 2127084.9189882
2 4 565568.882475 2127092.31709055
2 5 565562.586805441 2127105.3404182
2 6 565547.281621307 2127097.9410014
РЕДАКТИРОВАТЬ
РешениеМартина Смита должно быть действительно ПРИНЯТОМ. Если вы не можете создать таблицу чисел, вы можете использовать специальную таблицу.
пример
Select A.ID
,Seq = B.N
,X = Shape.STPointN(N).STX
,Y = Shape.STPointN(N).STY
From YourTable A
Cross Apply (Select Top (Shape.STNumPoints()) N=Row_Number() Over (Order By 1/0) From master..spt_values n1, master..spt_values n2 ) B
Запрошенная РЕДАКТИРОВАТЬ
;with cte as (
SELECT A.ID
,B.RetSeq
,X = left(C.RetVal,charindex(' ',C.RetVal)-1)
,Y = substring(C.RetVal,charindex(' ',C.RetVal)+1,50)
,Cnt = max(B.RetSeq) over (Partition by A.ID)
FROM YourTable A
Cross Apply (
Select RetSeq = row_number() over (Order By 1/0)
,RetVal = B2.i.value('(./text())[1]', 'varchar(100)')
From (Select x = Cast('<x>' + replace(A.Shape.STAsText(),',','</x><x>')+'</x>' as xml)) as B1
Cross Apply x.nodes('x') AS B2(i)
) B
Cross Apply ( values (ltrim(rtrim(replace(replace(replace(B.RetVal,'POLYGON',''),'(',''),')','')))) ) C(RetVal)
)
Select *
From cte
Where RetSeq<Cnt
Order By ID,RetSeq
ИЛИ... Обратите внимание на минус 1 в ТОПе
Select A.ID
,Seq = B.N
,X = Shape.STPointN(N).STX
,Y = Shape.STPointN(N).STY
From YourTable A
Cross Apply (Select Top (Shape.STNumPoints() - 1) N=Row_Number() Over (Order By 1/0) From master..spt_values n1, master..spt_values n2 ) B
Если вы сначала создадите таблицу чисел с последовательными целыми числами из 1 вверх и, по крайней мере, столько строк, сколько максимальное количество очков, с которыми вы когда-либо будете иметь дело, - это просто.
SELECT S.id,
X = S.GeomCol1.STPointN(N.number).STX,
Y = S.GeomCol1.STPointN(N.number).STY
FROM SpatialTable S
JOIN Numbers N
ON N.number <= S.GeomCol1.STNumPoints()
Код для создания и заполнения таблицы чисел приведен ниже.
CREATE TABLE dbo.Numbers(Number INT PRIMARY KEY);
WITH E1(N) AS
(
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
) -- 1*10^1 or 10 rows
, E2(N) AS (SELECT 1 FROM E1 a, E1 b) -- 1*10^2 or 100 rows
, E4(N) AS (SELECT 1 FROM E2 a, E2 b) -- 1*10^4 or 10,000 rows
, E8(N) AS (SELECT 1 FROM E4 a, E4 b) -- 1*10^8 or 100,000,000 rows
, Nums AS (SELECT TOP (10000000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N FROM E8)
INSERT INTO dbo.Numbers
SELECT N
FROM Nums
Вы можете использовать методы геометрии в CROSS APPLY, чтобы получить эти значения.
Затем получите X и Y из точек.
В приведенном ниже примере числа получены из spt_values, но это всего лишь один из методов получения таблицы Tally с числами в диапазоне.
WITH NUMS AS
(
SELECT DISTINCT number as n
FROM master..[spt_values]
WHERE number between 1 and 128
)
SELECT ID, GeoPoint.STX AS X, GeoPoint.STY AS Y
FROM Shapes s
CROSS APPLY
(
SELECT n as PointN, Shape.STPointN(n) AS GeoPoint
FROM NUMS
WHERE n BETWEEN 1 AND Shape.STNumPoints()
) ca;
Тест на дБ<> скрипка здесь
Наивный подход со строкой синтаксического анализа (SQL Server 2017):
CREATE TABLE SpatialTable
( id int IDENTITY (1,1),
GeomCol1 geometry );
INSERT INTO SpatialTable
SELECT geometry::STGeomFromText(
'POLYGON ((565542.98375 2127263.4997410, 565538.48450 2127261.3187302, 565541.96658 2127254.1162, 565546.465835 2127256.297297, 565542.98375 2127263.4997410))',0)
UNION ALL
SELECT geometry::STGeomFromText('POLYGON ((565547.281621307 2127097.9410014, 565549.457915 2127093.43948425, 565553.577449391 2127084.9189882, 565568.882475 2127092.31709055, 565562.586805441 2127105.3404182, 565547.281621307 2127097.9410014))',0);
Запрос:
SELECT ID, s1.rn, s3.x, s3.y, GeomCol1
FROM SpatialTable s
CROSS APPLY (SELECT value, ROW_NUMBER() OVER(ORDER BY 1/0) AS rn
FROM STRING_SPLIT(s.GeomCol1.STAsText() ,',')) s1
CROSS APPLY (SELECT TRIM(TRANSLATE(value, 'POLYGON()', ' '))) s2(r)
CROSS APPLY (SELECT TRY_CAST(LEFT(s2.r, CHARINDEX(' ',s2.r)) AS DECIMAL(18,6)),
TRY_CAST(RIGHT(s2.r,LEN(s2.r)-CHARINDEX(' ',s2.r)) AS DECIMAL(18,6))
) s3(x,y);