nominatim/api/results.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) 2023 by the Nominatim developer community.
   6 # For a full list of authors see the git log.
   7 """
   8 Dataclasses for search results and helper functions to fill them.
   9
  10 Data classes are part of the public API while the functions are for
  11 internal use only. That's why they are implemented as free-standing functions
  12 instead of member functions.
  13 """
  14 from typing import Optional, Tuple, Dict, Sequence, TypeVar, Type, List, cast
  15 import enum
  16 import dataclasses
  17 import datetime as dt
  18
  19 import sqlalchemy as sa
  20
  21 from nominatim.typing import SaSelect, SaRow
  22 from nominatim.db.sqlalchemy_functions import CrosscheckNames
  23 from nominatim.api.types import Point, Bbox, LookupDetails
  24 from nominatim.api.connection import SearchConnection
  25 from nominatim.api.logging import log
  26 from nominatim.api.localization import Locales
  27
  28 # This file defines complex result data classes.
  29 # pylint: disable=too-many-instance-attributes
  30
  31 def _mingle_name_tags(names: Optional[Dict[str, str]]) -> Optional[Dict[str, str]]:
  32     """ Mix-in names from linked places, so that they show up
  33         as standard names where necessary.
  34     """
  35     if not names:
  36         return None
  37
  38     out = {}
  39     for k, v in names.items():
  40         if k.startswith('_place_'):
  41             outkey = k[7:]
  42             out[k if outkey in names else outkey] = v
  43         else:
  44             out[k] = v
  45
  46     return out
  47
  48
  49 class SourceTable(enum.Enum):
  50     """ The `SourceTable` type lists the possible sources a result can have.
  51     """
  52     PLACEX = 1
  53     """ The placex table is the main source for result usually containing
  54         OSM data.
  55     """
  56     OSMLINE = 2
  57     """ The osmline table contains address interpolations from OSM data.
  58         Interpolation addresses are always approximate. The OSM id in the
  59         result refers to the OSM way with the interpolation line object.
  60     """
  61     TIGER = 3
  62     """ TIGER address data contains US addresses imported on the side,
  63         see [Installing TIGER data](../customize/Tiger.md).
  64         TIGER address are also interpolations. The addresses always refer
  65         to a street from OSM data. The OSM id in the result refers to
  66         that street.
  67     """
  68     POSTCODE = 4
  69     """ The postcode table contains artificial centroids for postcodes,
  70         computed from the postcodes available with address points. Results
  71         are always approximate.
  72     """
  73     COUNTRY = 5
  74     """ The country table provides a fallback, when country data is missing
  75         in the OSM data.
  76     """
  77
  78
  79 @dataclasses.dataclass
  80 class AddressLine:
  81     """ The `AddressLine` may contain the following fields about a related place
  82         and its function as an address object. Most fields are optional.
  83         Their presence depends on the kind and function of the address part.
  84     """
  85     category: Tuple[str, str]
  86     """ Main category of the place, described by a key-value pair.
  87     """
  88     names: Dict[str, str]
  89     """ All available names for the place including references, alternative
  90         names and translations.
  91     """
  92     fromarea: bool
  93     """ If true, then the exact area of the place is known. Without area
  94         information, Nominatim has to make an educated guess if an address
  95         belongs to one place or another.
  96     """
  97     isaddress: bool
  98     """ If true, this place should be considered for the final address display.
  99         Nominatim will sometimes include more than one candidate for
 100         the address in the list when it cannot reliably determine where the
 101         place belongs. It will consider names of all candidates when searching
 102         but when displaying the result, only the most likely candidate should
 103         be shown.
 104     """
 105     rank_address: int
 106     """ [Address rank](../customize/Ranking.md#address-rank) of the place.
 107     """
 108     distance: float
 109     """ Distance in degrees between the result place and this address part.
 110     """
 111     place_id: Optional[int] = None
 112     """ Internal ID of the place.
 113     """
 114     osm_object: Optional[Tuple[str, int]] = None
 115     """ OSM type and ID of the place, if such an object exists.
 116     """
 117     extratags: Optional[Dict[str, str]] = None
 118     """ Any extra information available about the place. This is a dictionary
 119         that usually contains OSM tag key-value pairs.
 120     """
 121
 122     admin_level: Optional[int] = None
 123     """ The administrative level of a boundary as tagged in the input data.
 124         This field is only meaningful for places of the category
 125         (boundary, administrative).
 126     """
 127
 128     local_name: Optional[str] = None
 129     """ Place holder for localization of this address part. See
 130         [Localization](#localization) below.
 131     """
 132
 133
 134 class AddressLines(List[AddressLine]):
 135     """ Sequence of address lines order in descending order by their rank.
 136     """
 137
 138     def localize(self, locales: Locales) -> List[str]:
 139         """ Set the local name of address parts according to the chosen
 140             locale. Return the list of local names without duplicates.
 141
 142             Only address parts that are marked as isaddress are localized
 143             and returned.
 144         """
 145         label_parts: List[str] = []
 146
 147         for line in self:
 148             if line.isaddress and line.names:
 149                 line.local_name = locales.display_name(line.names)
 150                 if not label_parts or label_parts[-1] != line.local_name:
 151                     label_parts.append(line.local_name)
 152
 153         return label_parts
 154
 155
 156
 157 @dataclasses.dataclass
 158 class WordInfo:
 159     """ Each entry in the list of search terms contains the
 160         following detailed information.
 161     """
 162     word_id: int
 163     """ Internal identifier for the word.
 164     """
 165     word_token: str
 166     """ Normalised and transliterated form of the word.
 167         This form is used for searching.
 168     """
 169     word: Optional[str] = None
 170     """ Untransliterated form, if available.
 171     """
 172
 173
 174 WordInfos = Sequence[WordInfo]
 175
 176
 177 @dataclasses.dataclass
 178 class BaseResult:
 179     """ Data class collecting information common to all
 180         types of search results.
 181     """
 182     source_table: SourceTable
 183     category: Tuple[str, str]
 184     centroid: Point
 185
 186     place_id : Optional[int] = None
 187     osm_object: Optional[Tuple[str, int]] = None
 188     parent_place_id: Optional[int] = None
 189     linked_place_id: Optional[int] = None
 190     admin_level: int = 15
 191
 192     locale_name: Optional[str] = None
 193     display_name: Optional[str] = None
 194
 195     names: Optional[Dict[str, str]] = None
 196     address: Optional[Dict[str, str]] = None
 197     extratags: Optional[Dict[str, str]] = None
 198
 199     housenumber: Optional[str] = None
 200     postcode: Optional[str] = None
 201     wikipedia: Optional[str] = None
 202
 203     rank_address: int = 30
 204     rank_search: int = 30
 205     importance: Optional[float] = None
 206
 207     country_code: Optional[str] = None
 208
 209     address_rows: Optional[AddressLines] = None
 210     linked_rows: Optional[AddressLines] = None
 211     parented_rows: Optional[AddressLines] = None
 212     name_keywords: Optional[WordInfos] = None
 213     address_keywords: Optional[WordInfos] = None
 214
 215     geometry: Dict[str, str] = dataclasses.field(default_factory=dict)
 216
 217     @property
 218     def lat(self) -> float:
 219         """ Get the latitude (or y) of the center point of the place.
 220         """
 221         return self.centroid[1]
 222
 223
 224     @property
 225     def lon(self) -> float:
 226         """ Get the longitude (or x) of the center point of the place.
 227         """
 228         return self.centroid[0]
 229
 230
 231     def calculated_importance(self) -> float:
 232         """ Get a valid importance value. This is either the stored importance
 233             of the value or an artificial value computed from the place's
 234             search rank.
 235         """
 236         return self.importance or (0.7500001 - (self.rank_search/40.0))
 237
 238
 239     def localize(self, locales: Locales) -> None:
 240         """ Fill the locale_name and the display_name field for the
 241             place and, if available, its address information.
 242         """
 243         self.locale_name = locales.display_name(self.names)
 244         if self.address_rows:
 245             self.display_name = ', '.join(self.address_rows.localize(locales))
 246         else:
 247             self.display_name = self.locale_name
 248
 249
 250
 251 BaseResultT = TypeVar('BaseResultT', bound=BaseResult)
 252
 253 @dataclasses.dataclass
 254 class DetailedResult(BaseResult):
 255     """ A search result with more internal information from the database
 256         added.
 257     """
 258     indexed_date: Optional[dt.datetime] = None
 259
 260
 261 @dataclasses.dataclass
 262 class ReverseResult(BaseResult):
 263     """ A search result for reverse geocoding.
 264     """
 265     distance: Optional[float] = None
 266     bbox: Optional[Bbox] = None
 267
 268
 269 class ReverseResults(List[ReverseResult]):
 270     """ Sequence of reverse lookup results ordered by distance.
 271         May be empty when no result was found.
 272     """
 273
 274
 275 @dataclasses.dataclass
 276 class SearchResult(BaseResult):
 277     """ A search result for forward geocoding.
 278     """
 279     bbox: Optional[Bbox] = None
 280     accuracy: float = 0.0
 281
 282
 283     @property
 284     def ranking(self) -> float:
 285         """ Return the ranking, a combined measure of accuracy and importance.
 286         """
 287         return (self.accuracy if self.accuracy is not None else 1) \
 288                - self.calculated_importance()
 289
 290
 291 class SearchResults(List[SearchResult]):
 292     """ Sequence of forward lookup results ordered by relevance.
 293         May be empty when no result was found.
 294     """
 295
 296
 297 def _filter_geometries(row: SaRow) -> Dict[str, str]:
 298     return {k[9:]: v for k, v in row._mapping.items() # pylint: disable=W0212
 299             if k.startswith('geometry_')}
 300
 301
 302 def create_from_placex_row(row: Optional[SaRow],
 303                            class_type: Type[BaseResultT]) -> Optional[BaseResultT]:
 304     """ Construct a new result and add the data from the result row
 305         from the placex table. 'class_type' defines the type of result
 306         to return. Returns None if the row is None.
 307     """
 308     if row is None:
 309         return None
 310
 311     return class_type(source_table=SourceTable.PLACEX,
 312                       place_id=row.place_id,
 313                       osm_object=(row.osm_type, row.osm_id),
 314                       category=(row.class_, row.type),
 315                       parent_place_id = row.parent_place_id,
 316                       linked_place_id = getattr(row, 'linked_place_id', None),
 317                       admin_level = getattr(row, 'admin_level', 15),
 318                       names=_mingle_name_tags(row.name),
 319                       address=row.address,
 320                       extratags=row.extratags,
 321                       housenumber=row.housenumber,
 322                       postcode=row.postcode,
 323                       wikipedia=row.wikipedia,
 324                       rank_address=row.rank_address,
 325                       rank_search=row.rank_search,
 326                       importance=row.importance,
 327                       country_code=row.country_code,
 328                       centroid=Point.from_wkb(row.centroid),
 329                       geometry=_filter_geometries(row))
 330
 331
 332 def create_from_osmline_row(row: Optional[SaRow],
 333                             class_type: Type[BaseResultT]) -> Optional[BaseResultT]:
 334     """ Construct a new result and add the data from the result row
 335         from the address interpolation table osmline. 'class_type' defines
 336         the type of result to return. Returns None if the row is None.
 337
 338         If the row contains a housenumber, then the housenumber is filled out.
 339         Otherwise the result contains the interpolation information in extratags.
 340     """
 341     if row is None:
 342         return None
 343
 344     hnr = getattr(row, 'housenumber', None)
 345
 346     res = class_type(source_table=SourceTable.OSMLINE,
 347                      place_id=row.place_id,
 348                      parent_place_id = row.parent_place_id,
 349                      osm_object=('W', row.osm_id),
 350                      category=('place', 'houses' if hnr is None else 'house'),
 351                      address=row.address,
 352                      postcode=row.postcode,
 353                      country_code=row.country_code,
 354                      centroid=Point.from_wkb(row.centroid),
 355                      geometry=_filter_geometries(row))
 356
 357     if hnr is None:
 358         res.extratags = {'startnumber': str(row.startnumber),
 359                          'endnumber': str(row.endnumber),
 360                          'step': str(row.step)}
 361     else:
 362         res.housenumber = str(hnr)
 363
 364     return res
 365
 366
 367 def create_from_tiger_row(row: Optional[SaRow],
 368                           class_type: Type[BaseResultT],
 369                           osm_type: Optional[str] = None,
 370                           osm_id: Optional[int] = None) -> Optional[BaseResultT]:
 371     """ Construct a new result and add the data from the result row
 372         from the Tiger data interpolation table. 'class_type' defines
 373         the type of result to return. Returns None if the row is None.
 374
 375         If the row contains a housenumber, then the housenumber is filled out.
 376         Otherwise the result contains the interpolation information in extratags.
 377     """
 378     if row is None:
 379         return None
 380
 381     hnr = getattr(row, 'housenumber', None)
 382
 383     res = class_type(source_table=SourceTable.TIGER,
 384                      place_id=row.place_id,
 385                      parent_place_id = row.parent_place_id,
 386                      osm_object=(osm_type or row.osm_type, osm_id or row.osm_id),
 387                      category=('place', 'houses' if hnr is None else 'house'),
 388                      postcode=row.postcode,
 389                      country_code='us',
 390                      centroid=Point.from_wkb(row.centroid),
 391                      geometry=_filter_geometries(row))
 392
 393     if hnr is None:
 394         res.extratags = {'startnumber': str(row.startnumber),
 395                          'endnumber': str(row.endnumber),
 396                          'step': str(row.step)}
 397     else:
 398         res.housenumber = str(hnr)
 399
 400     return res
 401
 402
 403 def create_from_postcode_row(row: Optional[SaRow],
 404                           class_type: Type[BaseResultT]) -> Optional[BaseResultT]:
 405     """ Construct a new result and add the data from the result row
 406         from the postcode table. 'class_type' defines
 407         the type of result to return. Returns None if the row is None.
 408     """
 409     if row is None:
 410         return None
 411
 412     return class_type(source_table=SourceTable.POSTCODE,
 413                       place_id=row.place_id,
 414                       parent_place_id = row.parent_place_id,
 415                       category=('place', 'postcode'),
 416                       names={'ref': row.postcode},
 417                       rank_search=row.rank_search,
 418                       rank_address=row.rank_address,
 419                       country_code=row.country_code,
 420                       centroid=Point.from_wkb(row.centroid),
 421                       geometry=_filter_geometries(row))
 422
 423
 424 def create_from_country_row(row: Optional[SaRow],
 425                         class_type: Type[BaseResultT]) -> Optional[BaseResultT]:
 426     """ Construct a new result and add the data from the result row
 427         from the fallback country tables. 'class_type' defines
 428         the type of result to return. Returns None if the row is None.
 429     """
 430     if row is None:
 431         return None
 432
 433     return class_type(source_table=SourceTable.COUNTRY,
 434                       category=('place', 'country'),
 435                       centroid=Point.from_wkb(row.centroid),
 436                       names=row.name,
 437                       rank_address=4, rank_search=4,
 438                       country_code=row.country_code,
 439                       geometry=_filter_geometries(row))
 440
 441
 442 async def add_result_details(conn: SearchConnection, results: List[BaseResultT],
 443                              details: LookupDetails) -> None:
 444     """ Retrieve more details from the database according to the
 445         parameters specified in 'details'.
 446     """
 447     if results:
 448         log().section('Query details for result')
 449         if details.address_details:
 450             log().comment('Query address details')
 451             await complete_address_details(conn, results)
 452         if details.linked_places:
 453             log().comment('Query linked places')
 454             for result in results:
 455                 await complete_linked_places(conn, result)
 456         if details.parented_places:
 457             log().comment('Query parent places')
 458             for result in results:
 459                 await complete_parented_places(conn, result)
 460         if details.keywords:
 461             log().comment('Query keywords')
 462             for result in results:
 463                 await complete_keywords(conn, result)
 464         for result in results:
 465             result.localize(details.locales)
 466
 467
 468 def _result_row_to_address_row(row: SaRow, isaddress: Optional[bool] = None) -> AddressLine:
 469     """ Create a new AddressLine from the results of a datbase query.
 470     """
 471     extratags: Dict[str, str] = getattr(row, 'extratags', {}) or {}
 472     if 'linked_place' in extratags:
 473         extratags['place'] = extratags['linked_place']
 474
 475     names = _mingle_name_tags(row.name) or {}
 476     if getattr(row, 'housenumber', None) is not None:
 477         names['housenumber'] = row.housenumber
 478
 479     if isaddress is None:
 480         isaddress = getattr(row, 'isaddress', True)
 481
 482     return AddressLine(place_id=row.place_id,
 483                        osm_object=None if row.osm_type is None else (row.osm_type, row.osm_id),
 484                        category=(getattr(row, 'class'), row.type),
 485                        names=names,
 486                        extratags=extratags,
 487                        admin_level=row.admin_level,
 488                        fromarea=row.fromarea,
 489                        isaddress=isaddress,
 490                        rank_address=row.rank_address,
 491                        distance=row.distance)
 492
 493
 494 def _get_address_lookup_id(result: BaseResultT) -> int:
 495     assert result.place_id
 496     if result.source_table != SourceTable.PLACEX or result.rank_search > 27:
 497         return result.parent_place_id or result.place_id
 498
 499     return result.linked_place_id or result.place_id
 500
 501
 502 async def _finalize_entry(conn: SearchConnection, result: BaseResultT) -> None:
 503     assert result.address_rows
 504     postcode = result.postcode
 505     if not postcode and result.address:
 506         postcode = result.address.get('postcode')
 507     if postcode and ',' not in postcode and ';' not in postcode:
 508         result.address_rows.append(AddressLine(
 509             category=('place', 'postcode'),
 510             names={'ref': postcode},
 511             fromarea=False, isaddress=True, rank_address=5,
 512             distance=0.0))
 513     if result.country_code:
 514         async def _get_country_names() -> Optional[Dict[str, str]]:
 515             t = conn.t.country_name
 516             sql = sa.select(t.c.name, t.c.derived_name)\
 517                     .where(t.c.country_code == result.country_code)
 518             for cres in await conn.execute(sql):
 519                 names = cast(Dict[str, str], cres[0])
 520                 if cres[1]:
 521                     names.update(cast(Dict[str, str], cres[1]))
 522                 return names
 523             return None
 524
 525         country_names = await conn.get_cached_value('COUNTRY_NAME',
 526                                                     result.country_code,
 527                                                     _get_country_names)
 528         if country_names:
 529             result.address_rows.append(AddressLine(
 530                 category=('place', 'country'),
 531                 names=country_names,
 532                 fromarea=False, isaddress=True, rank_address=4,
 533                 distance=0.0))
 534         result.address_rows.append(AddressLine(
 535             category=('place', 'country_code'),
 536             names={'ref': result.country_code}, extratags = {},
 537             fromarea=True, isaddress=False, rank_address=4,
 538             distance=0.0))
 539
 540
 541 def _setup_address_details(result: BaseResultT) -> None:
 542     """ Retrieve information about places that make up the address of the result.
 543     """
 544     result.address_rows = AddressLines()
 545     if result.names:
 546         result.address_rows.append(AddressLine(
 547             place_id=result.place_id,
 548             osm_object=result.osm_object,
 549             category=result.category,
 550             names=result.names,
 551             extratags=result.extratags or {},
 552             admin_level=result.admin_level,
 553             fromarea=True, isaddress=True,
 554             rank_address=result.rank_address or 100, distance=0.0))
 555     if result.source_table == SourceTable.PLACEX and result.address:
 556         housenumber = result.address.get('housenumber')\
 557                       or result.address.get('streetnumber')\
 558                       or result.address.get('conscriptionnumber')
 559     elif result.housenumber:
 560         housenumber = result.housenumber
 561     else:
 562         housenumber = None
 563     if housenumber:
 564         result.address_rows.append(AddressLine(
 565             category=('place', 'house_number'),
 566             names={'ref': housenumber},
 567             fromarea=True, isaddress=True, rank_address=28, distance=0))
 568     if result.address and '_unlisted_place' in result.address:
 569         result.address_rows.append(AddressLine(
 570             category=('place', 'locality'),
 571             names={'name': result.address['_unlisted_place']},
 572             fromarea=False, isaddress=True, rank_address=25, distance=0))
 573
 574
 575 async def complete_address_details(conn: SearchConnection, results: List[BaseResultT]) -> None:
 576     """ Retrieve information about places that make up the address of the result.
 577     """
 578     for result in results:
 579         _setup_address_details(result)
 580
 581     ### Lookup entries from place_address line
 582
 583     lookup_ids = [{'pid': r.place_id,
 584                    'lid': _get_address_lookup_id(r),
 585                    'names': list(r.address.values()) if r.address else [],
 586                    'c': ('SRID=4326;' + r.centroid.to_wkt()) if r.centroid else '' }
 587                   for r in results if r.place_id]
 588
 589     if not lookup_ids:
 590         return
 591
 592     ltab = sa.func.json_array_elements(sa.type_coerce(lookup_ids, sa.JSON))\
 593              .table_valued(sa.column('value', type_=sa.JSON)) # type: ignore[no-untyped-call]
 594
 595     t = conn.t.placex
 596     taddr = conn.t.addressline
 597
 598     sql = sa.select(ltab.c.value['pid'].as_integer().label('src_place_id'),
 599                     t.c.place_id, t.c.osm_type, t.c.osm_id, t.c.name,
 600                     t.c.class_, t.c.type, t.c.extratags,
 601                     t.c.admin_level, taddr.c.fromarea,
 602                     sa.case((t.c.rank_address == 11, 5),
 603                             else_=t.c.rank_address).label('rank_address'),
 604                     taddr.c.distance, t.c.country_code, t.c.postcode)\
 605             .join(taddr, sa.or_(taddr.c.place_id == ltab.c.value['pid'].as_integer(),
 606                                 taddr.c.place_id == ltab.c.value['lid'].as_integer()))\
 607             .join(t, taddr.c.address_place_id == t.c.place_id)\
 608             .order_by('src_place_id')\
 609             .order_by(sa.column('rank_address').desc())\
 610             .order_by((taddr.c.place_id == ltab.c.value['pid'].as_integer()).desc())\
 611             .order_by(sa.case((CrosscheckNames(t.c.name, ltab.c.value['names']), 2),
 612                               (taddr.c.isaddress, 0),
 613                               (sa.and_(taddr.c.fromarea,
 614                                        t.c.geometry.ST_Contains(
 615                                            sa.func.ST_GeomFromEWKT(
 616                                                ltab.c.value['c'].as_string()))), 1),
 617                               else_=-1).desc())\
 618             .order_by(taddr.c.fromarea.desc())\
 619             .order_by(taddr.c.distance.desc())\
 620             .order_by(t.c.rank_search.desc())
 621
 622
 623     current_result = None
 624     current_rank_address = -1
 625     for row in await conn.execute(sql):
 626         if current_result is None or row.src_place_id != current_result.place_id:
 627             current_result = next((r for r in results if r.place_id == row.src_place_id), None)
 628             assert current_result is not None
 629             current_rank_address = -1
 630
 631         location_isaddress = row.rank_address != current_rank_address
 632
 633         if current_result.country_code is None and row.country_code:
 634             current_result.country_code = row.country_code
 635
 636         if row.type in ('postcode', 'postal_code') and location_isaddress:
 637             if not row.fromarea or \
 638                (current_result.address and 'postcode' in current_result.address):
 639                 location_isaddress = False
 640             else:
 641                 current_result.postcode = None
 642
 643         assert current_result.address_rows is not None
 644         current_result.address_rows.append(_result_row_to_address_row(row, location_isaddress))
 645         current_rank_address = row.rank_address
 646
 647     for result in results:
 648         await _finalize_entry(conn, result)
 649
 650
 651     ### Finally add the record for the parent entry where necessary.
 652
 653     parent_lookup_ids = list(filter(lambda e: e['pid'] != e['lid'], lookup_ids))
 654     if parent_lookup_ids:
 655         ltab = sa.func.json_array_elements(sa.type_coerce(parent_lookup_ids, sa.JSON))\
 656                  .table_valued(sa.column('value', type_=sa.JSON)) # type: ignore[no-untyped-call]
 657         sql = sa.select(ltab.c.value['pid'].as_integer().label('src_place_id'),
 658                         t.c.place_id, t.c.osm_type, t.c.osm_id, t.c.name,
 659                         t.c.class_, t.c.type, t.c.extratags,
 660                         t.c.admin_level,
 661                         t.c.rank_address)\
 662                  .where(t.c.place_id == ltab.c.value['lid'].as_integer())
 663
 664         for row in await conn.execute(sql):
 665             current_result = next((r for r in results if r.place_id == row.src_place_id), None)
 666             assert current_result is not None
 667             assert current_result.address_rows is not None
 668
 669             current_result.address_rows.append(AddressLine(
 670                     place_id=row.place_id,
 671                     osm_object=(row.osm_type, row.osm_id),
 672                     category=(row.class_, row.type),
 673                     names=row.name, extratags=row.extratags or {},
 674                     admin_level=row.admin_level,
 675                     fromarea=True, isaddress=True,
 676                     rank_address=row.rank_address, distance=0.0))
 677
 678     ### Now sort everything
 679     for result in results:
 680         assert result.address_rows is not None
 681         result.address_rows.sort(key=lambda a: (-a.rank_address, a.isaddress))
 682
 683
 684 def _placex_select_address_row(conn: SearchConnection,
 685                                centroid: Point) -> SaSelect:
 686     t = conn.t.placex
 687     return sa.select(t.c.place_id, t.c.osm_type, t.c.osm_id, t.c.name,
 688                      t.c.class_.label('class'), t.c.type,
 689                      t.c.admin_level, t.c.housenumber,
 690                      sa.literal_column("""ST_GeometryType(geometry) in
 691                                         ('ST_Polygon','ST_MultiPolygon')""").label('fromarea'),
 692                      t.c.rank_address,
 693                      sa.literal_column(
 694                          f"""ST_DistanceSpheroid(geometry,
 695                                                  'SRID=4326;{centroid.to_wkt()}'::geometry,
 696                               'SPHEROID["WGS 84",6378137,298.257223563, AUTHORITY["EPSG","7030"]]')
 697                          """).label('distance'))
 698
 699
 700 async def complete_linked_places(conn: SearchConnection, result: BaseResult) -> None:
 701     """ Retrieve information about places that link to the result.
 702     """
 703     result.linked_rows = AddressLines()
 704     if result.source_table != SourceTable.PLACEX:
 705         return
 706
 707     sql = _placex_select_address_row(conn, result.centroid)\
 708             .where(conn.t.placex.c.linked_place_id == result.place_id)
 709
 710     for row in await conn.execute(sql):
 711         result.linked_rows.append(_result_row_to_address_row(row))
 712
 713
 714 async def complete_keywords(conn: SearchConnection, result: BaseResult) -> None:
 715     """ Retrieve information about the search terms used for this place.
 716
 717         Requires that the query analyzer was initialised to get access to
 718         the word table.
 719     """
 720     t = conn.t.search_name
 721     sql = sa.select(t.c.name_vector, t.c.nameaddress_vector)\
 722             .where(t.c.place_id == result.place_id)
 723
 724     result.name_keywords = []
 725     result.address_keywords = []
 726
 727     t = conn.t.meta.tables['word']
 728     sel = sa.select(t.c.word_id, t.c.word_token, t.c.word)
 729
 730     for name_tokens, address_tokens in await conn.execute(sql):
 731         for row in await conn.execute(sel.where(t.c.word_id == sa.any_(name_tokens))):
 732             result.name_keywords.append(WordInfo(*row))
 733
 734         for row in await conn.execute(sel.where(t.c.word_id == sa.any_(address_tokens))):
 735             result.address_keywords.append(WordInfo(*row))
 736
 737
 738 async def complete_parented_places(conn: SearchConnection, result: BaseResult) -> None:
 739     """ Retrieve information about places that the result provides the
 740         address for.
 741     """
 742     result.parented_rows = AddressLines()
 743     if result.source_table != SourceTable.PLACEX:
 744         return
 745
 746     sql = _placex_select_address_row(conn, result.centroid)\
 747             .where(conn.t.placex.c.parent_place_id == result.place_id)\
 748             .where(conn.t.placex.c.rank_search == 30)
 749
 750     for row in await conn.execute(sql):
 751         result.parented_rows.append(_result_row_to_address_row(row))