是否可以将 SqlGeography 与 Linq to Sql 一起使用？

Question

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

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

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

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

迭代查询时，ObjectMaterializer 会引发此错误。它似乎仅当包含地理列的表隐式包含在查询中时才会发生（即使用 EntityRef<> 属性进行联接）。

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

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

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

附加：

• Visual Studio dbml 设计器中“服务器数据类型”=“地理”的“地理”列生成此错误：指定的类型“地理”不是有效的提供程序类型。
• Visual Studio dbml 设计器中没有“服务器数据类型”的 geography 列会生成此错误：无法格式化节点“Value”以作为 SQL 执行。< /代码>

Answer 1

如果您只想使用 SqlGeography 跟踪点并利用 SQL Server 2008 的空间索引，那么您可以像其他人指出的那样，从 Linq to SQL 隐藏空间数据列并使用 UDF 或存储过程。假设您有一个包含纬度和经度字段的表 AddressFields。将该表添加到您的 DBML 文件中，并编写您想要设置纬度和经度字段的任何代码。然后，下面的 SQL 代码将向该表添加一个 Geo geogarphy 字段，并在数据库中创建一个触发器，根据纬度和经度字段自动设置 Geo 字段。同时，下面的代码还创建了其他有用的 UDF 和存储过程： DistanceBetween2（我已经有一个 DistanceBetween）返回 AddressField 中表示的地址与指定的纬度/经度对之间的距离； DistanceWithin 返回指定英里距离内所有地址字段的各个字段； UDFDistanceWithin 的作用与用户定义的函数相同（如果您想将其嵌入到更大的查询中，则很有用）； UDFNearestNeighbors 从 AddressField 返回与最接近特定点的指定数量的邻居相对应的字段。 （使用 UDFNearestNeighbors 的原因之一是，如果您只是通过调用 DistanceBetween2 来调用 order，则 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

---

--[2]

--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 PARA 1 LATITUDE 2 LONGITUDE

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] 创建纬度/经度值更改/插入触发器 ---> SET GEOCODE

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] CREATE PROC 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] 现有过程 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
--TEST

DistanceBetween2 12159,40.75889600,-73.99228900

---

--[8] 创建过程 USPDistanceWithin

-- 如果存在，则返回 AddressFields 表中的地址列表

（从 sysobjects 中选择名称，其中名称 = 'USPDistanceWithin' AND 类型 = '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

--TEST

--3 英里内
USP距离38.90606200内，-76.92943500,3内
去
--5英里以内
USP距离38.90606200内，-76.92943500,5内
去
--10英里以内
USPDistanceWithin 38.90606200,-76.92943500,10

---

--[9] 创建函数 FNDistanceWithin

--

如果存在则从 AddressFields 表返回地址列表（从 sysobjects 中选择名称，其中名称 = 'UDFDistanceWithin' AND 类型 = '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

--TEST

--3 英里内
从 UDFDistanceWithin(38.90606200,-76.92943500,3) 中选择 *
去
--5英里以内
从 UDFDistanceWithin( 38.90606200,-76.92943500,5) 选择 *
去
--10英里以内
select * from UDFDistanceWithin( 38.90606200,-76.92943500,10)

---

--[9] CREATE FUNCTION UDFNearestNeighbors

--

如果存在则从 AddressFields 表返回地址列表（从 sysobjects 中选择名称，其中名称 = 'UDFNearestNeighbors' AND 类型 = 'TF'）
删除函数 UDFNearestNeighbors

GO

IF EXISTS（从 sysobjects 中选择名称，其中 name = 'numbers' AND xtype = 'u'）
删除表编号

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

--TEST

--50 个邻居
从 UDFNearestNeighbors(38.90606200,-76.92943500,50) 选择 *
去
--200个邻居
从 UDFNearestNeighbors 选择 *( 38.90606200,-76.92943500,200)
去

Answer 2

Linq to SQL 不支持空间类型。支持并不是“不太好”——而是根本不存在。

您可以将它们作为 BLOB 读取，但不能通过简单地更改 Linq to SQL 中的列类型来实现这一点。您需要使用 CAST 语句在数据库级别更改查询，以将列作为 varbinary 返回。您可以通过添加计算的 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。