X-Git-Url: https://git.openstreetmap.org./nominatim.git/blobdiff_plain/63e35574d4998ea64c1b26b1680d14ff4fd14036..b5e5efc131a29a46a4c5d57f02f7c6b50126f86f:/nominatim/tools/postcodes.py?ds=sidebyside diff --git a/nominatim/tools/postcodes.py b/nominatim/tools/postcodes.py index 195d407e..dad1edff 100644 --- a/nominatim/tools/postcodes.py +++ b/nominatim/tools/postcodes.py @@ -1,15 +1,24 @@ +# SPDX-License-Identifier: GPL-2.0-only +# +# This file is part of Nominatim. (https://nominatim.org) +# +# Copyright (C) 2022 by the Nominatim developer community. +# For a full list of authors see the git log. """ Functions for importing, updating and otherwise maintaining the table of artificial postcode centroids. """ +from collections import defaultdict import csv import gzip import logging from math import isfinite -from psycopg2.extras import execute_values +from psycopg2 import sql as pysql from nominatim.db.connection import connect +from nominatim.utils.centroid import PointsCentroid +from nominatim.data.postcode_format import PostcodeFormatter LOG = logging.getLogger() @@ -30,14 +39,14 @@ class _CountryPostcodesCollector: def __init__(self, country): self.country = country - self.collected = dict() + self.collected = defaultdict(PointsCentroid) def add(self, postcode, x, y): """ Add the given postcode to the collection cache. If the postcode already existed, it is overwritten with the new centroid. """ - self.collected[postcode] = (x, y) + self.collected[postcode] += (x, y) def commit(self, conn, analyzer, project_dir): @@ -52,27 +61,26 @@ class _CountryPostcodesCollector: with conn.cursor() as cur: if to_add: - execute_values(cur, - """INSERT INTO location_postcode - (place_id, indexed_status, country_code, - postcode, geometry) VALUES %s""", - to_add, - template="""(nextval('seq_place'), 1, '{}', - %s, 'SRID=4326;POINT(%s %s)') - """.format(self.country)) + cur.execute_values( + """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))) if to_delete: cur.execute("""DELETE FROM location_postcode WHERE country_code = %s and postcode = any(%s) """, (self.country, to_delete)) if to_update: - execute_values(cur, - """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(self.country), - to_update) + cur.execute_values( + 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): @@ -88,16 +96,16 @@ class _CountryPostcodesCollector: WHERE country_code = %s""", (self.country, )) for postcode, x, y in cur: - newx, newy = self.collected.pop(postcode, (None, None)) - if newx is not None: - dist = (x - newx)**2 + (y - newy)**2 - if dist > 0.0000001: + pcobj = self.collected.pop(postcode, None) + if pcobj: + newx, newy = pcobj.centroid() + if (x - newx) > 0.0000001 or (y - newy) > 0.0000001: to_update.append((postcode, newx, newy)) else: to_delete.append(postcode) - to_add = [(k, v[0], v[1]) for k, v in self.collected.items()] - self.collected = [] + to_add = [(k, *v.centroid()) for k, v in self.collected.items()] + self.collected = None return to_add, to_delete, to_update @@ -120,8 +128,10 @@ class _CountryPostcodesCollector: postcode = analyzer.normalize_postcode(row['postcode']) if postcode not in self.collected: try: - self.collected[postcode] = (_to_float(row['lon'], 180), - _to_float(row['lat'], 90)) + # Do float conversation separately, it might throw + centroid = (_to_float(row['lon'], 180), + _to_float(row['lat'], 90)) + self.collected[postcode] += centroid except ValueError: LOG.warning("Bad coordinates %s, %s in %s country postcode file.", row['lat'], row['lon'], self.country) @@ -131,13 +141,13 @@ class _CountryPostcodesCollector: def _open_external(self, project_dir): - fname = project_dir / '{}_postcodes.csv'.format(self.country) + fname = project_dir / f'{self.country}_postcodes.csv' if fname.is_file(): LOG.info("Using external postcode file '%s'.", fname) - return open(fname, 'r') + return open(fname, 'r', encoding='utf-8') - fname = project_dir / '{}_postcodes.csv.gz'.format(self.country) + fname = project_dir / f'{self.country}_postcodes.csv.gz' if fname.is_file(): LOG.info("Using external postcode file '%s'.", fname) @@ -153,6 +163,7 @@ def update_postcodes(dsn, project_dir, tokenizer): potentially enhances it with external data and then updates the postcodes in the table 'location_postcode'. """ + matcher = PostcodeFormatter() with tokenizer.name_analyzer() as analyzer: with connect(dsn) as conn: # First get the list of countries that currently have postcodes. @@ -163,17 +174,18 @@ 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, + 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) xx + WHERE pc IS NOT null AND cc IS NOT null + ORDER BY country_code, pc""") collector = None @@ -183,7 +195,9 @@ def update_postcodes(dsn, project_dir, tokenizer): collector.commit(conn, analyzer, project_dir) collector = _CountryPostcodesCollector(country) todo_countries.discard(country) - collector.add(postcode, x, y) + match = matcher.match(country, postcode) + if match: + collector.add(matcher.normalize(country, match), x, y) if collector is not None: collector.commit(conn, analyzer, project_dir) @@ -195,3 +209,11 @@ 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: + return conn.table_exists('place')