From: Sarah Hoffmann <lonvia@denofr.de>
Date: Thu, 28 Oct 2021 13:28:47 +0000 (+0200)
Subject: Merge pull request #2498 from lonvia/ordering-for-unlisted-place-results
X-Git-Tag: v4.0.0~3
X-Git-Url: https://git.openstreetmap.org/nominatim.git/commitdiff_plain/d11bf9288e56023896067f7926bff5e4b39db96e?hp=86eeb4d2eddaa6c409f542169780f59dc31ffc1b

Merge pull request #2498 from lonvia/ordering-for-unlisted-place-results

Include unlisted places in ordering by housenumber
---

diff --git a/lib-php/SearchDescription.php b/lib-php/SearchDescription.php
index fa622e62..fcb87d43 100644
--- a/lib-php/SearchDescription.php
+++ b/lib-php/SearchDescription.php
@@ -581,32 +581,37 @@ class SearchDescription
 
         // Sort by existence of the requested house number but only if not
         // too many results are expected for the street, i.e. if the result
-        // will be narrowed down by an address. Remeber that with ordering
+        // will be narrowed down by an address. Remember that with ordering
         // every single result has to be checked.
         if ($this->sHouseNumber && ($this->bRareName || !empty($this->aAddress) || $this->sPostcode)) {
             $sHouseNumberRegex = '\\\\m'.$this->sHouseNumber.'\\\\M';
-            $aOrder[] = ' (';
-            $aOrder[0] .= 'EXISTS(';
-            $aOrder[0] .= '  SELECT place_id';
-            $aOrder[0] .= '  FROM placex';
-            $aOrder[0] .= '  WHERE parent_place_id = search_name.place_id';
-            $aOrder[0] .= "    AND housenumber ~* E'".$sHouseNumberRegex."'";
-            $aOrder[0] .= '  LIMIT 1';
-            $aOrder[0] .= ') ';
-            // also housenumbers from interpolation lines table are needed
-            if (preg_match('/[0-9]+/', $this->sHouseNumber)) {
-                $iHouseNumber = intval($this->sHouseNumber);
-                $aOrder[0] .= 'OR EXISTS(';
-                $aOrder[0] .= '  SELECT place_id ';
-                $aOrder[0] .= '  FROM location_property_osmline ';
-                $aOrder[0] .= '  WHERE parent_place_id = search_name.place_id';
-                $aOrder[0] .= '    AND startnumber is not NULL';
-                $aOrder[0] .= '    AND '.$iHouseNumber.'>=startnumber ';
-                $aOrder[0] .= '    AND '.$iHouseNumber.'<=endnumber ';
-                $aOrder[0] .= '  LIMIT 1';
-                $aOrder[0] .= ')';
+
+            // Housenumbers on streets and places.
+            $sChildHnr = 'SELECT * FROM placex WHERE parent_place_id = search_name.place_id';
+            $sChildHnr .= "    AND housenumber ~* E'".$sHouseNumberRegex."'";
+            // Interpolations on streets and places.
+            if (preg_match('/^[0-9]+$/', $this->sHouseNumber)) {
+                $sIpolHnr = 'SELECT * FROM location_property_osmline ';
+                $sIpolHnr .= 'WHERE parent_place_id = search_name.place_id ';
+                $sIpolHnr .= '  AND startnumber is not NULL';
+                $sIpolHnr .= '    AND '.$this->sHouseNumber.'>=startnumber ';
+                $sIpolHnr .= '    AND '.$this->sHouseNumber.'<=endnumber ';
+            } else {
+                $sIpolHnr = false;
+            }
+            // Housenumbers on the object iteself for unlisted places.
+            $sSelfHnr = 'SELECT * FROM placex WHERE place_id = search_name.place_id';
+            $sSelfHnr .= "    AND housenumber ~* E'".$sHouseNumberRegex."'";
+
+            $sSql = '(CASE WHEN address_rank = 30 THEN EXISTS('.$sSelfHnr.') ';
+            $sSql .= ' ELSE EXISTS('.$sChildHnr.') ';
+            if ($sIpolHnr) {
+                $sSql .= 'OR EXISTS('.$sIpolHnr.') ';
             }
-            $aOrder[0] .= ') DESC';
+            $sSql .= 'END) DESC';
+
+
+            $aOrder[] = $sSql;
         }
 
         if (!empty($this->aName)) {