Proximity Search With SQL 2000

The “Implementing a Proximity Search with SQL 2000” article describes how to create a local method of searching for nearby places on your own locally held data. However it makes a couple of assumptions, one being that you will import your data via Access.

It’s quite likely that you may want to put your place data directly into SQL Server, you can then calculate the X, Y and Z co-ordinates using a stored proceedure. Firstly I’ll assume you place the data into a table in your SQL database with fieldnames Latitude and Longitude storing the values in decimal degrees as a float type. You’ll also need to add columns named XAxis, YAxis and ZAxis to your table, again with a float type. Then the following stored proceedure can be used to populate these fields.

UPDATE [tablename] SET XAxis = cos(RADIANS(Latitude)) * cos(RADIANS(Longitude)),
YAxis = cos(RADIANS(Latitude)) * sin(RADIANS(Longitude)),
ZAxis = sin(RADIANS(Latitude))
WHERE XAxis IS NULL;

Note that the where clause ensures it doesn’t process rows you have already populated. You may choose to run this procedure manually when you add new data, or schedule it to run regularly. If your data is editable once it’s in the database, you should ensure the X,Y,Z values are removed if the Latitude and Longitude are changed on a record. Once you’ve run this on your data you can use the FindNearby procedure described in the article.