可以使用SqlGeography和Linq to Sql一起吗？

Question

我在使用Microsoft.SqlServer.Types.SqlGeography时遇到了很多问题。我非常清楚Linq to Sql中对其的支持并不好。我尝试了很多方法，从预期的方式开始（数据库类型为geography，CLR类型为SqlGeography）。这会产生NotSupportedException异常，这在博客上得到了广泛讨论。

然后我将geography列视为varbinary(max)类型，因为geography是以二进制形式存储的用户定义类型。这似乎可以正常工作（使用一些二进制读写扩展方法）。

然而，现在我遇到了一个相当晦涩的问题，似乎并没有发生在很多其他人身上。

System.InvalidCastException: 无法将类型为“Microsoft.SqlServer.Types.SqlGeography”的对象强制转换为类型“System.Byte[]”。

这个错误是在查询迭代时从ObjectMaterializer中抛出的。它似乎仅在隐式包含包含geography列的表的查询中发生（即使用EntityRef<>属性进行连接）。

System.Data.Linq.SqlClient.ObjectReaderCompiler.ObjectReader`2.MoveNext()

我的问题是：如果我将geography列检索为varbinary(max)，则可能会出现相反的错误：无法将byte[]强制转换为SqlGeography。那我能理解。但这个错误我就不理解了。我在部分LINQ to SQL类上有一些隐藏二进制转换的属性...这可能是问题吗？

感谢任何帮助，我知道可能没有足够的信息。

附加内容：

• 在Visual Studio dbml设计器中具有“服务器数据类型”= geography的geography列会生成此错误：指定的类型“geography”不是有效的提供程序类型。

在 Visual Studio dbml Designer 中，如果“地理”列没有选择“服务器数据类型”，则会生成此错误：无法将“值”节点格式化为 SQL 的执行形式。

- cofiem

Answer 1

如果您只想追踪点并利用 SQL Server 2008 的空间索引，则可以像其他人指出的那样，从 Linq to SQL 中隐藏空间数据列，并使用 UDF 或存储过程。假设您有一个包括纬度和经度字段的 AddressFields 表。将该表添加到 DBML 文件中，并编写任何设置纬度和经度字段的代码。然后，下面的 SQL 代码将在该表中添加一个 Geo geogarphy 字段，并在数据库中创建一个触发器，根据纬度和经度字段自动设置 Geo 字段。同时，下面的代码还创建了其他有用的 UDF 和存储过程：DistanceBetween2（我已经有了 DistanceBetween）返回地址字段表示的地址与指定的纬度/经度对之间的距离；DistanceWithin 返回指定英里距离内所有 AddressFields 的各种字段；UDFDistanceWithin 做同样的事情作为用户定义函数（如果您想将其嵌入到更大的查询中很有用）；而 UDFNearestNeighbors 返回对应于特定点附近的邻居数量的 AddressField 中的字段。（使用 UDFNearestNeighbors 的原因之一是，如果您只通过调用 DistanceBetween2 进行排序，SQL Server 2008 不会优化其使用的空间索引。）

您需要通过更改 AddressFields 到您的表并自定义要返回的来自该表的字段（查看围绕 AddressFieldID 引用的代码）来自定义此内容。然后，您可以在数据库上运行此内容，并将生成的存储过程和 UDF 复制到您的 DBML 中，然后您可以在查询中使用它们。总体而言，这使您可以相当轻松地利用点的空间索引。

-----------------------------------------------------------------------------------------

--[1]

--INITIAL AUDIT
select * from dbo.AddressFields
GO
--ADD COLUMN GEO
IF EXISTS (SELECT name FROM sysindexes WHERE name = 'SIndx_AddressFields_geo')
DROP INDEX SIndx_AddressFields_geo ON AddressFields
GO
IF EXISTS (SELECT b.name FROM sysobjects a, syscolumns b 
            WHERE a.id = b.id and a.name = 'AddressFields' and b.name ='Geo' and a.type ='U' )  
ALTER TABLE AddressFields DROP COLUMN Geo

GO
alter table AddressFields add Geo geography

Sorry，我只能使用英语进行回答。

--SET GEO VALUE
GO
UPDATE AddressFields
SET Geo = geography::STPointFromText('POINT(' + CAST([Longitude] AS VARCHAR(20)) + ' ' + 
                    CAST([Latitude] AS VARCHAR(20)) + ')', 4326)

---

--[3] 创建索引

IF EXISTS (SELECT name FROM sysindexes WHERE name = 'SIndx_AddressFields_geo')
DROP INDEX SIndx_AddressFields_geo ON AddressFields

GO

CREATE SPATIAL INDEX SIndx_AddressFields_geo 
   ON AddressFields(geo)

--UPDATE STATS
UPDATE STATISTICS AddressFields

--AUDIT
GO
select * from dbo.AddressFields

--[4] 创建存储过程 USP_SET_GEO_VALUE，参数1为纬度，参数2为经度

IF EXISTS (SELECT name FROM sysobjects  WHERE name = 'USPSetGEOValue' AND type = 'P')
    DROP PROC USPSetGEOValue
GO

GO
CREATE PROC USPSetGEOValue @latitude decimal(18,8), @longitude decimal(18,8)
AS
    UPDATE AddressFields
    SET Geo = geography::STPointFromText('POINT(' + CAST(@longitude AS VARCHAR(20)) + ' ' + 
                    CAST(@latitude AS VARCHAR(20)) + ')', 4326)
    WHERE [Longitude] =@longitude and [Latitude] = @latitude

GO
--TEST
EXEC USPSetGEOValue 38.87350500,-76.97627500

GO

-- [5] 创建触发器以在经纬度值更改/插入时设置地理编码

IF EXISTS (SELECT name FROM sysobjects  WHERE name = 'TRGSetGEOCode' AND type = 'TR')
DROP TRIGGER TRGSetGEOCode

GO

CREATE TRIGGER TRGSetGEOCode 
ON AddressFields
AFTER INSERT,UPDATE
AS
    DECLARE @latitude decimal(18,8), @longitude decimal(18,8)

    IF ( UPDATE (Latitude) OR UPDATE (Longitude) )
        BEGIN

            SELECT @latitude = latitude ,@longitude = longitude from inserted

            UPDATE AddressFields
            SET Geo = geography::STPointFromText('POINT(' + CAST(@longitude AS VARCHAR(20)) + ' ' + 
                        CAST(@latitude AS VARCHAR(20)) + ')', 4326)
            WHERE [Longitude] =@longitude and [Latitude] = @latitude
        END 
    ELSE
        BEGIN
            SELECT @latitude = latitude ,@longitude = longitude from inserted

            UPDATE AddressFields
            SET Geo = geography::STPointFromText('POINT(' + CAST(@longitude AS VARCHAR(20)) + ' ' + 
                        CAST(@latitude AS VARCHAR(20)) + ')', 4326)
            WHERE [Longitude] =@longitude and [Latitude] = @latitude
        END 
GO

---

--[6] 创建过程USP_SET_GEO_VALUE_INITIAL_LOAD ----> 仅运行一次

IF EXISTS (SELECT name FROM sysobjects  WHERE name = 'USPSetAllGeo' AND type = 'P')
    DROP PROC USPSetAllGeo
GO

CREATE PROC USPSetAllGeo
AS
UPDATE AddressFields
SET Geo = geography::STPointFromText('POINT(' + CAST([Longitude] AS VARCHAR(20)) + ' ' + 
                    CAST([Latitude] AS VARCHAR(20)) + ')', 4326)

GO

---

--[7] EXISTING PROC DistanceBetween，返回由纬度/经度坐标对指定的两个点之间的距离。

--通过纬度/经度坐标对。--ALTER PROC DistanceBetween2

IF EXISTS (SELECT name FROM sysobjects  WHERE name = 'DistanceBetween2' AND type = 'FN')
DROP FUNCTION DistanceBetween2

GO

CREATE FUNCTION [dbo].[DistanceBetween2] 
(@AddressFieldID as int, @Lat1 as real,@Long1 as real)
RETURNS real
AS
BEGIN

    DECLARE @KMperNM float = 1.0/1.852;

    DECLARE @nwi geography =(select geo from addressfields where AddressFieldID  = @AddressFieldID)

    DECLARE @edi geography = geography::STPointFromText('POINT(' + CAST(@Long1 AS VARCHAR(20)) + ' ' + 
                                CAST(@Lat1 AS VARCHAR(20)) + ')', 4326)

    DECLARE @dDistance as real = (SELECT (@nwi.STDistance(@edi)/1000.0) * @KMperNM)

    return (@dDistance);  

END

GO --测试

DistanceBetween2 12159,40.75889600,-73.99228900

---

--[8] 创建存储过程 USPDistanceWithin

-- 返回 AddressFields 表中的地址列表

如果存在 (SELECT name FROM sysobjects WHERE name = 'USPDistanceWithin' AND type = 'P') 删除存储过程 USPDistanceWithin

GO

CREATE PROCEDURE [dbo].USPDistanceWithin 
(@lat as real,@long as real, @distance as float)
AS
BEGIN

    DECLARE @edi geography = geography::STPointFromText('POINT(' + CAST(@Long AS VARCHAR(20)) + ' ' + 
                                CAST(@Lat AS VARCHAR(20)) + ')', 4326)

    SET @distance = @distance * 1609.344 -- convert distance into meter

    select 
         AddressFieldID
        ,FieldID
        ,AddressString
        ,Latitude
        ,Longitude
        ,LastGeocode
        ,Status
        --,Geo
    from 
        AddressFields a WITH(INDEX(SIndx_AddressFields_geo))
    where 
        a.geo.STDistance(@edi) < = @Distance 

END

GO

--测试

--在3英里内 USPDistanceWithin 38.90606200,-76.92943500,3 GO --在5英里内 USPDistanceWithin 38.90606200,-76.92943500,5 GO --在10英里内 USPDistanceWithin 38.90606200,-76.92943500,10

---

--[9] 创建函数FNDistanceWithin

--返回AddressFields表中的地址列表

IF EXISTS (SELECT name FROM sysobjects WHERE name = 'UDFDistanceWithin' AND type = 'TF') DROP FUNCTION UDFDistanceWithin

GO

CREATE FUNCTION UDFDistanceWithin 
(@lat as real,@long as real, @distance as real)
RETURNS @AddressIdsToReturn TABLE 
    (
         AddressFieldID INT
        ,FieldID INT
    )
AS
BEGIN

    DECLARE @edi geography = geography::STPointFromText('POINT(' + CAST(@Long AS VARCHAR(20)) + ' ' + 
                                CAST(@Lat AS VARCHAR(20)) + ')', 4326)

    SET @distance = @distance * 1609.344 -- convert distance into meter

    INSERT INTO @AddressIdsToReturn
    select 
         AddressFieldID
        ,FieldID
    from 
        AddressFields a WITH(INDEX(SIndx_AddressFields_geo))
    where 
        a.geo.STDistance(@edi) < = @Distance 

    RETURN 

END

GO

--测试

--距离3英里内 select * from UDFDistanceWithin(38.90606200,-76.92943500,3) GO --距离5英里内 select * from UDFDistanceWithin( 38.90606200,-76.92943500,5) GO --距离10英里内 select * from UDFDistanceWithin( 38.90606200,-76.92943500,10)

---

--[9] 创建函数UDFNearestNeighbors

-- 返回AddressFields表中地址列表

IF EXISTS (SELECT name FROM sysobjects WHERE name = 'UDFNearestNeighbors' AND type = 'TF') DROP FUNCTION UDFNearestNeighbors

GO

如果存在(SELECT name FROM sysobjects WHERE name = 'numbers' AND xtype = 'u')，则删除表 numbers。

GO
-- First, create a Numbers table that we will use below.
SELECT TOP 100000 IDENTITY(int,1,1) AS n INTO numbers FROM MASTER..spt_values a, MASTER..spt_values b CREATE UNIQUE CLUSTERED INDEX idx_1 ON numbers(n)

GO

CREATE FUNCTION UDFNearestNeighbors 
(@lat as real,@long as real, @neighbors as int)
RETURNS @AddressIdsToReturn TABLE 
    (
         AddressFieldID INT
        ,FieldID INT
    )
AS
BEGIN

    DECLARE @edi geography = geography::STPointFromText('POINT(' + CAST(@Long AS VARCHAR(20)) + ' ' + 
                                CAST(@Lat AS VARCHAR(20)) + ')', 4326)
    DECLARE @start FLOAT = 1000;

    WITH NearestPoints AS

    (

      SELECT TOP(@neighbors) WITH TIES *,  AddressFields.geo.STDistance(@edi) AS dist

      FROM Numbers JOIN AddressFields WITH(INDEX(SIndx_AddressFields_geo)) 

      ON AddressFields.geo.STDistance(@edi) < @start*POWER(2,Numbers.n)

      ORDER BY n

    )


    INSERT INTO @AddressIdsToReturn

    SELECT TOP(@neighbors)
         AddressFieldID
        ,FieldID
    FROM NearestPoints
    ORDER BY n DESC, dist

    RETURN 

END

GO

--测试

--50个邻居 select * from UDFNearestNeighbors(38.90606200,-76.92943500,50) GO --200个邻居 select * from UDFNearestNeighbors( 38.90606200,-76.92943500,200) GO

- mbabramo

Answer 2

Linq to SQL不支持空间类型。支持程度并不是“不太好”，而是根本不支持。

你可以将它们视为BLOBs进行读取，但无法通过简单更改Linq to SQL中的列类型来实现。你需要在数据库级别上修改查询，以将该列返回为varbinary，使用CAST语句。你可以通过添加计算的varbinary列来在表级别上执行此操作，Linq会很高兴地将其映射到byte[]。

换句话说，DDL可能是这样的:

ALTER TABLE FooTable
ADD LocationData AS CAST(Location AS varbinary(max))

然后，从你的 Linq to SQL 类中删除 Location 列，改用 LocationData。

如果需要访问实际的 SqlGeography 实例，则需要使用 STGeomFromWKB 和 STAsBinary 将其转换为和从字节数组。

你可以通过扩展部分的 Linq to SQL 实体类并添加自动转换属性来使这个过程更加“自动化”：

public partial class Foo
{
    public SqlGeography Location
    {
        get { return SqlGeography.STGeomFromWKB(LocationData, 4326); }
        set { LocationData = value.STAsBinary(); }
    }
}

假设 LocationData 是计算出的 varbinary 列的名称；在 Linq to SQL 定义中不要包含“真实”的 Location 列，而是通过以上的临时方式添加它。

还要注意，你不能够对这个列进行太多的操作，除了读和写之外；如果你尝试对它进行查询（即在 Where 谓词中包括它），那么你将得到类似的 NotSupportedException。

- Aaronaught

---

刚刚完成了按照这种方式实现它的方法。感谢清晰的解释。 - cofiem

我该如何在 SQL Server 上使用 LocationData 列进行查询？我需要将其转换回“地理”列吗？ - sabbour

@sabbour：你不能直接使用Linq to SQL实现这个功能。你需要编写UDFs或存储过程来完成。 - Aaronaught

我能否将“Foo”写回数据库？MSDN表示：“计算列不能成为INSERT或UPDATE语句的目标”。http://msdn.microsoft.com/en-us/library/ms174979.aspx - russau

我刚试着实现了这个，但是没有成功 :( 有其他方法来添加/查询地理数据吗？ - Nikola Sivkov

@Aviatrix，关于“没有起作用”的短语，您需要更具体一些，并且可能需要提出一个新问题。 - Aaronaught