Airport Search

PostgreSQL full-text search implementation for intelligent airport lookup with composite ranking algorithm. Searches across 9,068+ airports with weighted relevance scoring based on IATA codes, city names, airport names, and airport classification.

Quick Start

Basic Search

use App\Models\Airport;

// Search for airports
$results = Airport::active()
    ->fullTextSearch('london')
    ->limit(10)
    ->get();

foreach ($results as $airport) {
    echo "{$airport->iata_code} - {$airport->name} ({$airport->city})\n";
}

Output:

LGW - Gatwick Airport (London)
LHR - Heathrow Airport (London)
LCY - London City Airport (London)

FilamentPHP Integration

Select::make('departure_airport')
    ->label('From')
    ->searchable()
    ->getSearchResultsUsing(fn (string $search): array =>
        Airport::active()
            ->fullTextSearch($search)
            ->limit(10)
            ->get()
            ->mapWithKeys(fn (Airport $airport): array =>
                [$airport->iata_code => "{$airport->iata_code} - {$airport->name} ({$airport->city})"]
            )
            ->toArray()
    )
    ->required()

Architecture

Search Components

Database Layer:

• search_vector column of type tsvector, with a weighted GIN index (airports_search_vector_gin_idx USING GIN (search_vector))
• Auto-updating trigger for vector maintenance
• to_tsquery prefix matching (term:*) for partial word search, plus LOWER(col) LIKE 'x%' for IATA/city/name prefix scoring
• Composite scoring algorithm

The unaccent extension is enabled in the database (2026_03_26_095800_enable_unaccent_extension.php) but is not wired into the airports search trigger or query, so accent folding does not apply here (e.g., “Bogotá” is not normalized to “Bogota” for this search). There is no pg_trgm extension in this search path.

Model Layer:

• scopeFullTextSearch() - Main search scope
• scopeActive() - Filter active airports
• Helper methods for display formatting

Ranking Algorithm

The search uses a composite scoring system:

relevance_score =
    (FTS Relevance × 1000) +           -- Full-text match quality
    (Exact IATA Match × 10,000) +       -- Exact IATA code (e.g., "LHR")
    (IATA Prefix Match × 5,000) +       -- IATA starts with query
    (Exact City Match × 3,000) +        -- Exact city name
    (City Prefix Match × 1,500) +       -- City starts with query
    (Name Prefix Match × 500) +         -- Airport name starts with query
    (Airport Type Bonus)                -- Based on classification

Airport Type Bonuses:

• Large airports: 2,500 points (96 major hubs)
• Medium airports: 400 points
• Small airports: 100 points

Weighted Fields

Field	Weight	Priority	Purpose
iata_code	A	Highest	Primary identifier (e.g., “LHR”)
icao_code	A	Highest	Secondary identifier (e.g., “EGLL”)
city	B	High	Location search (e.g., “London”)
name	C	Medium	Airport name (e.g., “Heathrow”)
country	D	Low	Country-based filtering

Database Schema

Migration Structure

File: 2026_01_16_123135_add_search_vector_to_airports_table.php

Components:

1. search_vector column (tsvector) - Stores precomputed weighted text search vectors
2. GIN index - Fast full-text search queries, built directly on the column
3. Auto-update trigger - Maintains search vectors on INSERT/UPDATE

Search Vector Column

ALTER TABLE airports ADD COLUMN search_vector tsvector;

CREATE INDEX IF NOT EXISTS airports_search_vector_gin_idx
ON airports
USING GIN (search_vector);

Auto-Update Trigger

CREATE FUNCTION airports_search_vector_trigger() RETURNS trigger AS $$
BEGIN
    NEW.search_vector :=
        setweight(to_tsvector('english', COALESCE(NEW.iata_code, '')), 'A') ||
        setweight(to_tsvector('english', COALESCE(NEW.icao_code, '')), 'A') ||
        setweight(to_tsvector('english', COALESCE(NEW.city, '')), 'B') ||
        setweight(to_tsvector('english', COALESCE(NEW.name, '')), 'C') ||
        setweight(to_tsvector('english', COALESCE(NEW.country, '')), 'D');
    RETURN NEW;
END
$$ LANGUAGE plpgsql;

CREATE TRIGGER airports_search_vector_update
BEFORE INSERT OR UPDATE ON airports
FOR EACH ROW EXECUTE FUNCTION airports_search_vector_trigger();

Large Airport Classification

96 major international hubs receive a 2,500-point ranking bonus:

Europe & UK (32 airports):

