Physician Finder

Context

A pharmaceutical client needed a public-facing tool to help patients and caregivers find physicians who prescribe a specific specialty neurological product. The requirements were straightforward: search by location, show results on a map, let users switch to a list. The data source was a CSV of physician names and addresses, updated and re-imported every night.

The front-end complexity turned out to be the easy part.

What it does

Users search by zip code, city and state, or a free-form address using Google Places autocomplete. Results appear both on a map — custom-styled markers, no default Google chrome — and in a synchronized list view. Clicking a marker highlights the corresponding list item. Clicking a list item pans the map. The two views stay in sync without any coordination overhead because they're both reading from the same React state.

The problem worth talking about

Every night, the client's internal system would generate a fresh CSV export of the current physician list and drop it on the server. Their instruction was to truncate the physicians table and re-import from scratch.

I pushed back on this. A proper approach would have been a CRUD interface where physicians could be added and removed individually, keeping the data stable and auditable. The truncate-and-reimport method meant the database had no memory between runs — which created a specific and expensive problem: geocoding.

The Google Geocoding API charges per request. If you truncate 800 physicians and reimport them, you don't want to geocode 800 addresses every night. Most of those addresses haven't changed. Some physicians share an office. Some addresses have slight variations in the CSV that still resolve to the same coordinates. The client's approach required the nightly wipe. So the solution had to work around it.

The seeder

The import script separates the concepts of physician and location into two tables with a one-to-many relationship: many physicians can share a location, but a location is geocoded once.

When the seeder processes a row from the CSV, it hashes the address components (street, city, state, postal code) with MD5 to produce a stable location identifier. It does the same for the physician, hashing name plus address. Before touching the geocoding API, it checks whether a location with that hash already exists in the database.

// 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');

If the location exists, we skip geocoding entirely and attach the physician to the existing location ID. If the hash is new, we geocode — but before inserting a new location record, there's one more check: whether the geocoding API returned a formatted address that already exists in the database. This catches the case where two slightly different CSV inputs (a typo, an abbreviated street name) 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');

The result: on a typical nightly run, fewer than 5% of records require a geocoding API call. The other 95% match an existing location hash and pass straight through.

What I'd do differently

The nightly truncation was a constraint I inherited, not one I'd design. Given a second pass, I'd have pushed harder for a diff-based import: compare the incoming CSV against the existing physician list, insert new records, remove stale ones, and leave everything else untouched. No truncation, no hash dance, no edge cases around addresses that geocode identically but look different on paper.

The current solution works well within the constraint. It just didn't need to be a constraint.

Demo

The production deployment runs for a pharmaceutical client and isn't publicly accessible. To demonstrate the front-end without any data concerns, I rebuilt the same application with a dataset of about 20 bars in my neighborhood.

View demo → — username: visitor / password: drive-arrive5