use new tiger step column for queries

diff --git a/lib-php/PlaceLookup.php b/lib-php/PlaceLookup.php
index 0e4b63bc6ba81335b2c7ec78f3f9fa17363adcdd..120f55436acb482de8d374019b2a30b05ffd2c0b 100644 (file)
--- a/lib-php/PlaceLookup.php
+++ b/lib-php/PlaceLookup.php
@@ -348,7 +348,9 @@ class PlaceLookup
                     $sSQL .= '     null::text AS extra_place ';
                     $sSQL .= ' FROM (';
                     $sSQL .= '     SELECT place_id, ';    // interpolate the Tiger housenumbers here
-                    $sSQL .= '         ST_LineInterpolatePoint(linegeo, (housenumber_for_place-startnumber::float)/(endnumber-startnumber)::float) AS centroid, ';
+                    $sSQL .= '         CASE WHEN startnumber != endnumber';
+                    $sSQL .= '              THEN ST_LineInterpolatePoint(linegeo, (housenumber_for_place-startnumber::float)/(endnumber-startnumber)::float)';
+                    $sSQL .= '              ELSE ST_LineInterpolatePoint(linegeo, 0.5) END AS centroid, ';
                     $sSQL .= '         parent_place_id, ';
                     $sSQL .= '         housenumber_for_place';
                     $sSQL .= '     FROM (';

diff --git a/lib-php/ReverseGeocode.php b/lib-php/ReverseGeocode.php
index cccd5b35c97e5e6a37c14163d7a27b6048c28a86..646c592b9f5d4b3dc5efba035406602f0b44d3bc 100644 (file)
--- a/lib-php/ReverseGeocode.php
+++ b/lib-php/ReverseGeocode.php
@@ -327,9 +327,9 @@ class ReverseGeocode
                     && $this->iMaxRank >= 28
                 ) {
                     $sSQL = 'SELECT place_id,parent_place_id,30 as rank_search,';
-                    $sSQL .= 'ST_LineLocatePoint(linegeo,'.$sPointSQL.') as fraction,';
-                    $sSQL .= 'ST_distance('.$sPointSQL.', linegeo) as distance,';
-                    $sSQL .= 'startnumber,endnumber,interpolationtype';
+                    $sSQL .= '      (endnumber - startnumber) * ST_LineLocatePoint(linegeo,'.$sPointSQL.') as fhnr,';
+                    $sSQL .= '      startnumber, endnumber, step,';
+                    $sSQL .= '      ST_Distance('.$sPointSQL.', linegeo) as distance';
                     $sSQL .= ' FROM location_property_tiger WHERE parent_place_id = '.$oResult->iId;
                     $sSQL .= ' AND ST_DWithin('.$sPointSQL.', linegeo, 0.001)';
                     $sSQL .= ' ORDER BY distance ASC limit 1';
@@ -341,7 +341,11 @@ class ReverseGeocode
                     if ($aPlaceTiger) {
                         $aPlace = $aPlaceTiger;
                         $oResult = new Result($aPlaceTiger['place_id'], Result::TABLE_TIGER);
-                        $oResult->iHouseNumber = closestHouseNumber($aPlaceTiger);
+                        $iRndNum = max(0, round($aPlaceTiger['fhnr'] / $aPlaceTiger['step']) * $aPlaceTiger['step']);
+                        $oResult->iHouseNumber = $aPlaceTiger['startnumber'] + $iRndNum;
+                        if ($oResult->iHouseNumber > $aPlaceTiger['endnumber']) {
+                            $oResult->iHouseNumber = $aPlaceTiger['endnumber'];
+                        }
                         $iRankAddress = 30;
                     }
                 }

