From: Sarah Hoffmann Date: Fri, 17 Feb 2023 14:51:55 +0000 (+0100) Subject: Merge pull request #2983 from lonvia/improve-reverse-place-node-lookup X-Git-Tag: v4.3.0~101 X-Git-Url: https://git.openstreetmap.org/nominatim.git/commitdiff_plain/92e2f5ca8e270b4a236afac7242bc1e7fbf35c52?hp=3405dbf90ee650be80aa80c474d08da0286f0ed2 Merge pull request #2983 from lonvia/improve-reverse-place-node-lookup Improve reverse place node lookup --- diff --git a/lib-php/ReverseGeocode.php b/lib-php/ReverseGeocode.php index d12e4da3..712f1480 100644 --- a/lib-php/ReverseGeocode.php +++ b/lib-php/ReverseGeocode.php @@ -122,12 +122,13 @@ class ReverseGeocode $sSQL .= ' FROM placex'; $sSQL .= ' WHERE osm_type = \'N\''; $sSQL .= ' AND country_code = \''.$sCountryCode.'\''; - $sSQL .= ' AND rank_search < 26 '; // needed to select right index + $sSQL .= ' AND rank_address between 4 and 25'; // needed to select right index $sSQL .= ' AND rank_search between 5 and ' .min(25, $iMaxRank); - $sSQL .= ' AND class = \'place\' AND type != \'postcode\''; + $sSQL .= ' AND type != \'postcode\''; $sSQL .= ' AND name IS NOT NULL '; $sSQL .= ' and indexed_status = 0 and linked_place_id is null'; - $sSQL .= ' AND ST_DWithin('.$sPointSQL.', geometry, 1.8)) p '; + $sSQL .= ' AND ST_Buffer(geometry, reverse_place_diameter(rank_search)) && '.$sPointSQL; + $sSQL .= ') as a '; $sSQL .= 'WHERE distance <= reverse_place_diameter(rank_search)'; $sSQL .= ' ORDER BY rank_search DESC, distance ASC'; $sSQL .= ' LIMIT 1'; @@ -216,23 +217,18 @@ class ReverseGeocode $sSQL .= ' ST_distance('.$sPointSQL.', geometry) as distance'; $sSQL .= ' FROM placex'; $sSQL .= ' WHERE osm_type = \'N\''; - // using rank_search because of a better differentiation - // for place nodes at rank_address 16 $sSQL .= ' AND rank_search > '.$iRankSearch; $sSQL .= ' AND rank_search <= '.$iMaxRank; - $sSQL .= ' AND rank_search < 26 '; // needed to select right index - $sSQL .= ' AND rank_address > 0'; - $sSQL .= ' AND class = \'place\''; + $sSQL .= ' AND rank_address between 4 and 25'; // needed to select right index $sSQL .= ' AND type != \'postcode\''; $sSQL .= ' AND name IS NOT NULL '; $sSQL .= ' AND indexed_status = 0 AND linked_place_id is null'; - $sSQL .= ' AND ST_DWithin('.$sPointSQL.', geometry, reverse_place_diameter('.$iRankSearch.'::smallint))'; - $sSQL .= ' ORDER BY distance ASC,'; - $sSQL .= ' rank_address DESC'; - $sSQL .= ' limit 500) as a'; - $sSQL .= ' WHERE ST_CONTAINS((SELECT geometry FROM placex WHERE place_id = '.$iPlaceID.'), geometry )'; + $sSQL .= ' AND ST_Buffer(geometry, reverse_place_diameter(rank_search)) && '.$sPointSQL; + $sSQL .= ' ORDER BY rank_search DESC, distance ASC'; + $sSQL .= ' limit 100) as a'; + $sSQL .= ' WHERE ST_Contains((SELECT geometry FROM placex WHERE place_id = '.$iPlaceID.'), geometry )'; $sSQL .= ' AND distance <= reverse_place_diameter(rank_search)'; - $sSQL .= ' ORDER BY distance ASC, rank_search DESC'; + $sSQL .= ' ORDER BY rank_search DESC, distance ASC'; $sSQL .= ' LIMIT 1'; Debug::printSQL($sSQL); diff --git a/lib-sql/indices.sql b/lib-sql/indices.sql index 9130fb52..ed078895 100644 --- a/lib-sql/indices.sql +++ b/lib-sql/indices.sql @@ -30,6 +30,13 @@ CREATE INDEX IF NOT EXISTS idx_placex_geometry_reverse_lookupPolygon AND rank_address between 4 and 25 AND type != 'postcode' AND name is not null AND indexed_status = 0 AND linked_place_id is null; --- +-- used in reverse large area lookup +CREATE INDEX IF NOT EXISTS idx_placex_geometry_reverse_lookupPlaceNode + ON placex USING gist (ST_Buffer(geometry, reverse_place_diameter(rank_search))) + {{db.tablespace.search_index}} + WHERE rank_address between 4 and 25 AND type != 'postcode' + AND name is not null AND linked_place_id is null AND osm_type = 'N'; +--- CREATE INDEX IF NOT EXISTS idx_osmline_parent_place_id ON location_property_osmline USING BTREE (parent_place_id) {{db.tablespace.search_index}} WHERE parent_place_id is not null; diff --git a/lib-sql/tables.sql b/lib-sql/tables.sql index d576485e..17216b50 100644 --- a/lib-sql/tables.sql +++ b/lib-sql/tables.sql @@ -190,7 +190,6 @@ CREATE INDEX idx_placex_geometry_buildings ON placex -- Usage: - linking of similar named places to boundaries -- - linking of place nodes with same type to boundaries --- - lookupPolygon() CREATE INDEX idx_placex_geometry_placenode ON placex USING {{postgres.spgist_geom}} (geometry) {{db.tablespace.address_index}} WHERE osm_type = 'N' and rank_search < 26 diff --git a/nominatim/tools/migration.py b/nominatim/tools/migration.py index 7d117a8c..0c88493b 100644 --- a/nominatim/tools/migration.py +++ b/nominatim/tools/migration.py @@ -48,7 +48,8 @@ def migrate(config: Configuration, paths: Any) -> int: has_run_migration = False for version, func in _MIGRATION_FUNCTIONS: - if db_version <= version: + if db_version < version or \ + (db_version == (3, 5, 0, 99) and version == (3, 5, 0, 99)): title = func.__doc__ or '' LOG.warning("Running: %s (%s)", title.split('\n', 1)[0], version) kwargs = dict(conn=conn, config=config, paths=paths) @@ -371,3 +372,16 @@ def enable_forward_dependencies(conn: Connection, **_: Any) -> None: ON planet_osm_rels USING gin (parts) WITH (fastupdate=off)""") cur.execute("ANALYZE planet_osm_ways") + + +@_migration(4, 2, 99, 1) +def add_improved_geometry_reverse_placenode_index(conn: Connection, **_: Any) -> None: + """ Create improved index for reverse lookup of place nodes. + """ + with conn.cursor() as cur: + cur.execute("""CREATE INDEX IF NOT EXISTS idx_placex_geometry_reverse_lookupPlaceNode + ON placex + USING gist (ST_Buffer(geometry, reverse_place_diameter(rank_search))) + WHERE rank_address between 4 and 25 AND type != 'postcode' + AND name is not null AND linked_place_id is null AND osm_type = 'N' + """) diff --git a/nominatim/version.py b/nominatim/version.py index 40e3bda4..346af5eb 100644 --- a/nominatim/version.py +++ b/nominatim/version.py @@ -34,7 +34,7 @@ class NominatimVersion(NamedTuple): return f"{self.major}.{self.minor}.{self.patch_level}-{self.db_patch_level}" -NOMINATIM_VERSION = NominatimVersion(4, 2, 99, 0) +NOMINATIM_VERSION = NominatimVersion(4, 2, 99, 1) POSTGRESQL_REQUIRED_VERSION = (9, 6) POSTGIS_REQUIRED_VERSION = (2, 2)