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.

Answer

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

DECLARE @Lat float= -83.555
DECLARE @Long float = 43.555
SELECT TOP 100 ID, Abbr, Distance FROM 
(
    SELECT NL.NodeLocationID AS ID, NL.NodeLocationAbbr AS Abbr, (ABS(NL.Latitude - @Lat) + ABS(NL.Longitude - @Long)) / 2 AS Distance 
    FROM NodeLocations AS NL
    WHERE (NL.Latitude IS NOT NULL) AND (NL.Longitude IS NOT NULL)
) AS D1 
ORDER BY Distance

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.

SELECT *
FROM 
    (
    SELECT TOP 100 PERCENT *
        , ROW_NUMBER () OVER (PARTITION BY ID ORDER BY Distance ASC) AS Rnk
    FROM 
        (
            SELECT NL.NodeLocationID AS ID, NL.NodeLocationAbbr AS Abbr
                , NL2.NodeLocationID AS ID2, NL2.NodeLocationAbbr AS Abbr2
                , (ABS(NL.Latitude - NL2.Latitude) + ABS(NL.Longitude - NL2.Longitude)) / 2 AS Distance 
            FROM NodeLocations AS NL
                JOIN NodeLocations AS NL2 ON NL.NodeLocationID<>NL2.NodeLocationID AND (ABS(NL.Latitude - NL2.Latitude) + ABS(NL.Longitude - NL2.Longitude)) / 2 <= 0.1
            WHERE (NL.Latitude IS NOT NULL) AND (NL.Longitude IS NOT NULL)
                AND (NL2.Latitude IS NOT NULL) AND (NL2.Longitude IS NOT NULL)
        ) AS D1 
    ) AS D2
WHERE D2.Rnk=1