Getting list of spatial points from polygon within query

Question

I have a database with various defined polygons which represent the outer boundarys of buildings on a map of a business park.

If I perform a Select within Management Studio, I get a result similar to the following:

LocationCode    LocationPolygon
1               POLYGON((1 1, 2 1, 2 2, 1 2, 1 1))
2               POLYGON((10 10, 20 10, 20 20, 10 20, 10 10))

What I would like to get is the following:

LocationCode    PointX    PointY
1               1         1
1               2         1
1               2         2
1               1         2
2               10        10
etc             etc       etc

I cannot see anywhere where I can extract the points from the Polygon using SQL Server from within a SQL Query? I can evidentally take the whole polygon and then do the rest on the client, but I would rather deal in SQL if possible.

Any help appreciated in pointing me in the right direction.

@jpw - Sorry for not getting back to you - I promise I'll give this a go tomorrow and let you know how it goes! — KingCronus, Feb 05 '15 at 17:13
No worries, like I said, I'm just curious to see if it worked for you. — jpw, Feb 06 '15 at 10:08

score 8 · Accepted Answer · edited May 23 '17 at 12:33

I've answered a similar question before and that time I used a user defined function to extract the points and return a table. Assuming a table Locations defined as: (LocationCode int, LocationPolygon geometry) then the following function:

CREATE FUNCTION dbo.GetPoints()
RETURNS @ret TABLE (LocationCode INT, PointX INT, PointY INT)
AS
BEGIN
    DECLARE @max INT
    SET @max = (SELECT MAX(LocationPolygon.STNumPoints()) FROM Locations) 

    ;WITH Sequence(Number) AS
    (
        SELECT 1 AS Number
        UNION ALL
        SELECT Number + 1
        FROM Sequence
        WHERE Number < @max
    )
    INSERT INTO @ret 
    SELECT
        l.LocationCode
        ,l.LocationPolygon.STPointN(nums.number).STX AS PointX
        ,l.LocationPolygon.STPointN(nums.number).STY AS PointY
    FROM Locations l, Sequence nums
    WHERE nums.number <= l.LocationPolygon.STNumPoints()
    RETURN
END;

When executed as SELECT DISTINCT * FROM dbo.GetPoints() ORDER BY LocationCode; will give the following result (using your sample data):

| LOCATIONCODE | POINTX | POINTY |
|--------------|--------|--------|
|            1 |      1 |      1 |
|            1 |      1 |      2 |
|            1 |      2 |      1 |
|            1 |      2 |      2 |
|            2 |     10 |     10 |
|            2 |     10 |     20 |
|            2 |     20 |     10 |
|            2 |     20 |     20 |

I'm sure the function can be improved, but it should give you some ideas on how this problem can be solved.

Sample SQL Fiddle

Add `OPTION (MAXRECURSION 0)` to prevent "The maximum recursion 100 has been exhausted before statement completion" error when the geometry has more than 100 points. — Catch22, Sep 28 '16 at 10:18

Charlieface · Answer 2 · 2023-03-26T06:02:57.733

A somewhat faster method than the other answer is to use an inline Table Valued Function, and instead of using a recursive CTE use either the new GENERATE_SERIES or a numbers table or a numbers function.

CREATE FUNCTION dbo.GetPoints(@geog geography)
RETURNS TABLE
AS RETURN

SELECT
  Point = @geom.STPointN(n.value)
FROM GENERATE_SERIES(1, @geog.STNumPoints()) n;

You can put this into your existing query using CROSS APPLY

A version for geometry would be similar, but you also need to break out each figure

CREATE FUNCTION dbo.GetPoints(@geom geography)
RETURNS TABLE
AS RETURN

SELECT
  Point = v.figure.STPointN(n.value)
FROM GENERATE_SERIES(1, @geom.STNumGeometries()) m
CROSS APPLY (VALUES (
    @geom.STGeometryN(m.value)
)) v(figure)
CROSS APPLY GENERATE_SERIES(1, v.figure.STNumPoints()) n;

Getting list of spatial points from polygon within query

2 Answers2