Improved performance of the postcodes query and some code cleaning

diff --git a/nominatim/clicmd/refresh.py b/nominatim/clicmd/refresh.py
index c7142c5f2265e46400a52df20fdcc67f964c93b1..fbc23350c7212478f8c8b0c9fa484ec154f9f070 100644 (file)
--- a/nominatim/clicmd/refresh.py
+++ b/nominatim/clicmd/refresh.py
@@ -61,7 +61,7 @@ class UpdateRefresh:
                                   args.threads or 1)
                 indexer.index_postcodes()
             else:
-                LOG.error("The place table doesn\'t exists. " \
+                LOG.error("The place table doesn\'t exist. " \
                           "Postcode updates on a frozen database is not possible.")
 
         if args.word_counts:

diff --git a/nominatim/tools/postcodes.py b/nominatim/tools/postcodes.py
index 2be3ced6c2d299fcc063c0a9146a86f23c5488cd..cfd242e24d8d358cf48f77f2930e8a1a7171ef03 100644 (file)
--- a/nominatim/tools/postcodes.py
+++ b/nominatim/tools/postcodes.py
@@ -165,15 +165,13 @@ def update_postcodes(dsn, project_dir, tokenizer):
             with conn.cursor(name="placex_postcodes") as cur:
                 cur.execute("""
                 SELECT cc as country_code, pc, ST_X(centroid), ST_Y(centroid)
-                FROM (
-                    SELECT 
+                FROM (SELECT 
                         COALESCE(plx.country_code, get_country_code(ST_Centroid(pl.geometry))) as cc,
                         token_normalized_postcode(pl.address->'postcode') as pc,
-                        COALESCE(ST_Centroid(ST_Collect(plx.centroid)), 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
+                        ST_Centroid(ST_Collect(COALESCE(plx.centroid, 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
+                    GROUP BY cc, pc) xx
                 WHERE pc IS NOT null AND cc IS NOT null
                 ORDER BY country_code, pc""")
 

diff --git a/test/python/test_tools_postcodes.py b/test/python/test_tools_postcodes.py
index 7d98c452f9bde4abe780dc6d5b4693837194f359..a34157690c8bd9350fe1881b442abdc970c4fdde 100644 (file)
--- a/test/python/test_tools_postcodes.py
+++ b/test/python/test_tools_postcodes.py
@@ -114,7 +114,6 @@ def test_postcodes_ignore_empty_country(dsn, postcode_table, tmp_path,
                                         insert_implicit_postcode, tokenizer):
     insert_implicit_postcode(1, None, 'POINT(10 12)', dict(postcode='AB 4511'))
     postcodes.update_postcodes(dsn, tmp_path, tokenizer)
-    print(postcode_table.row_set)
     assert not postcode_table.row_set