Location hierarchy

Problem

In my previous post, I mentioned that we used Airtable to extract the itineraries provided by our operators. Due to everything was done in Airtable AI, the activites and locations association is sometimes done wrongly. Notable example is an activity is associated to Ubud (town on Bali, Indonesia), and sometimes it is associated to Bali.

System diagram

Diagrams

This diagram shows the product-level hierarchy that solves the core problem: a traveler searching “Bali” now also surfaces activities tagged only as “Ubud” or “Gianyar”, because they are descendants in the tree.

graph TD
    L1["🌍 Layer 1: Continent<br/><i>e.g. Asia, Europe</i>"]
    L2["🏳️ Layer 2: Country<br/><i>e.g. Indonesia, Japan</i>"]
    L3["🗺️ Layer 3: Admin1 -- State / Province<br/><i>e.g. Bali, Tokyo-to</i>"]
    L4["📍 Layer 4: Admin2 -- County / District<br/><i>e.g. Gianyar Regency, Shibuya</i>"]
    L5["🏙️ Layer 5: City / Locality<br/><i>e.g. Ubud, Shibuya Ward</i>"]
    L6["🎯 Optional: Place / POI<br/><i>e.g. Tirta Empul Temple</i>"]

    L1 --> L2
    L2 --> L3
    L3 --> L4
    L4 --> L5
    L5 --> L6

    style L1 fill:#4A90D9,color:#fff
    style L2 fill:#5BA85A,color:#fff
    style L3 fill:#E8A838,color:#fff
    style L4 fill:#D96B4A,color:#fff
    style L5 fill:#9B59B6,color:#fff
    style L6 fill:#7F8C8D,color:#fff

This diagram shows the operational pipeline — how legacy flat data is enriched via Google Places and reorganized into the hierarchy without breaking existing associations.

flowchart TD
    A["Legacy City Record\n(name + country_code only)"]
    B["Google Places API\ncity_search()"]
    C{"PlaceFinder\nlocation_type_from()"}
    D["HierarchyBuilder\nfind_or_create parents"]
    E["City record updated\n+ google_place_id\n+ latitude / longitude\n+ location_type\n+ parent_id"]
    F{"DuplicateMerger\nsame google_place_id?"}
    G["Merge: repoint\nactivities / itineraries\nto canonical record"]

    A -->|enrich task| B
    B -->|address components| C
    C -->|continent / country\nadmin1 / admin2 / city\nplace| D
    D --> E
    E --> F
    F -->|yes| G
    F -->|no| H["Data ready\nfor recursive queries"]
    G --> H

    style A fill:#BDC3C7
    style E fill:#5BA85A,color:#fff
    style H fill:#4A90D9,color:#fff