speed up reverse lookup of place nodes

Add a special index that contains the place nodes buffered by their
respective area according to their search rank. This replaces the
maximum area search for place nodes and reduces drastically the number
of place nodes that need to be retrieved.

diff --git a/lib-php/ReverseGeocode.php b/lib-php/ReverseGeocode.php
index d12e4da3c8d65c4ae9ba02f10a4a0d333d1e77f6..444ebdeb0b017a3b3c2d6283c33285664f66eaa7 100644 (file)
--- a/lib-php/ReverseGeocode.php
+++ b/lib-php/ReverseGeocode.php
@@ -216,23 +216,18 @@ class ReverseGeocode
                 $sSQL .= ' ST_distance('.$sPointSQL.', geometry) as distance';
                 $sSQL .= ' FROM placex';
                 $sSQL .= ' WHERE osm_type = \'N\'';
                 $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 > '.$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 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 .= ' 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);
 
                 $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;
 ---
     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;
 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
 
 -- 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
 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:
 
         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)
                 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")
                                  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}"
 
 
         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)
 
 POSTGRESQL_REQUIRED_VERSION = (9, 6)
 POSTGIS_REQUIRED_VERSION = (2, 2)