Divide the coordinates into X and Y coordinates from a table having geometry data in SQL Server 2012

Question

I have a table with a column Shape of geometry datatype.

This is the data in 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))

and so on....

I need output as

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

and so on in table format

Please make sure that you provide *valid* polygon string literal. — Lukasz Szozda, Dec 29 '18 at 13:38

John Cappelletti · Accepted Answer · 2019-01-02T13:49:15.120

Here is an option which will work with 2012. Note that we maintain the sequence (RetSeq)

Example

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)

Returns

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

EDIT

Martin Smith's solution should really be the ACCEPTED answer. If you can't create a numbers table, you can use an ad-hoc tally table.

Example

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

Requested EDIT

;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

OR ... Notice the minus 1 in the TOP

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

@zicobarretto Happy to help, but see comment and example below the EDIT — John Cappelletti, Dec 30 '18 at 11:17
Can u plz help me in editing the above query... I want to display the top 4 results instead of 5 results or top 5 results instead of 6 results or top 6 results instead of 7 results and so on... — zico barretto, Jan 02 '19 at 07:23
In short I don't want to display the last row derived by this query — zico barretto, Jan 02 '19 at 09:40
In the second query Top (Shape.STNumPoints() - 1) ... notice the minus 1. — John Cappelletti, Jan 02 '19 at 13:42

score 3 · Answer 2 · answered Dec 29 '18 at 14:47

If you first create a Numbers table with sequential integers from 1 upwards and at least as many rows as the maximum number of points you will ever be dealing with then this is straightforward.

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()

Code to create and populate a Numbers table is below.

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

Son of a Gun! I've parsed my polygons in the past. There was no need!! +1 — John Cappelletti, Dec 29 '18 at 15:01

score 1 · Answer 3 · answered Dec 29 '18 at 13:35

Naive approach with parsing string(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);

Query:

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);

db<>fiddle demo

I am using sql 2012.... hence I wont be able to use string_split function... can u help me with this query in sql 2012. — zico barretto, Dec 29 '18 at 14:13
Getting error Msg 195 Level 15 State 10 Line 6 'TRANSLATE' is not a recognized built-in function name. Msg 102 Level 15 State 1 Line 9 Incorrect syntax near 's3'. — zico barretto, Dec 29 '18 at 14:14

LukStorms · Answer 4 · 2018-12-29T19:23:13.567

1

You can use the geometry methods in a CROSS APPLY to get those values.
Then get the X and Y from the points.

In the example below the numbers come from spt_values, but that's just 1 one of the methods to obtain a Tally table with numbers in a range.

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;

Test on db<>fiddle here

edited Dec 29 '18 at 19:23

answered Dec 29 '18 at 14:48

LukStorms

28,916
5
31
45

1

@MartinSmith Thanks for noticing that. That parse wasn't needed after all. – LukStorms Dec 29 '18 at 14:54
And this is the best approach - using proper geometry method instead of parsing text form :) As for tally table I would use `ROW_NUMBER` - you will never know if spt_values change in the future – Lukasz Szozda Dec 29 '18 at 18:48

Divide the coordinates into X and Y coordinates from a table having geometry data in SQL Server 2012

4 Answers4