SQL-запрос, чтобы получить три ближайших местоположения

Вот моя загадка запроса: - У меня есть таблица клиентов с долготой и широтой для их почтового индекса. - У меня есть таблица поставщиков с указанием долготы и широты их почтовых индексов. - Отсутствуют связи между таблицами. - У меня есть определяемая пользователем функция, которая вычисляет расстояние между двумя наборами долготы и широты.

У меня есть следующий запрос, который может показать (до) трех ближайших поставщиков в радиусе 50 миль для данного клиента (dbo.latlngdistance — это определяемая пользователем функция):

select top 3 dname1, dcity, dstate, dzip, dbo.latlngdistance(dlr.latitude, dlr.longitude, 
    (select lat from homeowner where ho_identifier = 16), 
    (select lng from homeowner where ho_identifier = 16)) AS SortDistance
from dlr
where dbo.latlngdistance(dlr.latitude, dlr.longitude, 
    (select lat from homeowner ho_identifier = 16), 
    (select lng from homeowner where ho_identifier = 16)) between 0 and 50
Order By SortDistance

У меня есть еще один запрос, который возвращает всех поставщиков в радиусе 50 миль от всех клиентов (я ограничил количество клиентов в этом примере, потому что производительность этого запроса ужасна):

select ho.ho_identifier, ho.ho_firstname, ho.ho_lastname, ho.ho_email, dname1, dcity, dstate, dzip
from dlr
    cross join homeowner ho
where dbo.latlngdistance(dlr.latitude, dlr.longitude, 
    (select lat from homeowner ho2 where ho.ho_identifier = ho2.ho_identifier), 
    (select lng from homeowner ho3 where ho.ho_identifier = ho3.ho_identifier)) between 0 and 50
and ho.ho_identifier in (16, 17, 18, 19, 20)
Order By ho.ho_identifier

Итак, мой вопрос: как мне получить (до) трех ближайших поставщиков в радиусе 50 миль для всех клиентов?


sql sql-server sql-server-2000
person Vin Allen    schedule 09.10.2014    source источник
comment
попробуйте создать другую определяемую пользователем функцию или процедуру хранения для выполнения вашего первого запроса в цикле?   -  person Luo Sen    schedule 09.10.2014
comment
Я должен был упомянуть, что застрял на SQL Server 2000 — я знаю, верно?   -  person Vin Allen    schedule 09.10.2014
comment
это отстой, есть способ сделать это с помощью нескольких объединений, но я не думаю, что это хороший способ...   -  person Luo Sen    schedule 10.10.2014
comment
Если вы можете обновить базу данных до чего-то, сделанного в течение последнего десятилетия, вы можете использовать тип пространственных данных, который имеет встроенную поддержку для определения k ближайших соседей (blogs.msdn.com/b/isaac/archive/2008/10/23/).   -  person Justin R.    schedule 10.10.2014

Ответы (2)


arrow_upward
0
arrow_downward

Я бы создал представление с определением, подобным этому:

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

Учитывая, что это представление будет называться calculateDistances, вы можете найти ближайшего поставщика с чем-то вроде:

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

Еще раз - вам может понадобиться сохранить calcutedDistance в некоторых других таблицах, если у вас есть проблемы с производительностью.

Там, где я живу, уже довольно поздно, так что извините, если я сделал какие-то ошибки. Если это так, пожалуйста, прокомментируйте этот ответ, и я постараюсь исправить себя завтра.

person Apokralipsa    schedule 09.10.2014
comment
Представление, которое вы предлагаете, создает декартово соединение, которое, учитывая размер таблиц, с которыми я имею дело (+50 000 поставщиков и +26 000 домовладельцев), создает серьезные проблемы с производительностью (например, я убил запрос, используя один ho_identifier через две минуты). Но мне нравится направление, в котором вы идете! Кстати, у меня есть столбец dlr_identifier. - person Vin Allen; 09.10.2014
comment
Вот проблемы, о которых я писал. С другой стороны, у вас должны быть очень похожие планы выполнения ваших запросов. Возможно, вы могли бы создать таблицу, в которой будут храниться вычисленные результаты функции? - person Apokralipsa; 09.10.2014
comment
Ну, я ограничил представление расстояниями от 0 до 50 миль, что помогло для одного запроса ho_identifier, но запрос 5 ho_identifiers пока занимает более трех минут. Кстати, я использую последний запрос, чтобы найти три ближайших. - person Vin Allen; 09.10.2014
comment
Еще одна вещь, которая может помочь улучшить производительность, — это переписать представление, чтобы оно не использовало скалярную функцию, а выполняло встроенные вычисления. Полезное чтение: databasejournal.com/features/mssql/article.php/3845381/ - person Apokralipsa; 10.10.2014

arrow_upward
0
arrow_downward

Вот что предложил бывший коллега:

Я думаю, что я бы попытался собрать целевых домовладельцев во временную таблицу, чтобы сохранить содержимое, а затем использовать 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
person Vin Allen    schedule 16.10.2014