Finding the Nearest Help in Milliseconds with PostgreSQL

In emergency response platforms, routing needs to happen incredibly fast. I recently worked on a dispatch engine that connects patients, hospitals, and ambulance drivers in real time. The core technical problem was simple but critical. When a user presses an SOS button, the system must immediately identify the nearest available responders and route the request.

This post covers how we built the dispatch logic and why pushing a 19th-century navigation formula directly into PostgreSQL ended up being the most efficient solution for the MVP.

The Request Flow

Here is the general flow when an emergency is triggered:

1. Emergency Creation

The client application sends GPS coordinates to the API:

POST /v1/emergency/sos
{
  "latitude": 19.076,
  "longitude": 72.8777,
  "notes": "Patient unconscious, needs oxygen"
}

The server creates an emergency record and takes a snapshot of the user’s medical profile. We freeze this data so hospitals see the exact state of the patient at the time of the call, even if the user updates their profile later.

2. Finding the Nearest Hospitals

The system needs to locate the three closest hospitals. We cannot just query all hospitals. We only need facilities that are verified, currently active, and have bed capacity.

3. Acceptance Race

The selected hospitals receive the emergency request simultaneously. The first one to accept gets assigned to the case. The others receive a notification that the request was claimed.

4. Finding a Driver

Once a hospital is confirmed, the system broadcasts a pickup request to the nearest five available ambulance drivers. This follows the same pattern where the first to accept wins.

5. Live Tracking

After a driver is assigned, the patient’s family and the receiving hospital can track the ambulance. The driver app pushes GPS locations every few seconds, which we broadcast over WebSockets to all active subscribers.

The Geo Problem on a Sphere

At first glance, finding the nearest hospital looks like a basic bounding box query:

SELECT * FROM hospitals
WHERE abs(lat - 19.076) < 1
  AND abs(lng - 72.877) < 1

This works near the equator. It fails the closer you get to the poles because the Earth is a sphere. At the equator, one degree of longitude is roughly 111 kilometers. At 60 degrees latitude, it is only about 55 kilometers. A flat-earth bounding box query might exclude a hospital that is actually closer but falls outside the rigid rectangular window.

We needed to calculate the great-circle distance, which is the shortest path between two points along the surface of a sphere.

Implementing the Haversine Formula

The Haversine formula calculates spherical distance with very high accuracy:

a = sin²(Δlat/2) + cos(lat1)·cos(lat2)·sin²(Δlng/2)
c = 2 · atan2(√a, √(1-a))
d = 6371 · c

It gets its name from the haversine function (hav(θ) = sin²(θ/2)), which was historically used by ship navigators.

Here is how we implemented it directly in PostgreSQL:

SELECT hp.id, hp.account_id,
  (6371 * acos(
    cos(radians($1)) * cos(radians(hp.latitude))
    * cos(radians(hp.longitude) - radians($2))
    + sin(radians($1)) * sin(radians(hp.latitude))
  )) AS distance
FROM hospital_profiles hp
WHERE hp.is_verified = true
  AND hp.is_active = true
  AND hp.latitude IS NOT NULL
  AND hp.longitude IS NOT NULL
ORDER BY distance ASC
LIMIT 3;

Breaking down the query:

The API passes the patient’s coordinates as $1 and $2. The database compares every hospital’s coordinates and returns the three closest active facilities.

Why Use SQL Instead of Application Code?

A standard initial approach is to fetch the data and calculate the distance in the application layer:

const hospitals = await db.findAll();
hospitals.sort((a, b) => haversine(patient, a) - haversine(patient, b));
return hospitals.slice(0, 3);

This approach is fine for a few hundred records. However, scaling it up to tens of thousands of registered facilities creates a massive bottleneck. Loading everything into memory, running a sorting algorithm, and slicing the array on every single SOS request is highly inefficient.

By running the calculation in PostgreSQL:

• Only three rows are transmitted over the network.
• The database handles business condition filtering and spatial sorting in a single pass.
• Node.js memory pressure is kept completely flat.
• The query is ready to be optimized with PostGIS GiST spatial indexes once data volume demands it.

In load testing against 10,000 simulated facilities on a standard 2-core VM, the query executed in 8ms at the 95th percentile using a sequential scan.

Reusing the Logic

We used the exact same SQL pattern to locate ambulance drivers:

SELECT dp.id, dp.account_id,
  (6371 * acos(
    cos(radians($1)) * cos(radians(dp.current_latitude))
    * cos(radians(dp.current_longitude) - radians($2))
    + sin(radians($1)) * sin(radians(dp.current_latitude))
  )) AS distance
FROM driver_profiles dp
WHERE dp.is_available = true
  AND dp.is_verified = true
  AND dp.current_latitude IS NOT NULL
ORDER BY distance ASC
LIMIT 5;

We just target the driver_profiles table and filter for drivers who are currently online and verified. The logic is identical.

Handling Race Conditions

When multiple hospitals receive a request, they might try to accept it at the exact same millisecond.

We handle this using PostgreSQL’s row-level locking capabilities. The application executes an atomic update: UPDATE emergency_hospital_requests SET status = 'accepted' WHERE status = 'pending' AND id = $1 RETURNING *.

If two clients fire the update concurrently, only the first transaction finds a row matching status = 'pending'. The second transaction returns zero rows, which our API catches and safely returns as an HTTP 409 Conflict.

Future Scaling

The raw math approach works incredibly well for an MVP, but a production system at a national scale requires further layers of optimization:

For getting the system off the ground reliably, keeping the math directly inside standard SQL was the most effective engineering choice.

The Complete Pipeline

When the pieces are combined, a single SOS event triggers this chain:

[Phone GPS] -> [API: Create Emergency + Snapshot]
            -> [Haversine: Find 3 Nearest Hospitals]
            -> [Notify & Wait: First Accept Wins]
            -> [Haversine: Find 5 Nearest Drivers]
            -> [Notify & Wait: First Accept Wins]
            -> [WebSocket: Real-Time Tracking]
            -> [Complete or Cancel]

Each step runs inside a database transaction, relies on BullMQ for job retries, and streams state changes to clients via WebSockets. The routing math is just one component, but it keeps the latency low exactly when it matters most.

Key Takeaways

1. Leverage the database. It is tempting to write complex logic in Node.js, but standard SQL is an incredibly fast compute engine.
2. Keep the data model simple. Having uniform latitude and longitude columns across different entities allowed us to completely reuse our routing logic.
3. Handle concurrency explicitly. Atomic SQL updates with strict WHERE clauses are the easiest way to prevent double-booking resources.
4. Simple math ships faster. The Haversine formula assumes the Earth is a perfect sphere, which is technically inaccurate. However, it is simple to implement without complex extensions and gets the product into production. Upgrading to PostGIS can always happen later.

References

• Haversine formula (Wikipedia)
• PostGIS Spatial database extension
• BullMQ Node.js message queue