digitalBush

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?"

Example Radius Plot

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.