Also switches to using bind parameters for recurring parameters.
) -> Any:
""" Execute a 'scalar()' query on the connection.
"""
) -> Any:
""" Execute a 'scalar()' query on the connection.
"""
- log().sql(self.connection, sql)
+ log().sql(self.connection, sql, params)
return await self.connection.scalar(sql, params)
return await self.connection.scalar(sql, params)
) -> 'sa.Result[Any]':
""" Execute a 'execute()' query on the connection.
"""
) -> 'sa.Result[Any]':
""" Execute a 'execute()' query on the connection.
"""
- log().sql(self.connection, sql)
+ log().sql(self.connection, sql, params)
return await self.connection.execute(sql, params)
return await self.connection.execute(sql, params)
username=dsn.get('user'), password=dsn.get('password'),
host=dsn.get('host'), port=int(dsn['port']) if 'port' in dsn else None,
query=query)
username=dsn.get('user'), password=dsn.get('password'),
host=dsn.get('host'), port=int(dsn['port']) if 'port' in dsn else None,
query=query)
- engine = sa_asyncio.create_async_engine(dburl, future=True)
+ engine = sa_asyncio.create_async_engine(dburl, future=True,
+ echo=self.config.get_bool('DEBUG_SQL'))
try:
async with engine.begin() as conn:
try:
async with engine.begin() as conn:
"""
Functions for specialised logging with HTML output.
"""
"""
Functions for specialised logging with HTML output.
"""
-from typing import Any, Iterator, Optional, List, Tuple, cast
+from typing import Any, Iterator, Optional, List, Tuple, cast, Union, Mapping, Sequence
from contextvars import ContextVar
import datetime as dt
import textwrap
from contextvars import ContextVar
import datetime as dt
import textwrap
- def sql(self, conn: AsyncConnection, statement: 'sa.Executable') -> None:
+ def sql(self, conn: AsyncConnection, statement: 'sa.Executable',
+ params: Union[Mapping[str, Any], Sequence[Mapping[str, Any]], None]) -> None:
""" Print the SQL for the given statement.
"""
""" Print the SQL for the given statement.
"""
- def format_sql(self, conn: AsyncConnection, statement: 'sa.Executable') -> str:
+ def format_sql(self, conn: AsyncConnection, statement: 'sa.Executable',
+ extra_params: Union[Mapping[str, Any], Sequence[Mapping[str, Any]], None]) -> str:
""" Return the comiled version of the statement.
"""
""" Return the comiled version of the statement.
"""
- try:
- return str(cast('sa.ClauseElement', statement)
- .compile(conn.sync_engine, compile_kwargs={"literal_binds": True}))
- except sa.exc.CompileError:
- pass
- except NotImplementedError:
- pass
+ compiled = cast('sa.ClauseElement', statement).compile(conn.sync_engine)
- return str(cast('sa.ClauseElement', statement).compile(conn.sync_engine))
+ params = dict(compiled.params)
+ if isinstance(extra_params, Mapping):
+ for k, v in extra_params.items():
+ params[k] = str(v)
+ elif isinstance(extra_params, Sequence) and extra_params:
+ for k in extra_params[0]:
+ params[k] = f':{k}'
+
+ return str(compiled) % params
class HTMLLogger(BaseLogger):
class HTMLLogger(BaseLogger):
self._write(f'</dl><b>TOTAL:</b> {total}</p>')
self._write(f'</dl><b>TOTAL:</b> {total}</p>')
- def sql(self, conn: AsyncConnection, statement: 'sa.Executable') -> None:
+ def sql(self, conn: AsyncConnection, statement: 'sa.Executable',
+ params: Union[Mapping[str, Any], Sequence[Mapping[str, Any]], None]) -> None:
- sqlstr = self.format_sql(conn, statement)
+ sqlstr = self.format_sql(conn, statement, params)
if CODE_HIGHLIGHT:
sqlstr = highlight(sqlstr, PostgresLexer(),
HtmlFormatter(nowrap=True, lineseparator='<br />'))
if CODE_HIGHLIGHT:
sqlstr = highlight(sqlstr, PostgresLexer(),
HtmlFormatter(nowrap=True, lineseparator='<br />'))
self._write(f'TOTAL: {total}\n\n')
self._write(f'TOTAL: {total}\n\n')
- def sql(self, conn: AsyncConnection, statement: 'sa.Executable') -> None:
- sqlstr = '\n| '.join(textwrap.wrap(self.format_sql(conn, statement), width=78))
+ def sql(self, conn: AsyncConnection, statement: 'sa.Executable',
+ params: Union[Mapping[str, Any], Sequence[Mapping[str, Any]], None]) -> None:
+ sqlstr = '\n| '.join(textwrap.wrap(self.format_sql(conn, statement, params), width=78))
self._write(f"| {sqlstr}\n\n")
self._write(f"| {sqlstr}\n\n")
rank_search=row.rank_search,
importance=row.importance,
country_code=row.country_code,
rank_search=row.rank_search,
importance=row.importance,
country_code=row.country_code,
- centroid=Point.from_wkb(row.centroid.data),
+ centroid=Point.from_wkb(row.centroid),
geometry=_filter_geometries(row))
geometry=_filter_geometries(row))
address=row.address,
postcode=row.postcode,
country_code=row.country_code,
address=row.address,
postcode=row.postcode,
country_code=row.country_code,
- centroid=Point.from_wkb(row.centroid.data),
+ centroid=Point.from_wkb(row.centroid),
geometry=_filter_geometries(row))
if hnr is None:
geometry=_filter_geometries(row))
if hnr is None:
category=('place', 'houses' if hnr is None else 'house'),
postcode=row.postcode,
country_code='us',
category=('place', 'houses' if hnr is None else 'house'),
postcode=row.postcode,
country_code='us',
- centroid=Point.from_wkb(row.centroid.data),
+ centroid=Point.from_wkb(row.centroid),
geometry=_filter_geometries(row))
if hnr is None:
geometry=_filter_geometries(row))
if hnr is None:
rank_search=row.rank_search,
rank_address=row.rank_address,
country_code=row.country_code,
rank_search=row.rank_search,
rank_address=row.rank_address,
country_code=row.country_code,
- centroid=Point.from_wkb(row.centroid.data),
+ centroid=Point.from_wkb(row.centroid),
geometry=_filter_geometries(row))
geometry=_filter_geometries(row))
return class_type(source_table=SourceTable.COUNTRY,
category=('place', 'country'),
return class_type(source_table=SourceTable.COUNTRY,
category=('place', 'country'),
- centroid=Point.from_wkb(row.centroid.data),
+ centroid=Point.from_wkb(row.centroid),
names=row.name,
rank_address=4, rank_search=4,
country_code=row.country_code)
names=row.name,
rank_address=4, rank_search=4,
country_code=row.country_code)
import nominatim.api.results as nres
from nominatim.api.logging import log
from nominatim.api.types import AnyPoint, DataLayer, ReverseDetails, GeometryFormat, Bbox
import nominatim.api.results as nres
from nominatim.api.logging import log
from nominatim.api.types import AnyPoint, DataLayer, ReverseDetails, GeometryFormat, Bbox
+from nominatim.db.sqlalchemy_types import Geometry
# In SQLAlchemy expression which compare with NULL need to be expressed with
# the equal sign.
# In SQLAlchemy expression which compare with NULL need to be expressed with
# the equal sign.
RowFunc = Callable[[Optional[SaRow], Type[nres.ReverseResult]], Optional[nres.ReverseResult]]
RowFunc = Callable[[Optional[SaRow], Type[nres.ReverseResult]], Optional[nres.ReverseResult]]
-def _select_from_placex(t: SaFromClause, wkt: Optional[str] = None) -> SaSelect:
+WKT_PARAM = sa.bindparam('wkt', type_=Geometry)
+MAX_RANK_PARAM = sa.bindparam('max_rank')
+
+def _select_from_placex(t: SaFromClause, use_wkt: bool = True) -> SaSelect:
""" Create a select statement with the columns relevant for reverse
results.
"""
""" Create a select statement with the columns relevant for reverse
results.
"""
distance = t.c.distance
centroid = t.c.centroid
else:
distance = t.c.distance
centroid = t.c.centroid
else:
- distance = t.c.geometry.ST_Distance(wkt)
- centroid = sa.case((t.c.geometry.is_line_like(), t.c.geometry.ST_ClosestPoint(wkt)),
+ distance = t.c.geometry.ST_Distance(WKT_PARAM)
+ centroid = sa.case((t.c.geometry.is_line_like(), t.c.geometry.ST_ClosestPoint(WKT_PARAM)),
else_=t.c.centroid).label('centroid')
else_=t.c.centroid).label('centroid')
else_=table.c.linegeo.ST_LineInterpolatePoint(rounded_pos)).label('centroid')
else_=table.c.linegeo.ST_LineInterpolatePoint(rounded_pos)).label('centroid')
-def _locate_interpolation(table: SaFromClause, wkt: str) -> SaLabel:
+def _locate_interpolation(table: SaFromClause) -> SaLabel:
""" Given a position, locate the closest point on the line.
"""
""" Given a position, locate the closest point on the line.
"""
- return sa.case((table.c.linegeo.is_line_like(), table.c.linegeo.ST_LineLocatePoint(wkt)),
+ return sa.case((table.c.linegeo.is_line_like(),
+ table.c.linegeo.ST_LineLocatePoint(WKT_PARAM)),
else_=0).label('position')
else_=0).label('position')
sa.or_(table.c.housenumber != None,
table.c.name.has_key('housename')))
sa.or_(table.c.housenumber != None,
table.c.name.has_key('housename')))
def _get_closest(*rows: Optional[SaRow]) -> Optional[SaRow]:
return min(rows, key=lambda row: 1000 if row is None else row.distance)
def _get_closest(*rows: Optional[SaRow]) -> Optional[SaRow]:
return min(rows, key=lambda row: 1000 if row is None else row.distance)
class ReverseGeocoder:
""" Class implementing the logic for looking up a place from a
coordinate.
class ReverseGeocoder:
""" Class implementing the logic for looking up a place from a
coordinate.
self.conn = conn
self.params = params
self.conn = conn
self.params = params
+ self.bind_params = {'max_rank': params.max_rank}
+
@property
def max_rank(self) -> int:
@property
def max_rank(self) -> int:
"""
return self.layer_enabled(DataLayer.RAILWAY, DataLayer.MANMADE, DataLayer.NATURAL)
"""
return self.layer_enabled(DataLayer.RAILWAY, DataLayer.MANMADE, DataLayer.NATURAL)
def _add_geometry_columns(self, sql: SaSelect, col: SaColumn) -> SaSelect:
if not self.has_geometries():
return sql
def _add_geometry_columns(self, sql: SaSelect, col: SaColumn) -> SaSelect:
if not self.has_geometries():
return sql
return table.c.class_.in_(tuple(include))
return table.c.class_.in_(tuple(include))
- async def _find_closest_street_or_poi(self, wkt: str,
- distance: float) -> Optional[SaRow]:
+ async def _find_closest_street_or_poi(self, distance: float) -> Optional[SaRow]:
""" Look up the closest rank 26+ place in the database, which
is closer than the given distance.
"""
t = self.conn.t.placex
""" Look up the closest rank 26+ place in the database, which
is closer than the given distance.
"""
t = self.conn.t.placex
- sql = _select_from_placex(t, wkt)\
- .where(t.c.geometry.ST_DWithin(wkt, distance))\
+ sql = _select_from_placex(t)\
+ .where(t.c.geometry.ST_DWithin(WKT_PARAM, distance))\
.where(t.c.indexed_status == 0)\
.where(t.c.linked_place_id == None)\
.where(sa.or_(sa.not_(t.c.geometry.is_area()),
.where(t.c.indexed_status == 0)\
.where(t.c.linked_place_id == None)\
.where(sa.or_(sa.not_(t.c.geometry.is_area()),
- t.c.centroid.ST_Distance(wkt) < distance))\
+ t.c.centroid.ST_Distance(WKT_PARAM) < distance))\
.order_by('distance')\
.limit(1)
.order_by('distance')\
.limit(1)
t.c.class_.not_in(('place', 'building')),
sa.not_(t.c.geometry.is_line_like())))
if self.has_feature_layers():
t.c.class_.not_in(('place', 'building')),
sa.not_(t.c.geometry.is_line_like())))
if self.has_feature_layers():
- restrict.append(sa.and_(t.c.rank_search.between(26, self.max_rank),
+ restrict.append(sa.and_(t.c.rank_search.between(26, MAX_RANK_PARAM),
t.c.rank_address == 0,
self._filter_by_layer(t)))
if not restrict:
return None
t.c.rank_address == 0,
self._filter_by_layer(t)))
if not restrict:
return None
- return (await self.conn.execute(sql.where(sa.or_(*restrict)))).one_or_none()
+ sql = sql.where(sa.or_(*restrict))
+
+ return (await self.conn.execute(sql, self.bind_params)).one_or_none()
- async def _find_housenumber_for_street(self, parent_place_id: int,
- wkt: str) -> Optional[SaRow]:
+ async def _find_housenumber_for_street(self, parent_place_id: int) -> Optional[SaRow]:
- sql = _select_from_placex(t, wkt)\
- .where(t.c.geometry.ST_DWithin(wkt, 0.001))\
+ sql = _select_from_placex(t)\
+ .where(t.c.geometry.ST_DWithin(WKT_PARAM, 0.001))\
.where(t.c.parent_place_id == parent_place_id)\
.where(_is_address_point(t))\
.where(t.c.indexed_status == 0)\
.where(t.c.parent_place_id == parent_place_id)\
.where(_is_address_point(t))\
.where(t.c.indexed_status == 0)\
sql = self._add_geometry_columns(sql, t.c.geometry)
sql = self._add_geometry_columns(sql, t.c.geometry)
- return (await self.conn.execute(sql)).one_or_none()
+ return (await self.conn.execute(sql, self.bind_params)).one_or_none()
async def _find_interpolation_for_street(self, parent_place_id: Optional[int],
async def _find_interpolation_for_street(self, parent_place_id: Optional[int],
distance: float) -> Optional[SaRow]:
t = self.conn.t.osmline
sql = sa.select(t,
distance: float) -> Optional[SaRow]:
t = self.conn.t.osmline
sql = sa.select(t,
- t.c.linegeo.ST_Distance(wkt).label('distance'),
- _locate_interpolation(t, wkt))\
- .where(t.c.linegeo.ST_DWithin(wkt, distance))\
+ t.c.linegeo.ST_Distance(WKT_PARAM).label('distance'),
+ _locate_interpolation(t))\
+ .where(t.c.linegeo.ST_DWithin(WKT_PARAM, distance))\
.where(t.c.startnumber != None)\
.order_by('distance')\
.limit(1)
.where(t.c.startnumber != None)\
.order_by('distance')\
.limit(1)
sub = sql.subquery('geom')
sql = self._add_geometry_columns(sa.select(sub), sub.c.centroid)
sub = sql.subquery('geom')
sql = self._add_geometry_columns(sa.select(sub), sub.c.centroid)
- return (await self.conn.execute(sql)).one_or_none()
+ return (await self.conn.execute(sql, self.bind_params)).one_or_none()
async def _find_tiger_number_for_street(self, parent_place_id: int,
async def _find_tiger_number_for_street(self, parent_place_id: int,
- parent_type: str, parent_id: int,
- wkt: str) -> Optional[SaRow]:
+ parent_type: str,
+ parent_id: int) -> Optional[SaRow]:
t = self.conn.t.tiger
inner = sa.select(t,
t = self.conn.t.tiger
inner = sa.select(t,
- t.c.linegeo.ST_Distance(wkt).label('distance'),
- _locate_interpolation(t, wkt))\
- .where(t.c.linegeo.ST_DWithin(wkt, 0.001))\
+ t.c.linegeo.ST_Distance(WKT_PARAM).label('distance'),
+ _locate_interpolation(t))\
+ .where(t.c.linegeo.ST_DWithin(WKT_PARAM, 0.001))\
.where(t.c.parent_place_id == parent_place_id)\
.order_by('distance')\
.limit(1)\
.where(t.c.parent_place_id == parent_place_id)\
.order_by('distance')\
.limit(1)\
sub = sql.subquery('geom')
sql = self._add_geometry_columns(sa.select(sub), sub.c.centroid)
sub = sql.subquery('geom')
sql = self._add_geometry_columns(sa.select(sub), sub.c.centroid)
- return (await self.conn.execute(sql)).one_or_none()
+ return (await self.conn.execute(sql, self.bind_params)).one_or_none()
- async def lookup_street_poi(self,
- wkt: str) -> Tuple[Optional[SaRow], RowFunc]:
+ async def lookup_street_poi(self) -> Tuple[Optional[SaRow], RowFunc]:
""" Find a street or POI/address for the given WKT point.
"""
log().section('Reverse lookup on street/address level')
distance = 0.006
parent_place_id = None
""" Find a street or POI/address for the given WKT point.
"""
log().section('Reverse lookup on street/address level')
distance = 0.006
parent_place_id = None
- row = await self._find_closest_street_or_poi(wkt, distance)
+ row = await self._find_closest_street_or_poi(distance)
row_func: RowFunc = nres.create_from_placex_row
log().var_dump('Result (street/building)', row)
row_func: RowFunc = nres.create_from_placex_row
log().var_dump('Result (street/building)', row)
distance = 0.001
parent_place_id = row.place_id
log().comment('Find housenumber for street')
distance = 0.001
parent_place_id = row.place_id
log().comment('Find housenumber for street')
- addr_row = await self._find_housenumber_for_street(parent_place_id, wkt)
+ addr_row = await self._find_housenumber_for_street(parent_place_id)
log().var_dump('Result (street housenumber)', addr_row)
if addr_row is not None:
log().var_dump('Result (street housenumber)', addr_row)
if addr_row is not None:
log().comment('Find TIGER housenumber for street')
addr_row = await self._find_tiger_number_for_street(parent_place_id,
row.osm_type,
log().comment('Find TIGER housenumber for street')
addr_row = await self._find_tiger_number_for_street(parent_place_id,
row.osm_type,
log().var_dump('Result (street Tiger housenumber)', addr_row)
if addr_row is not None:
log().var_dump('Result (street Tiger housenumber)', addr_row)
if addr_row is not None:
if self.max_rank > 27 and self.layer_enabled(DataLayer.ADDRESS):
log().comment('Find interpolation for street')
addr_row = await self._find_interpolation_for_street(parent_place_id,
if self.max_rank > 27 and self.layer_enabled(DataLayer.ADDRESS):
log().comment('Find interpolation for street')
addr_row = await self._find_interpolation_for_street(parent_place_id,
log().var_dump('Result (street interpolation)', addr_row)
if addr_row is not None:
row = addr_row
log().var_dump('Result (street interpolation)', addr_row)
if addr_row is not None:
row = addr_row
- async def _lookup_area_address(self, wkt: str) -> Optional[SaRow]:
+ async def _lookup_area_address(self) -> Optional[SaRow]:
""" Lookup large addressable areas for the given WKT point.
"""
log().comment('Reverse lookup by larger address area features')
""" Lookup large addressable areas for the given WKT point.
"""
log().comment('Reverse lookup by larger address area features')
# The inner SQL brings results in the right order, so that
# later only a minimum of results needs to be checked with ST_Contains.
inner = sa.select(t, sa.literal(0.0).label('distance'))\
# The inner SQL brings results in the right order, so that
# later only a minimum of results needs to be checked with ST_Contains.
inner = sa.select(t, sa.literal(0.0).label('distance'))\
- .where(t.c.rank_search.between(5, self.max_rank))\
+ .where(t.c.rank_search.between(5, MAX_RANK_PARAM))\
.where(t.c.rank_address.between(5, 25))\
.where(t.c.geometry.is_area())\
.where(t.c.rank_address.between(5, 25))\
.where(t.c.geometry.is_area())\
- .where(t.c.geometry.intersects(wkt))\
+ .where(t.c.geometry.intersects(WKT_PARAM))\
.where(t.c.name != None)\
.where(t.c.indexed_status == 0)\
.where(t.c.linked_place_id == None)\
.where(t.c.name != None)\
.where(t.c.indexed_status == 0)\
.where(t.c.linked_place_id == None)\
.limit(50)\
.subquery('area')
.limit(50)\
.subquery('area')
- sql = _select_from_placex(inner)\
- .where(inner.c.geometry.ST_Contains(wkt))\
+ sql = _select_from_placex(inner, False)\
+ .where(inner.c.geometry.ST_Contains(WKT_PARAM))\
.order_by(sa.desc(inner.c.rank_search))\
.limit(1)
sql = self._add_geometry_columns(sql, inner.c.geometry)
.order_by(sa.desc(inner.c.rank_search))\
.limit(1)
sql = self._add_geometry_columns(sql, inner.c.geometry)
- address_row = (await self.conn.execute(sql)).one_or_none()
+ address_row = (await self.conn.execute(sql, self.bind_params)).one_or_none()
log().var_dump('Result (area)', address_row)
if address_row is not None and address_row.rank_search < self.max_rank:
log().comment('Search for better matching place nodes inside the area')
inner = sa.select(t,
log().var_dump('Result (area)', address_row)
if address_row is not None and address_row.rank_search < self.max_rank:
log().comment('Search for better matching place nodes inside the area')
inner = sa.select(t,
- t.c.geometry.ST_Distance(wkt).label('distance'))\
+ t.c.geometry.ST_Distance(WKT_PARAM).label('distance'))\
.where(t.c.osm_type == 'N')\
.where(t.c.rank_search > address_row.rank_search)\
.where(t.c.osm_type == 'N')\
.where(t.c.rank_search > address_row.rank_search)\
- .where(t.c.rank_search <= self.max_rank)\
+ .where(t.c.rank_search <= MAX_RANK_PARAM)\
.where(t.c.rank_address.between(5, 25))\
.where(t.c.name != None)\
.where(t.c.indexed_status == 0)\
.where(t.c.rank_address.between(5, 25))\
.where(t.c.name != None)\
.where(t.c.indexed_status == 0)\
.where(t.c.type != 'postcode')\
.where(t.c.geometry
.ST_Buffer(sa.func.reverse_place_diameter(t.c.rank_search))
.where(t.c.type != 'postcode')\
.where(t.c.geometry
.ST_Buffer(sa.func.reverse_place_diameter(t.c.rank_search))
+ .intersects(WKT_PARAM))\
.order_by(sa.desc(t.c.rank_search))\
.limit(50)\
.subquery('places')
touter = self.conn.t.placex.alias('outer')
.order_by(sa.desc(t.c.rank_search))\
.limit(50)\
.subquery('places')
touter = self.conn.t.placex.alias('outer')
- sql = _select_from_placex(inner)\
+ sql = _select_from_placex(inner, False)\
.join(touter, touter.c.geometry.ST_Contains(inner.c.geometry))\
.where(touter.c.place_id == address_row.place_id)\
.where(inner.c.distance < sa.func.reverse_place_diameter(inner.c.rank_search))\
.join(touter, touter.c.geometry.ST_Contains(inner.c.geometry))\
.where(touter.c.place_id == address_row.place_id)\
.where(inner.c.distance < sa.func.reverse_place_diameter(inner.c.rank_search))\
sql = self._add_geometry_columns(sql, inner.c.geometry)
sql = self._add_geometry_columns(sql, inner.c.geometry)
- place_address_row = (await self.conn.execute(sql)).one_or_none()
+ place_address_row = (await self.conn.execute(sql, self.bind_params)).one_or_none()
log().var_dump('Result (place node)', place_address_row)
if place_address_row is not None:
log().var_dump('Result (place node)', place_address_row)
if place_address_row is not None:
- async def _lookup_area_others(self, wkt: str) -> Optional[SaRow]:
+ async def _lookup_area_others(self) -> Optional[SaRow]:
- inner = sa.select(t, t.c.geometry.ST_Distance(wkt).label('distance'))\
+ inner = sa.select(t, t.c.geometry.ST_Distance(WKT_PARAM).label('distance'))\
.where(t.c.rank_address == 0)\
.where(t.c.rank_address == 0)\
- .where(t.c.rank_search.between(5, self.max_rank))\
+ .where(t.c.rank_search.between(5, MAX_RANK_PARAM))\
.where(t.c.name != None)\
.where(t.c.indexed_status == 0)\
.where(t.c.linked_place_id == None)\
.where(self._filter_by_layer(t))\
.where(t.c.geometry
.ST_Buffer(sa.func.reverse_place_diameter(t.c.rank_search))
.where(t.c.name != None)\
.where(t.c.indexed_status == 0)\
.where(t.c.linked_place_id == None)\
.where(self._filter_by_layer(t))\
.where(t.c.geometry
.ST_Buffer(sa.func.reverse_place_diameter(t.c.rank_search))
+ .intersects(WKT_PARAM))\
.order_by(sa.desc(t.c.rank_search))\
.limit(50)\
.subquery()
.order_by(sa.desc(t.c.rank_search))\
.limit(50)\
.subquery()
- sql = _select_from_placex(inner)\
- .where(sa.or_(not inner.c.geometry.is_area(),
- inner.c.geometry.ST_Contains(wkt)))\
+ sql = _select_from_placex(inner, False)\
+ .where(sa.or_(sa.not_(inner.c.geometry.is_area()),
+ inner.c.geometry.ST_Contains(WKT_PARAM)))\
.order_by(sa.desc(inner.c.rank_search), inner.c.distance)\
.limit(1)
sql = self._add_geometry_columns(sql, inner.c.geometry)
.order_by(sa.desc(inner.c.rank_search), inner.c.distance)\
.limit(1)
sql = self._add_geometry_columns(sql, inner.c.geometry)
- row = (await self.conn.execute(sql)).one_or_none()
+ row = (await self.conn.execute(sql, self.bind_params)).one_or_none()
log().var_dump('Result (non-address feature)', row)
return row
log().var_dump('Result (non-address feature)', row)
return row
- async def lookup_area(self, wkt: str) -> Optional[SaRow]:
- """ Lookup large areas for the given WKT point.
+ async def lookup_area(self) -> Optional[SaRow]:
+ """ Lookup large areas for the current search.
"""
log().section('Reverse lookup by larger area features')
if self.layer_enabled(DataLayer.ADDRESS):
"""
log().section('Reverse lookup by larger area features')
if self.layer_enabled(DataLayer.ADDRESS):
- address_row = await self._lookup_area_address(wkt)
+ address_row = await self._lookup_area_address()
else:
address_row = None
if self.has_feature_layers():
else:
address_row = None
if self.has_feature_layers():
- other_row = await self._lookup_area_others(wkt)
+ other_row = await self._lookup_area_others()
else:
other_row = None
return _get_closest(address_row, other_row)
else:
other_row = None
return _get_closest(address_row, other_row)
- async def lookup_country(self, wkt: str) -> Optional[SaRow]:
- """ Lookup the country for the given WKT point.
+ async def lookup_country(self) -> Optional[SaRow]:
+ """ Lookup the country for the current search.
"""
log().section('Reverse lookup by country code')
t = self.conn.t.country_grid
sql = sa.select(t.c.country_code).distinct()\
"""
log().section('Reverse lookup by country code')
t = self.conn.t.country_grid
sql = sa.select(t.c.country_code).distinct()\
- .where(t.c.geometry.ST_Contains(wkt))
+ .where(t.c.geometry.ST_Contains(WKT_PARAM))
- ccodes = tuple((r[0] for r in await self.conn.execute(sql)))
+ ccodes = tuple((r[0] for r in await self.conn.execute(sql, self.bind_params)))
log().var_dump('Country codes', ccodes)
if not ccodes:
log().var_dump('Country codes', ccodes)
if not ccodes:
log().comment('Search for place nodes in country')
inner = sa.select(t,
log().comment('Search for place nodes in country')
inner = sa.select(t,
- t.c.geometry.ST_Distance(wkt).label('distance'))\
+ t.c.geometry.ST_Distance(WKT_PARAM).label('distance'))\
.where(t.c.osm_type == 'N')\
.where(t.c.rank_search > 4)\
.where(t.c.osm_type == 'N')\
.where(t.c.rank_search > 4)\
- .where(t.c.rank_search <= self.max_rank)\
+ .where(t.c.rank_search <= MAX_RANK_PARAM)\
.where(t.c.rank_address.between(5, 25))\
.where(t.c.name != None)\
.where(t.c.indexed_status == 0)\
.where(t.c.rank_address.between(5, 25))\
.where(t.c.name != None)\
.where(t.c.indexed_status == 0)\
.where(t.c.country_code.in_(ccodes))\
.where(t.c.geometry
.ST_Buffer(sa.func.reverse_place_diameter(t.c.rank_search))
.where(t.c.country_code.in_(ccodes))\
.where(t.c.geometry
.ST_Buffer(sa.func.reverse_place_diameter(t.c.rank_search))
+ .intersects(WKT_PARAM))\
.order_by(sa.desc(t.c.rank_search))\
.limit(50)\
.subquery()
.order_by(sa.desc(t.c.rank_search))\
.limit(50)\
.subquery()
- sql = _select_from_placex(inner)\
+ sql = _select_from_placex(inner, False)\
.where(inner.c.distance < sa.func.reverse_place_diameter(inner.c.rank_search))\
.order_by(sa.desc(inner.c.rank_search), inner.c.distance)\
.limit(1)
sql = self._add_geometry_columns(sql, inner.c.geometry)
.where(inner.c.distance < sa.func.reverse_place_diameter(inner.c.rank_search))\
.order_by(sa.desc(inner.c.rank_search), inner.c.distance)\
.limit(1)
sql = self._add_geometry_columns(sql, inner.c.geometry)
- address_row = (await self.conn.execute(sql)).one_or_none()
+ address_row = (await self.conn.execute(sql, self.bind_params)).one_or_none()
log().var_dump('Result (addressable place node)', address_row)
else:
address_row = None
if address_row is None:
# Still nothing, then return a country with the appropriate country code.
log().var_dump('Result (addressable place node)', address_row)
else:
address_row = None
if address_row is None:
# Still nothing, then return a country with the appropriate country code.
- sql = _select_from_placex(t, wkt)\
+ sql = _select_from_placex(t)\
.where(t.c.country_code.in_(ccodes))\
.where(t.c.rank_address == 4)\
.where(t.c.rank_search == 4)\
.where(t.c.country_code.in_(ccodes))\
.where(t.c.rank_address == 4)\
.where(t.c.rank_search == 4)\
sql = self._add_geometry_columns(sql, t.c.geometry)
sql = self._add_geometry_columns(sql, t.c.geometry)
- address_row = (await self.conn.execute(sql)).one_or_none()
+ address_row = (await self.conn.execute(sql, self.bind_params)).one_or_none()
log().function('reverse_lookup', coord=coord, params=self.params)
log().function('reverse_lookup', coord=coord, params=self.params)
- wkt = f'POINT({coord[0]} {coord[1]})'
+ self.bind_params['wkt'] = f'SRID=4326;POINT({coord[0]} {coord[1]})'
row: Optional[SaRow] = None
row_func: RowFunc = nres.create_from_placex_row
if self.max_rank >= 26:
row: Optional[SaRow] = None
row_func: RowFunc = nres.create_from_placex_row
if self.max_rank >= 26:
- row, tmp_row_func = await self.lookup_street_poi(wkt)
+ row, tmp_row_func = await self.lookup_street_poi()
if row is not None:
row_func = tmp_row_func
if row is None and self.max_rank > 4:
if row is not None:
row_func = tmp_row_func
if row is None and self.max_rank > 4:
- row = await self.lookup_area(wkt)
+ row = await self.lookup_area()
if row is None and self.layer_enabled(DataLayer.ADDRESS):
if row is None and self.layer_enabled(DataLayer.ADDRESS):
- row = await self.lookup_country(wkt)
+ row = await self.lookup_country()
result = row_func(row, nres.ReverseResult)
if result is not None:
assert row is not None
result.distance = row.distance
if hasattr(row, 'bbox'):
result = row_func(row, nres.ReverseResult)
if result is not None:
assert row is not None
result.distance = row.distance
if hasattr(row, 'bbox'):
- result.bbox = Bbox.from_wkb(row.bbox.data)
+ result.bbox = Bbox.from_wkb(row.bbox)
await nres.add_result_details(self.conn, [result], self.params)
return result
await nres.add_result_details(self.conn, [result], self.params)
return result
import enum
import math
from struct import unpack
import enum
import math
from struct import unpack
+from binascii import unhexlify
- def from_wkb(wkb: bytes) -> 'Point':
+ def from_wkb(wkb: Union[str, bytes]) -> 'Point':
""" Create a point from EWKB as returned from the database.
"""
""" Create a point from EWKB as returned from the database.
"""
+ if isinstance(wkb, str):
+ wkb = unhexlify(wkb)
if len(wkb) != 25:
raise ValueError("Point wkb has unexpected length")
if wkb[0] == 0:
if len(wkb) != 25:
raise ValueError("Point wkb has unexpected length")
if wkb[0] == 0:
- def from_wkb(wkb: Optional[bytes]) -> 'Optional[Bbox]':
+ def from_wkb(wkb: Union[None, str, bytes]) -> 'Optional[Bbox]':
""" Create a Bbox from a bounding box polygon as returned by
the database. Return s None if the input value is None.
"""
if wkb is None:
return None
""" Create a Bbox from a bounding box polygon as returned by
the database. Return s None if the input value is None.
"""
if wkb is None:
return None
+ if isinstance(wkb, str):
+ wkb = unhexlify(wkb)
+
if len(wkb) != 97:
raise ValueError("WKB must be a bounding box polygon")
if wkb.startswith(WKB_BBOX_HEADER_LE):
if len(wkb) != 97:
raise ValueError("WKB must be a bounding box polygon")
if wkb.startswith(WKB_BBOX_HEADER_LE):
class comparator_factory(types.UserDefinedType.Comparator):
class comparator_factory(types.UserDefinedType.Comparator):
+ def intersects(self, other: SaColumn) -> SaColumn:
+ return self.op('&&')(other)
+
def is_line_like(self) -> SaColumn:
return sa.func.ST_GeometryType(self, type_=sa.String).in_(('ST_LineString',
'ST_MultiLineString'))
def is_line_like(self) -> SaColumn:
return sa.func.ST_GeometryType(self, type_=sa.String).in_(('ST_LineString',
'ST_MultiLineString'))
# Enable logging of requests into a file.
# To enable logging set this setting to the file to log to.
NOMINATIM_LOG_FILE=
# Enable logging of requests into a file.
# To enable logging set this setting to the file to log to.
NOMINATIM_LOG_FILE=
+
+# Echo raw SQL from SQLAlchemy statements.
+# Works only in command line/library use.
+NOMINATIM_DEBUG_SQL=no
projects / nominatim.git / commitdiff