¿Es posible usar SqlGeography con Linq a Sql?

He tenido bastantes problemas al intentar usar Microsoft.SqlServer.Types.SqlGeography . Sé muy bien que el soporte para esto en Linq a Sql no es genial. Lo he intentado de varias maneras, empezando por lo que se esperaba (tipo de geography de la base de datos, tipo CLR de SqlGeography ). Esto produce NotSupportedException , que se discute ampliamente a través de blogs.

A continuación, he seguido el camino de tratar la columna de geography como varbinary(max) , ya que la geography es un UDT almacenado como binario. Esto parece funcionar bien (con algunos métodos de extensión de lectura y escritura binarios).

Sin embargo, ahora me encuentro con un problema bastante oscuro, que no parece haberle sucedido a muchas otras personas.

System.InvalidCastException: no se puede convertir el objeto de tipo ‘Microsoft.SqlServer.Types.SqlGeography’ para escribir ‘System.Byte []’.

Este error se produce desde un ObjectMaterializer al iterar a través de una consulta. Parece ocurrir solo cuando las tablas que contienen columnas de geografía se incluyen implícitamente en una consulta (es decir, utilizando las propiedades EntityRef para hacer combinaciones).

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

Mi pregunta: si estoy recuperando la columna de geography como varbinary(max) , podría esperar el error inverso: no se puede convertir byte[] en SqlGeography . Eso lo entendería. Esto no. Tengo algunas propiedades en las clases parciales LINQ to SQL que ocultan la conversión binaria … ¿podría ser ese el problema?

Cualquier ayuda apreciada, y sé que probablemente no haya suficiente información.

Extras:

  • Una columna de geography en Visual Studio dbml Designer con ‘Server Data Type’ = geography genera este error: The specified type 'geography' is not a valid provider type.
  • Una columna de geography en Visual Studio dbml Designer sin ‘Tipo de datos del servidor’ genera este error: Could not format node 'Value' for execution as SQL.

Los tipos espaciales no son compatibles con Linq a SQL. El soporte no es “bueno”, es inexistente.

Puede leerlos como BLOB, pero no puede hacerlo simplemente cambiando el tipo de columna en Linq a SQL. Debe modificar sus consultas en el nivel de la base de datos para devolver la columna como varbinary , utilizando la instrucción CAST . Puede hacerlo a nivel de tabla agregando una columna varbinary calculada, que Linq correlacionará felizmente con un byte[] .

En otras palabras, algo de DDL como este:

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

Luego, elimine la columna Location de su clase Linq a SQL y use LocationData lugar.

Si luego necesita acceso a la instancia de SqlGeography real, deberá convertirla a la matriz de bytes y desde ella, utilizando STGeomFromWKB y STAsBinary .

Puede hacer que este proceso sea un poco más “automático” extendiendo la clase de entidad parcial Linq a SQL y agregando una propiedad de conversión automática:

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

Esto supone que LocationData es el nombre de la columna varbinary calculada; no incluye la columna de Location “real” en su definición de Linq a SQL, la agrega de la manera ad-hoc anterior.

Tenga en cuenta también que no podrá hacer mucho con esta columna que no sea leer y escribir en ella; si intenta consultarlo realmente (es decir, incluyéndolo en un predicado Where ), obtendrá una NotSupportedException similar.

Si todo lo que quiere hacer con SqlGeography es seguir los puntos y aprovechar los índices espaciales de SQL Server 2008, puede, como han señalado otros, ocultar su columna de datos espaciales de Linq a SQL y usar UDF o procedimientos almacenados. Supongamos que tiene una tabla AddressFields que incluye campos de latitud y longitud. Agregue esa tabla a su archivo DBML y escriba cualquier código que desee que establezca los campos de Latitud y Longitud. Luego, el siguiente código SQL agregará un campo Geo geogarphy a esa tabla y creará un disparador en la base de datos que automáticamente establece el campo Geo en función de los campos de Latitud y Longitud. Mientras tanto, el código siguiente también crea otras UDF útiles y procedimientos almacenados: DistanceBetween2 (ya tenía una DistanceBetween) devuelve la distancia entre la dirección representada en un AddressField y un par de latitud / longitud especificado; DistanceWithin devuelve varios campos de todos los campos de dirección dentro de una distancia de milla especificada; UDFDistanceWithin hace lo mismo que una función definida por el usuario (útil si desea incrustar esto en una consulta más grande); y UDFNearestNeighbors devuelve campos de AddressField que corresponden al número especificado de vecinos más cercanos a un punto en particular. (Una razón para usar UDFNeighborsNeighbors es que SQL Server 2008 no optimizará el uso de un índice espacial si solo llama al orden llamando a DistanceBetween2).

Tendrá que personalizar esto al cambiar los campos de dirección en su tabla y personalizar los campos de esa tabla que desea devolver (busque en el código las referencias a AddressFieldID). Luego puede ejecutar esto en su base de datos y copiar los procedimientos almacenados resultantes y las UDF en su DBML, y luego puede usarlos en las consultas. En general, esto le permite aprovechar un índice espacial de puntos con bastante facilidad.

 ----------------------------------------------------------------------------------------- 

– [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] CREAR ÍNDICE

 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] CREAR PROCEDIMIENTO 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] CREAR DISPARADOR EN 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] CREAR PROC USP_SET_GEO_VALUE_INITIAL_LOAD —-> SOLO UNA VEZ ÚNICAMENTE

 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 ExIST Distancia entre, que devuelve la distancia entre dos puntos especificados

–por pares de coordenadas de latitud / longitud. –ALTER PROC Distancia entre Between2

 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

DistanciaEntre2 12159,40.75889600, -73.99228900


– [8] CREAR PROCEDIMIENTO USPDistanceWithin

– DEVOLUCIONES LISTA DE DIRECCIONES DE la tabla AddressFields

IF EXISTS (SELECCIONE el nombre FROM sysobjects WHERE name = ‘USPDistanceWithin’ Y escriba = ‘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 

IR

--PRUEBA

- dentro de 3 millas USPDistance Dentro de 38.90606200, -76.92943500,3 GO - dentro de 5 millas USPDistance Dentro de 38.90606200, -76.92943500,5 GO - dentro de 10 millas USPDistance Dentro de 38.90606200, -76.92943500,10


- [9] CREAR FUNCIÓN FNDistanceWithin

- DEVOLUCIONES LISTA DE DIRECCIONES DE la tabla AddressFields

IF EXISTS (SELECCIONE el nombre 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 

IR

--PRUEBA

--dentro de 3 millas seleccione * de UDFDistanceWithin (38.90606200, -76.92943500,3) GO - dentro de 5 millas seleccione * de UDFDistanceWithin (38.90606200, -76.92943500,5) GO - dentro de 10 millas seleccione * de UDFDistanceWithin (38.90606200, -76.92943500 , 10)


- [9] CREAR FUNCIÓN UDFNeighborsNeighbors

- DEVOLUCIONES LISTA DE DIRECCIONES DE la tabla AddressFields

IF EXISTS (SELECCIONE el nombre FROM sysobjects WHERE name = 'UDFNeighborsNeighbors' AND type = 'TF') DROP FUNCTION UDFNearestNeighbors

 GO 

IF EXISTS (SELECCIONE el nombre 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 

IR

--PRUEBA

--50 vecinos seleccionan * de UDFNeighborsNeighbors (38.90606200, -76.92943500,50) GO - 200 vecinos seleccionan * de UDFNeighborsNeighbors (38.90606200, -76.92943500.200) GO