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, wof_search_place, wof_search_term. Search functions below. Not granted: wof_geometries, wof_shard, wof_locality_knn, timezone_slice. Prefer wof_place_search over querying the search matviews directly.

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¶

Experimental. geo.wof_place_search ranks places by name. Signature, columns, ranking, and the function name can change without deprecation. geo_settlement_wof_id, geo_county_wof_id, and geo.wof_in_place are stable.

After cuno_wake_geo, do not reuse cached search wof_ids.

geo.wof_place_search(query, max_rows DEFAULT 10, locales DEFAULT '{eng}',
placetypes DEFAULT NULL, mode DEFAULT 'auto', focus DEFAULT NULL,
include_catalog DEFAULT false)

Name queries need two or more characters (SQLSTATE 22023). % and _ are stripped. Cap is 50 rows.

Argument	Description
query	Name, numeric id, or wof:<id>. Optional type:locality (or a bare placetype token).
max_rows	1–50. Typeahead: 25.
locales	BCP-47 tags, preferred first. Display and complete retrieve use the first.
placetypes	Hard filter. On a miss, retries all types and sets type_relaxed.
mode	complete (prefix), match (full name / typo), auto (complete, then match if a 4+ character prefix misses).
focus	Optional point. Boosts nearby rows; not a filter.
include_catalog	Include identities outside the matching set. Default false.

A comma (Manhattan, USA) is a match parent filter. A trailing country or region alias (Houston US, Austin TX, Chi Mex) is too: complete and auto prefix-scan the name both with and without that parent. parent_terms on _wof_place_search_complete holds the alias (NULL = no filter). match still uses RUM. Call wof_place_search, not the helper.

score is ln(1+population)/(1+min_zoom), times focus decay. Prefixes shorter than four letters omit high-zoom hamlets. Columns include region, confidence (gap from #1 to #2), mode_used, and requires_child_match. If requires_child_match is true, live rows never store that id; filter with geo.wof_in_place.

geo.wof_placetypes() lists catalog types and counts.

SELECT wof_id, name, placetype, country, region, score, confidence, mode_used
FROM geo.wof_place_search('Hou', 8, ARRAY['eng'], ARRAY['locality'], 'complete');

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

SELECT placetype, n FROM geo.wof_placetypes();

Pick one wof_id. No 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;