Skip to content
Back to search

global-travel-intelligence

Global city ranking database and MCP server: GeoNames, WorldClim, Overture POIs/transport, Ookla broadband, Mobility Database GTFS, Eurostat tourism, and a FastMCP API.

Safety scan

A static text scan, not a guarantee. Select the badge to see what was scanned and every finding.

Badge for your README
findsafeskills safety rating

Markdown

[![findsafeskills safety rating](/api/listings/privateuseonlycodelol--global-travel-intelligence/badge.svg)](/listings/privateuseonlycodelol--global-travel-intelligence)

HTML

<a href="/listings/privateuseonlycodelol--global-travel-intelligence"><img src="/api/listings/privateuseonlycodelol--global-travel-intelligence/badge.svg" alt="findsafeskills safety rating" /></a>

Install

i

Install instructions from the README

As written by the repo's author. findsafeskills has not verified these steps.

python3 -m venv .venv
source .venv/bin/activate
pip install -r requirements.txt

Python 3.10+ is required. `rasterio` and `duckdb` ship manylinux wheels, so no GDAL install is needed on Linux/macOS.

All later commands run **from this project directory with the virtualenv active**. If you did not activate it, use `.venv/bin/python` explicitly instead of `python`. These are not bare-host commands: a machine without Python, PostgreSQL/PostGIS and the project files cannot run them as written.

What was scanned

The files the safety scan read, as written by the repository's author and shown as plain text. Lines with a finding are marked.

