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 17aaf826e2963e2f9405561bafd04604a2fad651..80449cb631d350797d1ab2679aee2c22c4343368 100644 (file)
--- a/lib/Geocode.php
+++ b/lib/Geocode.php
@@ -1555,8 +1555,9 @@ class Geocode
                             }
 
                             if (!$aSearch['sOperator'] || $aSearch['sOperator'] == 'near') { // & in
                             }
 
                             if (!$aSearch['sOperator'] || $aSearch['sOperator'] == 'near') { // & in

+                                $sClassTable = 'place_classtype_'.$aSearch['sClass'].'_'.$aSearch['sType'];

                                 $sSQL = "SELECT count(*) FROM pg_tables ";
                                 $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)";
                                 $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)";
                                         }
 
                                             $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 ";
                                         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)";
                                             $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);
                                         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 0074e3344fe5301f0523742d190394713be36f75..638177fdf1a1bb3845a9fce161e893d06a39c1cc 100644 (file)
--- 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
          | 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 |
         Then results contain
           | place_rank |
           | 30 |