I have a SQL Server (2000) table containing 120,000 addresses, each with latitude/longitude co-ordinates. I have several other tables for different places of interest - as an example, I'll use museums. Each record in museums also has latitude/longitude co-ordinates. Every night, I want to populate a column in the addresses table with the name of the closest museum by distance, as the crow flies. I'll be doing this with at least 10 columns so I need a fairly efficient method. I am stumped about the best way to implement this, but supposing it were as a user-defined function, it would look something like this:
UPDATE Addresses SET NearestMuseum = GetNearest('MuseumsTable',
'MuseumNameField', LatColumn, LngColumn)
Where it would be possible to use one user defined function that could accept the name of the table you want to query, the field you want to return and the Lat/Lng you want to find the nearest record to.
I have triggers set up on all of the tables to create columns ProximityX, ProximityY and ProximityZ, the purpose of which is to pre-calculate trigonometric functions so that spatial queries can be faster at runtime. I have a stored procedure that gets the closest n records to a given lat/lng which uses them. The code sample is an example of one of these triggers.
I need a good solution to this problem and quickly, so I'm assigning the maximum points I have at my disposal.
CREATE TRIGGER [ProximityColumnsUpdate_Addresses] ON GazetteerUser.Properties
FOR INSERT, UPDATE
SET ProximityX = (COS(RADIANS(i.Latitude)) * COS(RADIANS(i.Longitude))),
ProximityY = (COS(RADIANS(i.Latitude)) * SIN(RADIANS(i.Longitude))),
ProximityZ = (SIN(RADIANS(i.Latitude)))
FROM inserted i LEFT JOIN Addresses a ON i.id = p.id