+# 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()
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):
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):
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
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)
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)
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.
# 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
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)
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')
projects / nominatim.git / blobdiff