是否可以将SqlGeography与Linq结合使用到Sql？

Question

我在使用Microsoft.SqlServer.Types.SqlGeography时遇到了不少问题。我非常清楚，在Linq中对此的支持不是很好。我尝试过很多种方法，首先是预期的方法(数据库类型的geography，CLR类型的SqlGeography)。这就产生了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类中确实有一些属性，这些类隐藏了二进制转换.这会是问题吗？

任何帮助都很感激，而且我知道可能没有足够的信息。

临时演员：

• 中具有“服务器数据类型”= geography的geography列生成此错误：The specified type 'geography' is not a valid provider type.
• 中没有“服务器数据类型”的geography列生成此错误：Could not format node 'Value' for execution as SQL.

Answer 1

Linq to SQL不支持空间类型。支持并不是“不太好”--它是不存在的。

您可以将它们读取为BLOB，但不能简单地将Linq中的列类型更改为SQL。您需要在数据库级别更改查询，以便使用varbinary语句将列作为CAST返回。您可以通过添加计算的varbinary列在表级别完成此操作，Linq将很高兴地将该列映射到byte[]。

换句话说，这样的DDL：

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

然后，将Location列从Linq移到SQL类，然后使用LocationData。

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

通过将部分Linq扩展到SQL实体类并添加一个自动转换属性，您可以使这个过程更加“自动”：

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

这假设LocationData是计算出的varbinary列的名称；在Linq到SQL定义中不包括“真实”的Location列，而是按照上面的临时方式添加它。

还要注意的是，除了对该列进行读和写之外，您不能对它做更多的工作；如果您试图对它进行实际查询(即将它包含在Where谓词中)，那么您将得到一个类似的NotSupportedException。

Answer 2

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

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创建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现有的PROC DistanceBetween，它返回指定的两个点之间的距离

-按纬度/经度坐标对。-更改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

去--测试

DistanceBetween2 12159,40.75889600，-73.99228900

-8创建过程USPDistanceWithin

-从AddressFields表返回地址列表

如果存在(从sysobjects选择name = ' USPDistanceWithin‘并键入= '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

去

-测试

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

-9创建函数FNDistanceWithin

-从AddressFields表返回地址列表

如果存在(从sysobjects选择name = ' UDFDistanceWithin‘并键入= 'TF')，则删除函数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

去

-测试

-在3英里内选择*从UDFDistanceWithin(38.90606200，-76.929435003)出发--在5英里内选择*从UDFDistanceWithin( 38.90606200，-76.929435005)出发- 10英里内选择*从UDFDistanceWithin( 38.90606200，-76.9294350010)出发

-9创建函数UDFNearestNeighbors

-从AddressFields表返回地址列表

如果存在(从sysobjects选择name = ' UDFNearestNeighbors‘并键入= 'TF')，则删除函数UDFNearestNeighbors

GO

如果存在(从sysobjects选择name =‘number’和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

去

-测试

-50个邻居从UDFNearestNeighbors(38.90606200，-76.9294350050) GO中选择*-200个邻居选择*来自UDFNearestNeighbors( 38.90606200，-76.92943500200) GO