The Challenge
Dispatch had no way to match technicians to jobs by location. Assignment was manual or done by city name. Every technician record already carried latitude and longitude, but no part of the application queried against them. There was no spatial column, no index, and no proximity search.
The Solution
Installed PostGIS on the existing PostgreSQL database and added a location column typed as GEOGRAPHY(POINT, 4326) to the Users, FormSubmissions, and Addresses tables. Put a GIST index on the technician column to keep spatial filtering fast. Wrote a Sequelize beforeSave hook that converts the legacy latitude and longitude fields into a GEOGRAPHY point on every save, so existing API endpoints kept working without modification. The dispatcher endpoint now geocodes an inbound address, applies ST_DWithin against a 50-mile radius, computes ST_Distance as a selected column, and orders results nearest-first. One indexed query, no application-side coordination.
The Impact
Two location-aware surfaces shipped on the same spatial layer. Dispatchers got a NearbyTechnicians dashboard that returns ranked, online technicians within a 50-mile radius of a geocoded job site, with distance computed at the database. Technicians got a NearbyJobs view of work in their area. The legacy latitude and longitude columns stayed in place behind the Sequelize hook, which meant upstream callers, mobile clients, and admin tooling kept working without a coordinated release.