3

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

Shaunak
  • 17,377
  • 5
  • 53
  • 84

1 Answers1

2

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
Dharmendar Kumar 'DK'
  • 2,082
  • 17
  • 18
  • Thanks DK, although i did not use the query your described as is, but it gave me a fair idea of how to go about doing this. Thanks. I am now working on optimizing the query and have started another thread, can you please take a look at it? http://stackoverflow.com/questions/6654875/optimize-nearest-neighbor-query-on-70-million-extremely-high-density-spatial-poin – Shaunak Jul 11 '11 at 18:45