From: Sarah Hoffmann <lonvia@denofr.de>
Date: Sun, 8 Oct 2017 20:44:01 +0000 (+0200)
Subject: move viewbox sql to new SearchContext
X-Git-Tag: v3.1.0~47^2~6
X-Git-Url: https://git.openstreetmap.org/nominatim.git/commitdiff_plain/86c085813044e52a4897d6a85e4ae7700bd40f91

move viewbox sql to new SearchContext
---

diff --git a/lib/Geocode.php b/lib/Geocode.php
index a6baa96b..1138824c 100644
--- a/lib/Geocode.php
+++ b/lib/Geocode.php
@@ -37,9 +37,8 @@ class Geocode
 
     protected $bBoundedSearch = false;
     protected $aViewBox = false;
-    protected $sViewboxCentreSQL = false;
-    protected $sViewboxSmallSQL = false;
-    protected $sViewboxLargeSQL = false;
+    protected $aRoutePoints = false;
+    protected $aRouteWidth = false;
 
     protected $iMaxRank = 20;
     protected $iMinAddressRank = 0;
@@ -184,26 +183,6 @@ class Geocode
         $this->iMaxAddressRank = $iMax;
     }
 
-    public function setRoute($aRoutePoints, $fRouteWidth)
-    {
-        $this->aViewBox = false;
-
-        $this->sViewboxCentreSQL = "ST_SetSRID('LINESTRING(";
-        $sSep = '';
-        foreach ($aRoutePoints as $aPoint) {
-            $fPoint = (float)$aPoint;
-            $this->sViewboxCentreSQL .= $sSep.$fPoint;
-            $sSep = ($sSep == ' ') ? ',' : ' ';
-        }
-        $this->sViewboxCentreSQL .= ")'::geometry,4326)";
-
-        $this->sViewboxSmallSQL = 'ST_BUFFER('.$this->sViewboxCentreSQL;
-        $this->sViewboxSmallSQL .= ','.($fRouteWidth/69).')';
-
-        $this->sViewboxLargeSQL = 'ST_BUFFER('.$this->sViewboxCentreSQL;
-        $this->sViewboxLargeSQL .= ','.($fRouteWidth/30).')';
-    }
-
     public function setViewbox($aViewbox)
     {
         $this->aViewBox = array_map('floatval', $aViewbox);
@@ -218,29 +197,6 @@ class Geocode
         ) {
             userError("Bad parameter 'viewbox'. Not a box.");
         }
-
-        $fHeight = $this->aViewBox[0] - $this->aViewBox[2];
-        $fWidth = $this->aViewBox[1] - $this->aViewBox[3];
-        $aBigViewBox[0] = $this->aViewBox[0] + $fHeight;
-        $aBigViewBox[2] = $this->aViewBox[2] - $fHeight;
-        $aBigViewBox[1] = $this->aViewBox[1] + $fWidth;
-        $aBigViewBox[3] = $this->aViewBox[3] - $fWidth;
-
-        $this->sViewboxCentreSQL = false;
-        $this->sViewboxSmallSQL = sprintf(
-            'ST_SetSRID(ST_MakeBox2D(ST_Point(%F,%F),ST_Point(%F,%F)),4326)',
-            $this->aViewBox[0],
-            $this->aViewBox[1],
-            $this->aViewBox[2],
-            $this->aViewBox[3]
-        );
-        $this->sViewboxLargeSQL = sprintf(
-            'ST_SetSRID(ST_MakeBox2D(ST_Point(%F,%F),ST_Point(%F,%F)),4326)',
-            $aBigViewBox[0],
-            $aBigViewBox[1],
-            $aBigViewBox[2],
-            $aBigViewBox[3]
-        );
     }
 
     public function setQuery($sQueryString)
@@ -319,7 +275,8 @@ class Geocode
                 $aRoute = $oParams->getStringList('route');
                 $fRouteWidth = $oParams->getFloat('routewidth');
                 if ($aRoute && $fRouteWidth) {
-                    $this->setRoute($aRoute, $fRouteWidth);
+                    $this->aRoutePoints = $aRoute;
+                    $this->aRouteWidth = $fRouteWidth;
                 }
             }
         }
@@ -407,7 +364,7 @@ class Geocode
         return false;
     }
 
