Действительно ли возможно использовать SqlGeography с Linq к Sql?

Если все, что вы хотите делать с SqlGeography, - это отслеживать точки и использовать преимущества пространственных индексов SQL Server 2008, вы можете, как отмечали другие, скрыть столбец пространственных данных из Linq в SQL и использовать UDF или хранимые процедуры. Предположим, у вас есть таблица AddressFields, которая включает поля широты и долготы. Добавьте эту таблицу в свой файл DBML и напишите любой код, который вы хотите, чтобы установить поля Широта и Долгота. Затем приведенный ниже код SQL добавит в эту таблицу поле Geo geogarphy и создаст триггер в базе данных, который автоматически устанавливает поле Geo на основе полей широты и долготы. Между тем, приведенный ниже код также создает другие полезные UDF и хранимые процедуры: DistanceBetween2 (у меня уже был DistanceBetween) возвращает расстояние между адресом, представленным в AddressField, и указанной парой широта / долгота; DistanceWithin возвращает различные поля из всех AddressFields в пределах указанного расстояния в миле; UDFDistanceWithin делает то же самое, что и пользовательская функция (полезно, если вы хотите встроить это в более крупный запрос); а UDFNearestNeighbors возвращает поля из AddressField, соответствующие указанному количеству соседей, ближайших к определенной точке. (Одна из причин использования UDFNearestNeighbors заключается в том, что SQL Server 2008 не будет оптимизировать использование пространственного индекса, если вы просто вызовете порядок, вызвав DistanceBetween2.)

Вам нужно будет настроить это, изменив 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] CREATE TRIGGER ON LAT / LONG VALUE CHANGE / INSERT ---> 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 ----> ONE TIME RUN ONLY

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 WHERE 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

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 WHERE name = 'UDFDistanceWithin' AND type = 'UDFDistanceWithin' 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 миль выберите * из UDFDistanceWithin (38.90606200, -76.92943500,10)

---

- [9] СОЗДАТЬ ФУНКЦИЮ UDFNearestNeighbors

- ВОЗВРАЩАЕТ СПИСОК АДРЕСОВ ИЗ таблицы AddressFields

ЕСЛИ СУЩЕСТВУЕТ (ВЫБЕРИТЕ имя FROM sysobjects 'UDERFORSN 'И тип =' TF ') ФУНКЦИЯ УДАЛЕНИЯ UDFNearestNeighbors

GO

ЕСЛИ СУЩЕСТВУЕТ (ВЫБЕРИТЕ имя ИЗ sysobjects, ГДЕ name = 'numbers' И xtype = 'u') DROP TABLE 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

- TEST

- 50 соседей выберите * из UDFNearestNeighbors (38.90606200, -76.92943500,50) ИДТИ --200 соседей выберите * из UDFNearestNeighbors (38.90606200, -76.92943500,200) GO