• London: LHR, LGW, MAN, EDI
• France: CDG, ORY
• Germany: FRA, MUC, BER
• Spain: MAD, BCN
• Italy: FCO, MXP, VCE
• Plus: AMS, IST, ZRH, VIE, CPH, ARN, OSL, HEL, DUB, LIS, ATH, WAW, PRG, BUD, BRU

Asia-Pacific (30 airports):

• Major hubs: SIN, HKG, ICN, NRT, HND, KIX
• China: PVG, PEK, CAN, CTU, SZX, SHA, CKG, XIY
• Southeast Asia: BKK, KUL, MNL, CGK
• India: DEL, BOM, BLR

North America (29 airports):

• US West: LAX, SFO, SEA
• US Central: ORD, DEN, DFW, IAH, MSP, DTW, CLT, ATL
• US East: JFK, EWR, IAD, BOS, MIA, MCO, PHX, LAS
• Canada: YYZ, YVR, YUL, YYC

Middle East (9 airports): DXB, AUH, DOH, JED, RUH, CAI, TLV, AMM, KWI

Latin America (10 airports): MEX, PTY, CUN, BOG, LIM, GRU, GIG, EZE, SCL

Oceania (5 airports): SYD, MEL, BNE, PER, AKL

Africa (6 airports): JNB, CPT, ADD, NBO, LOS, CMN

Usage Examples

Search by IATA Code

// Exact IATA match (10,000 points bonus)
$results = Airport::active()
    ->fullTextSearch('LHR')
    ->limit(5)
    ->get();

// Returns: Heathrow (LHR) first

Search by City

// Exact city match (3,000 points bonus)
$results = Airport::active()
    ->fullTextSearch('barcelona')
    ->limit(5)
    ->get();

// BCN gets additional 2,500 points for being large_airport

Combining with Other Scopes

// Search + filter by country
$results = Airport::active()
    ->fullTextSearch('charles')
    ->country('FR')
    ->limit(5)
    ->get();

// Search + filter by type
$majorHubs = Airport::active()
    ->fullTextSearch('new york')
    ->type('large_airport')
    ->get();

Performance

Index Performance

GIN Index Benefits:

• Sub-millisecond search across 9,068+ airports
• Efficient prefix matching via to_tsquery (term:*)
• Scales to millions of records
• Low maintenance overhead

Query Performance:

// Typical search: ~2-5ms
$results = Airport::active()->fullTextSearch('london')->limit(10)->get();

// Large result set: ~10-20ms
$results = Airport::active()->fullTextSearch('international')->limit(100)->get();

Memory Optimization

// Use limit() for dropdown searches
->fullTextSearch($search)
->limit(10)
->get()

// Use cursor() for large exports
Airport::active()
    ->fullTextSearch($search)
    ->cursor()
    ->each(function ($airport) {
        // Process one at a time
    });

Troubleshooting

Search Returns No Results

Verify the column type is tsvector:

SELECT column_name, data_type FROM information_schema.columns
WHERE table_name = 'airports' AND column_name = 'search_vector';

Verify index:

SELECT indexname FROM pg_indexes
WHERE tablename = 'airports'
AND indexname = 'airports_search_vector_gin_idx';

Rebuild vectors:

php artisan tinker
>>> DB::statement("UPDATE airports SET search_vector =
    setweight(to_tsvector('english', COALESCE(iata_code, '')), 'A') ||
    setweight(to_tsvector('english', COALESCE(icao_code, '')), 'A') ||
    setweight(to_tsvector('english', COALESCE(city, '')), 'B') ||
    setweight(to_tsvector('english', COALESCE(name, '')), 'C') ||
    setweight(to_tsvector('english', COALESCE(country, '')), 'D')");

Performance Issues

Check query plan:

EXPLAIN ANALYZE
SELECT * FROM airports
WHERE COALESCE(search_vector, ''::tsvector)
      @@ to_tsquery('english', 'london:*')
LIMIT 10;

Expected: Bitmap Index Scan using airports_search_vector_gin_idx

Testing

test('full text search returns relevant results', function () {
    $results = Airport::active()
        ->fullTextSearch('london')
        ->get();

    expect($results)
        ->not->toBeEmpty()
        ->and($results->first()->city)->toContain('London');
});

test('exact IATA match ranks highest', function () {
    $results = Airport::active()
        ->fullTextSearch('lhr')
        ->get();

    expect($results->first()->iata_code)->toBe('LHR');
});

Related Documentation

• Flight Search UI - FilamentPHP interface using airport search
• AerTicket Integration - Flight search consuming airport data