Using PostGIS for Location-Based Services in Routiine App

Why Location Is the Core Feature

Routiine App is a marketplace that connects customers with mobile service providers. The fundamental matching criterion is location — when a customer needs a windshield chip repaired, they need a provider who can physically reach them within a reasonable timeframe. Everything else — ratings, pricing, availability — is secondary to geographic proximity.

This makes the location system the most important technical component in the backend. If the location matching is slow, inaccurate, or unreliable, the entire product fails. We needed a system that could answer the question "which available providers are within X miles of this customer?" in milliseconds, not seconds.

PostGIS Fundamentals

PostGIS is a PostgreSQL extension that adds support for geographic data types and spatial queries. Instead of storing latitude and longitude as two separate float columns and calculating distances in application code, PostGIS provides a native geography data type that represents points, lines, and polygons on the Earth's surface, with built-in functions for distance calculation, containment testing, and spatial indexing.

The key advantage of PostGIS over application-level distance calculations is performance. A naive approach — load all providers from the database, calculate the distance from each to the customer in JavaScript, filter and sort — requires reading every provider record on every query. As the provider count grows, this becomes a linear scan that degrades proportionally.

PostGIS spatial indexes (GiST indexes) allow the database to answer proximity queries using index scans. The query "find all providers within 15 miles of this point" uses the spatial index to eliminate candidates geometrically before calculating exact distances. This is the difference between scanning thousands of rows and scanning tens.

Data Model for Location

Provider locations are stored as PostGIS geography(Point, 4326) columns. The SRID 4326 specifies WGS 84, the coordinate system used by GPS. Each provider record includes their current location (updated periodically from the mobile app) and their service area (a configurable radius from their home base).

Customer request locations are also stored as geography points. When a customer submits a service request, the app captures their current GPS coordinates and the backend stores them as a PostGIS point.

The Prisma schema does not natively support PostGIS types, so we handle geographic columns through raw SQL migrations and raw queries. The Prisma model includes the provider's lat/lng as regular float fields for non-geographic operations (display, serialization), while the PostGIS geography column is maintained in parallel through a database trigger that updates the geography column whenever the lat/lng fields change.

This dual-column approach is a pragmatic compromise. Prisma handles the relational data — provider profiles, job records, payment information — with full type safety. PostGIS handles the spatial queries through $queryRaw calls. The two systems share the same database and the same records, but each handles the operations it is best suited for.

The Matching Query

The core matching query finds available providers within a specified radius of the customer's location, ordered by distance. In PostGIS SQL, this looks roughly like:

Select from providers where they are available, their status is active, and the distance between their location and the customer's point is less than the search radius, ordered by that distance ascending.

The ST_DWithin function is the key — it performs a spatial index-backed filter that eliminates providers outside the radius without calculating exact distances. The ST_Distance function in the ORDER BY clause calculates exact distances only for the remaining candidates, which is a much smaller set.

For the DFW MVP, the initial search radius is 15 miles. If no providers are found within 15 miles, the system expands to 25 miles and re-queries. This tiered expansion approach avoids the latency of a single large-radius query when a nearby provider is available, while still falling back to a wider search when needed.

Query performance with a GiST index on the geography column is consistently under 10 milliseconds for the DFW provider set. Even as the provider count grows into the hundreds, the spatial index keeps the query performance logarithmic rather than linear.

Real-Time Location Updates

Provider locations are not static. They move throughout the day as they travel between jobs. The matching system needs reasonably current location data to produce accurate results.

Providers' locations are updated through two mechanisms. The primary mechanism is background location tracking in the Expo mobile app. When a provider is in "available" status, the app reports their GPS coordinates to the backend every 60 seconds. This provides current-enough location data for matching without excessive battery drain or API traffic.

The secondary mechanism is job-based updates. When a provider starts or completes a job, their location is updated to the job's location. This captures the significant position changes (arriving at a customer's location) even if the background tracking is interrupted.

Location updates are batched on the client side and sent in bulk every 60 seconds rather than individually. Each update includes a timestamp, so the backend can determine which update is the most recent if they arrive out of order. The PostGIS geography column is updated with the most recent valid coordinate.

Accuracy and Edge Cases

GPS accuracy varies. A coordinate reported as a specific point might be accurate to 3 meters in open sky or 50 meters in an urban canyon between tall buildings. For a service marketplace where the provider drives to the customer, this level of inaccuracy is acceptable — the provider will navigate to the customer's address, not to the GPS coordinate. The geographic matching just needs to be accurate enough to identify providers in the general area.

The more significant edge case is provider availability. A provider may be technically within range but currently on a job, driving on a highway with no easy exit, or about to go off-duty. The matching system filters by availability status before applying the geographic filter, so only genuinely available providers are considered.

Another edge case is the DFW metro area's geography. The metroplex is large enough that a provider on the west side of Fort Worth and a customer on the east side of Dallas are technically in the same metro area but an hour apart. The radius-based matching handles this naturally — they simply will not match unless the radius is expanded significantly, which only happens when no closer providers are available. This geographic reality informed the decision to start the MVP in DFW specifically, where the density of demand and supply is high enough to make proximity matching viable.