From c13094acfdfb6f39e265a04ef6aa636ba64a78ea Mon Sep 17 00:00:00 2001 From: Sarah Hoffmann Date: Mon, 5 Jun 2017 21:33:50 +0200 Subject: [PATCH] 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. --- lib/Geocode.php | 9 ++++++--- test/bdd/api/search/queries.feature | 2 +- 2 files changed, 7 insertions(+), 4 deletions(-) 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 | -- 2.39.5