Merge pull request #3586 from lonvia/reduce-lookup-calls

[nominatim.git] / src / nominatim_db / tools / postcodes.py

diff --git a/src/nominatim_db/tools/postcodes.py b/src/nominatim_db/tools/postcodes.py
index 8dc5bdbdb43cacb46fec70a86d553734a4ef92d7..4763aa03388062b2171dc14652c39bae00aa7b94 100644 (file)
--- a/src/nominatim_db/tools/postcodes.py
+++ b/src/nominatim_db/tools/postcodes.py
@@ -16,15 +16,16 @@ import gzip
 import logging
 from math import isfinite
 
-from psycopg2 import sql as pysql
+from psycopg import sql as pysql
 
-from ..db.connection import connect, Connection
+from ..db.connection import connect, Connection, table_exists
 from ..utils.centroid import PointsCentroid
 from ..data.postcode_format import PostcodeFormatter, CountryPostcodeMatcher
 from ..tokenizer.base import AbstractAnalyzer, AbstractTokenizer
 
 LOG = logging.getLogger()
 
+
 def _to_float(numstr: str, max_value: float) -> float:
     """ Convert the number in string into a float. The number is expected
         to be in the range of [-max_value, max_value]. Otherwise rises a
@@ -36,6 +37,7 @@ def _to_float(numstr: str, max_value: float) -> float:
 
     return num
 
+
 class _PostcodeCollector:
     """ Collector for postcodes of a single country.
     """
@@ -46,7 +48,6 @@ class _PostcodeCollector:
         self.collected: Dict[str, PointsCentroid] = defaultdict(PointsCentroid)
         self.normalization_cache: Optional[Tuple[str, Optional[str]]] = None
 
-
     def add(self, postcode: str, x: float, y: float) -> None:
         """ Add the given postcode to the collection cache. If the postcode
             already existed, it is overwritten with the new centroid.
@@ -63,7 +64,6 @@ class _PostcodeCollector:
             if normalized:
                 self.collected[normalized] += (x, y)
 
-
     def commit(self, conn: Connection, analyzer: AbstractAnalyzer, project_dir: Path) -> None:
         """ Update postcodes for the country from the postcodes selected so far
             as well as any externally supplied postcodes.
@@ -76,30 +76,30 @@ class _PostcodeCollector:
 
         with conn.cursor() as cur:
             if to_add:
-                cur.execute_values(
+                cur.executemany(pysql.SQL(
                     """INSERT INTO location_postcode
                          (place_id, indexed_status, country_code,
-                          postcode, geometry) VALUES %s""",
-                    to_add,
-                    template=pysql.SQL("""(nextval('seq_place'), 1, {},
-                                          %s, 'SRID=4326;POINT(%s %s)')
-                                       """).format(pysql.Literal(self.country)))
+                          postcode, geometry)
+                       VALUES (nextval('seq_place'), 1, {}, %s,
+                               ST_SetSRID(ST_MakePoint(%s, %s), 4326))
+                    """).format(pysql.Literal(self.country)),
+                    to_add)
             if to_delete:
                 cur.execute("""DELETE FROM location_postcode
                                WHERE country_code = %s and postcode = any(%s)
                             """, (self.country, to_delete))
             if to_update:
-                cur.execute_values(
+                cur.executemany(
                     pysql.SQL("""UPDATE location_postcode
                                  SET indexed_status = 2,
-                                     geometry = ST_SetSRID(ST_Point(v.x, v.y), 4326)
-                                 FROM (VALUES %s) AS v (pc, x, y)
-                                 WHERE country_code = {} and postcode = pc
-                              """).format(pysql.Literal(self.country)), to_update)
-
-
-    def _compute_changes(self, conn: Connection) \
-          -> Tuple[List[Tuple[str, float, float]], List[str], List[Tuple[str, float, float]]]:
+                                     geometry = ST_SetSRID(ST_Point(%s, %s), 4326)
+                                 WHERE country_code = {} and postcode = %s
+                              """).format(pysql.Literal(self.country)),
+                    to_update)
+
+    def _compute_changes(
+            self, conn: Connection
+            ) -> Tuple[List[Tuple[str, float, float]], List[str], List[Tuple[float, float, str]]]:
         """ Compute which postcodes from the collected postcodes have to be
             added or modified and which from the location_postcode table
             have to be deleted.
@@ -116,7 +116,7 @@ class _PostcodeCollector:
                 if pcobj:
                     newx, newy = pcobj.centroid()
                     if (x - newx) > 0.0000001 or (y - newy) > 0.0000001:
-                        to_update.append((postcode, newx, newy))
+                        to_update.append((newx, newy, postcode))
                 else:
                     to_delete.append(postcode)
 
@@ -125,7 +125,6 @@ class _PostcodeCollector:
 
         return to_add, to_delete, to_update
 
-
     def _update_from_external(self, analyzer: AbstractAnalyzer, project_dir: Path) -> None:
         """ Look for an external postcode file for the active country in
             the project directory and add missing postcodes when found.
@@ -155,7 +154,6 @@ class _PostcodeCollector:
         finally:
             csvfile.close()
 
-
     def _open_external(self, project_dir: Path) -> Optional[TextIO]:
         fname = project_dir / f'{self.country}_postcodes.csv'
 
@@ -225,10 +223,11 @@ def update_postcodes(dsn: str, project_dir: Path, tokenizer: AbstractTokenizer)
 
         analyzer.update_postcodes_from_db()
 
+
 def can_compute(dsn: str) -> bool:
     """
         Check that the place table exists so that
         postcodes can be computed.
     """
     with connect(dsn) as conn:
-        return conn.table_exists('place')
+        return table_exists(conn, 'place')