| """ |
| Stage 5: build $TRIP_WORLD_ROOT/pois.parquet |
| |
| For every POI that survives the Trip World build pipeline (i.e. appears in |
| checkins_consolidated.parquet, which already enforces popularity >= 2 AND |
| region density >= 100), inherit per-POI metadata (FSQ-OS + Google + review |
| counts) from a precursor build at $PRECURSOR_BUILD_ROOT/pois.parquet, and |
| re-map the `locality` column with the Trip World metro map. |
| |
| The $PRECURSOR_BUILD_ROOT env var should point at a previous output of this |
| same pipeline whose POI metadata we want to extend; if you are bootstrapping |
| from scratch you can either skip this stage or supply an empty parquet — POIs |
| not present in the precursor will simply carry NULLs for the inherited |
| FSQ-OS / Google / review-count fields. |
| """ |
| import duckdb, json, time |
| from pathlib import Path |
| import os |
|
|
| ROOT = Path(os.environ.get("TRIP_WORLD_ROOT", Path(__file__).resolve().parent.parent)) |
| INC = ROOT / "_intermediate" / "checkins_consolidated.parquet" |
| V1_POIS = str(Path(os.environ["PRECURSOR_BUILD_ROOT"]) / "pois.parquet") |
| MM = ROOT / "_intermediate" / "metro_map.parquet" |
| OUT = ROOT / "pois.parquet" |
|
|
| t0 = time.time() |
| def step(msg): print(f"[{time.time()-t0:6.1f}s] {msg}", flush=True) |
|
|
| con = duckdb.connect() |
| con.execute("PRAGMA threads=32") |
| con.execute("SET memory_limit='32GB'") |
| con.execute(f"SET temp_directory='{os.environ.get('DUCKDB_TMP_DIR', '/tmp/duckdb_trip_world')}'") |
|
|
| step("collecting per-POI visit counts and locality from clean checkins ...") |
| con.execute(f""" |
| CREATE TABLE poi_facts AS |
| SELECT |
| venue_id AS fsq_place_id, |
| ANY_VALUE(region_id) AS locality, -- region_id == metro QID under clean map |
| ANY_VALUE(venue_category) AS venue_category, |
| ANY_VALUE(venue_schema) AS venue_schema, |
| COUNT(*) AS n_checkins, |
| COUNT(DISTINCT user_id) AS n_users_visited |
| FROM '{INC}' GROUP BY 1 |
| """) |
| n_pois = con.execute("SELECT COUNT(*) FROM poi_facts").fetchone()[0] |
| step(f" {n_pois:,} surviving POIs") |
|
|
| step("loading precursor pois.parquet for metadata inheritance ...") |
| con.execute(f"CREATE TABLE prev AS SELECT * FROM '{V1_POIS}'") |
|
|
| step("writing clean pois.parquet (left join on precursor metadata, locality from clean) ...") |
| con.execute(f""" |
| COPY ( |
| SELECT |
| pf.fsq_place_id, |
| pf.locality, |
| pf.venue_category, |
| pf.venue_schema, |
| pf.n_checkins::BIGINT AS n_checkins, |
| pf.n_users_visited::BIGINT AS n_users_visited, |
| p.google_cid, p.google_name, p.google_full_address, p.google_address, |
| p.google_website, p.google_rating, p.google_num_reviews, p.google_categories, |
| p.google_place_id, p.google_gmaps_url, |
| COALESCE(p.google_meta_source, 'none') AS google_meta_source, |
| COALESCE(p.has_google_metadata, FALSE) AS has_google_metadata, |
| COALESCE(p.n_reviews, 0)::BIGINT AS n_reviews, |
| COALESCE(p.n_reviews_with_text, 0)::BIGINT AS n_reviews_with_text, |
| p.review_source, |
| COALESCE(p.has_reviews, FALSE) AS has_reviews |
| FROM poi_facts pf |
| LEFT JOIN prev p USING (fsq_place_id) |
| ) TO '{OUT}' (FORMAT PARQUET, COMPRESSION 'zstd', ROW_GROUP_SIZE 200000) |
| """) |
|
|
| |
| r = con.execute(f""" |
| SELECT COUNT(*), |
| SUM(CASE WHEN has_google_metadata THEN 1 ELSE 0 END), |
| SUM(CASE WHEN has_reviews THEN 1 ELSE 0 END), |
| SUM(n_reviews), SUM(n_reviews_with_text) |
| FROM '{OUT}' |
| """).fetchone() |
| sz = OUT.stat().st_size / 1e6 |
| print() |
| print("=== Stage 5 output ===") |
| print(f" file: {OUT} ({sz:.1f} MB)") |
| print(f" POIs: {r[0]:,}") |
| print(f" with Google meta: {r[1]:,} ({r[1]/r[0]*100:.1f}%)") |
| print(f" with reviews: {r[2]:,} ({r[2]/r[0]*100:.1f}%)") |
| print(f" total reviews: {r[3]:,}") |
| print(f" total reviews w/text:{r[4]:,}") |
| print(f" total elapsed: {time.time()-t0:.1f}s") |
|
|