-    public function getDetails($aPlaceIDs)
+    public function getDetails($aPlaceIDs, $oCtx)
     {
         //$aPlaceIDs is an array with key: placeID and value: tiger-housenumber, if found, else -1
         if (sizeof($aPlaceIDs) == 0) return array();
@@ -419,16 +376,8 @@ class Geocode
         // Get the details for display (is this a redundant extra step?)
         $sPlaceIDs = join(',', array_keys($aPlaceIDs));
 
-        $sImportanceSQL = '';
-        $sImportanceSQLGeom = '';
-        if ($this->sViewboxSmallSQL) {
-            $sImportanceSQL .= " CASE WHEN ST_Contains($this->sViewboxSmallSQL, ST_Collect(centroid)) THEN 1 ELSE 0.75 END * ";
-            $sImportanceSQLGeom .= " CASE WHEN ST_Contains($this->sViewboxSmallSQL, geometry) THEN 1 ELSE 0.75 END * ";
-        }
-        if ($this->sViewboxLargeSQL) {
-            $sImportanceSQL .= " CASE WHEN ST_Contains($this->sViewboxLargeSQL, ST_Collect(centroid)) THEN 1 ELSE 0.75 END * ";
-            $sImportanceSQLGeom .= " CASE WHEN ST_Contains($this->sViewboxLargeSQL, geometry) THEN 1 ELSE 0.75 END * ";
-        }
+        $sImportanceSQL = $oCtx->viewboxImportanceSQL('ST_Collect(centroid)');
+        $sImportanceSQLGeom = $oCtx->viewboxImportanceSQL('geometry');
 
         $sSQL  = "SELECT ";
         $sSQL .= "    osm_type,";
@@ -448,7 +397,7 @@ class Geocode
         if ($this->bIncludeNameDetails) $sSQL .= "hstore_to_json(name)::text AS names,";
         $sSQL .= "    avg(ST_X(centroid)) AS lon, ";
         $sSQL .= "    avg(ST_Y(centroid)) AS lat, ";
-        $sSQL .= "    ".$sImportanceSQL."COALESCE(importance,0.75-(rank_search::float/40)) AS importance, ";
+        $sSQL .= "    COALESCE(importance,0.75-(rank_search::float/40)) $sImportanceSQL AS importance, ";
         $sSQL .= "    ( ";
         $sSQL .= "       SELECT max(p.importance*(p.rank_address+2))";
         $sSQL .= "       FROM ";
@@ -507,7 +456,7 @@ class Geocode
         if ($this->bIncludeExtraTags) $sSQL .= "null AS extra,";
         if ($this->bIncludeNameDetails) $sSQL .= "null AS names,";
         $sSQL .= "  ST_x(st_centroid(geometry)) AS lon, ST_y(st_centroid(geometry)) AS lat,";
-        $sSQL .=    $sImportanceSQLGeom."(0.75-(rank_search::float/40)) AS importance, ";
+        $sSQL .= "  (0.75-(rank_search::float/40)) $sImportanceSQLGeom AS importance, ";
         $sSQL .= "  (";
         $sSQL .= "     SELECT max(p.importance*(p.rank_address+2))";
         $sSQL .= "     FROM ";
@@ -555,7 +504,7 @@ class Geocode
                 if ($this->bIncludeNameDetails) $sSQL .= "null AS names,";
                 $sSQL .= "     avg(st_x(centroid)) AS lon, ";
                 $sSQL .= "     avg(st_y(centroid)) AS lat,";
-                $sSQL .= "     ".$sImportanceSQL."-1.15 AS importance, ";
+                $sSQL .= "     -1.15".$sImportanceSQL." AS importance, ";
                 $sSQL .= "     (";
                 $sSQL .= "        SELECT max(p.importance*(p.rank_address+2))";
                 $sSQL .= "        FROM ";
@@ -605,7 +554,7 @@ class Geocode
             if ($this->bIncludeNameDetails) $sSQL .= "null AS names, ";
             $sSQL .= "  AVG(st_x(centroid)) AS lon, ";
             $sSQL .= "  AVG(st_y(centroid)) AS lat, ";
-            $sSQL .= "  ".$sImportanceSQL."-0.1 AS importance, ";  // slightly smaller than the importance for normal houses with rank 30, which is 0
+            $sSQL .= "  -0.1".$sImportanceSQL." AS importance, ";  // slightly smaller than the importance for normal houses with rank 30, which is 0
             $sSQL .= "  (";
             $sSQL .= "     SELECT ";
             $sSQL .= "       MAX(p.importance*(p.rank_address+2)) ";
@@ -664,7 +613,7 @@ class Geocode
                 if ($this->bIncludeNameDetails) $sSQL .= "null AS names, ";
                 $sSQL .= "     avg(ST_X(centroid)) AS lon, ";
                 $sSQL .= "     avg(ST_Y(centroid)) AS lat, ";
-                $sSQL .= "     ".$sImportanceSQL."-1.10 AS importance, ";
+                $sSQL .= "     -1.10".$sImportanceSQL." AS importance, ";
                 $sSQL .= "     ( ";
                 $sSQL .= "       SELECT max(p.importance*(p.rank_address+2))";
                 $sSQL .= "       FROM ";
@@ -890,6 +839,17 @@ class Geocode
 
         $oCtx = new SearchContext();
 
+        if ($this->aRoutePoints) {
+            $oCtx->setViewboxFromRoute(
+                $this->oDB,
+                $this->aRoutePoints,
+                $this->aRouteWidth,
+                $this->bBoundedSearch
+            );
+        } else if ($this->aViewBox) {
+            $oCtx->setViewboxFromBox($this->aViewBox, $this->bBoundedSearch);
+        }
+
         $sNormQuery = $this->normTerm($this->sQuery);
         $sLanguagePrefArraySQL = getArraySQL(
             array_map("getDBQuoted", $this->aLangPrefOrder)
@@ -911,22 +871,6 @@ class Geocode
             $sQuery = preg_replace('/(^|,)\s*la\s*(,|$)/', '\1louisiana\2', $sQuery);
         }
 
-        $bBoundingBoxSearch = $this->bBoundedSearch && $this->sViewboxSmallSQL;
-        if ($this->sViewboxCentreSQL) {
-            // For complex viewboxes (routes) precompute the bounding geometry
-            $sGeom = chksql(
-                $this->oDB->getOne("select ".$this->sViewboxSmallSQL),
-                "Could not get small viewbox"
-            );
-            $this->sViewboxSmallSQL = "'".$sGeom."'::geometry";
-
-            $sGeom = chksql(
-                $this->oDB->getOne("select ".$this->sViewboxLargeSQL),
-                "Could not get large viewbox"
-            );
-            $this->sViewboxLargeSQL = "'".$sGeom."'::geometry";
-        }
-
         // Do we have anything that looks like a lat/lon pair?
         $sQuery = $oCtx->setNearPointFromQuery($sQuery);
 
@@ -1153,22 +1097,17 @@ class Geocode
                     if ($oSearch->isCountrySearch()) {
                         // Just looking for a country - look it up
                         if (4 >= $this->iMinAddressRank && 4 <= $this->iMaxAddressRank) {
-                            $aPlaceIDs = $oSearch->queryCountry(
-                                $this->oDB,
-                                $bBoundingBoxSearch ? $this->sViewboxSmallSQL : ''
-                            );
+                            $aPlaceIDs = $oSearch->queryCountry($this->oDB);
                         }
                     } elseif (!$oSearch->isNamedSearch()) {
                         // looking for a POI in a geographic area
-                        if (!$bBoundingBoxSearch && !$oCtx->hasNearPoint()) {
+                        if (!$oCtx->isBoundedSearch()) {
                             continue;
                         }
 
                         $aPlaceIDs = $oSearch->queryNearbyPoi(
                             $this->oDB,
                             $sCountryCodesSQL,
-                            $bBoundingBoxSearch ? $this->sViewboxSmallSQL : '',
-                            $this->sViewboxCentreSQL,
                             $this->aExcludePlaceIDs ? join(',', $this->aExcludePlaceIDs) : '',
                             $this->iLimit
                         );
@@ -1188,8 +1127,6 @@ class Geocode
                             $this->iMinAddressRank,
                             $this->iMaxAddressRank,
                             $this->aExcludePlaceIDs ? join(',', $this->aExcludePlaceIDs) : '',
-                            $bBoundingBoxSearch ? $this->sViewboxSmallSQL : '',
-                            $bBoundingBoxSearch ? $this->sViewboxLargeSQL : '',
                             $this->iLimit
                         );
 
@@ -1306,7 +1243,7 @@ class Geocode
 
             // Did we find anything?
             if (isset($aResultPlaceIDs) && sizeof($aResultPlaceIDs)) {
-                $aSearchResults = $this->getDetails($aResultPlaceIDs);
+                $aSearchResults = $this->getDetails($aResultPlaceIDs, $oCtx);
             }
         } else {
             // Just interpret as a reverse geocode
@@ -1318,7 +1255,7 @@ class Geocode
             if (CONST_Debug) var_dump("Reverse search", $aLookup);
 
             if ($aLookup['place_id']) {
-                $aSearchResults = $this->getDetails(array($aLookup['place_id'] => -1));
+                $aSearchResults = $this->getDetails(array($aLookup['place_id'] => -1), $oCtx);
                 $aResultPlaceIDs[$aLookup['place_id']] = -1;
             } else {
                 $aSearchResults = array();
diff --git a/lib/SearchContext.php b/lib/SearchContext.php
index a6b63586..7552ae99 100644
--- a/lib/SearchContext.php
+++ b/lib/SearchContext.php
@@ -16,10 +16,12 @@ require_once(CONST_BasePath.'/lib/lib.php');
 class SearchContext
 {
     private $fNearRadius = false;
-
-    // cached SQL
+    public $bViewboxBounded = false;
 
     public $sqlNear = '';
+    public $sqlViewboxSmall = '';
+    public $sqlViewboxLarge = '';
+    public $sqlViewboxCentre = '';
 
     public function hasNearPoint()
     {
@@ -37,6 +39,58 @@ class SearchContext
         $this->sqlNear = 'ST_SetSRID(ST_Point('.$fLon.','.$fLat.'),4326)';
     }
 
+    public function isBoundedSearch()
+    {
+        return $this->hasNearPoint() || ($this->sqlViewboxSmall && $this->bViewboxBounded);
+
+    }
+
+    public function setViewboxFromBox(&$aViewBox, $bBounded)
+    {
+        $this->bViewboxBounded = $bBounded;
+        $this->sqlViewboxCentre = '';
+
+        $this->sqlViewboxSmall = sprintf(
+            'ST_SetSRID(ST_MakeBox2D(ST_Point(%F,%F),ST_Point(%F,%F)),4326)',
+            $aViewBox[0],
+            $aViewBox[1],
+            $aViewBox[2],
+            $aViewBox[3]
+        );
+
+        $fHeight = $aViewBox[0] - $aViewBox[2];
+        $fWidth = $aViewBox[1] - $aViewBox[3];
+
+        $this->sqlViewboxLarge = sprintf(
+            'ST_SetSRID(ST_MakeBox2D(ST_Point(%F,%F),ST_Point(%F,%F)),4326)',
+            max($aViewBox[0], $aViewBox[2]) + $fHeight,
+            max($aViewBox[1], $aViewBox[3]) + $fWidth,
+            min($aViewBox[0], $aViewBox[2]) - $fHeight,
+            min($aViewBox[1], $aViewBox[3]) - $fWidth
+        );
+    }
+
+    public function setViewboxFromRoute(&$oDB, $aRoutePoints, $fRouteWidth, $bBounded)
+    {
+        $this->bViewboxBounded = $bBounded;
+        $this->sqlViewboxCentre = "ST_SetSRID('LINESTRING(";
+        $sSep = '';
+        foreach ($aRoutePoints as $aPoint) {
+            $fPoint = (float)$aPoint;
+            $this->sqlViewboxCentre .= $sSep.$fPoint;
+            $sSep = ($sSep == ' ') ? ',' : ' ';
+        }
+        $this->sqlViewboxCentre .= ")'::geometry,4326)";
+
+        $sSQL = 'ST_BUFFER('.$this->sqlViewboxCentre.','.($fRouteWidth/69).')';
+        $sGeom = chksql($oDB->getOne("select ".$sSQL), "Could not get small viewbox");
+        $this->sqlViewboxSmall = "'".$sGeom."'::geometry";
+
+        $sSQL = 'ST_BUFFER('.$this->sqlViewboxCentre.','.($fRouteWidth/30).')';
+        $sGeom = chksql($oDB->getOne("select ".$sSQL), "Could not get large viewbox");
+        $this->sqlViewboxLarge = "'".$sGeom."'::geometry";
+    }
+
     /**
      * Extract a coordinate point from a query string.
      *
@@ -70,4 +124,18 @@ class SearchContext
     {
         return sprintf('ST_DWithin(%s, %s, %F)', $sObj, $this->sqlNear, $this->fNearRadius);
     }
+
+    public function viewboxImportanceSQL($sObj)
+    {
+        $sSQL = '';
+
+        if ($this->sqlViewboxSmall) {
+            $sSQL = " * CASE WHEN ST_Contains($this->sqlViewboxSmall, $sObj) THEN 1 ELSE 0.5 END";
+        }
+        if ($this->sqlViewboxLarge) {
+            $sSQL = " * CASE WHEN ST_Contains($this->sqlViewboxLarge, $sObj) THEN 1 ELSE 0.5 END";
+        }
+
+        return $sSQL;
+    }
 }
diff --git a/lib/SearchDescription.php b/lib/SearchDescription.php
index 7073186b..e6cdca63 100644
--- a/lib/SearchDescription.php
+++ b/lib/SearchDescription.php
@@ -367,13 +367,13 @@ class SearchDescription
     /////////// Query functions
 
 
-    public function queryCountry(&$oDB, $sViewboxSQL)
+    public function queryCountry(&$oDB)
     {
         $sSQL = 'SELECT place_id FROM placex ';
         $sSQL .= "WHERE country_code='".$this->sCountryCode."'";
         $sSQL .= ' AND rank_search = 4';
-        if ($sViewboxSQL) {
-            $sSQL .= " AND ST_Intersects($sViewboxSQL, geometry)";
+        if ($this->oContext->bViewboxBounded) {
+            $sSQL .= ' AND ST_Intersects('.$this->oContext->sqlViewboxSmall.', geometry)';
         }
         $sSQL .= " ORDER BY st_area(geometry) DESC LIMIT 1";
 
@@ -382,7 +382,7 @@ class SearchDescription
         return chksql($oDB->getCol($sSQL));
     }
 
-    public function queryNearbyPoi(&$oDB, $sCountryList, $sViewboxSQL, $sViewboxCentreSQL, $sExcludeSQL, $iLimit)
+    public function queryNearbyPoi(&$oDB, $sCountryList, $sExcludeSQL, $iLimit)
     {
         if (!$this->sClass) {
             return array();
@@ -398,8 +398,8 @@ class SearchDescription
             }
             if ($this->oContext->hasNearPoint()) {
                 $sSQL .= ' WHERE '.$this->oContext->withinSQL('ct.centroid');
-            } else {
-                $sSQL .= " WHERE ST_Contains($sViewboxSQL, ct.centroid)";
+            } else if ($this->oContext->bViewboxBounded) {
+                $sSQL .= ' WHERE ST_Contains('.$this->oContext->sqlViewboxSmall.', ct.centroid)';
             }
             if ($sCountryList) {
                 $sSQL .= " AND country_code in ($sCountryList)";
@@ -407,8 +407,9 @@ class SearchDescription
             if ($sExcludeSQL) {
                 $sSQL .= ' AND place_id not in ('.$sExcludeSQL.')';
             }
-            if ($sViewboxCentreSQL) {
-                $sSQL .= " ORDER BY ST_Distance($sViewboxCentreSQL, ct.centroid) ASC";
+            if ($this->oContext->sqlViewboxCentre) {
+                $sSQL .= ' ORDER BY ST_Distance(';
+                $sSQL .= $this->oContext->sqlViewboxCentre.', ct.centroid) ASC';
             } elseif ($this->oContext->hasNearPoint()) {
                 $sSQL .= ' ORDER BY '.$this->oContext->distanceSQL('ct.centroid').' ASC';
             }
@@ -459,7 +460,7 @@ class SearchDescription
         return chksql($oDB->getCol($sSQL));
     }
 
-    public function queryNamedPlace(&$oDB, $aWordFrequencyScores, $sCountryList, $iMinAddressRank, $iMaxAddressRank, $sExcludeSQL, $sViewboxSmall, $sViewboxLarge, $iLimit)
+    public function queryNamedPlace(&$oDB, $aWordFrequencyScores, $sCountryList, $iMinAddressRank, $iMaxAddressRank, $sExcludeSQL, $iLimit)
     {
         $aTerms = array();
         $aOrder = array();
@@ -537,8 +538,8 @@ class SearchDescription
             $aTerms[] = 'place_id not in ('.$sExcludeSQL.')';
         }
 
-        if ($sViewboxSmall) {
-            $aTerms[] = 'centroid && '.$sViewboxSmall;
+        if ($this->oContext->bViewboxBounded) {
+            $aTerms[] = 'centroid && '.$this->oContext->sqlViewboxSmall;
         }
 
         if ($this->oContext->hasNearPoint()) {
@@ -550,12 +551,7 @@ class SearchDescription
         } else {
             $sImportanceSQL = '(CASE WHEN importance = 0 OR importance IS NULL THEN 0.75-(search_rank::float/40) ELSE importance END)';
         }
-        if ($sViewboxSmall) {
-            $sImportanceSQL .= " * CASE WHEN ST_Contains($sViewboxSmall, centroid) THEN 1 ELSE 0.5 END";
-        }
-        if ($sViewboxLarge) {
-            $sImportanceSQL .= " * CASE WHEN ST_Contains($sViewboxLarge, centroid) THEN 1 ELSE 0.5 END";
-        }
+        $sImportanceSQL .= $this->oContext->viewboxImportanceSQL('centroid');
         $aOrder[] = "$sImportanceSQL DESC";
 
         if (sizeof($this->aFullNameAddress)) {