How we replaced slow Overpass API queries with a 651k-row local UK geospatial engine in Laravel 12 & PHP 8.4

Jobbri UK — Wed, 28 Oct 2026 13:09:22 +0000

When building Jobbri UK — a direct-hire UK recruitment and gig economy platform designed to connect candidates with employers without recruitment agencies — we wanted to solve a very practical problem for British job seekers: commute and relocation transparency.

Most job boards only show a city name like "Manchester" or "London". Before applying for a role, candidates actually need to know:

How far is the office from the nearest train, tube, or bus station?
Are there supermarkets, GP practices, gyms, and nurseries nearby?
What percentage of the net take-home pay (in £ GBP) will go towards local rent in that specific UK postcode?

To answer this, we built Area Guide 2.0 directly into every live listing on our UK jobs search engine alongside our free UK salary calculator and career tools.

However, our initial architecture hit a major bottleneck in production: public Overpass API rate limits and timeouts. Here is how we re-engineered the entire pipeline using a local 651,779-object UK master database that resolves 10 categories of local amenities in under 15 milliseconds.

The problem with live Overpass API queries in production

Initially, whenever a new UK job was posted with a valid postcode (e.g., M1 1AL or SW1A 1BB), a background worker geocoded the postcode via postcodes.io and queried the public OpenStreetMap Overpass API for nearby transport nodes, supermarkets, healthcare facilities, hotels, and restaurants.

In production, this caused three critical issues:

HTTP 429 & 504 Timeouts: Public Overpass instances frequently throttle concurrent requests from cloud VPS ranges, leaving some job listings with empty neighbourhood tabs.
Rural vs. Urban Radius Mismatch: A fixed 1,500m radius works well in Central London (EC2N), where there are hundreds of tube stations and cafes. In smaller UK towns or industrial estates, the nearest hotel or railway station might be 4km to 10km away.
Latency: Each external Overpass HTTP call took between 2.5s and 8.0s to parse.

Step 1: Extracting 651,779 UK POIs with Geofabrik & C++ `osmium-tool`

Instead of querying an external API over the network, we decided to bring the entire United Kingdom OpenStreetMap dataset local to our Ubuntu VPS.

We downloaded the official united-kingdom-latest.osm.pbf archive (~1.9 GB compressed) from Geofabrik. Parsing a multi-gigabyte PBF file directly in PHP is inefficient, so we used the C++ osmium-tool (osmium tags-filter) to filter only the relevant nodes and ways (transport stops, railway stations, supermarkets, restaurants, GP surgeries, pharmacies, gyms, parks, and hotels) and export them into a clean GeoJSON stream:

osmium tags-filter united-kingdom-latest.osm.pbf \
  nwr/public_transport nwr/railway=station,tram_stop \
  nwr/highway=bus_stop nwr/amenity=restaurant,cafe,fast_food,pub,pharmacy,doctors,hospital,dentist,gym,cinema \
  nwr/shop=supermarket,convenience,mall \
  nwr/tourism=hotel,hostel,guest_house,attraction \
  nwr/leisure=park,fitness_centre \
  -o uk-filtered-amenities.osm.pbf -O

osmium export uk-filtered-amenities.osm.pbf -f geojsonseq -o uk-places.geojsonseq

This reduced the raw planetary data into 651,779 verified UK points of interest, which our Laravel console command (ImportUkMasterMap.php) streamed into a dedicated warehouse table (uk_master_places) in MySQL 8.0 using batch inserts and a composite spatial bounding-box index on (category, lat, lon).

Step 2: Two-tier table isolation (`uk_master_places` vs `nearby_places`)

A common mistake when storing 650k+ geospatial rows in MySQL is running Haversine distance queries directly from the frontend Livewire component on every page view.

To keep frontend page loads under 80ms, we separated storage into two tiers:

uk_master_places (651,779 rows): The offline read-only UK warehouse indexed by (category, lat, lon).
nearby_places (Lightweight active cache): Stores only the pre-computed top amenities for active job postcodes.

When a job is published on Jobbri UK, our AreaDataService runs an Adaptive Category Radius Algorithm (~2km -> ~6km -> ~13km).

If a job is located in a rural postcode and the 2km bounding box returns zero hotels or railway stations, the query automatically expands the bounding box for that specific category up to 13km and calculates the exact Haversine distance:

public function syncFromUkMaster(string $postcode, float $lat, float $lon): void
{
    $categories = ['transport', 'food', 'supermarkets', 'hotels', 'healthcare', 'entertainment', 'landmarks'];
    $radiiDegrees = [0.02, 0.055, 0.12]; // ~2.2km, ~6km, ~13km

    foreach ($categories as $category) {
        $found = collect();

        foreach ($radiiDegrees as $deg) {
            $found = DB::table('uk_master_places')
                ->where('category', $category)
                ->whereBetween('lat', [$lat - $deg, $lat + $deg])
                ->whereBetween('lon', [$lon - $deg, $lon + $deg])
                ->limit(80)
                ->get();

            if ($found->count() >= 6) {
                break;
            }
        }

        // Sort by exact Haversine distance and cache top 12 in nearby_places
    }
}

Because whereBetween('lat', ...) and whereBetween('lon', ...) hit the B-Tree composite index (category, lat, lon) before computing trigonometric distances in PHP, syncing all 7 categories for a new UK postcode takes less than 12 milliseconds with zero external HTTP calls!

Step 3: Combining geospatial data with 2026/27 UK take-home pay

Once the local amenities and transport links are mapped on Leaflet.js with OSRM walking/cycling routes, we combine the job's advertised salary with our UK salary and tax calculator (using the 2026/27 HMRC Personal Allowance of £12,570, Income Tax bands, and Class 1 National Insurance thresholds).

This lets candidates see—on a single screen—their exact monthly net pay in £ GBP alongside the average 1-bedroom and shared-room rent for that specific UK postcode sector.

Results in production

By moving from live Overpass HTTP calls to our local 651k-row uk_master_places engine:

0% API rate-limit failures: Every single UK job listing gets 100% populated neighbourhood tabs, even in rural postcodes.
Sub-100ms TTFB: Frontend job pages (JobDetails.php) load pre-indexed records instantaneously.
99/100 Google PageSpeed Desktop & Grade A+ On-Page SEO: Fast server response times directly improved our Core Web Vitals and crawl budget across 1,700+ UK job listings.

If you are building location-aware SaaS applications in Laravel and PHP 8.4, pre-filtering Geofabrik PBF extracts with osmium-tool into an indexed local warehouse table is one of the cleanest architectural upgrades you can make.

You can see the live postcode Area Guide and salary tools in action at https://jobbri.co.uk.

DEV Community: Jobbri UK