从几何类型生成带有X和Y的视图 [英] Generate view with X and Y from geometry type
本文介绍了从几何类型生成带有X和Y的视图的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
在sql-server -2012 我想生成包含几何类型所有点的视图.我怎样才能做到这一点? 查看示例
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 | Y2
2 | x1 | Y1
Oracle中带有sys.SDO的示例
Example in Oracle with sys.SDO
select c.ngeometrykey, z.*
from gis_map.mp_geometry c ,
table(sdo_util.getvertices(c.geometry)) z
推荐答案
我认为您不能在视图中执行此操作,但是可以创建一个表值用户定义函数(一个返回表的函数)来获取你想要什么.
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.
此示例使用的表定义为
CREATE TABLE GeoTable (GeomKey int, vector GEOMETRY)
存储不同的几何类型(在下面链接的示例中,我使用了POINT,MULTIPOINT,LINESTRING和POLYGON).
which stores different geometry types (in the example I linked below I used POINT, MULTIPOINT, LINESTRING and POLYGON).
CREATE FUNCTION dbo.GetVertices()
RETURNS @ret TABLE (GeomKey INT, X INT, Y INT, PointNo INT)
AS
BEGIN
DECLARE @max INT
SET @max = (SELECT MAX(vector.STNumPoints()) FROM GeoTable)
;WITH Sequence(Number) AS
(
SELECT 1 AS Number
UNION ALL
SELECT Number + 1
FROM Sequence
WHERE Number < @max
)
INSERT INTO @ret
SELECT
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()
RETURN
END;
有关完整的工作示例,请参见示例SQL Fiddle .
See this sample SQL Fiddle for a complete working example.
这篇关于从几何类型生成带有X和Y的视图的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文