Itz-Agasta [Sun, 9 Aug 2026 06:56:31 +0000 (12:26 +0530)]
Do not drop the place_classtype tables on migration
Migration and frontend restart do not happen at the same time. The documented order is to migrate first and restart the API afterwards, so there is a window where the old frontend code is still running against a migrated database. That code caches which place_classtype_* tables exist and would query tables that are already gone.
Leave the tables in place instead and document in the migration guide how to drop them manually once all frontends run the new version.
Itz-Agasta [Thu, 6 Aug 2026 09:02:07 +0000 (14:32 +0530)]
Drop the place_classtype tables on migration
Nothing creates or reads them anymore, so a migrated database would keep
carrying them around for nothing. On a planet that is 428 tables and a
good 8GB.
The migration runs after the POI backfill, which still needs the table
names to work out which rows to fill in.
Itz-Agasta [Thu, 6 Aug 2026 09:01:52 +0000 (14:31 +0530)]
Stop copying the place_classtype tables into SQLite
The conversion mirrored every table into the SQLite file and built a
spatial index on each of them. It also wrote a minimal stand-in for
pg_tables, which was only there so that get_class_table() could look the
tables up on SQLite. None of it is needed now that category search
matches on class/type.
Itz-Agasta [Thu, 6 Aug 2026 09:01:17 +0000 (14:31 +0530)]
Remove Connection.get_class_table()
The POI and near searches went through this to find the table for a
category. Both query the categories column directly since the switch, so
the helper has had no callers left.
Itz-Agasta [Thu, 6 Aug 2026 09:01:06 +0000 (14:31 +0530)]
Stop creating the place_classtype tables from special phrases
The special phrase import created one table per class/type pair, indexed
it on centroid and place_id and granted the webuser access to it. Nothing
reads those tables anymore, so all of that goes. What is left is
collecting the phrases and handing them to the tokenizer.
The --min option only decided which of the tables were created, it never
filtered the phrases themselves, so it has no meaning without them. It is
removed together with get_classtype_pairs().
The table counters in the statistics handler would report zero forever.
Itz-Agasta [Thu, 6 Aug 2026 09:00:55 +0000 (14:30 +0530)]
Stop maintaining the place_classtype tables on updates
Category search reads the categories column now, so the rows the insert
and delete triggers keep in sync are not read by anything anymore. Both
did a pg_tables lookup on every placex insert and delete to find out
whether a table for the class/type exists, which is pure overhead on
diff updates.
The webuser does not need read access to the tables either.
Itz-Agasta [Wed, 5 Aug 2026 14:59:32 +0000 (20:29 +0530)]
Simplify the POI backfill by filtering the classtype tables
Only consider place_classtype_* tables whose name consists of valid
ltree label characters. The class and type of the rows to update then
are valid labels by construction and do not need to be sanitized.
Itz-Agasta [Sat, 1 Aug 2026 06:43:48 +0000 (12:13 +0530)]
Restrict the POI category backfill to searchable categories
Only class/type combinations that have a place_classtype_* table can be
reached through a special phrase, and those are exactly the ones master
resolved through Connection.get_class_table(). Look the tables up the
same way and restrict the UPDATE to rows that match one. That takes the
planet from 303,652,952 to 46,222,253 rows and skips the step entirely
on databases that never imported special phrases.
The value expression now matches backfill_categories, so a POI also
picks up the osm.place.* category from its extratags.
Categories without a table are no longer found by the [key=value]
syntax on a migrated database. Those need a reimport.
Also unskip the [club=scout] search test. It asserted on the reported
class/type, which is the row's own main tag rather than the queried
category. Check for the two deterministic objects instead; the third
result is subject to deduplication.
The backfill in the previous migration only filled the rank_address < 26
rows that place linking needs. Category search reads the column for POIs
as well, so a migrated database would find almost nothing for them.
Only the class/type category can be derived from placex, which is the
same one the lazy backfill in placex_update adds. Categories from
secondary tags still need a reimport.
The earlier migration does not create idx_placex_categories anymore, so
this only cleans up after databases that ran it before that change. Drop
it before creating the combined index, so that the two are never on disk
at the same time.
The near/small-radius branch searched placex directly while the other
branch went through the place_classtype tables. Now that both go through
the categories column, the only remaining differences were the importance
column and the linked_place_id filter, so fold them into one query.
The remaining geometry conditions in the near branch move to the centroid
as well, so that the combined centroid/categories index can serve the
spatial and the category condition at once. Linked places are now excluded
in all cases, as they are duplicates of the place they are linked to.
Match categories on the centroid instead of the geometry
Category search used to run against the place_classtype tables, which
hold a centroid and index it. Moving to the categories column also
moved the searches onto placex.geometry, which changed what the filter
means and traded a small centroid index for the 22GB geometry index
over the whole of placex.
Put both searches back on the centroid, where the combined
centroid/categories index can serve them. This also reverts the
geometry prefilter in the near search: it was there to prune the
non-spatial categories index, and the combined index prunes on its
own.
Note that the near_radius branch of the POI search is left alone. It
queried placex.geometry before this change as well.
Index placex centroid for the SQLite category search
SQLite has no ltree, so category search matches on class/type, but it
still needs the spatial part of the query to be indexed. Only
placex.geometry had a spatial index, so moving the category searches
onto the centroid would leave them without one and the SpatialIndex
subquery would match nothing.
Use a combined centroid/categories index for category search
The plain ltree index on categories is not spatial, so a category
search bounded by a viewbox or a search area had to combine it with
a second index and AND two large bitmaps together. On a full planet
that made the near search take 510ms and the bounded POI search
106ms, against 22ms and 0.7ms for the place_classtype tables they
replace.
Put the centroid in front of the categories in a combined GiST index
instead. Measured on a full planet import, that brings the POI search
to 1.2ms and the near search to 74ms.
Two details on the definition. The centroid comes first because a
GiST index is ineffective when its first column has few distinct
values, which is exactly what the lossy signatures of the categories
column are; it also builds faster and smaller (12:22 and 5125MB
against 19:45 and 5376MB for the other order). The index is not
partial because the predicate would otherwise make it unusable for
queries on the centroid alone, which cost only 10 to 20% on the
category queries but takes a plain centroid lookup from a sequential
scan to an index scan.
siglen=8 keeps the size manageable. The default for gist__ltree_ops
is 28 bytes, which on a planet with full category coverage would put
the index well above the 22GB it takes now.
The index gets a new name, so the now unused idx_placex_categories
is simply dropped from the earlier migration rather than replaced.
Since that migration is already released, the new index needs a
migration step of its own.
Introduce search_area to prune with the geometry GiST index and
keep the centroid containment as a recheck. When rank_address > 9
and the geometry is an area, use the geometry; otherwise expand
the centroid by 0.05. Update the join to intersect with search_area
and use ST_CoveredBy(search_area) for containment.
Keep the object class/type in the result category field
Revert the per-category match tracking; report the row's own class/type
as before and collapse the POI query into a single indexed lookup.
Re-skip the club=scout scenario: a merged placex row cannot surface a
non-winning searched category under this model.(we will fix it soon)
Rework category matching: move to sql functions, reject invalid
categories
Relocate CategoryMatch to sqlalchemy_functions.py alongside the other
compiled functions. Reject class/type special tokens that cannot form a
valid ltree at token collection, so search never emits an unindexed
class/type fallback; category_filter now always hits the ltree index.
Update the S-token test mocks to carry a class.
Populate categories in the placex test fixture, drop the obsolete place_classtype table helper and its scenarios, and unskip the key/value category search BDD test.
Query the categories column for POI and near search
Replace the place_classtype_* table lookups in PoiSearch and NearSearch with queries against the ltree categories column (categories <@ 'osm.<class>.<type>'), backed by the GiST index. SQLite, which has no ltree, falls back to class/type equality. Because a merged placex row now carries several categories, both searches report the searched category rather than the row's class/type. get_class_table() is kept with a TODO; the place_classtype_* tables are removed in a follow-up PR.
Add ltree categories column to the API placex schema
Introduce a CategoryArray SQLAlchemy type that maps to the native ltree[] column on PostgreSQL and to comma-separated text on SQLite. Register the categories column on the placex table so the search side can query it.
PostgreSQL 12 is out of support upstream and lacks operator class
parameters, which are needed to keep the ltree GiST indexes at a
reasonable size. Raise the minimum version to 13 and run the ubuntu-22
CI job against PostgreSQL 13.
Return empty result list for category searches without matches
A /search request using the [key=value] category syntax together with a
near-phrase crashed with a 500 error whenever the search legitimately
found nothing.
Two places in ForwardGeocoder.lookup_pois() indexed into an empty list:
* sort_and_cut_results() accessed results[0] without checking that any
results were found. The guard was lost in 87a8c246.
* execute_searches() accessed searches[0] when the near-phrase produced
no search interpretations at all. lookup_pois() guarded this with
'if query', which is always true because QueryStruct has no __bool__.
Use 'if searches' instead, as lookup() already does.
Adapt test fixtures and assertions for the new categories column and single-row merge model. Verify that categories are generated, propagated, and queried correctly using ltree operators.
Add migration for categories on existing databases
Add a schema migration that introduces the categories ltree[]
column to place and placex, backfills it from existing class/type data and extratags->place values for rank_address < 26 with triggers disabled, creates the required indexes, and runs ANALYZE after the bulk update.
Update SQL ranking and triggers to use ltree categories
Adapt is_rankable_place(), compute_place_rank(), and the
placex_update trigger to query the categories column using ltree
containment operators (<@) instead of class/type comparisons.
compute_place_rank() now iterates all categories and selects the
best rank. Boundary linking extracts the place type from
categories instead of extratags.
Add ltree category generation and single-row merge model in Lua
Introduce sanitize_label() and get_category() to derive ltree
categories from OSM class/type tags. Merge multi-tag OSM objects
into a single place row with all categories in the new ltree[]
column, replacing the previous one-row-per-tag write_place() /
write_row() model.
Also add build_extratags() and simplify the grab_name_parts()
fallback logic.
Add ltree categories column to place/placex schema
Add categories ltree[] column to place and placex tables with a conditional GIST index. Requires the ltree extension. Stores derived OSM categories as ltree paths for efficient containment queries on multi-tag objects.
Sarah Hoffmann [Fri, 12 Jun 2026 13:19:39 +0000 (15:19 +0200)]
remove use of postcode analyser
This is no longer needed as postcodes are now parsed out of the
query directly. Skips over loading the analyser with id @postcode
emitting a warning. Drops the now useless postcodes analyser.
projects / nominatim.git / log