Case study — 2023

Physician Finder

ReactLaravelGoogle MapsMySQL

Context

Pharma client needed physician finder for their website. Your basic search-by-location, plot results on a map, switch to a list. Data came from a CSV of names and addresses, re-imported every night.

Front-end was the easy part.

What it does

  • Search by zip, city/state, or free-form address (Google Places autocomplete)
  • Results on a custom-styled map and in a synced list
  • Click a marker — highlights the list item; click a list item — pans the map
  • Both views read the same React state, no coordination layer

The problem

Every night the client's system dropped a fresh CSV on the server. Instruction: truncate the physicians table and re-import from scratch. As I was building it out (and testing), I realized this was going to be an expensive geocoding problem.

I tried to push back at first, maybe a CRUD interface to keep data stable and auditable. They said, "Unfortunately, we just have a sales guy who needs to upload a CSV somewhere.""

So:

  • Most addresses haven't changed
  • Some physicians share an office
  • Some CSV rows look different but resolve to the same coordinates

I had to work around it and made a seeder which checks for existing geocodes.

The seeder

Split physician and location into two tables — one-to-many. Many physicians can share a location; geocode each location once.

For each CSV row:

  • Hash address parts (street, city, state, postal) with MD5 for a stable location ID
  • Hash name + address for the physician—catches duplicates at the same address
  • If the location hash already exists, skip geocoding and attach the physician to that location
// Location hash — street + city + state + postal
$l_arr = [$street, $city, $state, $postal];
$l_hex = md5(implode('|', $l_arr));

// Physician hash — name + address (catches duplicate physicians at same address)
$p_arr = [$name] + $l_arr;
$p_hex = md5(implode('|', $p_arr));

$location_id = DB::table('locations')
    ->whereRaw("HEX(`location_hash`) = ?", [$l_hex])
    ->value('id');

One more check before inserting a new location: does the geocoding API's formatted address already exist in the database? Catches typos and abbreviations (Rd / Road, East, NE) that still resolve to the same real-world address.

// Geocoding API returned a canonical address —
// check if we already have it before inserting a new location record.
$location_id = DB::table('locations')
    ->where('address', $address)
    ->value('id');

Typical nightly cron run: fewer than 5% of records hit the geocoding API. The rest match an existing hash and pass through.

What I'd do differently

Nightly truncation was inherited, not designed. Diff-based import would have made most of the seeder unnecessary. Either way, works well within the constraint.

Bonus! Demo

Look ma, I built a map.

Production runs for a pharma client and isn't public. I rebuilt the same front end with ~20 bars in my neighborhood so there's something to click through.

Spoiler alert: It's not going to blow your mind.

View demo