README.md · README · 688 lines
  1. # Global destinations DB (cities ≥ 40k)
  2. PostGIS bootstrap for a travel-ranking database covering every GeoNames city
  3. with **population ≥ 40,000** (~13k destinations after metro de-duplication),
  4. with climate, POI, airport, broadband, walkability/transit and optional monthly
  5. housing data attached.
  6. The goal is a **fixed number of bulk operations** instead of per-city API/S3
  7. calls:
  8. | Step | Source | Operation |
  9. | --- | --- | --- |
  10. | Cities | GeoNames `cities15000.zip` | one download, one `COPY` |
  11. | Airports | OurAirports `airports.csv` | one download, one PostGIS join |
  12. | Climate | WorldClim 2.1 `5m` GeoTIFFs | one local raster sample per variable, one `COPY` |
  13. | POIs | Overture Maps Places (S3 Parquet) | one DuckDB scan + grid hash join, one `COPY` |
  14. | Broadband | Ookla Open Data (S3 Parquet) | one DuckDB scan + grid hash join |
  15. | Walkability/transit | Overture Maps Transportation + Mobility Database GTFS | one transport scan + one CSV join |
  16. | Housing (optional) | Inside Airbnb city dumps | local CSV scan |
  17. | Tourism pressure (optional) | Eurostat NUTS 2 tourism + population | one API + GISCO boundary join |
  18. ## Files
  19. | File | Purpose |
  20. | --- | --- |
  21. | `bootstrap_global_40k.py` | Phase 1 pipeline (cities, airports, climate, POIs) |
  22. | `bootstrap_phase3.py` | Phase 3 pipeline (broadband, walkability/transit, optional housing) and `rank_destinations_v3` |
  23. | `bootstrap_transit_stops.py` | Overture Places public-transport facility counts within 5 km |
  24. | `bootstrap_supplemental.py` | optional official-data loaders (currently Eurostat NUTS 2 tourism pressure) |
  25. | `mcp_server.py` | Phase 4 FastMCP server (Streamable HTTP, optional SerpApi live pricing) |
  26. | `Dockerfile` | container image for the pipeline and MCP server |
  27. | `docker-compose.yml` | MCP service + optional local PostGIS profile |
  28. | `requirements.txt` | Python dependencies for the full pipeline |
  29. | `.env.example` | Supabase and SerpApi connection settings |
  30. | `mcp.client.example.json` | example HTTP MCP client entry for the local server |
  31. | `docs/DATA_SOURCES.md` | source-by-source provenance, licences, coverage and candidate sources |
  32. | `docs/COVERAGE_REPORT.md` | measured coverage snapshot for the global run |
  33. | `sql/rank_examples.sql` | Phase 1/v2 scorer examples |
  34. | `sql/rank_examples_v3.sql` | Phase 3/v3 scorer examples |
  35. | `tests/` | offline unit tests for the Phase 1, Phase 3 and MCP helpers |
  36. | `data_cache/` | downloaded source data (git-ignored) |
  37. ## 1. Install
  38. ```bash
  39. python3 -m venv .venv
  40. source .venv/bin/activate
  41. pip install -r requirements.txt
  42. ```
  43. Python 3.10+ is required. `rasterio` and `duckdb` ship manylinux wheels, so no
  44. GDAL install is needed on Linux/macOS.
  45. All later commands run **from this project directory with the virtualenv
  46. active**. If you did not activate it, use `.venv/bin/python` explicitly instead
  47. of `python`. These are not bare-host commands: a machine without Python,
  48. PostgreSQL/PostGIS and the project files cannot run them as written.
  49. ## 1b. Docker (recommended on Unraid / Tower)
  50. If the host Python lacks the `ssl` module or you do not want to install
  51. PostGIS on the host, run the project in Docker.
  52. ```bash
  53. cd /mnt/cache/appdata/travel
  54. cp .env.example .env
  55. # edit .env and set SUPABASE_DB_URL (and optionally SERPAPI_API_KEY)
  56. docker compose up -d --build
  57. curl http://127.0.0.1:8000/health
  58. ```
  59. The `mcp` service builds the full image and runs `mcp_server.py` on port 8000.
  60. Batch jobs use the same image:
  61. ```bash
  62. docker compose run --rm mcp python bootstrap_transit_stops.py
  63. docker compose run --rm mcp python bootstrap_phase3.py \
  64. --countries TR,PT,GB,FR,DE --skip-ookla --skip-gtfs
  65. ```
  66. For a fully local PostGIS database instead of Supabase:
  67. ```bash
  68. # in .env:
  69. # SUPABASE_DB_URL=postgresql://postgres:travel@db:5432/travel_global
  70. docker compose --profile local-db up -d --build
  71. docker compose run --rm mcp python bootstrap_global_40k.py
  72. ```
  73. The `db` service stores data in `./pgdata`; the app caches downloads in
  74. `./data_cache` and DuckDB spill files in `./duckdb_spill`.
  75. ```bash
  76. export SUPABASE_DB_URL="postgresql://postgres:<PASSWORD>@db.<PROJECT_REF>.supabase.co:5432/postgres"
  77. ```
  78. Notes:
  79. - Many new Supabase projects expose the direct `db.<ref>.supabase.co` host over
  80. **IPv6 only**. If your network has no IPv6, use the **session pooler**
  81. (dashboard → Connect → Session pooler):
  82. `postgresql://postgres.<ref>:<PASSWORD>@aws-0-<region>.pooler.supabase.com:5432/postgres`.
  83. - The script disables client-side prepared statements automatically for
  84. `pooler.supabase.com` and port `6543`. The **session pooler (5432)** is the
  85. safest choice because the pipeline uses `COPY ... FROM STDIN` and DDL.
  86. - Enable PostGIS once if `CREATE EXTENSION` is rejected:
  87. dashboard → Database → Extensions → `postgis`.
  88. - Free tier (500 MB) is enough: ~13k cities, ~157k climate rows and ~13k POI
  89. rows measured 57–76 MB including indexes depending on vacuum state (about
  90. 45 MB of that is `climate_monthly`; repeated upserts leave dead tuples until
  91. `VACUUM`).
  92. ## 3. Run
  93. Fast end-to-end check (~1–2 min; Luxembourg is the smallest useful test):
  94. ```bash
  95. python bootstrap_global_40k.py --countries LU
  96. ```
  97. Country pair from the original plan:
  98. ```bash
  99. python bootstrap_global_40k.py --countries SE,ES
  100. ```
  101. Full global run:
  102. ```bash
  103. python bootstrap_global_40k.py
  104. ```
  105. Useful flags:
  106. | Flag | Effect |
  107. | --- | --- |
  108. | `--countries SE,ES` | limit cities (still uses the same global schema) |
  109. | `--skip-pois` | skip the Overture/S3 scan |
  110. | `--skip-climate` | skip WorldClim sampling |
  111. | `--skip-airports` | skip airport matching |
  112. | `--no-metro-dedupe` | keep PPLA2 boroughs and same-municipality districts (`PPLX` is always excluded) |
  113. | `--dedupe-radius-km 20` | radius for merging `PPLA2` boroughs |
  114. | `--municipality-radius-km 15` | radius for merging same-municipality districts |
  115. | `--municipality-pop-ratio 5` | how much larger the parent city must be |
  116. | `--no-prune` | keep rows in scope that this run no longer selects |
  117. | `--min-pop 40000` | city population threshold |
  118. | `--airport-km 100` | airport search radius |
  119. | `--overture-release 2026-11-18.0` | pin a different Overture release |
  120. | `--db-url ...` | override `SUPABASE_DB_URL` |
  121. | `--query-only` | only run coverage + ranking queries |
  122. Run the offline tests with:
  123. ```bash
  124. python -m unittest discover -s tests -v
  125. ```
  126. ### Start the local services
  127. ```bash
  128. # Start PostgreSQL + the MCP server
  129. ./scripts/start_all.sh
  130. # Check server health
  131. ./scripts/status_mcp.sh
  132. # Stop only the MCP server
  133. ./scripts/stop_mcp.sh
  134. ```
  135. The local server listens on `http://127.0.0.1:8000/mcp`; the health probe is
  136. `http://127.0.0.1:8000/health`. `mcp.client.example.json` is a generic HTTP
  137. client entry; client-specific config formats may differ.
  138. The script is **idempotent**: re-running updates cities, climate and POIs in
  139. place. Each run is authoritative for its scope: a global run prunes cities that
  140. no longer pass the filters, and a `--countries` run prunes only within those
  141. countries. Use `--no-prune` to keep stale rows. Airport updates are restricted
  142. to the run's cities, so a small test run cannot wipe a global dataset's codes.
  143. ## 4. Verify
  144. ```bash
  145. python bootstrap_global_40k.py --query-only
  146. ```
  147. or from `psql`:
  148. ```sql
  149. SELECT COUNT(*) FROM destinations;
  150. SELECT COUNT(*) FROM climate_monthly;
  151. SELECT name, country_iso2, population, iata_codes, final_score,
  152. data_completeness, metrics
  153. FROM rank_destinations_v2(
  154. p_months => ARRAY[6],
  155. p_ideal_temp_min => 20.0,
  156. p_ideal_temp_max => 26.0,
  157. p_max_temp_c => 31.0,
  158. p_require_airport => TRUE,
  159. p_limit => 20
  160. );
  161. ```
  162. More examples in [`sql/rank_examples.sql`](sql/rank_examples.sql).
  163. ## 5. Measured Phase 1 local timings
  164. On a 12-core box with cached downloads and a local PostGIS 15:
  165. | Step | Global (13k cities) | SE+ES (~200 cities) | LU |
  166. | --- | --- | --- | --- |
  167. | schema + scorer | < 1 s | < 1 s | < 1 s |
  168. | GeoNames load + dedupe | ~2 s | < 1 s | < 1 s |
  169. | airport matching | ~5–10 s | < 1 s | < 1 s |
  170. | WorldClim sampling (3 × 12 rasters) | ~30–60 s | ~1.5 s | ~1 s |
  171. | Overture POI scan | ~5–15 min (network bound) | ~1.5 min | ~50 s |
  172. | verification queries | < 1 s | < 1 s | < 1 s |
  173. Reference run in this workspace: **13,101 cities**, 11,073 with an IATA code,
  174. 157,188 climate rows, 76 MB total. A full-global POI pass depends on sustained
  175. S3 bandwidth; the country-scoped passes (LU, SE+ES) are the recommended smoke
  176. test before committing to it.
  177. The first DuckDB/Overture query is slow because DuckDB fetches Parquet metadata
  178. from S3; subsequent queries in the same process are much faster. Keep a warm
  179. `data_cache/` between runs. Long S3 scans retry up to 5 times on transient
  180. DNS/HTTP errors; each retry restarts the scan, so budget extra time on a flaky
  181. network.
  182. ## 6. What was fixed relative to the draft
  183. The draft's architecture is sound. These are the correctness/scaling changes in
  184. this implementation:
  185. 1. **NYC boroughs are `PPLA2`, not `PPLX`.** Filtering only `PPLX` leaves
  186. Manhattan, Brooklyn, Queens and the Bronx in the table (verified against the
  187. live GeoNames dump). Two merge rules run after the `PPLX` filter:
  188. - `PPLA2` boroughs within 20 km of a ≥2× larger city (NYC boroughs);
  189. - any record sharing country/admin1/admin2/admin3 within 15 km of a ≥5×
  190. larger city (Barcelona's Sant Martí, Madrid's Chamartín, the Paris
  191. arrondissements, Warsaw's districts).
  192. The 5× threshold matters: admin3 is a county in some countries, so a lower
  193. ratio merged Venice into Mestre and Grevenbroich into Neuss. Separate
  194. municipalities in the same metro (L'Hospitalet, Badalona, Cambridge,
  195. Oakland) are kept. The result is **~13,100 cities** (13,101 in the reference
  196. run; raw ≥40k after the `PPLX` filter: 14,156). Disable both rules with
  197. `--no-metro-dedupe`.
  198. 2. **Country-scoped Overture scans.** A `--countries SE,ES` run now issues one
  199. Parquet scan per country envelope and unions the results by place `id`, so
  200. each scan can prune its own row groups. An OR-of-envelopes filter is
  201. logically equivalent but blocks DuckDB's row-group pruning and turned a
  202. 2-country run into a 10+ minute global scan.
  203. 3. **Antimeridian-safe grid cells.** Longitude cells are wrapped with
  204. `((x % 3600) + 3600) % 3600`; the original 5×3 halo breaks at ±180°.
  205. 4. **Null-safe Overture flags.** `list_contains(NULL, ...)` returns `NULL`, not
  206. `FALSE`; flags are wrapped in `COALESCE`.
  207. 5. **Closed businesses excluded.** `operating_status = 'permanently_closed'` is
  208. filtered out (NULL is treated as open).
  209. 6. **Cuisine entropy is actually about cuisine.** Generic
  210. `taxonomy.primary = 'restaurant'` is excluded from the entropy buckets;
  211. otherwise ~2k generic restaurants in Barcelona alone dominate one bucket and
  212. flatten the signal.
  213. 7. **Airport reset is scoped.** The draft's unconditional reset/update would
  214. clear airports from cities outside a later `--countries` run. Both reset and
  215. match are restricted to the current run's `destination_id`s.
  216. 8. **Schema compatibility check.** `CREATE TABLE IF NOT EXISTS` silently keeps
  217. an incompatible Phase-1 table; the script now fails with a column-level
  218. message instead.
  219. 9. **Download hardening.** Retries, `.part` files and zip validation.
  220. 10. **Pooler support.** Prepared statements are disabled for Supabase poolers.
  221. 11. **Deterministic rankings.** Ties break on population, not physical row
  222. order.
  223. 12. **Verification respects `--countries`**, so a test run does not rank rows
  224. left over from earlier runs.
  225. 13. **`restaurant_count` and cuisine entropy are separated.** The draft
  226. computed the count from the same CTE that drops generic `restaurant`
  227. labels, undercounting restaurants by roughly a third. The count now
  228. includes every restaurant; only the entropy buckets exclude the generic
  229. label.
  230. 14. **Large airports are preferred.** Sorting purely by distance put Teterboro
  231. ahead of JFK and San Carlos ahead of San Jose. The query now orders by
  232. `large_airport` first, then distance, and stores 3 codes (the draft's
  233. `[1:4]` slice stored 4).
  234. ### Phase 3 fixes relative to the draft
  235. 1. **Ookla tile coordinates are longitude/latitude centroids**, not Web Mercator
  236. indices; the grid join uses `tile_x`/`tile_y` directly. The median is
  237. **tests-weighted**, not a plain median.
  238. 2. **Antimeridian-safe Ookla/Overture/listing cells** use wrapped longitude
  239. buckets, and each country gets its own pruned Parquet envelope (grouped into
  240. 45° bands above 40 countries), with opposite-edge bands for cities just
  241. across the dateline.
  242. 3. **Mobility Database column names are the v2 ones**: `data_type='gtfs'`,
  243. `status='active'`, `redirect.id`, and the four
  244. `location.bounding_box.*` columns. `gtfs_rt` and `gbfs` feeds are excluded.
  245. 4. **Overture transportation values are verified**: `subtype` is `road`/`rail`
  246. and pedestrian classes include `footway`, `pedestrian`, `living_street`,
  247. `steps`, `cycleway`, `path` and `bridleway`. The walkability score blends
  248. the 2 km pedestrian ratio (60%) with the log of pedestrian segment count
  249. (40%), so a tiny settlement cannot win on a fluke ratio of one footway.
  250. 5. **GTFS-only transit is scored.** The draft's transit percentile required
  251. rail data, so `--skip-overture-transport` silently dropped GTFS from the
  252. score. The v3 scorer computes a rail percentile where rail exists and adds a
  253. GTFS-feed component, so GTFS still contributes without the huge transport
  254. scan.
  255. 6. **Provenance is structured for every dimension**, including mobility,
  256. broadband and housing, and empty dimensions are omitted instead of appearing
  257. as `{}`.
  258. 7. **Ties break on population**, not physical row order, as in v2.
  259. 8. **The short-stay exit-gate example no longer requires GTFS.** Requiring a
  260. feed for a three-day city break discarded cities that simply do not publish
  261. one; the corrected example is in `sql/rank_examples_v3.sql`.
  262. 9. **Inside Airbnb is a real optional loader**, not just a schema table. It
  263. matches listings by coordinates, filters 30-night-capable listings and
  264. records the sample size.
  265. 10. **The Schengen rule is explicit**: 29 countries, applied only to stays
  266. longer than 90 days for non-EU/EEA passports, with a legal caveat in the
  267. docs.
  268. 11. **Phase 4 split quote caches** key flight quotes by route+dates and hotel
  269. quotes by city+dates, so changing the departure airport only refetches
  270. flights.
  271. 12. **Percentile scores are non-degenerate and missing data is neutral.**
  272. Relative dimensions map to `0.05..0.95` (single/all-equal -> `0.5`), and
  273. missing dimensions are imputed at `0.5` for the score. Complete-but-equal
  274. data no longer scores zero, and a climate-only row cannot win just by
  275. renormalizing its best dimension to 100%; `data_completeness` still exposes
  276. thin evidence.
  277. 13. **Inside Airbnb currency is explicit.** The column is
  278. `median_monthly_local`, not `median_monthly_usd`; the loader performs no FX
  279. conversion, and the docs say to zero the housing weight for cross-currency
  280. searches.
  281. 14. **Public transport access is measured directly.** Overture Places transit
  282. facilities within 5 km provide a stop-density signal distinct from GTFS
  283. feed presence; GTFS remains a separate coverage/frequency proxy.
  284. Walkability combines the pedestrian-road ratio and pedestrian segment
  285. count from Overture Transportation.
  286. ## 7. Data caveats (read before trusting a ranking)
  287. - **City population** comes from GeoNames city-proper records and can be years
  288. out of date. It is not a metro population. The source file is
  289. `cities15000.zip`, so `--min-pop` below 15,000 cannot add smaller places.
  290. - **Metro de-duplication is a heuristic.** It removes sub-city records
  291. (Manhattan/Brooklyn, Tokyo wards, Barcelona's Sant Martí). Separate
  292. municipalities in the same metro — L'Hospitalet, Badalona, Cambridge,
  293. Oakland, Pantin, Montreuil — are kept, because they are legally distinct
  294. cities, and can rank beside the core city because their 5 km POI radii
  295. overlap. London boroughs such as Westminster and Islington are `PPLA3` with
  296. distinct admin3 codes, so they survive too; filter them or add a
  297. metro-cluster key if you want one row per metro. Use
  298. `--no-metro-dedupe` for every non-`PPLX` GeoNames row.
  299. - **WorldClim 2.1 is a 1970–2000 climatology**, not recent daily ERA5. The
  300. `median_temp_max_c` / `median_temp_min_c` columns are monthly **means of daily
  301. max/min** (the Phase-1 column names are retained); `precip_mm` is the monthly
  302. total. WorldClim's own terms allow academic/non-commercial use and require
  303. permission for redistribution or commercial use; see section 12.
  304. - **`avg_rainy_days` and `avg_hot_days_30c` are intentionally NULL** until the
  305. Phase-2/4 Open-Meteo enrichment. The scorer falls back to `precip_mm` for the
  306. rain component.
  307. - **Overture POIs** use the place's bbox center and a 5 km Haversine radius.
  308. A large park or mall whose center is outside 5 km is not counted. Coverage is
  309. excellent in North America/Europe and thinner elsewhere; a zero count can
  310. mean "no data", not "no restaurants".
  311. - **`tourism_pressure` is partial, not global.** The supplemental Eurostat
  312. loader fills NUTS 2 tourism nights for EU/EFTA regions (1,405 cities in the
  313. reference run); non-EU cities remain NULL. All cities in the same NUTS 2
  314. region share the same regional value, so it is a pressure proxy, not an
  315. independent city observation. There is no single official
  316. city-level global tourism dataset, so `s_low_crowding` is only present where
  317. Eurostat publishes a value. The v2 scorer renormalizes missing crowding; the
  318. v3 scorer neutral-imputes it at 0.5 and can reach 0.95 completeness. A high
  319. score with low completeness (e.g. 0.35) means thin evidence — treat it as
  320. provisional.
  321. - **Some Chinese district records survive.** GeoNames tags district-level
  322. records such as Pudong and Minhang as `PPLA3`/`PPLA4` with district
  323. populations, not as subdivisions of a shared municipality code, so the dedupe
  324. rules keep them. Filter `country_iso2 <> 'CN'` or add a district exclusion if
  325. that skews your results.
  326. - **Airports are straight-line within 100 km.** That can match an airport across
  327. water or a mountain range that is not practically reachable. Large airports
  328. are preferred over nearer medium/GA fields, then sorted by distance; only the
  329. 3 best IATA codes are stored. Without that preference, Teterboro outranks JFK
  330. for New York and San Carlos outranks San Jose for San Francisco.
  331. - **GEONAMES `PPLX` exclusion** removes Tokyo's special wards but not all
  332. borough-style records; see the metro pass above.
  333. ## 8. Scaling notes for Phase 4
  334. `destination_id` is the stable anchor (`gn-<geonameid>`). Later sources can join
  335. on it without schema rewrites:
  336. - Open-Meteo daily ERA5: fill `avg_rainy_days` / `avg_hot_days_30c` for finalists.
  337. - Ookla Open Data (global Parquet): broadband metrics keyed by tile, spatially
  338. joined to `centroid`.
  339. - Overture Maps Transportation: walkability and rail segment counts keyed by
  340. segment bbox center.
  341. - Mobility Database GTFS feeds: active-feed coverage keyed by feed bbox and
  342. joined by `destination_id`.
  343. For global Overture runs, 5–15 minutes is bandwidth-bound. If that becomes the
  344. bottleneck, materialize a filtered `places` Parquet subset in your own bucket
  345. once per Overture release and point `OVERTURE_S3_ROOT` at it.
  346. ## 9. Phase 3: broadband, walkability, transit and housing
  347. Phase 3 enriches the Phase 1 `destinations` table and installs
  348. `rank_destinations_v3`. It assumes Phase 1 has already run on the same database.
  349. ```bash
  350. # Fast partial load: Ookla + Mobility Database GTFS, no huge transportation scan
  351. python bootstrap_phase3.py --countries SE,ES --skip-overture-transport
  352. # Full Phase 3 for a small country (recommended first full test)
  353. python bootstrap_phase3.py --countries LU
  354. # Optional Inside Airbnb medians from local city dumps
  355. python bootstrap_phase3.py --countries ES \
  356. --skip-ookla --skip-overture-transport --skip-gtfs \
  357. --airbnb-dir data_cache/airbnb
  358. # Optional official EU tourism pressure (NUTS 2, Eurostat)
  359. python bootstrap_supplemental.py --eurostat-tourism --year 2024
  360. # Public transport access: Overture Places transit facilities within 5 km
  361. python bootstrap_transit_stops.py
  362. ```
  363. | Flag | Effect |
  364. | --- | --- |
  365. | `--countries SE,ES` | only enrich cities in these countries |
  366. | `--min-pop 40000` | only enrich cities at/above this population |
  367. | `--skip-ookla` | skip the Ookla broadband scan |
  368. | `--skip-overture-transport` | skip the Overture transportation scan |
  369. | `--skip-gtfs` | skip Mobility Database GTFS |
  370. | `--skip-airbnb` | skip Inside Airbnb even if `--airbnb-dir` is set |
  371. | `--ookla-year 2026` | Ookla dataset year |
  372. | `--ookla-quarter 2` | Ookla quarter (1–4) |
  373. | `--ookla-km 5` | broadband search radius |
  374. | `--overture-release 2026-11-18.0` | pin an Overture release |
  375. | `--airbnb-dir DIR` | directory containing `**/listings.csv.gz` |
  376. | `--airbnb-room-type "Entire home/apt"` | room-type filter; pass `''` for all |
  377. | `--db-url ...` | override `SUPABASE_DB_URL` |
  378. | `--cache-dir DIR` | download/cache directory |
  379. | `--query-only` | only print coverage and a sample v3 ranking |
  380. `bootstrap_phase3.py` is idempotent. A country-scoped run only updates the
  381. cities in scope; re-running replaces the columns owned by each loader (Ookla
  382. and housing are replaced, while Overture and GTFS preserve each other's
  383. columns).
  384. ### What Phase 3 adds
  385. | Table | Contents |
  386. | --- | --- |
  387. | `broadband_metrics` | tests-weighted median down/up Mbps, weighted latency, tile count, total tests |
  388. | `mobility_metrics` | 2 km pedestrian-road ratio, 2 km road/pedestrian segment counts, 5 km rail segment count, active GTFS feed count, 5 km transit facility count |
  389. | `housing_monthly` | median 30-night monthly cost in the listing's local currency, plus listing sample size (optional) |
  390. | `tourism_pressure` | Eurostat NUTS 2 annual tourist nights and nights per NUTS 2 resident (EU coverage only) |
  391. `rank_destinations_v3` scores eight dimensions with weights that sum to 1:
  392. climate, culture, food, walkability, transit, broadband, low crowding and
  393. housing cost. Missing dimensions are **neutral-imputed at 0.5 for the score**
  394. so a data-rich city is not punished for having measured, mediocre values;
  395. `data_completeness` reports how much real evidence the row has, so check it
  396. before trusting a high score. The Phase 3 tables are one row per city, so
  397. a global load adds only a few MB on top of Phase 1. Direct SQL calls use the
  398. extended-stay weights by default; the MCP server applies the
  399. short-stay/extended-stay weight profiles automatically. Relative dimensions
  400. (culture, food, walkability, transit, broadband, crowding, housing) are mapped
  401. to `0.05..0.95`; a single available value or an all-equal set scores `0.5`
  402. instead of collapsing to zero.
  403. The transport sub-scores are explicit:
  404. - **Walkability** = 60% pedestrian-road ratio percentile + 40% pedestrian
  405. segment-count percentile (Overture Transportation, 2 km).
  406. - **Transit** = 40% transit-facility count percentile + 30% active GTFS feed
  407. percentile + 30% rail-segment percentile (available components renormalized).
  408. - **Public transport access** is the 5 km count of Overture Places
  409. `bus_station`, `train_station`, `metro_station`,
  410. `public_transit_facility_or_service` and similar facilities. Walkability itself is 60% pedestrian-ratio rank
  411. plus 40% log pedestrian-segment-count rank, so both density and absolute
  412. walkable-network size matter.
  413. ```sql
  414. SELECT name, country_iso2, population, final_score, data_completeness,
  415. metrics, provenance
  416. FROM rank_destinations_v3(
  417. p_months => ARRAY[6],
  418. p_ideal_temp_min => 20.0,
  419. p_ideal_temp_max => 26.0,
  420. p_require_airport => TRUE,
  421. p_min_broadband_mbps => 50,
  422. p_require_gtfs => TRUE,
  423. p_limit => 10
  424. );
  425. ```
  426. More examples, including the short-stay and four-month extended-stay exit
  427. gates, are in [`sql/rank_examples_v3.sql`](sql/rank_examples_v3.sql).
  428. Large transportation scans are memory-bound. DuckDB gets **8 GiB** by default
  429. and spills to `data_cache/duckdb_tmp` (falling back to the system temp
  430. directory), so that path must be writable. Override with
  431. `DUCKDB_MEMORY_LIMIT=12GiB` / `DUCKDB_TEMP_LIMIT=32GiB`, or run
  432. country-scoped. A 2 GiB limit OOMs on Spain+Sweden; 8 GiB completed it.
  433. ### Phase 3 data caveats
  434. - **Ookla** values are quarterly and tile-based. The scorer uses a
  435. **tests-weighted median** of tile medians within 5 km, not a user-level
  436. speed test. A city with no tiles has no broadband row.
  437. - **Overture transportation** uses each segment's bbox center. Long motorway or
  438. rail segments whose center lies outside the radius are not counted even if
  439. part of the segment is nearby. Pedestrian classes are `footway`,
  440. `pedestrian`, `living_street`, `steps`, `path` and `bridleway`; the
  441. denominator is every road segment within 2 km. The walk score combines the
  442. pedestrian-road ratio (60%) with the pedestrian segment count (40%). The
  443. 2026-11-21 run loaded walk/rail for **1,131 cities** across DE, GB, TR, ES,
  444. FR, PT, SE and LU; it is not global.
  445. - **GTFS coverage** comes from Mobility Database feed bounding boxes, not from
  446. stop locations or service frequency. A national feed can cover many cities;
  447. feeds wider than 15° are ignored so one national file does not mark a whole
  448. continent as transit-served. `gtfs_active_feeds` counts active, non-superseded
  449. static GTFS feeds whose bbox contains the city. Feeds whose bbox crosses the
  450. antimeridian are skipped (rare).
  451. - **Transit stop counts use Overture Places facilities** (`bus_station`,
  452. `train_station`, `metro_station`, `public_transit_facility_or_service`,
  453. `light_rail_and_subway_station`, and similar) within 5 km. The reference run
  454. populated **9,008 cities**. This counts stations/facilities, not individual
  455. bus stop poles, and is not a frequency, reliability or journey-time measure.
  456. Overture coverage varies by city.
  457. - **Inside Airbnb** is optional and city-by-city. The loader matches listing
  458. coordinates to the nearest destination within 10 km, keeps listings that
  459. allow a 30-night stay (`minimum_nights <= 30` and `maximum_nights >= 30`,
  460. with `0` treated as unlimited), and estimates the monthly cost as
  461. `nightly_price * 30`. It does not
  462. model weekly/monthly discounts, taxes or cleaning fees. A median from a
  463. handful of listings is noisy; `sample_listings` is reported so you can apply
  464. your own minimum. **Inside Airbnb prices are in the listing's local
  465. currency**, not USD: the loader does not perform FX conversion, so
  466. `median_monthly_local` is only comparable within a currency area. Zero the
  467. `p_w_housing` weight for cross-currency searches, or convert the column
  468. before scoring. Listings are attributed to the nearest destination within
  469. 10 km, so a small-town dump near a larger city can inflate the larger city's
  470. sample.
  471. - **The Schengen filter** implements the 90/180-day short-stay rule for
  472. non-EU/EEA passports by excluding all 29 Schengen countries on stays longer
  473. than 90 days. The SQL function applies `p_exclude_schengen` whenever it is
  474. true; the MCP server is what gates it on `duration > 90 and not
  475. passport_eu_eea`. It is a conservative planning heuristic, not legal advice,
  476. and it does not model long-stay visas or bilateral agreements.
  477. - **Licences/attribution** differ by source (Ookla Open Data, Mobility
  478. Database, Overture Maps, Inside Airbnb). Check the upstream terms before
  479. redistributing the derived tables.
  480. ## 10. Phase 4: MCP server and live pricing
  481. `mcp_server.py` exposes three tools over Streamable HTTP:
  482. | Tool | Purpose |
  483. | --- | --- |
  484. | `rank_destinations` | stage 1 SQL ranking, optional stage 2 live flight/hotel pricing |
  485. | `inspect_destination` | one city's climate curve, POIs, mobility, broadband, housing, tourism pressure and provenance |
  486. | `refresh_live_quotes` | force-refresh SerpApi quotes for 1–5 finalists |
  487. ```bash
  488. export SUPABASE_DB_URL="postgresql://..."
  489. export SERPAPI_API_KEY="..." # optional; without it stage 2 is skipped
  490. export QUOTE_TTL_HOURS="12"
  491. python mcp_server.py
  492. ```
  493. The server creates `flight_quote_cache` and `hotel_quote_cache` on startup.
  494. Flight quotes are keyed by `(origin_iata, destination_iata, start_date,
  495. end_date)`; hotel quotes are keyed by `(destination_id, start_date, end_date)`,
  496. so changing the departure airport does not refetch hotels.
  497. Stage 2 runs only for short stays with exact dates and an origin airport. It
  498. calls SerpApi for the finalist pool (default 8, max 15), concurrently, and
  499. re-ranks by static score plus live trip cost and flight duration. Without
  500. dates, an origin airport or `SERPAPI_API_KEY`, the server returns stage 1
  501. rankings untouched with `live_pricing_status` explaining why. Flight pricing
  502. uses the first stored `iata_codes` entry (large-airport-first, then distance),
  503. so in a multi-airport metro it may not be the cheapest international gateway
  504. (e.g. LGA before JFK for New York).
  505. Recommended first request after the database is loaded:
  506. ```bash
  507. curl -s http://localhost:8000/health
  508. ```
  509. Then point your MCP client at `http://localhost:8000/mcp`.
  510. ### Deploy
  511. ```bash
  512. docker build -t travel-ranking .
  513. docker run --rm -p 8000:8000 \
  514. -e SUPABASE_DB_URL="postgresql://..." \
  515. -e MCP_PATH="/mcp-<random-32-char-hex>" \
  516. travel-ranking
  517. ```
  518. ChatGPT cannot reach `localhost`; use a public HTTPS host (Railway, Fly.io,
  519. Render, Cloud Run) or OpenAI's Secure MCP Tunnel. The server is read-only except
  520. for the two quote-cache tables, so a public endpoint relies on an unguessable
  521. `MCP_PATH`; set `MCP_PATH` to a random value rather than leaving `/mcp` public.
  522. Add `SERPAPI_API_KEY` only when live pricing is wanted.
  523. ## 11. Measured Phase 3 local timings
  524. These are from the local PostGIS 15 workspace used for development, with warm
  525. downloads and a single-country scope:
  526. | Step | LU | Notes |
  527. | --- | --- | --- |
  528. | schema + scorer | < 1 s | `CREATE OR REPLACE FUNCTION` |
  529. | Ookla (LU bbox) | ~10 s | one HTTPS Parquet scan |
  530. | Overture transportation (LU bbox) | ~15 s | one S3 scan |
  531. | Mobility Database GTFS join | ~1 s | global CSV, 2.7k usable feeds |
  532. | Inside Airbnb (local fixture) | < 1 s | local files |
  533. | Transit stops, global Overture Places | ~25 min | 9,008 cities; S3-bound |
  534. | v3 ranking | < 1 s | expected sub-second on 13k rows |
  535. Full global Phase 3 is bandwidth-bound, especially the Overture transportation
  536. scan. Use `--countries` for a smoke test, then a single global pass when the
  537. network is stable.
  538. ## 12. Third-party data, licences and provenance
  539. Every dataset in this project is third-party data. The only first-party
  540. artifacts are the derived scores, percentiles, `data_completeness` and the
  541. loader code. The full source-by-source registry, with URLs, licences, coverage,
  542. granularity and implementation status, is in
  543. [`docs/DATA_SOURCES.md`](docs/DATA_SOURCES.md); a measured snapshot is in
  544. [`docs/COVERAGE_REPORT.md`](docs/COVERAGE_REPORT.md).
  545. Implemented third-party sources:
  546. | Dimension | Source | Licence / terms | Coverage |
  547. | --- | --- | --- | --- |
  548. | Cities | GeoNames `cities15000` | CC BY; attribution required | global |
  549. | Airports | OurAirports | Unlicense/public domain | global |
  550. | Climate | WorldClim 2.1 `5m` | **non-commercial academic use; redistribution/commercial use requires permission** | global |
  551. | POIs | Overture Maps Places | CDLA-Permissive 2.0 (some source datasets Apache 2.0/CC0) | global |
  552. | Walkability/rail | Overture Maps Transportation | **ODbL; © OpenStreetMap contributors; share-alike for derived databases** | partial: 1,131 cities across DE/GB/TR/ES/FR/PT/SE/LU |
  553. | Broadband | Ookla Open Data | **CC BY-NC-SA 4.0; non-commercial; attribution required** | global tiles |
  554. | GTFS coverage | Mobility Database | catalog metadata CC0 1.0; individual feeds retain provider licences | global catalog |
  555. | Public transport stops | Overture Places transit facilities | CDLA-Permissive 2.0 / per-provider terms | global where Overture Places has facilities |
  556. | Housing (optional) | Inside Airbnb | CC BY 4.0; local currency, no FX conversion | city/regional dumps |
  557. | Tourism pressure (optional) | Eurostat `tour_occ_nin2m` + `demo_r_pjangrp3` | Eurostat reuse with attribution (CC BY 4.0 for editorial content; statistical data reuse subject to Eurostat notice) | EU/EFTA NUTS 2 |
  558. | Live quotes (optional) | SerpApi Google Flights/Hotels | proprietary API terms; not open data | query-time |
  559. **Important commercial-use caveats.** WorldClim and Ookla both restrict
  560. commercial use/redistribution; Overture Transportation is ODbL and
  561. share-alike; individual GTFS feeds and Inside Airbnb data carry their own
  562. terms. Do not redistribute the derived database commercially without checking
  563. those terms. `SerpApi` data is subject to SerpApi/Google terms.
  564. **Missing data policy.** Null means unavailable; it is never replaced with a
  565. fabricated city-level value. The v3 scorer neutral-imputes a missing dimension
  566. at `0.5` **only for ranking arithmetic**, and `data_completeness` continues to
  567. report the missing evidence. Where a global city-level official source does
  568. not exist (for example safety/crime or rent), the dimension remains absent and
  569. the candidate official sources are listed in `docs/DATA_SOURCES.md` rather
  570. than filled with a low-quality proxy.
  571. The supplementary official source currently implemented is Eurostat NUTS 2
  572. tourism pressure:
  573. ```bash
  574. python bootstrap_supplemental.py --eurostat-tourism --year 2024
  575. ```
  576. It assigns a city to a NUTS 2 region by official GISCO boundaries, stores the
  577. NUTS 2 annual nights and nights per NUTS 2 resident, and leaves non-EU or
  578. unpublished regions NULL.

Ratings and comments

Sign in to rate this skill.

Sign in

Free to use. If it’s helped, pass it on.