Рекурсивный геометрический запрос: пять ближайших объектов

Question

Вопрос в том, может ли описанный ниже запрос быть выполнен без обращения к процедурной логике, то есть, может ли он быть обработан с помощью SQL, CTE и одной только оконной функции? Я использую SQL Server 2012, но вопрос не ограничивается этим движком.

Предположим, у нас есть национальная база данных учителей музыки с 250000 строк:

  teacherName, address, city, state, zipcode, geolocation, primaryInstrument

где geolocation столбец geography::point тип данных с оптимальным индексом тесселяции.

Пользователь хочет, чтобы пять ближайших учителей гитары были на его месте. Запрос, использующий оконную функцию, работает достаточно хорошо, если мы выберем некоторую произвольную отсечку расстояния, скажем, 50 миль, так что мы не выбираем все 250000 строк, а затем ранжируем их по расстоянию и выбираем ближайшие 5.

Но это произвольное ограничение радиуса в 50 миль не всегда может охватить 5 учителей, если, например, пользователь выбирает инструмент из другой культуры, такой как ситар или уд или балалайка; не может быть пяти учителей таких инструментов в пределах 50 миль от ее местоположения.

Кроме того, теперь представьте, что у нас есть запрос, в котором музыкальная консерватория прислала нам список из 250 певцов, которые являются учениками, которые были приняты в школу на следующий год, и они хотят, чтобы мы отправили им пять ближайших голосовых тренеров для каждый человек в списке, так что эти студенты могут договориться, чтобы получить некоторую тренировку, прежде чем они прибудут в кампус Мы должны отсканировать базу данных учителей 250 раз (т.е. отсканировать индекс геолокации), потому что все эти ученики живут в разных местах по всей стране.

Итак, мне было интересно, возможно ли для этого последнего запроса, включающего список из 250 местоположений учащихся, написать рекурсивный запрос, где радиус начинается с малого, скажем, в 10 милях, а затем увеличивается на 10 миль с каждой итерацией, пока либо достигнут максимальный радиус 100 миль, либо найдено пять (5) учителей? И может ли это быть сделано только для тех учеников, которые еще не получили 5 преподавателей?

Я думаю, что это не может быть сделано только с помощью SQL, и должно быть сделано с помощью циклов и временной таблицы - но, возможно, это потому, что я не понял, как это сделать только с SQL.

PS Столбец primaryInstrument может также уменьшить размер набора, ранжированного по расстоянию, но ради этого вопроса забудьте об этом.

РЕДАКТИРОВАТЬ: Вот пример запроса. Набор данных SINGER (представленный) содержит столбец с произвольным радиусом, чтобы ограничить гео-результаты меньшим подмножеством, но, как указано выше, этот радиус может определять круг (центральная точка которого - геолокация учащегося), который может не включать требуемое число учителей. Иногда предоставляемые наборы данных содержат тысячи адресов, а не просто несколько сотен.

select TEACHERSRANKEDBYDISTANCE.* from
(
select STUDENTSANDTEACHERSINRADIUS.*,
rowpos = row_number() 
over(partition by 
        STUDENTSANDTEACHERSINRADIUS.zipcode+STUDENTSANDTEACHERSINRADIUS.streetaddress 
      order by DistanceInMiles)
from
(
select
SINGER.name, 
SINGER.streetaddress, 
SINGER.city, 
SINGER.state, 
SINGER.zipcode, 
TEACHERS.name as TEACHERname, 
TEACHERS.streetaddress as TEACHERaddress, 
TEACHERS.city as TEACHERcity, 
TEACHERS.state as TEACHERstate, 
TEACHERS.zipcode as TEACHERzip,
TEACHERS.teacherid,
geography::Point(SINGER.lat, SINGER.lon, 4326).STDistance(TEACHERS.geolocation)
            / (1.6 * 1000) as DistanceInMiles
from 
SINGER left join TEACHERS
on 
( TEACHERS.geolocation).STDistance( geography::Point(SINGER.lat, SINGER.lon, 4326))   
     < (SINGER.radius * (1.6 * 1000 ))
 and TEACHERS.primaryInstrument='voice'
) as STUDENTSANDTEACHERSINRADIUS
) as TEACHERSRANKEDBYDISTANCE
where rowpos < 6    -- closest 5 is an abitrary requirement given to us

Answer 1

Я думаю, может быть, если вам нужно просто найти 5 ближайших учителей независимо от радиуса, вы можете написать что-то вроде этого. Студент будет дублировать 5 раз в этом запросе, я не знаю, что вы хотите получить.

select
    S.name, 
    S.streetaddress, 
    S.city, 
    S.state, 
    S.zipcode, 
    T.name as TEACHERname, 
    T.streetaddress as TEACHERaddress, 
    T.city as TEACHERcity, 
    T.state as TEACHERstate, 
    T.zipcode as TEACHERzip,
    T.teacherid,
    T.geolocation.STDistance(geography::Point(S.lat, S.lon, 4326))
    / (1.6 * 1000) as DistanceInMiles
from SINGER as S
    outer apply (
        select top 5 TT.*
        from TEACHERS as TT
        where TT.primaryInstrument='voice'
        order by TT.geolocation.STDistance(geography::Point(S.lat, S.lon, 4326)) asc
    ) as T