SQL заявка за получаване на три най-близки местоположения

Бих създал изглед с дефиниция, подобна на тази:

SELECT 
    ho.ho_identifier, dlr.dname1, --dlr.id would be even better, but you didnt give the name of the id column
    dbo.latlngdistance(dlr.latitute, dlr.longtitude, ho.lat, ho.lng) AS distance
FROM dlr d
CROSS JOIN homeowner ho

Като се има предвид, че този изглед ще се наричаcalculedDistances, можете да намерите най-близкия доставчик с нещо като:

SELECT ho.ho_identifier, dlr.dname1
FROM homeowner ho
JOIN calculatedDistances dis
    ON dis.ho_identifier = ho.ho_identifier
WHERE 
    NOT EXISTS(
        SELECT 1
        FROM calculatedDistances nearer
        WHERE
            nearer.distance < dis.distance
            AND nearer.ho_identifier = ho.ho_identifier
    )

Намирането на три най-близки би било по-трудно и може да доведе до някои сериозни проблеми с производителността. Бихте могли да направите нещо като:

SELECT 
    dis.ho_identifier, dis.dname1
FROM calculatedDistances dis
LEFT JOIN calculatedDistances nearer
    ON nearer.ho_identifier = dis.ho_identifier
    AND nearer.distance < dis.distance
GROUP BY dis.ho_identifier, dis.dname1
HAVING ISNULL(COUNT(1), 0) < 3

Още веднъж - може да се наложи да съхраните изчисленото разстояние в някои други таблици, ако имате проблеми с производителността.

Сега е доста късно там, където живея, така че съжалявам, ако съм направил грешки. Ако е така, моля, коментирайте този отговор и аз ще се опитам да се коригирам утре.

Ето какво предложи бивш колега:

Мисля, че ще се опитам да събера целевите собственици на жилища във временна таблица, за да запазя нещата, и след това да използвам SQL, за да премина процедурно през всяка стъпка през всяка от тях.

като например (имайте предвид, че не мога да тествам нищо от това)

Select HO_Identifier, zip.lat, zip.lng INTO MyTempHO
From DBC_Homeowner
LEFT Join Zip on zip.zip = left(5,HO_Zip) AND Zip.ll = 'L'  //I think there might also be a function to get lat/lng from zip....
WHERE <Your homeowner selection criteria>

Това трябва да ви даде по-малка маса, с която да работите. Проверете дали всички полета Lat, Lng са попълнени, може да има проблем с циповете на някои. Разбира се отляво (5 работи само за САЩ, не за Канада)

Създайте друга таблица, за да съхранявате резултатите, напр.

CREATE TABLE MyResults (HO_Identifier int,DLR_Identifier int)

Записва цикъл, за да премине през всеки ред и да намери първите 3 дилъра, като напр

//изпълнете всичко това като един израз

Declare @HO_Identifier int, @HO_Lat double, @HO_Lng double

Declare Cursor HO from select HO_Identifier , Lat, Lang from MyTempHO

OPEN HO
FETCH FROM HO Into @HO_Identifier, @HO_Lat, @HO_Lng 
While @@INDEX = 0
BEGIN
    Insert MyResults (@HO_Identifier, Dlr.dlr_Identifier) Select TOP 3 DLR_Identifier from dlr 
        where dbo.LAtLngDistance(@HO_Lat, @HO_Lng, dlr.lat, dlr.lng) < 100
        AND <Your other dlr criteria>
        AND dlr.lng between HO_Lng + <some negative amount to limit results> AND HO_Lng - <Same negative amount> //Note lng is negative!
        ORDER BY dbo.LAtLngDistance(@HO_Lat, @HO_Lng, dlr.lat, dlr.lng)

    FETCH NEXT FROM HO Into @HO_Identifier, @HO_Lat, @HO_Lng 
END

CLOSE HO
DEALLOCATE HO