Как следует из названия, я пытаюсь рассчитать кратчайшее расстояние между многими наборами точек долготы и широты. У меня есть набор домов и набор магазинов. Для каждого дома я пытаюсь определить, какой магазин находится ближе всего в радиусе 20 миль.
SQL, который я написал, работает, но плохо масштабируется при добавлении дополнительных домов к выполнению. Я пытаюсь найти способ сделать этот расчет эффективно. Даже если это займет несколько часов, я был бы доволен этим, так как могу запускать это ежемесячно. Однако в нынешнем виде этот запрос будет выполняться несколько дней, если я попытаюсь применить его к полному объему домов в моей базе данных.
What I've Tried So Far
- Используя руководство из этого вопроса, я использовал Пакет Oracle SDO_GEOM для расчета расстояния.
- Что касается эффективности, я следовал рекомендациям в этом руководстве настроить индексы для каждого столбца long/lat, а также код в предложении where для ограничения радиуса в 20 миль в попытке сразу отфильтровать недопустимые значения long/lats, тем самым уменьшив лишние вычисления.
- Я могу добавить в запрос параллелизм, но мне кажется, что это грубый метод сокращения времени выполнения. Хотя я думаю, что дополнение с помощью параллелизма вполне осуществимо, я хочу прийти к решению, которое сделает запрос эффективным до того, как я задействую процессоры.
Data Setup
Я работаю над базой данных Oracle 19c с двумя наборами данных:
<сильный>1. Список HOME_ID и связанных с ними значений долготы и широты
create table tmp_homes (
home_id number not null,
home_long float not null,
home_lat float not null,
primary key(home_id)
) nologging compress pctfree 0
;
Этот список может состоять из сотен тысяч записей.
Индекс устанавливается для каждого столбца long/lat.
<сильный>2. Список STORE_ID и связанных с ними значений долготы и широты
create table tmp_stores (
store_id number not null,
store_long float not null,
store_lat float not null,
primary key(store_id)
) nologging compress pctfree 0
;
Этот список состоит примерно из тысячи записей.
Индекс устанавливается для каждого столбца long/lat.
Query
create table tmp_homes_to_stores compress nologging pctfree 0 as
select *
from (
select
h.home_id,
s.store_id,
sdo_geom.sdo_distance(
sdo_geometry(2001, 4326, sdo_point_type(h.home_long, h.home_lat, null), null, null),
sdo_geometry(2001, 4326, sdo_point_type(s.store_long, s.store_lat, null), null, null),
0.01,
'unit=KM'
) as distance,
s.radius
from tmp_homes h
cross join (
select store_id, store_long, store_lat, 32.1869 as radius, 111.045 as distance_unit, 0.0174532925 as deg2rad--, 57.2957795 as rad2deg
from tmp_stores
) s
where h.home_lat between s.store_lat - (s.radius / s.distance_unit) and s.store_lat + (s.radius / s.distance_unit)
and h.home_long between s.store_long - (s.radius / (s.distance_unit * cos(s.deg2rad * (s.store_lat)))) and s.store_long + (s.radius / (s.distance_unit * cos(s.deg2rad * (s.store_lat))))
)
where distance <= radius -- 32.1869km = 20.00mi
;
Этот запрос работает хорошо, если я запускаю его для нескольких записей. К сожалению, в тот момент, когда я тестирую его на значительной части своих рабочих данных, на его выполнение уходят часы. Какие модификации или приемы я могу использовать, чтобы этот запрос выполнялся значительно быстрее?
Note
Запрос в его текущем состоянии вернет все STORE_ID, связанные с HOME_ID в радиусе 20 миль. Следующий шаг — упорядочить вывод по расстоянию для каждого HOME_ID и выбрать запись с кратчайшим расстоянием до магазина. Для справки, этот запрос выглядит так:
select home_id, store_id, distance
from (
select
hs.*,
row_number() over(partition by home_id order by distance asc) as distance_rank
from tmp_homes_to_stores hs
)
where distance_rank = 1
;