diff --git a/lib-php/SearchDescription.php b/lib-php/SearchDescription.php
index d6fa704fd2347b3ccc31fe7b954755f09d4ba95b..d2995e8e08ea3e62c859c537b3ff7c35356a626c 100644 (file)
--- a/lib-php/SearchDescription.php
+++ b/lib-php/SearchDescription.php
@@ -786,15 +786,9 @@ class SearchDescription
         // If nothing found then search in Tiger data (location_property_tiger)
         if (CONST_Use_US_Tiger_Data && $sRoadPlaceIDs && $bIsIntHouseNumber && empty($aResults)) {
             $sSQL = 'SELECT place_id FROM location_property_tiger';
-            $sSQL .= ' WHERE parent_place_id in ('.$sRoadPlaceIDs.') and (';
-            if ($iHousenumber % 2 == 0) {
-                $sSQL .= "interpolationtype='even'";
-            } else {
-                $sSQL .= "interpolationtype='odd'";
-            }
-            $sSQL .= " or interpolationtype='all') and ";
-            $sSQL .= $iHousenumber.'>=startnumber and ';
-            $sSQL .= $iHousenumber.'<=endnumber';
+            $sSQL .= ' WHERE parent_place_id in ('.$sRoadPlaceIDs.')';
+            $sSQL .= '  and ('.$iHousenumber.' - startnumber) % step = 0';
+            $sSQL .= '  and '.$iHousenumber.' between startnumber and endnumber';
             $sSQL .= $this->oContext->excludeSQL(' AND place_id');
 
             Debug::printSQL($sSQL);

diff --git a/lib-php/lib.php b/lib-php/lib.php
index 3ba50dc03fd7533c6c7dcc2982f66a0ff2e86f46..9babe5ed907ca91c290b967630f777510fe21df9 100644 (file)
--- a/lib-php/lib.php
+++ b/lib-php/lib.php
@@ -206,26 +206,6 @@ function parseLatLon($sQuery)
     return array($sFound, $fQueryLat, $fQueryLon);
 }
 
-function closestHouseNumber($aRow)
-{
-    $fHouse = $aRow['startnumber']
-                + ($aRow['endnumber'] - $aRow['startnumber']) * $aRow['fraction'];
-
-    switch ($aRow['interpolationtype']) {
-        case 'odd':
-            $iHn = (int)($fHouse/2) * 2 + 1;
-            break;
-        case 'even':
-            $iHn = (int)(round($fHouse/2)) * 2;
-            break;
-        default:
-            $iHn = (int)(round($fHouse));
-            break;
-    }
-
-    return max(min($aRow['endnumber'], $iHn), $aRow['startnumber']);
-}
-
 if (!function_exists('array_key_last')) {
     function array_key_last(array $array)
     {

diff --git a/lib-sql/tiger_import_start.sql b/lib-sql/tiger_import_start.sql
index 84992dfdc1fec529278d151bc970b23fe30d2831..0ff534368d0929f6c58eb7b2fb6b2b6ab9d5402a 100644 (file)
--- a/lib-sql/tiger_import_start.sql
+++ b/lib-sql/tiger_import_start.sql
@@ -5,7 +5,15 @@
 -- Copyright (C) 2022 by the Nominatim developer community.
 -- For a full list of authors see the git log.
 DROP TABLE IF EXISTS location_property_tiger_import;
-CREATE TABLE location_property_tiger_import (linegeo GEOMETRY, place_id BIGINT, partition INTEGER, parent_place_id BIGINT, startnumber INTEGER, endnumber INTEGER, interpolationtype TEXT, postcode TEXT);
+CREATE TABLE location_property_tiger_import (
+  linegeo GEOMETRY,
+  place_id BIGINT,
+  partition INTEGER,
+  parent_place_id BIGINT,
+  startnumber INTEGER,
+  endnumber INTEGER,
+  step SMALLINT,
+  postcode TEXT);
 
 CREATE OR REPLACE FUNCTION tiger_line_import(linegeo GEOMETRY, in_startnumber INTEGER,
                                              in_endnumber INTEGER, interpolationtype TEXT,

diff --git a/test/bdd/api/reverse/queries.feature b/test/bdd/api/reverse/queries.feature
index 303af2c35734120872587911f1d7282a15f24c39..a2b0f033739e7088fd843874ae77a92a87aee206 100644 (file)
--- a/test/bdd/api/reverse/queries.feature
+++ b/test/bdd/api/reverse/queries.feature
@@ -10,7 +10,7 @@ Feature: Reverse geocoding
           | way      | place    | house |
         And result addresses contain
           | house_number | road                | postcode | country_code |
-          | 697          | Upper Kingston Road | 36067    | us |
+          | 707          | Upper Kingston Road | 36067    | us |
 
     @Tiger
     Scenario: No TIGER house number for zoom < 18