From: Sarah Hoffmann <lonvia@denofr.de>
Date: Mon, 5 Jun 2017 19:33:50 +0000 (+0200)
Subject: limit number of considered places in POI queries
X-Git-Tag: v3.0.0~13^2
X-Git-Url: https://git.openstreetmap.org./nominatim.git/commitdiff_plain/c13094acfdfb6f39e265a04ef6aa636ba64a78ea?hp=13674c3939a1fc03aaef349061dd2a2203afbf27

limit number of considered places in POI queries

When searching for POIs in place_classtype_ tables limit the number
of objects considered to 300. The distinct and order by clauses
forced until now to retrive all matching objects and order them
first which can cause long running queries when retriving them
for large areas like the US.

Fixes #735.
---

diff --git a/lib/Geocode.php b/lib/Geocode.php
index 17aaf826..80449cb6 100644
--- a/lib/Geocode.php
+++ b/lib/Geocode.php
@@ -1555,8 +1555,9 @@ class Geocode
                             }
 
                             if (!$aSearch['sOperator'] || $aSearch['sOperator'] == 'near') { // & in
+                                $sClassTable = 'place_classtype_'.$aSearch['sClass'].'_'.$aSearch['sType'];
                                 $sSQL = "SELECT count(*) FROM pg_tables ";
-                                $sSQL .= "WHERE tablename = 'place_classtype_".$aSearch['sClass']."_".$aSearch['sType']."'";
+                                $sSQL .= "WHERE tablename = '$sClassTable'";
                                 $bCacheTable = chksql($this->oDB->getOne($sSQL));
 
                                 $sSQL = "SELECT min(rank_search) FROM placex WHERE place_id in ($sPlaceIDs)";
@@ -1604,7 +1605,8 @@ class Geocode
                                             $sOrderBysSQL = "ST_Distance(st_centroid('".$sPlaceGeom."'), l.centroid)";
                                         }
 
-                                        $sSQL = "select distinct l.place_id".($sOrderBySQL?','.$sOrderBySQL:'')." from place_classtype_".$aSearch['sClass']."_".$aSearch['sType']." as l";
+                                        $sSQL = "select distinct i.place_id".($sOrderBySQL?', i.order_term':'')." from (";
+                                        $sSQL .= "select l.place_id".($sOrderBySQL?','.$sOrderBySQL.' as order_term':'')." from ".$sClassTable." as l";
                                         if ($sCountryCodesSQL) $sSQL .= " join placex as lp using (place_id)";
                                         if ($sPlaceIDs) {
                                             $sSQL .= ",placex as f where ";
@@ -1618,7 +1620,8 @@ class Geocode
                                             $sSQL .= " and l.place_id not in (".join(',', $this->aExcludePlaceIDs).")";
                                         }
                                         if ($sCountryCodesSQL) $sSQL .= " and lp.country_code in ($sCountryCodesSQL)";
-                                        if ($sOrderBySQL) $sSQL .= "order by ".$sOrderBySQL." asc";
+                                        $sSQL .= 'limit 300) i ';
+                                        if ($sOrderBySQL) $sSQL .= "order by order_term asc";
                                         if ($this->iOffset) $sSQL .= " offset $this->iOffset";
                                         $sSQL .= " limit $this->iLimit";
                                         if (CONST_Debug) var_dump($sSQL);
diff --git a/test/bdd/api/search/queries.feature b/test/bdd/api/search/queries.feature
index 0074e334..638177fd 100644
--- a/test/bdd/api/search/queries.feature
+++ b/test/bdd/api/search/queries.feature
@@ -52,7 +52,7 @@ Feature: Search queries
          | way |
 
     Scenario: Search with class-type feature
-        When sending jsonv2 search query "Hotel California"
+        When sending jsonv2 search query "Hotel in California"
         Then results contain
           | place_rank |
           | 30 |