src/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) 2024 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, Callable
  15 import enum
  16 import dataclasses
  17 import datetime as dt
  18
  19 import sqlalchemy as sa
  20
  21 from .typing import SaSelect, SaRow
  22 from .sql.sqlalchemy_types import Geometry
  23 from .types import Point, Bbox, LookupDetails
  24 from .connection import SearchConnection
  25 from .logging import log
  26 from .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](Result-Handling.md#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.40001 - (self.rank_search/75.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 database 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 is not None
 504     if result.category[0] not in ('boundary', 'place')\
 505        or result.category[1] not in ('postal_code', 'postcode'):
 506         postcode = result.postcode
 507         if not postcode and result.address:
 508             postcode = result.address.get('postcode')
 509         if postcode and ',' not in postcode and ';' not in postcode:
 510             result.address_rows.append(AddressLine(
 511                 category=('place', 'postcode'),
 512                 names={'ref': postcode},
 513                 fromarea=False, isaddress=True, rank_address=5,
 514                 distance=0.0))
 515     if result.country_code:
 516         async def _get_country_names() -> Optional[Dict[str, str]]:
 517             t = conn.t.country_name
 518             sql = sa.select(t.c.name, t.c.derived_name)\
 519                     .where(t.c.country_code == result.country_code)
 520             for cres in await conn.execute(sql):
 521                 names = cast(Dict[str, str], cres[0])
 522                 if cres[1]:
 523                     names.update(cast(Dict[str, str], cres[1]))
 524                 return names
 525             return None
 526
 527         country_names = await conn.get_cached_value('COUNTRY_NAME',
 528                                                     result.country_code,
 529                                                     _get_country_names)
 530         if country_names:
 531             result.address_rows.append(AddressLine(
 532                 category=('place', 'country'),
 533                 names=country_names,
 534                 fromarea=False, isaddress=True, rank_address=4,
 535                 distance=0.0))
 536         result.address_rows.append(AddressLine(
 537             category=('place', 'country_code'),
 538             names={'ref': result.country_code}, extratags = {},
 539             fromarea=True, isaddress=False, rank_address=4,
 540             distance=0.0))
 541
 542
 543 def _setup_address_details(result: BaseResultT) -> None:
 544     """ Retrieve information about places that make up the address of the result.
 545     """
 546     result.address_rows = AddressLines()
 547     if result.names:
 548         result.address_rows.append(AddressLine(
 549             place_id=result.place_id,
 550             osm_object=result.osm_object,
 551             category=result.category,
 552             names=result.names,
 553             extratags=result.extratags or {},
 554             admin_level=result.admin_level,
 555             fromarea=True, isaddress=True,
 556             rank_address=result.rank_address, distance=0.0))
 557     if result.source_table == SourceTable.PLACEX and result.address:
 558         housenumber = result.address.get('housenumber')\
 559                       or result.address.get('streetnumber')\
 560                       or result.address.get('conscriptionnumber')
 561     elif result.housenumber:
 562         housenumber = result.housenumber
 563     else:
 564         housenumber = None
 565     if housenumber:
 566         result.address_rows.append(AddressLine(
 567             category=('place', 'house_number'),
 568             names={'ref': housenumber},
 569             fromarea=True, isaddress=True, rank_address=28, distance=0))
 570     if result.address and '_unlisted_place' in result.address:
 571         result.address_rows.append(AddressLine(
 572             category=('place', 'locality'),
 573             names={'name': result.address['_unlisted_place']},
 574             fromarea=False, isaddress=True, rank_address=25, distance=0))
 575
 576
 577 async def complete_address_details(conn: SearchConnection, results: List[BaseResultT]) -> None:
 578     """ Retrieve information about places that make up the address of the result.
 579     """
 580     for result in results:
 581         _setup_address_details(result)
 582
 583     ### Lookup entries from place_address line
 584
 585     lookup_ids = [{'pid': r.place_id,
 586                    'lid': _get_address_lookup_id(r),
 587                    'names': list(r.address.values()) if r.address else [],
 588                    'c': ('SRID=4326;' + r.centroid.to_wkt()) if r.centroid else '' }
 589                   for r in results if r.place_id]
 590
 591     if not lookup_ids:
 592         return
 593
 594     ltab = sa.func.JsonArrayEach(sa.type_coerce(lookup_ids, sa.JSON))\
 595              .table_valued(sa.column('value', type_=sa.JSON))
 596
 597     t = conn.t.placex
 598     taddr = conn.t.addressline
 599
 600     sql = sa.select(ltab.c.value['pid'].as_integer().label('src_place_id'),
 601                     t.c.place_id, t.c.osm_type, t.c.osm_id, t.c.name,
 602                     t.c.class_, t.c.type, t.c.extratags,
 603                     t.c.admin_level, taddr.c.fromarea,
 604                     sa.case((t.c.rank_address == 11, 5),
 605                             else_=t.c.rank_address).label('rank_address'),
 606                     taddr.c.distance, t.c.country_code, t.c.postcode)\
 607             .join(taddr, sa.or_(taddr.c.place_id == ltab.c.value['pid'].as_integer(),
 608                                 taddr.c.place_id == ltab.c.value['lid'].as_integer()))\
 609             .join(t, taddr.c.address_place_id == t.c.place_id)\
 610             .order_by('src_place_id')\
 611             .order_by(sa.column('rank_address').desc())\
 612             .order_by((taddr.c.place_id == ltab.c.value['pid'].as_integer()).desc())\
 613             .order_by(sa.case((sa.func.CrosscheckNames(t.c.name, ltab.c.value['names']), 2),
 614                               (taddr.c.isaddress, 0),
 615                               (sa.and_(taddr.c.fromarea,
 616                                        t.c.geometry.ST_Contains(
 617                                            sa.func.ST_GeomFromEWKT(
 618                                                ltab.c.value['c'].as_string()))), 1),
 619                               else_=-1).desc())\
 620             .order_by(taddr.c.fromarea.desc())\
 621             .order_by(taddr.c.distance.desc())\
 622             .order_by(t.c.rank_search.desc())
 623
 624
 625     current_result = None
 626     current_rank_address = -1
 627     for row in await conn.execute(sql):
 628         if current_result is None or row.src_place_id != current_result.place_id:
 629             current_result = next((r for r in results if r.place_id == row.src_place_id), None)
 630             assert current_result is not None
 631             current_rank_address = -1
 632
 633         location_isaddress = row.rank_address != current_rank_address
 634
 635         if current_result.country_code is None and row.country_code:
 636             current_result.country_code = row.country_code
 637
 638         if row.type in ('postcode', 'postal_code') and location_isaddress:
 639             if not row.fromarea or \
 640                (current_result.address and 'postcode' in current_result.address):
 641                 location_isaddress = False
 642             else:
 643                 current_result.postcode = None
 644
 645         assert current_result.address_rows is not None
 646         current_result.address_rows.append(_result_row_to_address_row(row, location_isaddress))
 647         current_rank_address = row.rank_address
 648
 649     for result in results:
 650         await _finalize_entry(conn, result)
 651
 652
 653     ### Finally add the record for the parent entry where necessary.
 654
 655     parent_lookup_ids = list(filter(lambda e: e['pid'] != e['lid'], lookup_ids))
 656     if parent_lookup_ids:
 657         ltab = sa.func.JsonArrayEach(sa.type_coerce(parent_lookup_ids, sa.JSON))\
 658                  .table_valued(sa.column('value', type_=sa.JSON))
 659         sql = sa.select(ltab.c.value['pid'].as_integer().label('src_place_id'),
 660                         t.c.place_id, t.c.osm_type, t.c.osm_id, t.c.name,
 661                         t.c.class_, t.c.type, t.c.extratags,
 662                         t.c.admin_level,
 663                         t.c.rank_address)\
 664                  .where(t.c.place_id == ltab.c.value['lid'].as_integer())
 665
 666         for row in await conn.execute(sql):
 667             current_result = next((r for r in results if r.place_id == row.src_place_id), None)
 668             assert current_result is not None
 669             assert current_result.address_rows is not None
 670
 671             current_result.address_rows.append(AddressLine(
 672                     place_id=row.place_id,
 673                     osm_object=(row.osm_type, row.osm_id),
 674                     category=(row.class_, row.type),
 675                     names=row.name, extratags=row.extratags or {},
 676                     admin_level=row.admin_level,
 677                     fromarea=True, isaddress=True,
 678                     rank_address=row.rank_address, distance=0.0))
 679
 680     ### Now sort everything
 681     def mk_sort_key(place_id: Optional[int]) -> Callable[[AddressLine], Tuple[bool, int, bool]]:
 682         return lambda a: (a.place_id != place_id, -a.rank_address, a.isaddress)
 683
 684     for result in results:
 685         assert result.address_rows is not None
 686         result.address_rows.sort(key=mk_sort_key(result.place_id))
 687
 688
 689 def _placex_select_address_row(conn: SearchConnection,
 690                                centroid: Point) -> SaSelect:
 691     t = conn.t.placex
 692     return sa.select(t.c.place_id, t.c.osm_type, t.c.osm_id, t.c.name,
 693                      t.c.class_.label('class'), t.c.type,
 694                      t.c.admin_level, t.c.housenumber,
 695                      t.c.geometry.is_area().label('fromarea'),
 696                      t.c.rank_address,
 697                      t.c.geometry.distance_spheroid(
 698                        sa.bindparam('centroid', value=centroid, type_=Geometry)).label('distance'))
 699
 700
 701 async def complete_linked_places(conn: SearchConnection, result: BaseResult) -> None:
 702     """ Retrieve information about places that link to the result.
 703     """
 704     result.linked_rows = AddressLines()
 705     if result.source_table != SourceTable.PLACEX:
 706         return
 707
 708     sql = _placex_select_address_row(conn, result.centroid)\
 709             .where(conn.t.placex.c.linked_place_id == result.place_id)
 710
 711     for row in await conn.execute(sql):
 712         result.linked_rows.append(_result_row_to_address_row(row))
 713
 714
 715 async def complete_keywords(conn: SearchConnection, result: BaseResult) -> None:
 716     """ Retrieve information about the search terms used for this place.
 717
 718         Requires that the query analyzer was initialised to get access to
 719         the word table.
 720     """
 721     t = conn.t.search_name
 722     sql = sa.select(t.c.name_vector, t.c.nameaddress_vector)\
 723             .where(t.c.place_id == result.place_id)
 724
 725     result.name_keywords = []
 726     result.address_keywords = []
 727
 728     t = conn.t.meta.tables['word']
 729     sel = sa.select(t.c.word_id, t.c.word_token, t.c.word)
 730
 731     for name_tokens, address_tokens in await conn.execute(sql):
 732         for row in await conn.execute(sel.where(t.c.word_id.in_(name_tokens))):
 733             result.name_keywords.append(WordInfo(*row))
 734
 735         for row in await conn.execute(sel.where(t.c.word_id.in_(address_tokens))):
 736             result.address_keywords.append(WordInfo(*row))
 737
 738
 739 async def complete_parented_places(conn: SearchConnection, result: BaseResult) -> None:
 740     """ Retrieve information about places that the result provides the
 741         address for.
 742     """
 743     result.parented_rows = AddressLines()
 744     if result.source_table != SourceTable.PLACEX:
 745         return
 746
 747     sql = _placex_select_address_row(conn, result.centroid)\
 748             .where(conn.t.placex.c.parent_place_id == result.place_id)\
 749             .where(conn.t.placex.c.rank_search == 30)
 750
 751     for row in await conn.execute(sql):
 752         result.parented_rows.append(_result_row_to_address_row(row))