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
 440
 441 async def add_result_details(conn: SearchConnection, results: List[BaseResultT],
 442                              details: LookupDetails) -> None:
 443     """ Retrieve more details from the database according to the
 444         parameters specified in 'details'.
 445     """
 446     if results:
 447         log().section('Query details for result')
 448         if details.address_details:
 449             log().comment('Query address details')
 450             await complete_address_details(conn, results)
 451         if details.linked_places:
 452             log().comment('Query linked places')
 453             for result in results:
 454                 await complete_linked_places(conn, result)
 455         if details.parented_places:
 456             log().comment('Query parent places')
 457             for result in results:
 458                 await complete_parented_places(conn, result)
 459         if details.keywords:
 460             log().comment('Query keywords')
 461             for result in results:
 462                 await complete_keywords(conn, result)
 463         for result in results:
 464             result.localize(details.locales)
 465
 466
 467 def _result_row_to_address_row(row: SaRow, isaddress: Optional[bool] = None) -> AddressLine:
 468     """ Create a new AddressLine from the results of a datbase query.
 469     """
 470     extratags: Dict[str, str] = getattr(row, 'extratags', {}) or {}
 471     if 'linked_place' in extratags:
 472         extratags['place'] = extratags['linked_place']
 473
 474     names = _mingle_name_tags(row.name) or {}
 475     if getattr(row, 'housenumber', None) is not None:
 476         names['housenumber'] = row.housenumber
 477
 478     if isaddress is None:
 479         isaddress = getattr(row, 'isaddress', True)
 480
 481     return AddressLine(place_id=row.place_id,
 482                        osm_object=None if row.osm_type is None else (row.osm_type, row.osm_id),
 483                        category=(getattr(row, 'class'), row.type),
 484                        names=names,
 485                        extratags=extratags,
 486                        admin_level=row.admin_level,
 487                        fromarea=row.fromarea,
 488                        isaddress=isaddress,
 489                        rank_address=row.rank_address,
 490                        distance=row.distance)
 491
 492
 493 def _get_housenumber_details(results: List[BaseResultT]) -> Tuple[List[int], List[int]]:
 494     places = []
 495     hnrs = []
 496     for result in results:
 497         if result.place_id:
 498             housenumber = -1
 499             if result.source_table in (SourceTable.TIGER, SourceTable.OSMLINE):
 500                 if result.housenumber is not None:
 501                     housenumber = int(result.housenumber)
 502                 elif result.extratags is not None and 'startnumber' in result.extratags:
 503                     # details requests do not come with a specific house number
 504                     housenumber = int(result.extratags['startnumber'])
 505             places.append(result.place_id)
 506             hnrs.append(housenumber)
 507
 508     return places, hnrs
 509
 510
 511 def _get_address_lookup_id(result: BaseResultT) -> int:
 512     assert result.place_id
 513     if result.source_table != SourceTable.PLACEX or result.rank_search > 27:
 514         return result.parent_place_id or result.place_id
 515
 516     return result.linked_place_id or result.place_id
 517
 518
 519 async def _finalize_entry(conn: SearchConnection, result: BaseResultT) -> None:
 520     assert result.address_rows
 521     postcode = result.postcode
 522     if not postcode and result.address:
 523         postcode = result.address.get('postcode')
 524     if postcode and ',' not in postcode and ';' not in postcode:
 525         result.address_rows.append(AddressLine(
 526             category=('place', 'postcode'),
 527             names={'ref': postcode},
 528             fromarea=False, isaddress=True, rank_address=5,
 529             distance=0.0))
 530     if result.country_code:
 531         async def _get_country_names() -> Optional[Dict[str, str]]:
 532             t = conn.t.country_name
 533             sql = sa.select(t.c.name, t.c.derived_name)\
 534                     .where(t.c.country_code == result.country_code)
 535             for cres in await conn.execute(sql):
 536                 names = cast(Dict[str, str], cres[0])
 537                 if cres[1]:
 538                     names.update(cast(Dict[str, str], cres[1]))
 539                 return names
 540             return None
 541
 542         country_names = await conn.get_cached_value('COUNTRY_NAME',
 543                                                     result.country_code,
 544                                                     _get_country_names)
 545         if country_names:
 546             result.address_rows.append(AddressLine(
 547                 category=('place', 'country'),
 548                 names=country_names,
 549                 fromarea=False, isaddress=True, rank_address=4,
 550                 distance=0.0))
 551         result.address_rows.append(AddressLine(
 552             category=('place', 'country_code'),
 553             names={'ref': result.country_code}, extratags = {},
 554             fromarea=True, isaddress=False, rank_address=4,
 555             distance=0.0))
 556
 557
 558 def _setup_address_details(result: BaseResultT) -> None:
 559     """ Retrieve information about places that make up the address of the result.
 560     """
 561     result.address_rows = AddressLines()
 562     if result.names:
 563         result.address_rows.append(AddressLine(
 564             place_id=result.place_id,
 565             osm_object=result.osm_object,
 566             category=result.category,
 567             names=result.names,
 568             extratags=result.extratags or {},
 569             admin_level=result.admin_level,
 570             fromarea=True, isaddress=True,
 571             rank_address=result.rank_address, distance=0.0))
 572     if result.source_table == SourceTable.PLACEX and result.address:
 573         housenumber = result.address.get('housenumber')\
 574                       or result.address.get('streetnumber')\
 575                       or result.address.get('conscriptionnumber')
 576     elif result.housenumber:
 577         housenumber = result.housenumber
 578     else:
 579         housenumber = None
 580     if housenumber:
 581         result.address_rows.append(AddressLine(
 582             category=('place', 'house_number'),
 583             names={'ref': housenumber},
 584             fromarea=True, isaddress=True, rank_address=28, distance=0))
 585     if result.address and '_unlisted_place' in result.address:
 586         result.address_rows.append(AddressLine(
 587             category=('place', 'locality'),
 588             names={'name': result.address['_unlisted_place']},
 589             fromarea=False, isaddress=True, rank_address=25, distance=0))
 590
 591
 592 async def complete_address_details(conn: SearchConnection, results: List[BaseResultT]) -> None:
 593     """ Retrieve information about places that make up the address of the result.
 594     """
 595     for result in results:
 596         _setup_address_details(result)
 597
 598     ### Lookup entries from place_address line
 599
 600     lookup_ids = [{'pid': r.place_id,
 601                    'lid': _get_address_lookup_id(r),
 602                    'names': list(r.address.values()) if r.address else [],
 603                    'c': ('SRID=4326;' + r.centroid.to_wkt()) if r.centroid else '' }
 604                   for r in results if r.place_id]
 605
 606     if not lookup_ids:
 607         return
 608
 609     ltab = sa.func.json_array_elements(sa.type_coerce(lookup_ids, sa.JSON))\
 610              .table_valued(sa.column('value', type_=sa.JSON)) # type: ignore[no-untyped-call]
 611
 612     t = conn.t.placex
 613     taddr = conn.t.addressline
 614
 615     sql = sa.select(ltab.c.value['pid'].as_integer().label('src_place_id'),
 616                     t.c.place_id, t.c.osm_type, t.c.osm_id, t.c.name,
 617                     t.c.class_, t.c.type, t.c.extratags,
 618                     t.c.admin_level, taddr.c.fromarea,
 619                     sa.case((t.c.rank_address == 11, 5),
 620                             else_=t.c.rank_address).label('rank_address'),
 621                     taddr.c.distance, t.c.country_code, t.c.postcode)\
 622             .join(taddr, sa.or_(taddr.c.place_id == ltab.c.value['pid'].as_integer(),
 623                                 taddr.c.place_id == ltab.c.value['lid'].as_integer()))\
 624             .join(t, taddr.c.address_place_id == t.c.place_id)\
 625             .order_by('src_place_id')\
 626             .order_by(sa.column('rank_address').desc())\
 627             .order_by((taddr.c.place_id == ltab.c.value['pid'].as_integer()).desc())\
 628             .order_by(sa.case((CrosscheckNames(t.c.name, ltab.c.value['names']), 2),
 629                               (taddr.c.isaddress, 0),
 630                               (sa.and_(taddr.c.fromarea,
 631                                        t.c.geometry.ST_Contains(
 632                                            sa.func.ST_GeomFromEWKT(
 633                                                ltab.c.value['c'].as_string()))), 1),
 634                               else_=-1).desc())\
 635             .order_by(taddr.c.fromarea.desc())\
 636             .order_by(taddr.c.distance.desc())\
 637             .order_by(t.c.rank_search.desc())
 638
 639
 640     current_result = None
 641     current_rank_address = -1
 642     for row in await conn.execute(sql):
 643         if current_result is None or row.src_place_id != current_result.place_id:
 644             current_result = next((r for r in results if r.place_id == row.src_place_id), None)
 645             assert current_result is not None
 646             current_rank_address = -1
 647
 648         location_isaddress = row.rank_address != current_rank_address
 649
 650         if current_result.country_code is None and row.country_code:
 651             current_result.country_code = row.country_code
 652
 653         if row.type in ('postcode', 'postal_code') and location_isaddress:
 654             if not row.fromarea or \
 655                (current_result.address and 'postcode' in current_result.address):
 656                 location_isaddress = False
 657             else:
 658                 current_result.postcode = None
 659
 660         assert current_result.address_rows is not None
 661         current_result.address_rows.append(_result_row_to_address_row(row, location_isaddress))
 662         current_rank_address = row.rank_address
 663
 664     for result in results:
 665         await _finalize_entry(conn, result)
 666
 667
 668     ### Finally add the record for the parent entry where necessary.
 669
 670     parent_lookup_ids = list(filter(lambda e: e['pid'] != e['lid'], lookup_ids))
 671     if parent_lookup_ids:
 672         ltab = sa.func.json_array_elements(sa.type_coerce(parent_lookup_ids, sa.JSON))\
 673                  .table_valued(sa.column('value', type_=sa.JSON)) # type: ignore[no-untyped-call]
 674         sql = sa.select(ltab.c.value['pid'].as_integer().label('src_place_id'),
 675                         t.c.place_id, t.c.osm_type, t.c.osm_id, t.c.name,
 676                         t.c.class_, t.c.type, t.c.extratags,
 677                         t.c.admin_level,
 678                         t.c.rank_address)\
 679                  .where(t.c.place_id == ltab.c.value['lid'].as_integer())
 680
 681         for row in await conn.execute(sql):
 682             current_result = next((r for r in results if r.place_id == row.src_place_id), None)
 683             assert current_result is not None
 684             assert current_result.address_rows is not None
 685
 686             current_result.address_rows.append(AddressLine(
 687                     place_id=row.place_id,
 688                     osm_object=(row.osm_type, row.osm_id),
 689                     category=(row.class_, row.type),
 690                     names=row.name, extratags=row.extratags or {},
 691                     admin_level=row.admin_level,
 692                     fromarea=True, isaddress=True,
 693                     rank_address=row.rank_address, distance=0.0))
 694
 695     ### Now sort everything
 696     for result in results:
 697         assert result.address_rows is not None
 698         result.address_rows.sort(key=lambda a: (-a.rank_address, a.isaddress))
 699
 700
 701 def _placex_select_address_row(conn: SearchConnection,
 702                                centroid: Point) -> SaSelect:
 703     t = conn.t.placex
 704     return sa.select(t.c.place_id, t.c.osm_type, t.c.osm_id, t.c.name,
 705                      t.c.class_.label('class'), t.c.type,
 706                      t.c.admin_level, t.c.housenumber,
 707                      sa.literal_column("""ST_GeometryType(geometry) in
 708                                         ('ST_Polygon','ST_MultiPolygon')""").label('fromarea'),
 709                      t.c.rank_address,
 710                      sa.literal_column(
 711                          f"""ST_DistanceSpheroid(geometry,
 712                                                  'SRID=4326;{centroid.to_wkt()}'::geometry,
 713                               'SPHEROID["WGS 84",6378137,298.257223563, AUTHORITY["EPSG","7030"]]')
 714                          """).label('distance'))
 715
 716
 717 async def complete_linked_places(conn: SearchConnection, result: BaseResult) -> None:
 718     """ Retrieve information about places that link to the result.
 719     """
 720     result.linked_rows = AddressLines()
 721     if result.source_table != SourceTable.PLACEX:
 722         return
 723
 724     sql = _placex_select_address_row(conn, result.centroid)\
 725             .where(conn.t.placex.c.linked_place_id == result.place_id)
 726
 727     for row in await conn.execute(sql):
 728         result.linked_rows.append(_result_row_to_address_row(row))
 729
 730
 731 async def complete_keywords(conn: SearchConnection, result: BaseResult) -> None:
 732     """ Retrieve information about the search terms used for this place.
 733
 734         Requires that the query analyzer was initialised to get access to
 735         the word table.
 736     """
 737     t = conn.t.search_name
 738     sql = sa.select(t.c.name_vector, t.c.nameaddress_vector)\
 739             .where(t.c.place_id == result.place_id)
 740
 741     result.name_keywords = []
 742     result.address_keywords = []
 743
 744     t = conn.t.meta.tables['word']
 745     sel = sa.select(t.c.word_id, t.c.word_token, t.c.word)
 746
 747     for name_tokens, address_tokens in await conn.execute(sql):
 748         for row in await conn.execute(sel.where(t.c.word_id == sa.any_(name_tokens))):
 749             result.name_keywords.append(WordInfo(*row))
 750
 751         for row in await conn.execute(sel.where(t.c.word_id == sa.any_(address_tokens))):
 752             result.address_keywords.append(WordInfo(*row))
 753
 754
 755 async def complete_parented_places(conn: SearchConnection, result: BaseResult) -> None:
 756     """ Retrieve information about places that the result provides the
 757         address for.
 758     """
 759     result.parented_rows = AddressLines()
 760     if result.source_table != SourceTable.PLACEX:
 761         return
 762
 763     sql = _placex_select_address_row(conn, result.centroid)\
 764             .where(conn.t.placex.c.parent_place_id == result.place_id)\
 765             .where(conn.t.placex.c.rank_search == 30)
 766
 767     for row in await conn.execute(sql):
 768         result.parented_rows.append(_result_row_to_address_row(row))