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:

• pg_trgm extension for trigram similarity matching
• search_vector column with weighted GIN index
• Auto-updating trigger for vector maintenance
• Composite scoring algorithm

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: 2025_10_10_192151_add_fts_to_airports_table.php

Components:

1. pg_trgm extension - Trigram-based similarity matching
2. search_vector column - Stores precomputed text search vectors
3. GIN index - Fast full-text search queries
4. Auto-update trigger - Maintains search vectors on INSERT/UPDATE

Search Vector Column

ALTER TABLE airports ADD COLUMN search_vector TEXT;

CREATE INDEX airports_search_vector_gin
ON airports
USING GIN(to_tsvector('english', COALESCE(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 with trigrams
• 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

Check extension:

SELECT * FROM pg_extension WHERE extname = 'pg_trgm';

Verify index:

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

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 to_tsvector('english', COALESCE(search_vector, ''))
      @@ to_tsquery('english', 'london:*')
LIMIT 10;

Expected: Index Scan using airports_search_vector_gin

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