CODEWITHSUNDEEP
.netsql-server-2008-r2geocodinggeospatialgeography
Shax
Shax

Reputation: 4307

How to find nearest Geo location in the postcode table of SQL Server 2008 R2

I am getting location update from windows phone 7.5 into my sql server 2008 R2 database. The mobile is in the car and working as a tracing device also.

for example: this location (Latitude: 51.5557830164189 Longitude: 0.0711440443992739) is what I received from the mobile. Now I want to find out the nearest location or postcode against this location in my postcode table which is having 1.7m records almost.

My postcode table definition is 

CREATE TABLE [dbo].[PostCode1](
    [Postcode] [nvarchar](50) NOT NULL,
    [Coordinates] [geography] NOT NULL,
 CONSTRAINT [PK_PostCode1] PRIMARY KEY CLUSTERED 
(
    [Postcode] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

I've tried lots of blog by googling but unable to find the answer

can somebody guide me how can I achieve this by using query and I need only 1 record from the table and in less time.

Thanks

Upvotes: 0

Views: 713

Answers (1)

Ross Turner
Ross Turner

Reputation: 21

I found this function below to be very helpful. I've modified it so it's in miles rather than km.

You can use this as the basis of building a procedure that returns the nearest postcode.

If you create a view / @temptable you can work out the distance from point to point and then filter by top 1 with the shortest distance.

/****** Object:  UserDefinedFunction [dbo].[DISTANCE] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER function [dbo].[DISTANCE]
    (
    @Latitude1  float,
    @Longitude1 float,
    @Latitude2  float,
    @Longitude2 float
    )
returns float
as
/*
fUNCTION: F_GREAT_CIRCLE_DISTANCE

    Computes the Great Circle distance in kilometers
    between two points on the Earth using the
    Haversine formula distance calculation.

Input Parameters:
    @Longitude1 - Longitude in degrees of point 1
    @Latitude1  - Latitude  in degrees of point 1
    @Longitude2 - Longitude in degrees of point 2
    @Latitude2  - Latitude  in degrees of point 2

*/
begin
declare @radius float

declare @lon1  float
declare @lon2  float
declare @lat1  float
declare @lat2  float

declare @a float
declare @distance float

-- Sets average radius of Earth in Kilometers
set @radius = 3959.0E

-- Convert degrees to radians
set @lon1 = radians( @Longitude1 )
set @lon2 = radians( @Longitude2 )
set @lat1 = radians( @Latitude1 )
set @lat2 = radians( @Latitude2 )

set @a = sqrt(square(sin((@lat2-@lat1)/2.0E)) + 
    (cos(@lat1) * cos(@lat2) * square(sin((@lon2-@lon1)/2.0E))) )

set @distance =
    @radius * ( 2.0E *asin(case when 1.0E < @a then 1.0E else @a end ))

return @distance

end

Upvotes: 2

Related Questions