src/nominatim_api/reverse.py

   1 # SPDX-License-Identifier: GPL-3.0-or-later
   2 #
   3 # This file is part of Nominatim. (https://nominatim.org)
   4 #
   5 # Copyright (C) 2024 by the Nominatim developer community.
   6 # For a full list of authors see the git log.
   7 """
   8 Implementation of reverse geocoding.
   9 """
  10 from typing import Optional, List, Callable, Type, Tuple, Dict, Any, cast, Union
  11 import functools
  12
  13 import sqlalchemy as sa
  14
  15 from .typing import SaColumn, SaSelect, SaFromClause, SaLabel, SaRow, \
  16                     SaBind, SaLambdaSelect
  17 from .sql.sqlalchemy_types import Geometry
  18 from .connection import SearchConnection
  19 from . import results as nres
  20 from .logging import log
  21 from .types import AnyPoint, DataLayer, ReverseDetails, GeometryFormat, Bbox
  22
  23
  24 RowFunc = Callable[[SaRow, Type[nres.ReverseResult]], nres.ReverseResult]
  25
  26 WKT_PARAM: SaBind = sa.bindparam('wkt', type_=Geometry)
  27 MAX_RANK_PARAM: SaBind = sa.bindparam('max_rank')
  28
  29
  30 def no_index(expr: SaColumn) -> SaColumn:
  31     """ Wrap the given expression, so that the query planner will
  32         refrain from using the expression for index lookup.
  33     """
  34     return sa.func.coalesce(sa.null(), expr)
  35
  36
  37 def _select_from_placex(t: SaFromClause, use_wkt: bool = True) -> SaSelect:
  38     """ Create a select statement with the columns relevant for reverse
  39         results.
  40     """
  41     if not use_wkt:
  42         distance = t.c.distance
  43         centroid = t.c.centroid
  44     else:
  45         distance = t.c.geometry.ST_Distance(WKT_PARAM)
  46         centroid = sa.case((t.c.geometry.is_line_like(), t.c.geometry.ST_ClosestPoint(WKT_PARAM)),
  47                            else_=t.c.centroid).label('centroid')
  48
  49     return sa.select(t.c.place_id, t.c.osm_type, t.c.osm_id, t.c.name,
  50                      t.c.class_, t.c.type,
  51                      t.c.address, t.c.extratags,
  52                      t.c.housenumber, t.c.postcode, t.c.country_code,
  53                      t.c.importance, t.c.wikipedia,
  54                      t.c.parent_place_id, t.c.rank_address, t.c.rank_search,
  55                      centroid,
  56                      t.c.linked_place_id, t.c.admin_level,
  57                      distance.label('distance'),
  58                      t.c.geometry.ST_Expand(0).label('bbox'))
  59
  60
  61 def _interpolated_housenumber(table: SaFromClause) -> SaLabel:
  62     return sa.cast(table.c.startnumber
  63                    + sa.func.round(((table.c.endnumber - table.c.startnumber) * table.c.position)
  64                                    / table.c.step) * table.c.step,
  65                    sa.Integer).label('housenumber')
  66
  67
  68 def _interpolated_position(table: SaFromClause) -> SaLabel:
  69     fac = sa.cast(table.c.step, sa.Float) / (table.c.endnumber - table.c.startnumber)
  70     rounded_pos = sa.func.round(table.c.position / fac) * fac
  71     return sa.case(
  72         (table.c.endnumber == table.c.startnumber, table.c.linegeo.ST_Centroid()),
  73         else_=table.c.linegeo.ST_LineInterpolatePoint(rounded_pos)).label('centroid')
  74
  75
  76 def _locate_interpolation(table: SaFromClause) -> SaLabel:
  77     """ Given a position, locate the closest point on the line.
  78     """
  79     return sa.case((table.c.linegeo.is_line_like(),
  80                     table.c.linegeo.ST_LineLocatePoint(WKT_PARAM)),
  81                    else_=0).label('position')
  82
  83
  84 def _get_closest(*rows: Optional[SaRow]) -> Optional[SaRow]:
  85     return min(rows, key=lambda row: 1000 if row is None else row.distance)
  86
  87
  88 class ReverseGeocoder:
  89     """ Class implementing the logic for looking up a place from a
  90         coordinate.
  91     """
  92
  93     def __init__(self, conn: SearchConnection, params: ReverseDetails,
  94                  restrict_to_country_areas: bool = False) -> None:
  95         self.conn = conn
  96         self.params = params
  97         self.restrict_to_country_areas = restrict_to_country_areas
  98
  99         self.bind_params: Dict[str, Any] = {'max_rank': params.max_rank}
 100
 101     @property
 102     def max_rank(self) -> int:
 103         """ Return the maximum configured rank.
 104         """
 105         return self.params.max_rank
 106
 107     def has_geometries(self) -> bool:
 108         """ Check if any geometries are requested.
 109         """
 110         return bool(self.params.geometry_output)
 111
 112     def layer_enabled(self, *layer: DataLayer) -> bool:
 113         """ Return true when any of the given layer types are requested.
 114         """
 115         return any(self.params.layers & ly for ly in layer)
 116
 117     def layer_disabled(self, *layer: DataLayer) -> bool:
 118         """ Return true when none of the given layer types is requested.
 119         """
 120         return not any(self.params.layers & ly for ly in layer)
 121
 122     def has_feature_layers(self) -> bool:
 123         """ Return true if any layer other than ADDRESS or POI is requested.
 124         """
 125         return self.layer_enabled(DataLayer.RAILWAY, DataLayer.MANMADE, DataLayer.NATURAL)
 126
 127     def _add_geometry_columns(self, sql: SaLambdaSelect, col: SaColumn) -> SaSelect:
 128         out = []
 129
 130         if self.params.geometry_simplification > 0.0:
 131             col = sa.func.ST_SimplifyPreserveTopology(col, self.params.geometry_simplification)
 132
 133         if self.params.geometry_output & GeometryFormat.GEOJSON:
 134             out.append(sa.func.ST_AsGeoJSON(col, 7).label('geometry_geojson'))
 135         if self.params.geometry_output & GeometryFormat.TEXT:
 136             out.append(sa.func.ST_AsText(col).label('geometry_text'))
 137         if self.params.geometry_output & GeometryFormat.KML:
 138             out.append(sa.func.ST_AsKML(col, 7).label('geometry_kml'))
 139         if self.params.geometry_output & GeometryFormat.SVG:
 140             out.append(sa.func.ST_AsSVG(col, 0, 7).label('geometry_svg'))
 141
 142         return sql.add_columns(*out)
 143
 144     def _filter_by_layer(self, table: SaFromClause) -> SaColumn:
 145         if self.layer_enabled(DataLayer.MANMADE):
 146             exclude = []
 147             if self.layer_disabled(DataLayer.RAILWAY):
 148                 exclude.append('railway')
 149             if self.layer_disabled(DataLayer.NATURAL):
 150                 exclude.extend(('natural', 'water', 'waterway'))
 151             return table.c.class_.not_in(tuple(exclude))
 152
 153         include = []
 154         if self.layer_enabled(DataLayer.RAILWAY):
 155             include.append('railway')
 156         if self.layer_enabled(DataLayer.NATURAL):
 157             include.extend(('natural', 'water', 'waterway'))
 158         return table.c.class_.in_(tuple(include))
 159
 160     async def _find_closest_street_or_pois(self, distance: float,
 161                                            fuzziness: float) -> list[SaRow]:
 162         """ Look up the closest rank 26+ place in the database.
 163             The function finds the object that is closest to the reverse
 164             search point as well as all objects within 'fuzziness' distance
 165             to that best result.
 166         """
 167         t = self.conn.t.placex
 168
 169         # PostgreSQL must not get the distance as a parameter because
 170         # there is a danger it won't be able to properly estimate index use
 171         # when used with prepared statements
 172         diststr = sa.text(f"{distance + fuzziness}")
 173
 174         sql: SaLambdaSelect = sa.lambda_stmt(
 175             lambda: _select_from_placex(t)
 176             .where(t.c.geometry.within_distance(WKT_PARAM, diststr))
 177             .where(t.c.indexed_status == 0)
 178             .where(t.c.linked_place_id == None)
 179             .where(sa.or_(sa.not_(t.c.geometry.is_area()),
 180                           t.c.centroid.ST_Distance(WKT_PARAM) < diststr)))
 181
 182         if self.has_geometries():
 183             sql = self._add_geometry_columns(sql, t.c.geometry)
 184
 185         restrict: List[Union[SaColumn, Callable[[], SaColumn]]] = []
 186
 187         if self.layer_enabled(DataLayer.ADDRESS):
 188             max_rank = min(29, self.max_rank)
 189             restrict.append(lambda: no_index(t.c.rank_address).between(26, max_rank))
 190             if self.max_rank == 30:
 191                 restrict.append(lambda: sa.func.IsAddressPoint(t))
 192         if self.layer_enabled(DataLayer.POI) and self.max_rank == 30:
 193             restrict.append(lambda: sa.and_(no_index(t.c.rank_search) == 30,
 194                                             t.c.class_.not_in(('place', 'building')),
 195                                             sa.not_(t.c.geometry.is_line_like())))
 196         if self.has_feature_layers():
 197             restrict.append(sa.and_(no_index(t.c.rank_search).between(26, MAX_RANK_PARAM),
 198                                     no_index(t.c.rank_address) == 0,
 199                                     self._filter_by_layer(t)))
 200
 201         if not restrict:
 202             return []
 203
 204         inner = sql.where(sa.or_(*restrict)) \
 205                    .add_columns(t.c.geometry.label('_geometry')) \
 206                    .subquery()
 207
 208         # Use a window function to get the closest results to the best result.
 209         windowed = sa.select(inner,
 210                              sa.func.first_value(inner.c.distance)
 211                                     .over(order_by=inner.c.distance)
 212                                     .label('_min_distance'),
 213                              sa.func.first_value(
 214                                         sa.case((inner.c.rank_search <= 27,
 215                                                  inner.c._geometry.ST_ClosestPoint(WKT_PARAM)),
 216                                                 else_=None))
 217                                     .over(order_by=inner.c.distance)
 218                                     .label('_closest_point'),
 219                              sa.func.first_value(sa.case((sa.or_(inner.c.rank_search <= 27,
 220                                                                  inner.c.osm_type == 'N'), None),
 221                                                          else_=inner.c._geometry))
 222                                     .over(order_by=inner.c.distance)
 223                                     .label('_best_geometry')) \
 224                      .subquery()
 225
 226         outer = sa.select(*(c for c in windowed.c if not c.key.startswith('_')),
 227                           sa.case((sa.or_(windowed.c._closest_point == None,
 228                                           windowed.c.housenumber == None), None),
 229                                   else_=windowed.c.centroid.ST_Distance(windowed.c._closest_point))
 230                             .label('distance_from_best'),
 231                           sa.case((sa.or_(windowed.c._best_geometry == None,
 232                                           windowed.c.rank_search <= 27,
 233                                           windowed.c.osm_type != 'N'), False),
 234                                   else_=windowed.c.centroid.ST_CoveredBy(windowed.c._best_geometry))
 235                             .label('best_inside')) \
 236                   .where(windowed.c.distance < windowed.c._min_distance + fuzziness) \
 237                   .order_by(windowed.c.distance)
 238
 239         return list(await self.conn.execute(outer, self.bind_params))
 240
 241     async def _find_housenumber_for_street(self, parent_place_id: int) -> Optional[SaRow]:
 242         t = self.conn.t.placex
 243
 244         def _base_query() -> SaSelect:
 245             return _select_from_placex(t)\
 246                 .where(t.c.geometry.within_distance(WKT_PARAM, 0.001))\
 247                 .where(t.c.parent_place_id == parent_place_id)\
 248                 .where(sa.func.IsAddressPoint(t))\
 249                 .where(t.c.indexed_status == 0)\
 250                 .where(t.c.linked_place_id == None)\
 251                 .order_by('distance')\
 252                 .limit(1)
 253
 254         sql: SaLambdaSelect
 255         if self.has_geometries():
 256             sql = self._add_geometry_columns(_base_query(), t.c.geometry)
 257         else:
 258             sql = sa.lambda_stmt(_base_query)
 259
 260         return (await self.conn.execute(sql, self.bind_params)).one_or_none()
 261
 262     async def _find_interpolation_for_street(self, parent_place_id: Optional[int],
 263                                              distance: float) -> Optional[SaRow]:
 264         t = self.conn.t.osmline
 265
 266         sql = sa.select(t,
 267                         t.c.linegeo.ST_Distance(WKT_PARAM).label('distance'),
 268                         _locate_interpolation(t))\
 269                 .where(t.c.linegeo.within_distance(WKT_PARAM, distance))\
 270                 .where(t.c.startnumber != None)\
 271                 .order_by('distance')\
 272                 .limit(1)
 273
 274         if parent_place_id is not None:
 275             sql = sql.where(t.c.parent_place_id == parent_place_id)
 276
 277         inner = sql.subquery('ipol')
 278
 279         sql = sa.select(inner.c.place_id, inner.c.osm_id,
 280                         inner.c.parent_place_id, inner.c.address,
 281                         _interpolated_housenumber(inner),
 282                         _interpolated_position(inner),
 283                         inner.c.postcode, inner.c.country_code,
 284                         inner.c.distance)
 285
 286         if self.has_geometries():
 287             sub = sql.subquery('geom')
 288             sql = self._add_geometry_columns(sa.select(sub), sub.c.centroid)
 289
 290         return (await self.conn.execute(sql, self.bind_params)).one_or_none()
 291
 292     async def _find_tiger_number_for_street(self, parent_place_id: int) -> Optional[SaRow]:
 293         t = self.conn.t.tiger
 294
 295         def _base_query() -> SaSelect:
 296             inner = sa.select(t,
 297                               t.c.linegeo.ST_Distance(WKT_PARAM).label('distance'),
 298                               _locate_interpolation(t))\
 299                       .where(t.c.linegeo.within_distance(WKT_PARAM, 0.001))\
 300                       .where(t.c.parent_place_id == parent_place_id)\
 301                       .order_by('distance')\
 302                       .limit(1)\
 303                       .subquery('tiger')
 304
 305             return sa.select(inner.c.place_id,
 306                              inner.c.parent_place_id,
 307                              _interpolated_housenumber(inner),
 308                              _interpolated_position(inner),
 309                              inner.c.postcode,
 310                              inner.c.distance)
 311
 312         sql: SaLambdaSelect
 313         if self.has_geometries():
 314             sub = _base_query().subquery('geom')
 315             sql = self._add_geometry_columns(sa.select(sub), sub.c.centroid)
 316         else:
 317             sql = sa.lambda_stmt(_base_query)
 318
 319         return (await self.conn.execute(sql, self.bind_params)).one_or_none()
 320
 321     async def lookup_street_poi(self) -> Tuple[Optional[SaRow], RowFunc]:
 322         """ Find a street or POI/address for the given WKT point.
 323         """
 324         log().section('Reverse lookup on street/address level')
 325         row_func: RowFunc = nres.create_from_placex_row
 326         distance = 0.006
 327
 328         result = None
 329         hnr_distance = None
 330         parent_street = None
 331         for row in await self._find_closest_street_or_pois(distance, 0.001):
 332             if result is None:
 333                 log().var_dump('Closest result', row)
 334                 result = row
 335                 if self.max_rank > 27 \
 336                         and self.layer_enabled(DataLayer.ADDRESS) \
 337                         and result.rank_address <= 27:
 338                     parent_street = result.place_id
 339                     distance = 0.001
 340                 else:
 341                     distance = row.distance
 342             # If the closest result was a street but an address was requested,
 343             # see if we can refine the result with a housenumber closeby.
 344             elif parent_street is not None \
 345                     and row.distance_from_best is not None \
 346                     and row.distance_from_best < 0.001 \
 347                     and (hnr_distance is None or hnr_distance > row.distance_from_best) \
 348                     and row.parent_place_id == parent_street:
 349                 log().var_dump('Housenumber to closest result', row)
 350                 result = row
 351                 hnr_distance = row.distance_from_best
 352                 distance = row.distance
 353             # If the closest object is inside an area, then check if there is
 354             # a POI nearby and return that with preference.
 355             elif result.osm_type != 'N' and result.rank_search > 27 \
 356                     and result.distance == 0 \
 357                     and row.best_inside:
 358                 log().var_dump('POI near closest result area', row)
 359                 result = row
 360                 break  # it can't get better than that, everything else is farther away
 361
 362         # For the US also check the TIGER data, when no housenumber/POI was found.
 363         if result is not None and parent_street is not None and hnr_distance is None \
 364                 and result.country_code == 'us':
 365             log().comment('Find TIGER housenumber for street')
 366             addr_row = await self._find_tiger_number_for_street(parent_street)
 367             log().var_dump('Result (street Tiger housenumber)', addr_row)
 368
 369             if addr_row is not None:
 370                 row_func = cast(RowFunc,
 371                                 functools.partial(nres.create_from_tiger_row,
 372                                                   osm_type=row.osm_type,
 373                                                   osm_id=row.osm_id))
 374                 result = addr_row
 375
 376         # Check for an interpolation that is either closer than our result
 377         # or belongs to a close street found.
 378         # No point in doing this when the result is already inside a building,
 379         # i.e. when the distance is already 0.
 380         if self.max_rank > 27 and self.layer_enabled(DataLayer.ADDRESS) and distance > 0:
 381             log().comment('Find interpolation for street')
 382             addr_row = await self._find_interpolation_for_street(parent_street, distance)
 383             log().var_dump('Result (street interpolation)', addr_row)
 384             if addr_row is not None:
 385                 return addr_row, nres.create_from_osmline_row
 386
 387         return result, row_func
 388
 389     async def _lookup_area_address(self) -> Optional[SaRow]:
 390         """ Lookup large addressable areas for the given WKT point.
 391         """
 392         log().comment('Reverse lookup by larger address area features')
 393         t = self.conn.t.placex
 394
 395         def _base_query() -> SaSelect:
 396             # The inner SQL brings results in the right order, so that
 397             # later only a minimum of results needs to be checked with ST_Contains.
 398             inner = sa.select(t, sa.literal(0.0).label('distance'))\
 399                       .where(t.c.rank_search.between(5, MAX_RANK_PARAM))\
 400                       .where(t.c.rank_address != 5)\
 401                       .where(t.c.rank_address != 11)\
 402                       .where(t.c.geometry.intersects(WKT_PARAM))\
 403                       .where(sa.func.PlacexGeometryReverseLookuppolygon())\
 404                       .order_by(sa.desc(t.c.rank_search))\
 405                       .limit(50)\
 406                       .subquery('area')
 407
 408             return _select_from_placex(inner, False)\
 409                 .where(inner.c.geometry.ST_Contains(WKT_PARAM))\
 410                 .order_by(sa.desc(inner.c.rank_search))\
 411                 .limit(1)
 412
 413         sql: SaLambdaSelect = sa.lambda_stmt(_base_query)
 414         if self.has_geometries():
 415             sql = self._add_geometry_columns(sql, sa.literal_column('area.geometry'))
 416
 417         address_row = (await self.conn.execute(sql, self.bind_params)).one_or_none()
 418         log().var_dump('Result (area)', address_row)
 419
 420         if address_row is not None and address_row.rank_search < self.max_rank:
 421             log().comment('Search for better matching place nodes inside the area')
 422
 423             address_rank = address_row.rank_search
 424             address_id = address_row.place_id
 425
 426             def _place_inside_area_query() -> SaSelect:
 427                 inner = \
 428                     sa.select(t, t.c.geometry.ST_Distance(WKT_PARAM).label('distance'))\
 429                     .where(t.c.rank_search > address_rank)\
 430                     .where(t.c.rank_search <= MAX_RANK_PARAM)\
 431                     .where(t.c.indexed_status == 0)\
 432                     .where(sa.func.IntersectsReverseDistance(t, WKT_PARAM))\
 433                     .order_by(sa.desc(t.c.rank_search))\
 434                     .limit(50)\
 435                     .subquery('places')
 436
 437                 touter = t.alias('outer')
 438                 return _select_from_placex(inner, False)\
 439                     .join(touter, touter.c.geometry.ST_Contains(inner.c.geometry))\
 440                     .where(touter.c.place_id == address_id)\
 441                     .where(sa.func.IsBelowReverseDistance(inner.c.distance, inner.c.rank_search))\
 442                     .order_by(sa.desc(inner.c.rank_search), inner.c.distance)\
 443                     .limit(1)
 444
 445             if self.has_geometries():
 446                 sql = self._add_geometry_columns(_place_inside_area_query(),
 447                                                  sa.literal_column('places.geometry'))
 448             else:
 449                 sql = sa.lambda_stmt(_place_inside_area_query)
 450
 451             place_address_row = (await self.conn.execute(sql, self.bind_params)).one_or_none()
 452             log().var_dump('Result (place node)', place_address_row)
 453
 454             if place_address_row is not None:
 455                 return place_address_row
 456
 457         return address_row
 458
 459     async def _lookup_area_others(self) -> Optional[SaRow]:
 460         t = self.conn.t.placex
 461
 462         inner = sa.select(t, t.c.geometry.ST_Distance(WKT_PARAM).label('distance'))\
 463                   .where(t.c.rank_address == 0)\
 464                   .where(t.c.rank_search.between(5, MAX_RANK_PARAM))\
 465                   .where(t.c.name != None)\
 466                   .where(t.c.indexed_status == 0)\
 467                   .where(t.c.linked_place_id == None)\
 468                   .where(self._filter_by_layer(t))\
 469                   .where(t.c.geometry.intersects(sa.func.ST_Expand(WKT_PARAM, 0.007)))\
 470                   .order_by(sa.desc(t.c.rank_search))\
 471                   .order_by('distance')\
 472                   .limit(50)\
 473                   .subquery()
 474
 475         sql = _select_from_placex(inner, False)\
 476             .where(sa.or_(sa.not_(inner.c.geometry.is_area()),
 477                           inner.c.geometry.ST_Contains(WKT_PARAM)))\
 478             .order_by(sa.desc(inner.c.rank_search), inner.c.distance)\
 479             .limit(1)
 480
 481         if self.has_geometries():
 482             sql = self._add_geometry_columns(sql, inner.c.geometry)
 483
 484         row = (await self.conn.execute(sql, self.bind_params)).one_or_none()
 485         log().var_dump('Result (non-address feature)', row)
 486
 487         return row
 488
 489     async def lookup_area(self) -> Optional[SaRow]:
 490         """ Lookup large areas for the current search.
 491         """
 492         log().section('Reverse lookup by larger area features')
 493
 494         if self.layer_enabled(DataLayer.ADDRESS):
 495             address_row = await self._lookup_area_address()
 496         else:
 497             address_row = None
 498
 499         if self.has_feature_layers():
 500             other_row = await self._lookup_area_others()
 501         else:
 502             other_row = None
 503
 504         return _get_closest(address_row, other_row)
 505
 506     async def lookup_country_codes(self) -> List[str]:
 507         """ Lookup the country for the current search.
 508         """
 509         log().section('Reverse lookup by country code')
 510         t = self.conn.t.country_grid
 511         sql = sa.select(t.c.country_code).distinct()\
 512                 .where(t.c.geometry.ST_Contains(WKT_PARAM))
 513
 514         ccodes = [cast(str, r[0]) for r in await self.conn.execute(sql, self.bind_params)]
 515         log().var_dump('Country codes', ccodes)
 516         return ccodes
 517
 518     async def lookup_country(self, ccodes: List[str]) -> Tuple[Optional[SaRow], RowFunc]:
 519         """ Lookup the country for the current search.
 520         """
 521         row_func = nres.create_from_placex_row
 522         if not ccodes:
 523             ccodes = await self.lookup_country_codes()
 524
 525         if not ccodes:
 526             return None, row_func
 527
 528         t = self.conn.t.placex
 529         if self.max_rank > 4:
 530             log().comment('Search for place nodes in country')
 531
 532             def _base_query() -> SaSelect:
 533                 inner = sa.select(t, t.c.geometry.ST_Distance(WKT_PARAM).label('distance'))\
 534                           .where(t.c.rank_search > 4)\
 535                           .where(t.c.rank_search <= MAX_RANK_PARAM)\
 536                           .where(t.c.indexed_status == 0)\
 537                           .where(t.c.country_code.in_(ccodes))\
 538                           .where(sa.func.IntersectsReverseDistance(t, WKT_PARAM))\
 539                           .order_by(sa.desc(t.c.rank_search))\
 540                           .limit(50)\
 541                           .subquery('area')
 542
 543                 return _select_from_placex(inner, False)\
 544                     .where(sa.func.IsBelowReverseDistance(inner.c.distance, inner.c.rank_search))\
 545                     .order_by(sa.desc(inner.c.rank_search), inner.c.distance)\
 546                     .limit(1)
 547
 548             sql: SaLambdaSelect
 549             if self.has_geometries():
 550                 sql = self._add_geometry_columns(_base_query(),
 551                                                  sa.literal_column('area.geometry'))
 552             else:
 553                 sql = sa.lambda_stmt(_base_query)
 554
 555             address_row = (await self.conn.execute(sql, self.bind_params)).one_or_none()
 556             log().var_dump('Result (addressable place node)', address_row)
 557         else:
 558             address_row = None
 559
 560         if address_row is None:
 561             # Still nothing, then return a country with the appropriate country code.
 562             def _country_base_query() -> SaSelect:
 563                 return _select_from_placex(t)\
 564                          .where(t.c.country_code.in_(ccodes))\
 565                          .where(t.c.rank_address == 4)\
 566                          .where(t.c.rank_search == 4)\
 567                          .where(t.c.linked_place_id == None)\
 568                          .order_by('distance')\
 569                          .limit(1)
 570
 571             if self.has_geometries():
 572                 sql = self._add_geometry_columns(_country_base_query(), t.c.geometry)
 573             else:
 574                 sql = sa.lambda_stmt(_country_base_query)
 575
 576             address_row = (await self.conn.execute(sql, self.bind_params)).one_or_none()
 577
 578         if address_row is None:
 579             # finally fall back to country table
 580             t = self.conn.t.country_name
 581             tgrid = self.conn.t.country_grid
 582
 583             sql = sa.select(tgrid.c.country_code,
 584                             tgrid.c.geometry.ST_Centroid().ST_Collect().ST_Centroid()
 585                                  .label('centroid'),
 586                             tgrid.c.geometry.ST_Collect().ST_Expand(0).label('bbox'))\
 587                     .where(tgrid.c.country_code.in_(ccodes))\
 588                     .group_by(tgrid.c.country_code)
 589
 590             sub = sql.subquery('grid')
 591             sql = sa.select(t.c.country_code,
 592                             t.c.name.merge(t.c.derived_name).label('name'),
 593                             sub.c.centroid, sub.c.bbox)\
 594                     .join(sub, t.c.country_code == sub.c.country_code)\
 595                     .order_by(t.c.country_code)\
 596                     .limit(1)
 597
 598             sql = self._add_geometry_columns(sql, sub.c.centroid)
 599
 600             address_row = (await self.conn.execute(sql, self.bind_params)).one_or_none()
 601             row_func = nres.create_from_country_row
 602
 603         return address_row, row_func
 604
 605     async def lookup(self, coord: AnyPoint) -> Optional[nres.ReverseResult]:
 606         """ Look up a single coordinate. Returns the place information,
 607             if a place was found near the coordinates or None otherwise.
 608         """
 609         log().function('reverse_lookup', coord=coord, params=self.params)
 610
 611         self.bind_params['wkt'] = f'POINT({coord[0]} {coord[1]})'
 612
 613         row: Optional[SaRow] = None
 614         row_func: RowFunc = nres.create_from_placex_row
 615
 616         if self.max_rank >= 26:
 617             row, tmp_row_func = await self.lookup_street_poi()
 618             if row is not None:
 619                 row_func = tmp_row_func
 620
 621         if row is None:
 622             if self.restrict_to_country_areas:
 623                 ccodes = await self.lookup_country_codes()
 624                 if not ccodes:
 625                     return None
 626             else:
 627                 ccodes = []
 628
 629             if self.max_rank > 4:
 630                 row = await self.lookup_area()
 631             if row is None and self.layer_enabled(DataLayer.ADDRESS):
 632                 row, row_func = await self.lookup_country(ccodes)
 633
 634         if row is None:
 635             return None
 636
 637         result = row_func(row, nres.ReverseResult)
 638         result.distance = getattr(row,  'distance', 0)
 639         if hasattr(row, 'bbox'):
 640             result.bbox = Bbox.from_wkb(row.bbox)
 641         await nres.add_result_details(self.conn, [result], self.params)
 642
 643         return result