Generate View With X And Y From Geometry Type

In sql-server-2012 I want to generate view with all points from geometry type. How can I do this? View example GeomKey | X | Y --------+----+----- 1 | X1 | Y1 1 | x2 |

Solution 1:

I don't think you can do this in a view but you can create a table-valued user defined function (a function that returns a table) to get what you want.

This example uses a table defined as

CREATETABLE GeoTable (GeomKey int, vector GEOMETRY)

which stores different geometry types (in the example I linked below I used POINT, MULTIPOINT, LINESTRING and POLYGON).

CREATEFUNCTION dbo.GetVertices()
RETURNS@retTABLE (GeomKey INT, X INT, Y INT, PointNo INT)
ASBEGINDECLARE@maxINTSET@max= (SELECTMAX(vector.STNumPoints()) FROM GeoTable) 

    ;WITH Sequence(Number) AS
    (
        SELECT1AS Number
        UNIONALLSELECT Number +1FROM Sequence
        WHERE Number <@max
    )
    INSERTINTO@retSELECT
        gt.GeomKey
        ,gt.vector.STPointN(nums.number).STX AS X
        ,gt.vector.STPointN(nums.number).STY AS Y
        ,nums.number AS PointNo
    FROM GeoTable gt, Sequence nums
    WHERE nums.number <= gt.vector.STNumPoints()
    RETURNEND;

See this sample SQL Fiddle for a complete working example.