Who's On First places¶

Live rows carry matched settlement / admin IDs. Resolve in geo.wof_places; geo.wof_hierarchy is containment (county, region, country).

SELECT: wof_places, wof_names, wof_hierarchy, wof_supersession, wof_concordances, wof_release, timezone, timezone_release, catalog_publication. Search functions below. Not granted: wof_geometries, wof_shard, wof_locality_knn, timezone_slice.

Columns on live views¶

Column	Meaning
geo_settlement_wof_id	Place the point is in or near (borough, locality, or localadmin).
geo_settlement_distance_m	0 means in. Anything else is meters to that place, 1–8000. Null only when the settlement id is null.
geo_county_wof_id	Finest exclusive administration covering the point: county, else region, else country.

On the point tables: sub_v2_spawns, sub_v2_gyms, sub_v2_pokestops, sub_v2_stations. Weather isn't tagged. Legacy sub_raids / sub_quests already include parent geography.

Autocomplete / resolve a name¶

geo.wof_place_search(query, max_rows DEFAULT 10, locales DEFAULT '{eng}', placetypes DEFAULT NULL) is prefix-first. After three characters it also allows trigram typos. % and _ in the query are stripped (they aren't wildcards). At most 50 rows.

Argument	Meaning
query	Typed text, a numeric WOF id, or wof:<id>. Optional type:locality (or a bare placetype token next to a name).
max_rows	Cap (1–50).
locales	BCP-47 language tags, most-preferred first (spa, eng, …). Display name and rank prefer the first locale, then other locales, then any preferred name.
placetypes	Hard filter ({locality,borough}). If that set has no prefix/id hit, the function retries all types and sets type_relaxed true (did-you-mean).

geo.wof_placetypes() lists types in the catalog with counts.

SELECT wof_id, name, placetype, country, score, matched_name, matched_language, type_relaxed
FROM geo.wof_place_search('Hou', 8, ARRAY['eng'], ARRAY['locality']);

SELECT wof_id, name, placetype
FROM geo.wof_place_search('wof:101725629');

SELECT placetype, n FROM geo.wof_placetypes();

Pick one wof_id. No combined metro aliases (DFW, etc.).

Filter live rows by a place and everything under it¶

geo.wof_in_place(place_id): that id plus descendants. Nested places, not other cities in the containing county.

SELECT s.spawn_id, s.geo_settlement_wof_id, s.geo_settlement_distance_m
FROM pogo_live.sub_v2_spawns AS s
WHERE s.expires > now()
  AND (
    s.geo_settlement_wof_id IN (SELECT wof_id FROM geo.wof_in_place(101725629))
    OR s.geo_county_wof_id IN (SELECT wof_id FROM geo.wof_in_place(101725629))
  );

Replace 101725629 (Houston, Texas) with the wof_id you chose.

Direct id / hierarchy¶

SELECT wof_id, name, placetype, country
FROM geo.wof_places
WHERE wof_id = 101725629;

SELECT a.depth, p.wof_id, p.placetype, p.name
FROM geo.wof_hierarchy AS a
JOIN geo.wof_places AS p ON p.wof_id = a.parent_id
WHERE a.child_id = 101725629
ORDER BY a.branch, a.depth;