From a4733eed90b2ee5e7b0aac2da3f8e71cffd340a6 Mon Sep 17 00:00:00 2001
From: AntoJvlt <antonin.jolivat@gmail.com>
Date: Fri, 4 Jun 2021 21:26:13 +0200
Subject: [PATCH 1/1] Use place instead of placex to compute postcodes

---
 lib-sql/functions/place_triggers.sql |  6 +++++
 nominatim/clicmd/refresh.py          | 18 ++++++++------
 nominatim/tools/database_import.py   |  2 +-
 nominatim/tools/postcodes.py         | 36 +++++++++++++++++++---------
 4 files changed, 43 insertions(+), 19 deletions(-)

diff --git a/lib-sql/functions/place_triggers.sql b/lib-sql/functions/place_triggers.sql
index 53163746..c19b2274 100644
--- a/lib-sql/functions/place_triggers.sql
+++ b/lib-sql/functions/place_triggers.sql
@@ -77,6 +77,12 @@ BEGIN
 
   ELSE -- insert to placex
 
+    -- Pure postcodes are never queried from placex so we don't add them.
+    -- location_postcodes is filled from the place table directly.
+    IF NEW.class = 'place' AND NEW.type = 'postcode' THEN
+      RETURN NEW;
+    END IF;
+
     -- Patch in additional country names
     IF NEW.admin_level = 2 AND NEW.type = 'administrative'
           AND NEW.address is not NULL AND NEW.address ? 'country' THEN
diff --git a/nominatim/clicmd/refresh.py b/nominatim/clicmd/refresh.py
index 386516d6..c7142c5f 100644
--- a/nominatim/clicmd/refresh.py
+++ b/nominatim/clicmd/refresh.py
@@ -52,13 +52,17 @@ class UpdateRefresh:
 
 
         if args.postcodes:
-            LOG.warning("Update postcodes centroid")
-            tokenizer = self._get_tokenizer(args.config)
-            postcodes.update_postcodes(args.config.get_libpq_dsn(),
-                                       args.project_dir, tokenizer)
-            indexer = Indexer(args.config.get_libpq_dsn(), tokenizer,
-                              args.threads or 1)
-            indexer.index_postcodes()
+            if postcodes.can_compute(args.config.get_libpq_dsn()):
+                LOG.warning("Update postcodes centroid")
+                tokenizer = self._get_tokenizer(args.config)
+                postcodes.update_postcodes(args.config.get_libpq_dsn(),
+                                           args.project_dir, tokenizer)
+                indexer = Indexer(args.config.get_libpq_dsn(), tokenizer,
+                                  args.threads or 1)
+                indexer.index_postcodes()
+            else:
+                LOG.error("The place table doesn\'t exists. " \
+                          "Postcode updates on a frozen database is not possible.")
 
         if args.word_counts:
             LOG.warning('Recompute frequency of full-word search terms')
diff --git a/nominatim/tools/database_import.py b/nominatim/tools/database_import.py
index 664d3c6b..28a10ebe 100644
--- a/nominatim/tools/database_import.py
+++ b/nominatim/tools/database_import.py
@@ -199,7 +199,7 @@ def load_data(dsn, threads):
         conn.perform("""INSERT INTO placex ({0})
                          SELECT {0} FROM place
                          WHERE osm_id % {1} = {2}
-                           AND NOT (class='place' and type='houses')
+                           AND NOT (class='place' and (type='houses' or type='postcode'))
                            AND ST_IsValid(geometry)
                      """.format(_COPY_COLUMNS, place_threads, imod))
         sel.register(conn, selectors.EVENT_READ, conn)
diff --git a/nominatim/tools/postcodes.py b/nominatim/tools/postcodes.py
index 195d407e..fd355079 100644
--- a/nominatim/tools/postcodes.py
+++ b/nominatim/tools/postcodes.py
@@ -163,17 +163,19 @@ def update_postcodes(dsn, project_dir, tokenizer):
 
             # Recompute the list of valid postcodes from placex.
             with conn.cursor(name="placex_postcodes") as cur:
-                cur.execute("""SELECT country_code, pc, ST_X(centroid), ST_Y(centroid)
-                               FROM (
-                                 SELECT country_code,
-                                        token_normalized_postcode(address->'postcode') as pc,
-                                        ST_Centroid(ST_Collect(ST_Centroid(geometry))) as centroid
-                                 FROM placex
-                                 WHERE address ? 'postcode' and geometry IS NOT null
-                                       and country_code is not null
-                                 GROUP BY country_code, pc) xx
-                               WHERE pc is not null
-                               ORDER BY country_code, pc""")
+                cur.execute("""
+                SELECT cc as country_code, pc, ST_X(centroid), ST_Y(centroid)
+                FROM (
+                    SELECT 
+                        COALESCE(plx.country_code, get_country_code(ST_Centroid(pl.geometry))) as cc,
+                        token_normalized_postcode(pl.address->'postcode') as pc,
+                        ST_Centroid(ST_Collect(ST_Centroid(pl.geometry))) as centroid
+                    FROM place AS pl LEFT OUTER JOIN placex AS plx ON pl.osm_id = plx.osm_id AND pl.osm_type = plx.osm_type
+                    WHERE pl.address ? 'postcode' AND pl.geometry IS NOT null
+                    GROUP BY cc, pc
+                ) xx
+                WHERE pc IS NOT null AND cc IS NOT null
+                ORDER BY country_code, pc""")
 
                 collector = None
 
@@ -195,3 +197,15 @@ def update_postcodes(dsn, project_dir, tokenizer):
             conn.commit()
 
         analyzer.update_postcodes_from_db()
+
+def can_compute(dsn):
+    """
+        Check that the place table exists so that
+        postcodes can be computed.
+    """
+    with connect(dsn) as conn:
+        with conn.cursor() as cur:
+            cur.execute("""
+                select exists(select 1 from information_schema.tables where table_name='place')
+            """)
+            return cur.fetchone()[0]
-- 
2.39.5