如何保留计算的 GEOMETRY 或 GEOGRAPHY 列

发布于 2024-07-08 12:02:34 字数 703 浏览 8 评论 0原文

我正在尝试在 SQL Server 2008 下创建一个表，其中包含 GEOMETRY 列及其计算的变体。

考虑下表，其中计算列返回缓冲几何图形：

CREATE TABLE [dbo].[Test] (
    [Geometry]      GEOMETRY    NOT NULL,
    [Buffer]        FLOAT       NOT NULL,
    [BufferedGeometry] AS ([Geometry].STBuffer([Buffer])) PERSISTED
);

这样做的问题是它会导致以下错误：

消息 4994，级别 16，状态 1，第 2 行计算列“BufferedGeometry” 表“测试”无法持久化因为列类型“几何”，是非字节顺序的 CLR 类型。

我搜索了 BOL 和网络，似乎找不到解决我的问题的方法。我真的希望它能够被持久化，这样我就可以有效地对其进行索引。我可以在代码中设置它，但随后我可能会出现数据不一致的情况，因为我在某个时间点需要这两个值。

有人玩过这个并知道解决方案或解决方法吗？

更新：Microsoft 在 SQL Server 2012 中添加了此功能。

原文

I'm trying to create a table under SQL Server 2008 containing a GEOMETRY column and a calculated variation thereof.

Considering the following table where the calculated column returns a buffered geometry:

CREATE TABLE [dbo].[Test] (
    [Geometry]      GEOMETRY    NOT NULL,
    [Buffer]        FLOAT       NOT NULL,
    [BufferedGeometry] AS ([Geometry].STBuffer([Buffer])) PERSISTED
);

The problem with this is it results in the following error:

Msg 4994, Level 16, State 1, Line 2
Computed column 'BufferedGeometry' in
table 'Test' cannot be persisted
because the column type, 'geometry',
is a non-byte-ordered CLR type.

I have search BOL and the web and can't seem to find a solution to my problem. I really would like it to be persisted so I can index it effectively. I could set it in code, but then I have the possibility of inconsistent data as I require both values at some point in time.

Anyone played with this and know a solution or workaround?

Update: Microsoft has added this functionality in SQL Server 2012.

分享到QQ

分享到微博