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 d12e4da3c8d65c4ae9ba02f10a4a0d333d1e77f6..712f14809bb07f0edffc3b0e371cf791119380db 100644 (file)
--- 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 9130fb52998f4188dce449b96f2bd132b76abdee..ed078895ee8901473ac0f613b97e6d9cabe8c88e 100644 (file)
--- 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 d576485e33839ea4b7ccbe163bab1bf530b4c9bc..17216b50990dfd2b24e3b47b8969dac5904109c2 100644 (file)
--- 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 7d117a8c7f495209703c25d9b8a1364d8725515c..0c88493bfbd34567f2d6ec4fb9d02deca0217065 100644 (file)
--- 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 40e3bda42d9f6f2a06fad2bf8a588b803d98c9b8..346af5eb651f969bd8bd5694c34df4bc0840171e 100644 (file)
--- 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)