find closest lat long to a input lat long Sql server 2008

Question

Hi I have a point cloud in my database (Sql server 2008 spatial). That is about 6 million records. There are 3 columns: id, value , geom. What is the most optimized way of getting the 'value' at input lat long ??

I am new to spatial queries in SQL Server 2008. Can some one post simple example of finding the point in geom column, matching or closest from the input lat long?

Thanks Shaunak

Answer 1

Assuming that you have a table Wifi with columns: id, placeName, locationCoord (geography):

CREATE TABLE [dbo].[WiFi](
[id] [int] IDENTITY(1,1) NOT NULL,
[placeName] [varchar](500) NULL,
[locationCoord] [geography] NULL,
CONSTRAINT [PK_WiFi] PRIMARY KEY CLUSTERED ([id] ASC))

Here the locationCoord is a geography type. Lets say the input is a latitude and longitude as varchar datatypes. You can get the nearest points/locations by using something like:

declare @longitude varchar(50) = '-77.26939916610718', @latitude varchar(50) = '39.168516439779914'

declare @ms_at geography, @locationString nvarchar(1000)

set @locationString = 'SELECT @ms_at = geography::STGeomFromText(''POINT(' + @longitude + ' ' + @latitude + ')'', 4326)'

exec sp_executesql @locationString, N'@ms_at geography OUT', @ms_at OUT

select nearPoints.placeName, nearPoints.locationCoord.STDistance(@ms_at) as distance 
,RANK() OVER (ORDER BY nearPoints.locationCoord.STDistance(@ms_at)) AS 'Rank'
from
(
select r.id, r.placeName, r.locationCoord 
from WiFi r 
where r.locationCoord.STIntersects(@ms_at.STBuffer(10000)) = 1
) nearPoints