lib/SearchDescription.php

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