lib/SearchDescription.php

   1 <?php
   2
   3 namespace Nominatim;
   4
   5 require_once(CONST_BasePath.'/lib/SpecialSearchOperator.php');
   6
   7 /**
   8  * Description of a single interpretation of a search query.
   9  */
  10 class SearchDescription
  11 {
  12     /// Ranking how well the description fits the query.
  13     private $iSearchRank = 0;
  14     /// Country code of country the result must belong to.
  15     private $sCountryCode = '';
  16     /// List of word ids making up the name of the object.
  17     private $aName = array();
  18     /// List of word ids making up the address of the object.
  19     private $aAddress = array();
  20     /// Subset of word ids of full words making up the address.
  21     private $aFullNameAddress = array();
  22     /// List of word ids that appear in the name but should be ignored.
  23     private $aNameNonSearch = array();
  24     /// List of word ids that appear in the address but should be ignored.
  25     private $aAddressNonSearch = array();
  26     /// Kind of search for special searches, see Nominatim::Operator.
  27     private $iOperator = Operator::NONE;
  28     /// Class of special feature to search for.
  29     private $sClass = '';
  30     /// Type of special feature to search for.
  31     private $sType = '';
  32     /// Housenumber of the object.
  33     private $sHouseNumber = '';
  34     /// Postcode for the object.
  35     private $sPostcode = '';
  36     /// Geographic search area.
  37     private $oNearPoint = false;
  38
  39     // Temporary values used while creating the search description.
  40
  41     /// Index of phrase currently processed
  42     private $iNamePhrase = -1;
  43
  44
  45     public function getRank()
  46     {
  47         return $this->iSearchRank;
  48     }
  49
  50     public function addToRank($iAddRank)
  51     {
  52         $this->iSearchRank += $iAddRank;
  53         return $this->iSearchRank;
  54     }
  55
  56     public function getPostCode()
  57     {
  58         return $this->sPostcode;
  59     }
  60
  61     public function setNear(&$oNearPoint)
  62     {
  63         $this->oNearPoint = $oNearPoint;
  64     }
  65
  66     public function setPoiSearch($iOperator, $sClass, $sType)
  67     {
  68         $this->iOperator = $iOperator;
  69         $this->sClass = $sClass;
  70         $this->sType = $sType;
  71     }
  72
  73     public function isNamedSearch()
  74     {
  75         return sizeof($this->aName) > 0 || sizeof($this->aAddress) > 0;
  76     }
  77
  78     public function isCountrySearch()
  79     {
  80         return $this->sCountryCode && sizeof($this->aName) == 0
  81                && !$this->iOperator && !$this->oNearPoint;
  82     }
  83
  84     public function isNearSearch()
  85     {
  86         return (bool) $this->oNearPoint;
  87     }
  88
  89     public function isPoiSearch()
  90     {
  91         return (bool) $this->sClass;
  92     }
  93
  94     public function looksLikeFullAddress()
  95     {
  96         return sizeof($this->aName)
  97                && (sizeof($this->aAddress || $this->sCountryCode))
  98                && preg_match('/[0-9]+/', $this->sHouseNumber);
  99     }
 100
 101     public function isOperator($iType)
 102     {
 103         return $this->iOperator == $iType;
 104     }
 105
 106     public function hasHouseNumber()
 107     {
 108         return (bool) $this->sHouseNumber;
 109     }
 110
 111     private function poiTable()
 112     {
 113         return 'place_classtype_'.$this->sClass.'_'.$this->sType;
 114     }
 115
 116     public function countryCodeSQL($sVar, $sCountryList)
 117     {
 118         if ($this->sCountryCode) {
 119             return $sVar.' = \''.$this->sCountryCode."'";
 120         }
 121         if ($sCountryList) {
 122             return $sVar.' in ('.$sCountryList.')';
 123         }
 124
 125         return '';
 126     }
 127
 128     public function hasOperator()
 129     {
 130         return $this->iOperator != Operator::NONE;
 131     }
 132
 133     public function extractKeyValuePairs($sQuery)
 134     {
 135         // Search for terms of kind [<key>=<value>].
 136         preg_match_all(
 137             '/\\[([\\w_]*)=([\\w_]*)\\]/',
 138             $sQuery,
 139             $aSpecialTermsRaw,
 140             PREG_SET_ORDER
 141         );
 142
 143         foreach ($aSpecialTermsRaw as $aTerm) {
 144             $sQuery = str_replace($aTerm[0], ' ', $sQuery);
 145             if (!$this->hasOperator()) {
 146                 $this->setPoiSearch(Operator::TYPE, $aTerm[1], $aTerm[2]);
 147             }
 148         }
 149
 150         return $sQuery;
 151     }
 152
 153     public function isValidSearch(&$aCountryCodes)
 154     {
 155         if (!sizeof($this->aName)) {
 156             if ($this->sHouseNumber) {
 157                 return false;
 158             }
 159         }
 160         if ($aCountryCodes
 161             && $this->sCountryCode
 162             && !in_array($this->sCountryCode, $aCountryCodes)
 163         ) {
 164             return false;
 165         }
 166
 167         return true;
 168     }
 169
 170     /////////// Search building functions
 171
 172
 173     public function extendWithFullTerm($aSearchTerm, $bWordInQuery, $bHasPartial, $sPhraseType, $bFirstToken, $bFirstPhrase, $bLastToken, &$iGlobalRank)
 174     {
 175         $aNewSearches = array();
 176
 177         if (($sPhraseType == '' || $sPhraseType == 'country')
 178             && !empty($aSearchTerm['country_code'])
 179             && $aSearchTerm['country_code'] != '0'
 180         ) {
 181             if (!$this->sCountryCode) {
 182                 $oSearch = clone $this;
 183                 $oSearch->iSearchRank++;
 184                 $oSearch->sCountryCode = $aSearchTerm['country_code'];
 185                 // Country is almost always at the end of the string
 186                 // - increase score for finding it anywhere else (optimisation)
 187                 if (!$bLastToken) {
 188                     $oSearch->iSearchRank += 5;
 189                 }
 190                 $aNewSearches[] = $oSearch;
 191
 192                 // If it is at the beginning, we can be almost sure that
 193                 // the terms are in the wrong order. Increase score for all searches.
 194                 if ($bFirstToken) {
 195                     $iGlobalRank++;
 196                 }
 197             }
 198         } elseif (($sPhraseType == '' || $sPhraseType == 'postalcode')
 199                   && $aSearchTerm['class'] == 'place' && $aSearchTerm['type'] == 'postcode'
 200         ) {
 201             // We need to try the case where the postal code is the primary element
 202             // (i.e. no way to tell if it is (postalcode, city) OR (city, postalcode)
 203             // so try both.
 204             if (!$this->sPostcode && $bWordInQuery
 205                 && pg_escape_string($aSearchTerm['word']) == $aSearchTerm['word']
 206             ) {
 207                 // If we have structured search or this is the first term,
 208                 // make the postcode the primary search element.
 209                 if ($this->iOperator == Operator::NONE
 210                     && ($sPhraseType == 'postalcode' || $bFirstToken)
 211                 ) {
 212                     $oSearch = clone $this;
 213                     $oSearch->iSearchRank++;
 214                     $oSearch->iOperator = Operator::POSTCODE;
 215                     $oSearch->aAddress = array_merge($this->aAddress, $this->aName);
 216                     $oSearch->aName =
 217                         array($aSearchTerm['word_id'] => $aSearchTerm['word']);
 218                     $aNewSearches[] = $oSearch;
 219                 }
 220
 221                 // If we have a structured search or this is not the first term,
 222                 // add the postcode as an addendum.
 223                 if ($this->iOperator != Operator::POSTCODE
 224                     && ($sPhraseType == 'postalcode' || sizeof($this->aName))
 225                 ) {
 226                     $oSearch = clone $this;
 227                     $oSearch->iSearchRank++;
 228                     $oSearch->sPostcode = $aSearchTerm['word'];
 229                     $aNewSearches[] = $oSearch;
 230                 }
 231             }
 232         } elseif (($sPhraseType == '' || $sPhraseType == 'street')
 233                  && $aSearchTerm['class'] == 'place' && $aSearchTerm['type'] == 'house'
 234         ) {
 235             if (!$this->sHouseNumber && $this->iOperator != Operator::POSTCODE) {
 236                 $oSearch = clone $this;
 237                 $oSearch->iSearchRank++;
 238                 $oSearch->sHouseNumber = trim($aSearchTerm['word_token']);
 239                 // sanity check: if the housenumber is not mainly made
 240                 // up of numbers, add a penalty
 241                 if (preg_match_all("/[^0-9]/", $oSearch->sHouseNumber, $aMatches) > 2) {
 242                     $oSearch->iSearchRank++;
 243                 }
 244                 if (!isset($aSearchTerm['word_id'])) {
 245                     $oSearch->iSearchRank++;
 246                 }
 247                 // also must not appear in the middle of the address
 248                 if (sizeof($this->aAddress) || sizeof($this->aAddressNonSearch)) {
 249                     $oSearch->iSearchRank++;
 250                 }
 251                 $aNewSearches[] = $oSearch;
 252             }
 253         } elseif ($sPhraseType == ''
 254                   && $aSearchTerm['class'] !== '' && $aSearchTerm['class'] !== null
 255         ) {
 256             // require a normalized exact match of the term
 257             // if we have the normalizer version of the query
 258             // available
 259             if ($this->iOperator == Operator::NONE
 260                 && (isset($aSearchTerm['word']) && $aSearchTerm['word'])
 261                 && $bWordInQuery
 262             ) {
 263                 $oSearch = clone $this;
 264                 $oSearch->iSearchRank++;
 265
 266                 $iOp = Operator::NEAR; // near == in for the moment
 267                 if ($aSearchTerm['operator'] == '') {
 268                     if (sizeof($this->aName)) {
 269                         $iOp = Operator::NAME;
 270                     }
 271                     $oSearch->iSearchRank += 2;
 272                 }
 273
 274                 $oSearch->setPoiSearch($iOp, $aSearchTerm['class'], $aSearchTerm['type']);
 275                 $aNewSearches[] = $oSearch;
 276             }
 277         } elseif (isset($aSearchTerm['word_id']) && $aSearchTerm['word_id']) {
 278             $iWordID = $aSearchTerm['word_id'];
 279             if (sizeof($this->aName)) {
 280                 if (($sPhraseType == '' || !$bFirstPhrase)
 281                     && $sPhraseType != 'country'
 282                     && !$bHasPartial
 283                 ) {
 284                     $oSearch = clone $this;
 285                     $oSearch->iSearchRank++;
 286                     $oSearch->aAddress[$iWordID] = $iWordID;
 287                     $aNewSearches[] = $oSearch;
 288                 } else {
 289                     $this->aFullNameAddress[$iWordID] = $iWordID;
 290                 }
 291             } else {
 292                 $oSearch = clone $this;
 293                 $oSearch->iSearchRank++;
 294                 $oSearch->aName = array($iWordID => $iWordID);
 295                 $aNewSearches[] = $oSearch;
 296             }
 297         }
 298
 299         return $aNewSearches;
 300     }
 301
 302     public function extendWithPartialTerm($aSearchTerm, $bStructuredPhrases, $iPhrase, &$aWordFrequencyScores, $aFullTokens)
 303     {
 304         // Only allow name terms.
 305         if (!(isset($aSearchTerm['word_id']) && $aSearchTerm['word_id'])) {
 306             return array();
 307         }
 308
 309         $aNewSearches = array();
 310         $iWordID = $aSearchTerm['word_id'];
 311
 312         if ((!$bStructuredPhrases || $iPhrase > 0)
 313             && sizeof($this->aName)
 314             && strpos($aSearchTerm['word_token'], ' ') === false
 315         ) {
 316             if ($aWordFrequencyScores[$iWordID] < CONST_Max_Word_Frequency) {
 317                 $oSearch = clone $this;
 318                 $oSearch->iSearchRank++;
 319                 $oSearch->aAddress[$iWordID] = $iWordID;
 320                 $aNewSearches[] = $oSearch;
 321             } else {
 322                 $oSearch = clone $this;
 323                 $oSearch->iSearchRank++;
 324                 $oSearch->aAddressNonSearch[$iWordID] = $iWordID;
 325                 if (preg_match('#^[0-9]+$#', $aSearchTerm['word_token'])) {
 326                     $oSearch->iSearchRank += 2;
 327                 }
 328                 if (sizeof($aFullTokens)) {
 329                     $oSearch->iSearchRank++;
 330                 }
 331                 $aNewSearches[] = $oSearch;
 332
 333                 // revert to the token version?
 334                 foreach ($aFullTokens as $aSearchTermToken) {
 335                     if (empty($aSearchTermToken['country_code'])
 336                         && empty($aSearchTermToken['lat'])
 337                         && empty($aSearchTermToken['class'])
 338                     ) {
 339                         $oSearch = clone $this;
 340                         $oSearch->iSearchRank++;
 341                         $oSearch->aAddress[$aSearchTermToken['word_id']] = $aSearchTermToken['word_id'];
 342                         $aNewSearches[] = $oSearch;
 343                     }
 344                 }
 345             }
 346         }
 347
 348         if ((!$this->sPostcode && !$this->aAddress && !$this->aAddressNonSearch)
 349             && (!sizeof($this->aName) || $this->iNamePhrase == $iPhrase)
 350         ) {
 351             $oSearch = clone $this;
 352             $oSearch->iSearchRank++;
 353             if (!sizeof($this->aName)) {
 354                 $oSearch->iSearchRank += 1;
 355             }
 356             if (preg_match('#^[0-9]+$#', $aSearchTerm['word_token'])) {
 357                 $oSearch->iSearchRank += 2;
 358             }
 359             if ($aWordFrequencyScores[$iWordID] < CONST_Max_Word_Frequency) {
 360                 $oSearch->aName[$iWordID] = $iWordID;
 361             } else {
 362                 $oSearch->aNameNonSearch[$iWordID] = $iWordID;
 363             }
 364             $oSearch->iNamePhrase = $iPhrase;
 365             $aNewSearches[] = $oSearch;
 366         }
 367
 368         return $aNewSearches;
 369     }
 370
 371     /////////// Query functions
 372
 373
 374     public function queryCountry(&$oDB, $sViewboxSQL)
 375     {
 376         $sSQL = 'SELECT place_id FROM placex ';
 377         $sSQL .= "WHERE country_code='".$this->sCountryCode."'";
 378         $sSQL .= ' AND rank_search = 4';
 379         if ($sViewboxSQL) {
 380             $sSQL .= " AND ST_Intersects($sViewboxSQL, geometry)";
 381         }
 382         $sSQL .= " ORDER BY st_area(geometry) DESC LIMIT 1";
 383
 384         if (CONST_Debug) var_dump($sSQL);
 385
 386         return chksql($oDB->getCol($sSQL));
 387     }
 388
 389     public function queryNearbyPoi(&$oDB, $sCountryList, $sViewboxSQL, $sViewboxCentreSQL, $sExcludeSQL, $iLimit)
 390     {
 391         if (!$this->sClass) {
 392             return array();
 393         }
 394
 395         $sPoiTable = $this->poiTable();
 396
 397         $sSQL = 'SELECT count(*) FROM pg_tables WHERE tablename = \''.$sPoiTable."'";
 398         if (chksql($oDB->getOne($sSQL))) {
 399             $sSQL = 'SELECT place_id FROM '.$sPoiTable.' ct';
 400             if ($sCountryList) {
 401                 $sSQL .= ' JOIN placex USING (place_id)';
 402             }
 403             if ($this->oNearPoint) {
 404                 $sSQL .= ' WHERE '.$this->oNearPoint->withinSQL('ct.centroid');
 405             } else {
 406                 $sSQL .= " WHERE ST_Contains($sViewboxSQL, ct.centroid)";
 407             }
 408             if ($sCountryList) {
 409                 $sSQL .= " AND country_code in ($sCountryList)";
 410             }
 411             if ($sExcludeSQL) {
 412                 $sSQL .= ' AND place_id not in ('.$sExcludeSQL.')';
 413             }
 414             if ($sViewboxCentreSQL) {
 415                 $sSQL .= " ORDER BY ST_Distance($sViewboxCentreSQL, ct.centroid) ASC";
 416             } elseif ($this->oNearPoint) {
 417                 $sSQL .= ' ORDER BY '.$this->oNearPoint->distanceSQL('ct.centroid').' ASC';
 418             }
 419             $sSQL .= " limit $iLimit";
 420             if (CONST_Debug) var_dump($sSQL);
 421             return chksql($oDB->getCol($sSQL));
 422         }
 423
 424         if ($this->oNearPoint) {
 425             $sSQL = 'SELECT place_id FROM placex WHERE ';
 426             $sSQL .= 'class=\''.$this->sClass."' and type='".$this->sType."'";
 427             $sSQL .= ' AND '.$this->oNearPoint->withinSQL('geometry');
 428             $sSQL .= ' AND linked_place_id is null';
 429             if ($sCountryList) {
 430                 $sSQL .= " AND country_code in ($sCountryList)";
 431             }
 432             $sSQL .= ' ORDER BY '.$this->oNearPoint->distanceSQL('centroid')." ASC";
 433             $sSQL .= " LIMIT $iLimit";
 434             if (CONST_Debug) var_dump($sSQL);
 435             return chksql($oDB->getCol($sSQL));
 436         }
 437
 438         return array();
 439     }
 440
 441     public function queryPostcode(&$oDB, $sCountryList, $iLimit)
 442     {
 443         $sSQL = 'SELECT p.place_id FROM location_postcode p ';
 444
 445         if (sizeof($this->aAddress)) {
 446             $sSQL .= ', search_name s ';
 447             $sSQL .= 'WHERE s.place_id = p.parent_place_id ';
 448             $sSQL .= 'AND array_cat(s.nameaddress_vector, s.name_vector)';
 449             $sSQL .= '      @> '.getArraySQL($this->aAddress).' AND ';
 450         } else {
 451             $sSQL .= 'WHERE ';
 452         }
 453
 454         $sSQL .= "p.postcode = '".reset($this->aName)."'";
 455         $sCountryTerm = $this->countryCodeSQL('p.country_code', $sCountryList);
 456         if ($sCountryTerm) {
 457             $sSQL .= ' AND '.$sCountryTerm;
 458         }
 459         $sSQL .= " LIMIT $iLimit";
 460
 461         if (CONST_Debug) var_dump($sSQL);
 462
 463         return chksql($oDB->getCol($sSQL));
 464     }
 465
 466     public function queryNamedPlace(&$oDB, $aWordFrequencyScores, $sCountryList, $iMinAddressRank, $iMaxAddressRank, $sExcludeSQL, $sViewboxSmall, $sViewboxLarge, $iLimit)
 467     {
 468         $aTerms = array();
 469         $aOrder = array();
 470
 471         if ($this->sHouseNumber && sizeof($this->aAddress)) {
 472             $sHouseNumberRegex = '\\\\m'.$this->sHouseNumber.'\\\\M';
 473             $aOrder[] = ' (';
 474             $aOrder[0] .= 'EXISTS(';
 475             $aOrder[0] .= '  SELECT place_id';
 476             $aOrder[0] .= '  FROM placex';
 477             $aOrder[0] .= '  WHERE parent_place_id = search_name.place_id';
 478             $aOrder[0] .= "    AND transliteration(housenumber) ~* E'".$sHouseNumberRegex."'";
 479             $aOrder[0] .= '  LIMIT 1';
 480             $aOrder[0] .= ') ';
 481             // also housenumbers from interpolation lines table are needed
 482             if (preg_match('/[0-9]+/', $this->sHouseNumber)) {
 483                 $iHouseNumber = intval($this->sHouseNumber);
 484                 $aOrder[0] .= 'OR EXISTS(';
 485                 $aOrder[0] .= '  SELECT place_id ';
 486                 $aOrder[0] .= '  FROM location_property_osmline ';
 487                 $aOrder[0] .= '  WHERE parent_place_id = search_name.place_id';
 488                 $aOrder[0] .= '    AND startnumber is not NULL';
 489                 $aOrder[0] .= '    AND '.$iHouseNumber.'>=startnumber ';
 490                 $aOrder[0] .= '    AND '.$iHouseNumber.'<=endnumber ';
 491                 $aOrder[0] .= '  LIMIT 1';
 492                 $aOrder[0] .= ')';
 493             }
 494             $aOrder[0] .= ') DESC';
 495         }
 496
 497         if (sizeof($this->aName)) {
 498             $aTerms[] = 'name_vector @> '.getArraySQL($this->aName);
 499         }
 500         if (sizeof($this->aAddress)) {
 501             // For infrequent name terms disable index usage for address
 502             if (CONST_Search_NameOnlySearchFrequencyThreshold
 503                 && sizeof($this->aName) == 1
 504                 && $aWordFrequencyScores[$this->aName[reset($this->aName)]]
 505                      < CONST_Search_NameOnlySearchFrequencyThreshold
 506             ) {
 507                 $aTerms[] = 'array_cat(nameaddress_vector,ARRAY[]::integer[]) @> '.getArraySQL($this->aAddress);
 508             } else {
 509                 $aTerms[] = 'nameaddress_vector @> '.getArraySQL($this->aAddress);
 510             }
 511         }
 512
 513         $sCountryTerm = $this->countryCodeSQL('country_code', $sCountryList);
 514         if ($sCountryTerm) {
 515             $aTerms[] = $sCountryTerm;
 516         }
 517
 518         if ($this->sHouseNumber) {
 519             $aTerms[] = "address_rank between 16 and 27";
 520         } elseif (!$this->sClass || $this->iOperator == Operator::NAME) {
 521             if ($iMinAddressRank > 0) {
 522                 $aTerms[] = "address_rank >= ".$iMinAddressRank;
 523             }
 524             if ($iMaxAddressRank < 30) {
 525                 $aTerms[] = "address_rank <= ".$iMaxAddressRank;
 526             }
 527         }
 528
 529         if ($this->oNearPoint) {
 530             $aTerms[] = $this->oNearPoint->withinSQL('centroid');
 531             $aOrder[] = $this->oNearPoint->distanceSQL('centroid');
 532         } elseif ($this->sPostcode) {
 533             if (!sizeof($this->aAddress)) {
 534                 $aTerms[] = "EXISTS(SELECT place_id FROM location_postcode p WHERE p.postcode = '".$this->sPostcode."' AND ST_DWithin(search_name.centroid, p.geometry, 0.1))";
 535             } else {
 536                 $aOrder[] = "(SELECT min(ST_Distance(search_name.centroid, p.geometry)) FROM location_postcode p WHERE p.postcode = '".$this->sPostcode."')";
 537             }
 538         }
 539
 540         if ($sExcludeSQL) {
 541             $aTerms[] = 'place_id not in ('.$sExcludeSQL.')';
 542         }
 543
 544         if ($sViewboxSmall) {
 545             $aTerms[] = 'centroid && '.$sViewboxSmall;
 546         }
 547
 548         if ($this->oNearPoint) {
 549             $aOrder[] = $this->oNearPoint->distanceSQL('centroid');
 550         }
 551
 552         if ($this->sHouseNumber) {
 553             $sImportanceSQL = '- abs(26 - address_rank) + 3';
 554         } else {
 555             $sImportanceSQL = '(CASE WHEN importance = 0 OR importance IS NULL THEN 0.75-(search_rank::float/40) ELSE importance END)';
 556         }
 557         if ($sViewboxSmall) {
 558             $sImportanceSQL .= " * CASE WHEN ST_Contains($sViewboxSmall, centroid) THEN 1 ELSE 0.5 END";
 559         }
 560         if ($sViewboxLarge) {
 561             $sImportanceSQL .= " * CASE WHEN ST_Contains($sViewboxLarge, centroid) THEN 1 ELSE 0.5 END";
 562         }
 563         $aOrder[] = "$sImportanceSQL DESC";
 564
 565         if (sizeof($this->aFullNameAddress)) {
 566             $sExactMatchSQL = ' ( ';
 567             $sExactMatchSQL .= ' SELECT count(*) FROM ( ';
 568             $sExactMatchSQL .= '  SELECT unnest('.getArraySQL($this->aFullNameAddress).')';
 569             $sExactMatchSQL .= '    INTERSECT ';
 570             $sExactMatchSQL .= '  SELECT unnest(nameaddress_vector)';
 571             $sExactMatchSQL .= ' ) s';
 572             $sExactMatchSQL .= ') as exactmatch';
 573             $aOrder[] = 'exactmatch DESC';
 574         } else {
 575             $sExactMatchSQL = '0::int as exactmatch';
 576         }
 577
 578         if ($this->sHouseNumber || $this->sClass) {
 579             $iLimit = 20;
 580         }
 581
 582         if (sizeof($aTerms)) {
 583             $sSQL = 'SELECT place_id,'.$sExactMatchSQL;
 584             $sSQL .= ' FROM search_name';
 585             $sSQL .= ' WHERE '.join(' and ', $aTerms);
 586             $sSQL .= ' ORDER BY '.join(', ', $aOrder);
 587             $sSQL .= ' LIMIT '.$iLimit;
 588
 589             if (CONST_Debug) var_dump($sSQL);
 590
 591             return chksql(
 592                 $oDB->getAll($sSQL),
 593                 "Could not get places for search terms."
 594             );
 595         }
 596
 597         return array();
 598     }
 599
 600
 601     public function queryHouseNumber(&$oDB, $aRoadPlaceIDs, $sExcludeSQL, $iLimit)
 602     {
 603         $sPlaceIDs = join(',', $aRoadPlaceIDs);
 604
 605         $sHouseNumberRegex = '\\\\m'.$this->sHouseNumber.'\\\\M';
 606         $sSQL = 'SELECT place_id FROM placex ';
 607         $sSQL .= 'WHERE parent_place_id in ('.$sPlaceIDs.')';
 608         $sSQL .= "  AND transliteration(housenumber) ~* E'".$sHouseNumberRegex."'";
 609         if ($sExcludeSQL) {
 610             $sSQL .= ' AND place_id not in ('.$sExcludeSQL.')';
 611         }
 612         $sSQL .= " LIMIT $iLimit";
 613
 614         if (CONST_Debug) var_dump($sSQL);
 615
 616         $aPlaceIDs = chksql($oDB->getCol($sSQL));
 617
 618         if (sizeof($aPlaceIDs)) {
 619             return array('aPlaceIDs' => $aPlaceIDs, 'iHouseNumber' => -1);
 620         }
 621
 622         $bIsIntHouseNumber= (bool) preg_match('/[0-9]+/', $this->sHouseNumber);
 623         $iHousenumber = intval($this->sHouseNumber);
 624         if ($bIsIntHouseNumber) {
 625             // if nothing found, search in the interpolation line table
 626             $sSQL = 'SELECT distinct place_id FROM location_property_osmline';
 627             $sSQL .= ' WHERE startnumber is not NULL';
 628             $sSQL .= '  AND parent_place_id in ('.$sPlaceIDs.') AND (';
 629             if ($iHousenumber % 2 == 0) {
 630                 // If housenumber is even, look for housenumber in streets
 631                 // with interpolationtype even or all.
 632                 $sSQL .= "interpolationtype='even'";
 633             } else {
 634                 // Else look for housenumber with interpolationtype odd or all.
 635                 $sSQL .= "interpolationtype='odd'";
 636             }
 637             $sSQL .= " or interpolationtype='all') and ";
 638             $sSQL .= $iHousenumber.">=startnumber and ";
 639             $sSQL .= $iHousenumber."<=endnumber";
 640
 641             if ($sExcludeSQL) {
 642                 $sSQL .= ' AND place_id not in ('.$sExcludeSQL.')';
 643             }
 644             $sSQL .= " limit $iLimit";
 645
 646             if (CONST_Debug) var_dump($sSQL);
 647
 648             $aPlaceIDs = chksql($oDB->getCol($sSQL, 0));
 649
 650             if (sizeof($aPlaceIDs)) {
 651                 return array('aPlaceIDs' => $aPlaceIDs, 'iHouseNumber' => $iHousenumber);
 652             }
 653         }
 654
 655         // If nothing found try the aux fallback table
 656         if (CONST_Use_Aux_Location_data) {
 657             $sSQL = 'SELECT place_id FROM location_property_aux';
 658             $sSQL .= ' WHERE parent_place_id in ('.$sPlaceIDs.')';
 659             $sSQL .= " AND housenumber = '".$this->sHouseNumber."'";
 660             if ($sExcludeSQL) {
 661                 $sSQL .= " AND place_id not in ($sExcludeSQL)";
 662             }
 663             $sSQL .= " limit $iLimit";
 664
 665             if (CONST_Debug) var_dump($sSQL);
 666
 667             $aPlaceIDs = chksql($oDB->getCol($sSQL));
 668
 669             if (sizeof($aPlaceIDs)) {
 670                 return array('aPlaceIDs' => $aPlaceIDs, 'iHouseNumber' => -1);
 671             }
 672         }
 673
 674         // If nothing found then search in Tiger data (location_property_tiger)
 675         if (CONST_Use_US_Tiger_Data && $bIsIntHouseNumber) {
 676             $sSQL = 'SELECT distinct place_id FROM location_property_tiger';
 677             $sSQL .= ' WHERE parent_place_id in ('.$sPlaceIDs.') and (';
 678             if ($iHousenumber % 2 == 0) {
 679                 $sSQL .= "interpolationtype='even'";
 680             } else {
 681                 $sSQL .= "interpolationtype='odd'";
 682             }
 683             $sSQL .= " or interpolationtype='all') and ";
 684             $sSQL .= $iHousenumber.">=startnumber and ";
 685             $sSQL .= $iHousenumber."<=endnumber";
 686
 687             if ($sExcludeSQL) {
 688                 $sSQL .= ' AND place_id not in ('.$sExcludeSQL.')';
 689             }
 690             $sSQL .= " limit $iLimit";
 691
 692             if (CONST_Debug) var_dump($sSQL);
 693
 694             $aPlaceIDs = chksql($oDB->getCol($sSQL, 0));
 695
 696             if (sizeof($aPlaceIDs)) {
 697                 return array('aPlaceIDs' => $aPlaceIDs, 'iHouseNumber' => $iHousenumber);
 698             }
 699         }
 700
 701         return array();
 702     }
 703
 704
 705     public function queryPoiByOperator(&$oDB, $aParentIDs, $sExcludeSQL, $iLimit)
 706     {
 707         $sPlaceIDs = join(',', $aParentIDs);
 708         $aClassPlaceIDs = array();
 709
 710         if ($this->iOperator == Operator::TYPE || $this->iOperator == Operator::NAME) {
 711             // If they were searching for a named class (i.e. 'Kings Head pub')
 712             // then we might have an extra match
 713             $sSQL = 'SELECT place_id FROM placex ';
 714             $sSQL .= " WHERE place_id in ($sPlaceIDs)";
 715             $sSQL .= "   AND class='".$this->sClass."' ";
 716             $sSQL .= "   AND type='".$this->sType."'";
 717             $sSQL .= "   AND linked_place_id is null";
 718             $sSQL .= " ORDER BY rank_search ASC ";
 719             $sSQL .= " LIMIT $iLimit";
 720
 721             if (CONST_Debug) var_dump($sSQL);
 722
 723             $aClassPlaceIDs = chksql($oDB->getCol($sSQL));
 724         }
 725
 726         // NEAR and IN are handled the same
 727         if ($this->iOperator == Operator::TYPE || $this->iOperator == Operator::NEAR) {
 728             $sClassTable = $this->poiTable();
 729             $sSQL = "SELECT count(*) FROM pg_tables WHERE tablename = '$sClassTable'";
 730             $bCacheTable = (bool) chksql($oDB->getOne($sSQL));
 731
 732             $sSQL = "SELECT min(rank_search) FROM placex WHERE place_id in ($sPlaceIDs)";
 733             if (CONST_Debug) var_dump($sSQL);
 734             $iMaxRank = (int)chksql($oDB->getOne($sSQL));
 735
 736             // For state / country level searches the normal radius search doesn't work very well
 737             $sPlaceGeom = false;
 738             if ($iMaxRank < 9 && $bCacheTable) {
 739                 // Try and get a polygon to search in instead
 740                 $sSQL = 'SELECT geometry FROM placex';
 741                 $sSQL .= " WHERE place_id in ($sPlaceIDs)";
 742                 $sSQL .= "   AND rank_search < $iMaxRank + 5";
 743                 $sSQL .= "   AND ST_GeometryType(geometry) in ('ST_Polygon','ST_MultiPolygon')";
 744                 $sSQL .= " ORDER BY rank_search ASC ";
 745                 $sSQL .= " LIMIT 1";
 746                 if (CONST_Debug) var_dump($sSQL);
 747                 $sPlaceGeom = chksql($oDB->getOne($sSQL));
 748             }
 749
 750             if ($sPlaceGeom) {
 751                 $sPlaceIDs = false;
 752             } else {
 753                 $iMaxRank += 5;
 754                 $sSQL = 'SELECT place_id FROM placex';
 755                 $sSQL .= " WHERE place_id in ($sPlaceIDs) and rank_search < $iMaxRank";
 756                 if (CONST_Debug) var_dump($sSQL);
 757                 $aPlaceIDs = chksql($oDB->getCol($sSQL));
 758                 $sPlaceIDs = join(',', $aPlaceIDs);
 759             }
 760
 761             if ($sPlaceIDs || $sPlaceGeom) {
 762                 $fRange = 0.01;
 763                 if ($bCacheTable) {
 764                     // More efficient - can make the range bigger
 765                     $fRange = 0.05;
 766
 767                     $sOrderBySQL = '';
 768                     if ($this->oNearPoint) {
 769                         $sOrderBySQL = $this->oNearPoint->distanceSQL('l.centroid');
 770                     } elseif ($sPlaceIDs) {
 771                         $sOrderBySQL = "ST_Distance(l.centroid, f.geometry)";
 772                     } elseif ($sPlaceGeom) {
 773                         $sOrderBySQL = "ST_Distance(st_centroid('".$sPlaceGeom."'), l.centroid)";
 774                     }
 775
 776                     $sSQL = 'SELECT distinct i.place_id';
 777                     if ($sOrderBySQL) {
 778                         $sSQL .= ', i.order_term';
 779                     }
 780                     $sSQL .= ' from (SELECT l.place_id';
 781                     if ($sOrderBySQL) {
 782                         $sSQL .= ','.$sOrderBySQL.' as order_term';
 783                     }
 784                     $sSQL .= ' from '.$sClassTable.' as l';
 785
 786                     if ($sPlaceIDs) {
 787                         $sSQL .= ",placex as f WHERE ";
 788                         $sSQL .= "f.place_id in ($sPlaceIDs) ";
 789                         $sSQL .= " AND ST_DWithin(l.centroid, f.centroid, $fRange)";
 790                     } elseif ($sPlaceGeom) {
 791                         $sSQL .= " WHERE ST_Contains('$sPlaceGeom', l.centroid)";
 792                     }
 793
 794                     if ($sExcludeSQL) {
 795                         $sSQL .= ' AND l.place_id not in ('.$sExcludeSQL.')';
 796                     }
 797                     $sSQL .= 'limit 300) i ';
 798                     if ($sOrderBySQL) {
 799                         $sSQL .= 'order by order_term asc';
 800                     }
 801                     $sSQL .= " limit $iLimit";
 802
 803                     if (CONST_Debug) var_dump($sSQL);
 804
 805                     $aClassPlaceIDs = array_merge($aClassPlaceIDs, chksql($oDB->getCol($sSQL)));
 806                 } else {
 807                     if ($this->oNearPoint) {
 808                         $fRange = $this->oNearPoint->radius();
 809                     }
 810
 811                     $sOrderBySQL = '';
 812                     if ($this->oNearPoint) {
 813                         $sOrderBySQL = $this->oNearPoint->distanceSQL('l.geometry');
 814                     } else {
 815                         $sOrderBySQL = "ST_Distance(l.geometry, f.geometry)";
 816                     }
 817
 818                     $sSQL = 'SELECT distinct l.place_id';
 819                     if ($sOrderBySQL) {
 820                         $sSQL .= ','.$sOrderBySQL.' as orderterm';
 821                     }
 822                     $sSQL .= ' FROM placex as l, placex as f';
 823                     $sSQL .= " WHERE f.place_id in ($sPlaceIDs)";
 824                     $sSQL .= "  AND ST_DWithin(l.geometry, f.centroid, $fRange)";
 825                     $sSQL .= "  AND l.class='".$this->sClass."'";
 826                     $sSQL .= "  AND l.type='".$this->sType."'";
 827                     if ($sExcludeSQL) {
 828                         $sSQL .= " AND l.place_id not in (".$sExcludeSQL.")";
 829                     }
 830                     if ($sOrderBySQL) {
 831                         $sSQL .= "ORDER BY orderterm ASC";
 832                     }
 833                     $sSQL .= " limit $iLimit";
 834
 835                     if (CONST_Debug) var_dump($sSQL);
 836
 837                     $aClassPlaceIDs = array_merge($aClassPlaceIDs, chksql($oDB->getCol($sSQL)));
 838                 }
 839             }
 840         }
 841
 842         return $aClassPlaceIDs;
 843     }
 844
 845
 846     /////////// Sort functions
 847
 848
 849     public static function bySearchRank($a, $b)
 850     {
 851         if ($a->iSearchRank == $b->iSearchRank) {
 852             return $a->iOperator + strlen($a->sHouseNumber)
 853                      - $b->iOperator - strlen($b->sHouseNumber);
 854         }
 855
 856         return $a->iSearchRank < $b->iSearchRank ? -1 : 1;
 857     }
 858
 859     //////////// Debugging functions
 860
 861
 862     public function dumpAsHtmlTableRow(&$aWordIDs)
 863     {
 864         $kf = function ($k) use (&$aWordIDs) {
 865             return $aWordIDs[$k];
 866         };
 867
 868         echo "<tr>";
 869         echo "<td>$this->iSearchRank</td>";
 870         echo "<td>".join(', ', array_map($kf, $this->aName))."</td>";
 871         echo "<td>".join(', ', array_map($kf, $this->aNameNonSearch))."</td>";
 872         echo "<td>".join(', ', array_map($kf, $this->aAddress))."</td>";
 873         echo "<td>".join(', ', array_map($kf, $this->aAddressNonSearch))."</td>";
 874         echo "<td>".$this->sCountryCode."</td>";
 875         echo "<td>".Operator::toString($this->iOperator)."</td>";
 876         echo "<td>".$this->sClass."</td>";
 877         echo "<td>".$this->sType."</td>";
 878         echo "<td>".$this->sPostcode."</td>";
 879         echo "<td>".$this->sHouseNumber."</td>";
 880
 881         if ($this->oNearPoint) {
 882             echo "<td>".$this->oNearPoint->lat()."</td>";
 883             echo "<td>".$this->oNearPoint->lon()."</td>";
 884             echo "<td>".$this->oNearPoint->radius()."</td>";
 885         } else {
 886             echo "<td></td><td></td><td></td>";
 887         }
 888
 889         echo "</tr>";
 890     }
 891 }