enable API use with psycopg 3

[nominatim.git] / nominatim / api / reverse.py

diff --git a/nominatim/api/reverse.py b/nominatim/api/reverse.py
index 60b24fdc0923b99c92245c59fa1bc98325f4a214..42fe8f36af7deb613d3db15d3d850e05048e1a75 100644 (file)
--- a/nominatim/api/reverse.py
+++ b/nominatim/api/reverse.py
@@ -30,8 +30,15 @@ def _select_from_placex(t: SaFromClause, wkt: Optional[str] = None) -> SaSelect:
     """
     if wkt is None:
         distance = t.c.distance
+        centroid = t.c.centroid
     else:
         distance = t.c.geometry.ST_Distance(wkt)
+        centroid = sa.case(
+                       (t.c.geometry.ST_GeometryType().in_(('ST_LineString',
+                                                           'ST_MultiLineString')),
+                        t.c.geometry.ST_ClosestPoint(wkt)),
+                       else_=t.c.centroid).label('centroid')
+
 
     return sa.select(t.c.place_id, t.c.osm_type, t.c.osm_id, t.c.name,
                      t.c.class_, t.c.type,
@@ -39,11 +46,7 @@ def _select_from_placex(t: SaFromClause, wkt: Optional[str] = None) -> SaSelect:
                      t.c.housenumber, t.c.postcode, t.c.country_code,
                      t.c.importance, t.c.wikipedia,
                      t.c.parent_place_id, t.c.rank_address, t.c.rank_search,
-                     sa.case(
-                       (t.c.geometry.ST_GeometryType().in_(('ST_LineString',
-                                                           'ST_MultiLineString')),
-                        t.c.geometry.ST_ClosestPoint(wkt)),
-                       else_=t.c.centroid).label('centroid'),
+                     centroid,
                      distance.label('distance'),
                      t.c.geometry.ST_Expand(0).label('bbox'))
 
@@ -63,6 +66,14 @@ def _interpolated_position(table: SaFromClause) -> SaLabel:
               else_=table.c.linegeo.ST_LineInterpolatePoint(rounded_pos)).label('centroid')
 
 
+def _locate_interpolation(table: SaFromClause, wkt: WKTElement) -> SaLabel:
+    """ Given a position, locate the closest point on the line.
+    """
+    return sa.case((table.c.linegeo.ST_GeometryType() == 'ST_LineString',
+                    sa.func.ST_LineLocatePoint(table.c.linegeo, wkt)),
+                   else_=0).label('position')
+
+
 def _is_address_point(table: SaFromClause) -> SaColumn:
     return sa.and_(table.c.rank_address == 30,
                    sa.or_(table.c.housenumber != None,
@@ -204,8 +215,9 @@ class ReverseGeocoder:
 
         sql = sa.select(t,
                         t.c.linegeo.ST_Distance(wkt).label('distance'),
-                        t.c.linegeo.ST_LineLocatePoint(wkt).label('position'))\
+                        _locate_interpolation(t, wkt))\
                 .where(t.c.linegeo.ST_DWithin(wkt, distance))\
+                .where(t.c.startnumber != None)\
                 .order_by('distance')\
                 .limit(1)
 
@@ -235,7 +247,7 @@ class ReverseGeocoder:
 
         inner = sa.select(t,
                           t.c.linegeo.ST_Distance(wkt).label('distance'),
-                          sa.func.ST_LineLocatePoint(t.c.linegeo, wkt).label('position'))\
+                          _locate_interpolation(t, wkt))\
                   .where(t.c.linegeo.ST_DWithin(wkt, 0.001))\
                   .where(t.c.parent_place_id == parent_place_id)\
                   .order_by('distance')\
@@ -488,7 +500,8 @@ class ReverseGeocoder:
                       .where(t.c.rank_address == 4)\
                       .where(t.c.rank_search == 4)\
                       .where(t.c.linked_place_id == None)\
-                      .order_by('distance')
+                      .order_by('distance')\
+                      .limit(1)
 
             sql = self._add_geometry_columns(sql, t.c.geometry)