Если все, что вы хотите делать с SqlGeography, — это отслеживать точки и использовать преимущества пространственных индексов SQL Server 2008, вы можете, как отмечали другие, скрыть столбец пространственных данных от Linq до SQL и использовать пользовательские функции или хранимые процедуры. Предположим, у вас есть таблица AddressFields, которая включает поля Latitude и Longitude. Добавьте эту таблицу в свой файл DBML и напишите любой код, который вы хотите, который устанавливает поля широты и долготы. Затем приведенный ниже код SQL добавит в эту таблицу поле «Геогеография» и создаст триггер в базе данных, который автоматически установит поле «Гео» на основе полей «Широта» и «Долгота». Между тем, приведенный ниже код также создает другие полезные UDF и хранимые процедуры: DistanceBetween2 (у меня уже есть DistanceBetween) возвращает расстояние между адресом, представленным в AddressField, и заданной парой широта/долгота; DistanceWithin возвращает различные поля из всех AddressField в пределах указанного расстояния в миле; UDFDistanceWithin делает то же самое, что и определяемая пользователем функция (полезно, если вы хотите встроить это в более крупный запрос); а UDFNearestNeighbors возвращает поля из AddressField, соответствующие заданному количеству соседей, ближайших к конкретной точке. (Одной из причин использования UDFNearestNeighbors является то, что SQL Server 2008 не оптимизирует использование пространственного индекса, если вы просто вызываете порядок, вызывая DistanceBetween2.)
Вам нужно будет настроить это, изменив AddressFields на свою таблицу и настроив поля из этой таблицы, которые вы хотите вернуть (посмотрите в коде ссылки на AddressFieldID). Затем вы можете запустить это в своей базе данных и скопировать полученные хранимые процедуры и определяемые пользователем функции в свою 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] CREATE PROCEDURE 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] СОЗДАТЬ ТРИГГЕР ПРИ ИЗМЕНЕНИИ/ВСТАВКЕ ШИРОТЫ/ДОЛГ. ЗНАЧЕНИЯ ---> УСТАНОВИТЬ ГЕОКОД
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] 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
НАЧАТЬ --ТЕСТ
DistanceBetween2 12159,40,75889600,-73,99228900
--[8] СОЗДАТЬ ПРОЦЕДУРУ USPDistanceWithin
-- ВОЗВРАЩАЕТ СПИСОК АДРЕСОВ ИЗ таблицы AddressFields
ЕСЛИ СУЩЕСТВУЕТ (ВЫБЕРИТЕ имя ИЗ sysobjects, ГДЕ имя = '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
--ТЕСТОВОЕ ЗАДАНИЕ
--в пределах 3 миль (USPDistanceWithin) 38,90606200, - 76,92943500,3 GO -- в пределах 5 миль (USPDistance) в пределах 38 90606 200, - 76,92943500,5 GO -- в пределах 10 миль
--[9] СОЗДАТЬ ФУНКЦИЮ FNDistanceWithin
-- ВОЗВРАЩАЕТ СПИСОК АДРЕСОВ ИЗ таблицы AddressFields
ЕСЛИ СУЩЕСТВУЕТ (ВЫБЕРИТЕ имя ИЗ sysobjects, ГДЕ имя = '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
--ТЕСТОВОЕ ЗАДАНИЕ
--в пределах 3 миль выберите * из UDFDistanceWithin(38.90606200,-76.92943500,3) GO --в пределах 5 миль выберите * из UDFDistanceWithin( 38.90606200,-76.92943500,5) GO --в пределах 10 миль выберите * из UDFDistanceWithin( 38.90606.92,-730096200,5) ,10)
--[9] СОЗДАТЬ ФУНКЦИЮ UDFNearestNeighbours
-- ВОЗВРАЩАЕТ СПИСОК АДРЕСОВ ИЗ таблицы AddressFields
ЕСЛИ СУЩЕСТВУЕТ (ВЫБЕРИТЕ имя ИЗ sysobjects, ГДЕ имя = 'UDFNearestNeighbors' И тип = 'TF') DROP FUNCTION UDFNearestNeighbors
GO
ЕСЛИ СУЩЕСТВУЕТ (ВЫБЕРИТЕ имя ИЗ sysobjects, ГДЕ name = 'numbers' AND 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
--ТЕСТОВОЕ ЗАДАНИЕ
--50 соседей выбирают * из UDFNearestNeighbors(38.90606200,-76.92943500,50) GO --200 соседей выбирают * из UDFNearestNeighbors(38.90606200,-76.92943500,200) GO
person
mbabramo
schedule
03.06.2010
