Speeding up lon lat Proximity / Nearest Places Search

[gavin]

Hi, firstly - cheers for such an excellent source of geo data!

I'm wondering if any of you geo data gurus can help me out?

I've downloaded the data and am doing proximity searches on it,
finding all places within 100 kms for example of a lon lat. The only
trouble is that this data takes about 2 mins to return results for a
single lon lat. I'm wondering if anyone has any techniques to speed up
the search results. Maybe some SQL WHERE clause I can apply to the lon
lats in order to arrive at a subset of data to perform calculations
on?

My simplified SQL (MS SQL Server 2005) is below:

DECLARE @GeoNameID int
SET @GeoNameID = 2656031

DECLARE @EarthRadius decimal(10,3)
SET @EarthRadius = 6378.137 -- km

DECLARE @MaxDistance int
SET @MaxDistance= 100

DECLARE @Latitude decimal(23,20), @Longitude Decimal(23,20)
SELECT @Latitude = Latitude, @Longitude = Longitude FROM GeoName
WHERE GeoNameID = @GeoNameID

SELECT geo.GeoNameID,
ROUND(@EarthRadius * ACOS((SIN(RADIANS(@Latitude)) *
SIN(RADIANS(geo.Latitude))) +
(COS(RADIANS(@Latitude)) * COS(RADIANS(geo.Latitude)) *
COS(RADIANS(geo.Longitude) - RADIANS(@Longitude)))),0) AS Distance
FROM GeoName AS geo
WHERE ROUND(@EarthRadius * ACOS((SIN(RADIANS(@Latitude)) *
SIN(RADIANS(geo.Latitude))) +
(COS(RADIANS(@Latitude)) * COS(RADIANS(geo.Latitude)) *
COS(RADIANS(geo.Longitude) - RADIANS(@Longitude)))),0) <=
@MaxDistance

Any pointers / suggestions would be greatly appreciated.

Cheers,
Gavin.

[Aaron Kreider]

Do as many of the calcuations as possible outside of the sql statement.

Example:

COS(RADIANS(@Latitude) - can be calculated outside the sql.

Aaron

[Marc Wick]

Hi Gavin

I fear SQL Server does not have inbuilt spatial support.

There is a spatial extension for mssql 2005 :
http://www.codeplex.com/MsSqlSpatial

And a microsoft paper :
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/TblValFuncSQL.asp

The next version of mssql server will have geospatial support :
http://industry.slashgeo.org/article.pl?sid=07/05/11/1329253

In any case you probably can do without geospatial functions and use
simple indices on the lat and lng columns. Use a rectangular bounding
box for your search large enough to include all your records and filter
out the rest based on exact distance. As a rule of thumb you can assume
one degree is around 111km (=40.000/360).

Hope this helps

Marc

[gavin]

Marc, cheers for your input - it was very much appreciated.

For anyone who's trying to do anything similar and stumbles upon this
post, I've added a few observations / tips from my experience -
http://forum.geonames.org/gforum/posts/list/0/522.page#2492

On Jul 21, 5:09 am, Marc Wick <m...@geonames.org> wrote:
> Hi Gavin
>
> I fear SQL Server does not have inbuilt spatial support.
>
> There is a spatial extension for mssql 2005 :http://www.codeplex.com/MsSqlSpatial
>

> And a microsoft paper :http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsq...