In my previous post, I outlined the process which I'm using to speed up radius searches over a large pool of potential matches. With that I was able to answer the question "How many within n miles?" More recently I've been asked to answer the question "What is the minimum radius which contains n records?"
So, let's say that the red dot represents 2 records and the green dot represents 3 records. If I need to find the minimum radius to contain 4 records, then I'll need to go out to the green dot to satisfy that requirement. I think it's time I introduced my friendly query:
WITH available_records_by_zip as ( select zip, count(1) locations from records inner join addresses a using(address_id) group by zip ) SELECT origin_zip, radius FROM ( SELECT origin_zip, radius, cnt, row_number() over(PARTITION BY origin_zip ORDER BY radius) rnk FROM ( SELECT zr.zip_from origin_zip, radius, SUM(available_records_by_zip .locations) over( --This is the analytic function PARTITION BY zr.zip_from --which makes this possible. ORDER BY radius ) cnt FROM zip_radius_map zr, available_records_by_zip where zr.zip_from=:search_zip and zr.zip_to= available_records_by_zip.zip ) WHERE cnt >= 4 -- Number of locations needed ) WHERE rnk = 1
This is what it does: Given a pool or records, group then by origin zip and order them by distance. Only pull those which meet the minimum requirement by summing their count with all of the counts before them. Order that set by radius and take the first one. That guy is your minimum requirement.
In the real world, I'm joining to a table of target zips, but you get the idea. Doing it this way(cheating), I can do a single analysis for a few thousand target zips in a handful of seconds. The time it takes really just depends on the size of your set of available providers. These Oracle analytics functions are very powerful once you understand them.