From 3676310efea43835142118d821603d9af923f7df Mon Sep 17 00:00:00 2001 From: AntoJvlt Date: Sat, 12 Jun 2021 15:46:08 +0200 Subject: [PATCH 1/1] Improved performance of the postcodes query and some code cleaning --- nominatim/clicmd/refresh.py | 2 +- nominatim/tools/postcodes.py | 10 ++++------ test/python/test_tools_postcodes.py | 1 - 3 files changed, 5 insertions(+), 8 deletions(-) diff --git a/nominatim/clicmd/refresh.py b/nominatim/clicmd/refresh.py index c7142c5f..fbc23350 100644 --- 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 2be3ced6..cfd242e2 100644 --- 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 7d98c452..a3415769 100644 --- 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 -- 2.39.5