PostGIS Spatial Queries in Laravel: How We Resolve Booking Zones at Scale

Ride-booking platforms live and die by location. When a customer opens the app, we need to know: what service zone covers this pickup point? That answer determines which vehicle classes are available, what the fare structure is, whether there's a surge multiplier, and whether the service is active.

We need that answer fast, on every fare estimate request, for every concurrent user. Here's how it's implemented.

The Zone Model

Zones are polygons. Each zone has a human-readable polygon column (GeoJSON, used for display and management) and a geom column (MySQL GEOMETRY type with a spatial index, used for queries):

// Modules/BookingConfig/app/Models/Zone.php

use MatanYadaev\EloquentSpatial\Objects\MultiPolygon;
use MatanYadaev\EloquentSpatial\Traits\HasSpatial;

class Zone extends Model
{
    use HasUuids, SoftDeletes, HasSpatial;

    protected $casts = [
        'polygon'    => 'array',               // GeoJSON for display
        'geom'       => MultiPolygon::class,   // PostGIS geometry
        'is_active'  => 'boolean',
    ];
}

matanyadaev/laravel-eloquent-spatial <!-- REFERRAL: matanyadaev/laravel-eloquent-spatial --> adds the HasSpatial trait and the whereContains() / whereWithin() query scopes. It handles casting between PHP objects and MySQL geometry binary format.

The Migration: Dual Geometry Columns

Schema::create('zones', function (Blueprint $table) {
    $table->uuid('id')->primary();
    $table->unsignedBigInteger('country_id');
    $table->string('name', 100);

    // GeoJSON — for API responses, admin UI, polygon editing
    $table->json('polygon')->nullable();

    // MySQL GEOMETRY — for PostGIS spatial queries
    $table->geometry('geom', subtype: 'multipolygon');
    $table->spatialIndex('geom');  // Critical for performance

    $table->boolean('is_active')->default(true);
    $table->index(['country_id', 'is_active']);
});

The spatialIndex on geom tells MySQL to build an R-tree index on the geometry column. Without it, ST_Contains() does a full table scan.

The Zone Resolver: Three-Layer Cache

// Modules/BookingConfig/app/Services/ZoneResolverService.php

class ZoneResolverService
{
    public function resolve(float $lat, float $lng, ?int $countryId = null): ?Zone
    {
        $cacheKey = $this->cacheKey($lat, $lng, $countryId);

        $zoneId = Cache::remember(
            $cacheKey,
            config('bookingconfig.cache.ttl.zone_resolve'),  // 100 seconds
            fn () => $this->findZoneId($lat, $lng, $countryId)
        );

        return $zoneId ? Zone::with(['country', 'state', 'city'])->find($zoneId) : null;
    }

    private function findZoneId(float $lat, float $lng, ?int $countryId): ?string
    {
        $point = new Point($lat, $lng);

        return Zone::query()
            ->when($countryId, fn ($q) => $q->where('country_id', $countryId))
            ->where('is_active', true)
            ->whereContains('geom', $point)   // PostGIS ST_Contains()
            ->value('id');
    }

    private function cacheKey(float $lat, float $lng, ?int $countryId): string
    {
        // Grid-based quantization: ~111 meters per 0.001 degrees
        // Nearby pickup points (e.g., same street) resolve to the same cache key
        $gridSize = 0.001;
        $latGrid  = floor($lat / $gridSize) * $gridSize;
        $lngGrid  = floor($lng / $gridSize) * $gridSize;

        return sprintf('bc:zone:resolve:%s:%.3f:%.3f', $countryId, $latGrid, $lngGrid);
    }
}

The cache key is grid-quantized. A 0.001-degree grid is approximately 111 meters × 111 meters. Any pickup within that ~12,000 m² cell gets the same cache entry. A user who moves 30 meters doesn't invalidate the cache — they're still in the same zone.

This means the PostGIS query runs once per grid cell per 100-second window, regardless of how many concurrent requests hit that cell. In a busy pickup zone at 8 AM, a single DB query serves hundreds of concurrent fare estimates.

PHP Ray-Casting Fallback

The Zone model includes a PHP-based point-in-polygon check for cases where the PostGIS index can't be used (tests with SQLite, export tools, admin verification):

// Modules/BookingConfig/app/Models/Zone.php

public function containsPoint(float $lat, float $lng): bool
{
    $polygon = $this->polygon;
    if (empty($polygon['coordinates'][0])) {
        return false;
    }

    $vertices = $polygon['coordinates'][0];
    $count    = count($vertices);
    $inside   = false;

    for ($i = 0, $j = $count - 1; $i < $count; $j = $i++) {
        $xi = (float) $vertices[$i][0];  // lng
        $yi = (float) $vertices[$i][1];  // lat
        $xj = (float) $vertices[$j][0];
        $yj = (float) $vertices[$j][1];

        $intersects = (($yi > $lat) !== ($yj > $lat))
            && ($lng < ($xj - $xi) * ($lat - $yi) / ($yj - $yi) + $xi);

        if ($intersects) {
            $inside = !$inside;
        }
    }

    return $inside;
}

The ray-casting algorithm casts a horizontal ray from the test point and counts polygon edge crossings. An odd number of crossings means the point is inside the polygon.

The bounding box pre-filter avoids running the ray-casting calculation on zones that obviously don't contain the point:

public function getBoundingBox(): ?array
{
    $polygon = $this->polygon;
    if (empty($polygon['coordinates'][0])) return null;

    $lngs = array_column($polygon['coordinates'][0], 0);
    $lats = array_column($polygon['coordinates'][0], 1);

    return [
        'min_lat' => min($lats), 'max_lat' => max($lats),
        'min_lng' => min($lngs), 'max_lng' => max($lngs),
    ];
}

public function boundingBoxContains(float $lat, float $lng): bool
{
    $bbox = $this->getBoundingBox();
    return $bbox
        && $lat >= $bbox['min_lat'] && $lat <= $bbox['max_lat']
        && $lng >= $bbox['min_lng'] && $lng <= $bbox['max_lng'];
}

Check bounding box first. If the point is outside the bounding box, skip the ray-casting. Bounding box checks are four comparisons. Ray-casting is proportional to the number of polygon vertices.

The Result

A fare estimate request resolves the zone in one of three paths:

1. Grid cache hit (Redis) — sub-millisecond
2. Cache miss + PostGIS query (MySQL with spatial index) — 5–20ms
3. Cache miss + no spatial support (test environment, SQLite) — PHP ray-casting fallback

In production, path 1 handles the overwhelming majority of requests. The PostGIS query runs only once per grid cell per TTL window.