Is it possible to use SqlGeography with Linq to Sql?

by

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

More Related Contents:

  1. Accessing System Databases/Tables using LINQ to SQL?
  2. How to pass an array into a SQL Server stored procedure
  3. Call a stored procedure with parameter in c#
  4. Execute a large SQL script (with GO commands)
  5. Calling stored procedure with return value
  6. Using StringWriter for XML Serialization
  7. LINQ to SQL – Left Outer Join with multiple join conditions
  8. Entity framework very slow to load for first time after every compilation
  9. TransactionScope vs Transaction in LINQ to SQL
  10. Get SQL query from LINQ to SQL?
  11. How to write Asynchronous LINQ query?
  12. How do you perform a CROSS JOIN with LINQ to SQL?
  13. Using DateTime in a SqlParameter for Stored Procedure, format error
  14. Code to validate SQL Scripts
  15. How do I perform an insert and return inserted identity with Dapper?
  16. Creating a database programmatically in SQL Server
  17. How can I call a SQL Stored Procedure using EntityFramework 7 and Asp.Net 5
  18. Get all associate/composite objects inside an object (in Abstract way)
  19. Optimistic concurrency: IsConcurrencyToken and RowVersion
  20. ALTER TABLE DROP COLUMN failed because one or more objects access this column
  21. Can I use ADFS 2.0 to authenticate certain users against SQL Server?
  22. Filling missing dates by group
  23. How to pass a null variable to a SQL Stored Procedure from C#.net code
  24. Is there a .NET equivalent to SQL Server’s newsequentialid()
  25. How to Specify Primary Key Name in EF-Code-First
  26. SQL Server: Dynamic where-clause
  27. Should the order of LINQ query clauses affect Entity Framework performance?
  28. How do I connect to a SQL database from C#?
  29. Explicit construction of entity type [MyClass] in query is not allowed
  30. Is Injection Possible through Dynamic LINQ?

Leave a Comment