Is it possible to use SqlGeography with Linq to Sql?
I've been having quite a few problems trying to use Microsoft.SqlServer.Types.SqlGeography. I know full well that support for this in Linq to Sql is not great. I've tried numerous ways, beginning with what would the expected way (Database type of geography, CLR type of SqlGeography). This produces the NotSupportedException, which is widely discussed via blogs.
I've then gone down the path of treating the geography column as a varbinary(max), as geography is a UDT stored as binary. This seems to work fine (with some binary reading and writing extension methods).
However, I'm now running into a rather obscure issue, which does not seem to have happened to many other people.
System.InvalidCastException: Unable to cast object of type 'Microsoft.SqlServer.Types.SqlGeography' to type 'System.Byte[]'.
This error is thrown from an ObjectMaterializer when iterating through a query. It seems to occur only when the tables containing geography columns are included in a query implicitly (ie. using the EntityRef<> properties to do joins).
System.Data.Linq.SqlClient.ObjectReaderCompiler.ObjectReader`2.MoveNext()
My question: If I'm retrieving the geography column as varbinary(max), I might expect the reverse error: can't cast byte[] to SqlGeography. That I would understand. This I don't. I do have some properies on the partial LINQ to SQL classes that hide the binary conversion... could those be the issue?
Any help appreciated, and I know there's probably not enough information.
Extras:
- A
geographycolumn in the Visual Studio dbml Designer with 'Server Data Type' =geographygenerates this error:The specified type 'geography' is not a valid provider type. - A
geographycolumn in the Visual Studio dbml Designer with no 'Server Data Type' generates this error:Could not format node 'Value' for execution as SQL.
Answer
If all you want to do with SqlGeography is track points and take advantage of SQL Server 2008's spatial indices, you can, as others have noted, hide your spatial data column from Linq to SQL and use UDFs or stored procedures. Suppose that you have a table AddressFields that includes Latitude and Longitude fields. Add that table to your DBML file, and write any code that you want that sets the Latitude and Longitude fields. Then, the SQL code below will add a Geo geogarphy field to that table and create a trigger in the database that automatically sets the Geo field based on the Latitude and Longitude fields. Meanwhile, the code below also creates other useful UDFs and stored procedures: DistanceBetween2 (I already had a DistanceBetween) returns the distance between the address represented in an AddressField and a specified latitude/longitude pair; DistanceWithin returns various fields from all AddressFields within a specified mile distance; UDFDistanceWithin does the same as a user-defined function (useful if you want to embed this in a larger query); and UDFNearestNeighbors returns fields from AddressField corresponding to the specified number of neighbors nearest a particular point. (One reason for using UDFNearestNeighbors is that SQL Server 2008 won't optimize its use of a spatial index if you just call order by calling DistanceBetween2.)
You'll need to customize this by changing AddressFields to your table and customizing the fields from that table that you want returned (look in the code around references to AddressFieldID). You can then run this on your database and copy the resulting stored procedures and UDFs onto your DBML, and then you can use them in queries. Overall, this allows you to take advantage of a spatial index of points fairly easily.
-----------------------------------------------------------------------------------------
--[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] CREATE INDEX
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] 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] EXISTING PROC DistanceBetween, which returns the distance between two points specified
--by latitude/longitude coordinate pairs. --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] CREATE PROCEDURE USPDistanceWithin
-- RETURNS LIST OF ADDRESSES FROM AddressFields table
IF EXISTS (SELECT name FROM sysobjects WHERE name = 'USPDistanceWithin' AND type = 'P') DROP PROCEDURE 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
--within 3 miles USPDistanceWithin 38.90606200,-76.92943500,3 GO --within 5 miles USPDistanceWithin 38.90606200,-76.92943500,5 GO --within 10 mile USPDistanceWithin 38.90606200,-76.92943500,10
--[9] CREATE FUNCTION FNDistanceWithin
-- RETURNS LIST OF ADDRESSES FROM AddressFields table
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
--TEST
--within 3 miles select * from UDFDistanceWithin(38.90606200,-76.92943500,3) GO --within 5 miles select * from UDFDistanceWithin( 38.90606200,-76.92943500,5) GO --within 10 mile select * from UDFDistanceWithin( 38.90606200,-76.92943500,10)
--[9] CREATE FUNCTION UDFNearestNeighbors
-- RETURNS LIST OF ADDRESSES FROM AddressFields table
IF EXISTS (SELECT name FROM sysobjects WHERE name = 'UDFNearestNeighbors' AND type = 'TF') DROP FUNCTION UDFNearestNeighbors
GO
IF EXISTS (SELECT name FROM sysobjects WHERE 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
--TEST
--50 neighbors select * from UDFNearestNeighbors(38.90606200,-76.92943500,50) GO --200 neighbors select * from UDFNearestNeighbors( 38.90606200,-76.92943500,200) GO