Skip to content
Advertisement

SQL Server: how to find closest location?

I have two tables, Houses and Stores. Both have Latitude and Longitude fields. I need to make a view that would return for each record in Houses HouseId and StoreId, where StoreId is the ID of the store closest to the house. I do have a function CalculateDistance(float Long1, float Lat1, float Long2, float Lat2). But I cannot come up with the query that would make the view I need.

Thanks.

Advertisement

Answer

Working Example From one of my Applications… You will need to remap the columns and tables obviously.

The below finds the closest items relative to other items in the same table (can be altered by changing NL2 to another table). This works to find the closest items, however performance is horrid depending on how many records your dealing with. I would suggest you create a static field and calculate each one via a background process.

User contributions licensed under: CC BY-SA
4 People found this is helpful
Advertisement