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                 // If we have structured search or this is the first term,
 206                 // make the postcode the primary search element.
 207                 if ($this->iOperator == Operator::NONE
 208                     && ($sPhraseType == 'postalcode' || $bFirstToken)
 209                 ) {
 210                     $oSearch = clone $this;
 211                     $oSearch->iSearchRank++;
 212                     $oSearch->iOperator = Operator::POSTCODE;
 213                     $oSearch->aAddress = array_merge($this->aAddress, $this->aName);
 214                     $oSearch->aName =
 215                         array($aSearchTerm['word_id'] => $aSearchTerm['word']);
 216                     $aNewSearches[] = $oSearch;
 217                 }
 218
 219                 // If we have a structured search or this is not the first term,
 220                 // add the postcode as an addendum.
 221                 if ($this->iOperator != Operator::POSTCODE
 222                     && ($sPhraseType == 'postalcode' || sizeof($this->aName))
 223                 ) {
 224                     $oSearch = clone $this;
 225                     $oSearch->iSearchRank++;
 226                     $oSearch->sPostcode = $aSearchTerm['word'];
 227                     $aNewSearches[] = $oSearch;
 228                 }
 229             }
 230         } elseif (($sPhraseType == '' || $sPhraseType == 'street')
 231                  && $aSearchTerm['class'] == 'place' && $aSearchTerm['type'] == 'house'
 232         ) {
 233             if (!$this->sHouseNumber && $this->iOperator != Operator::POSTCODE) {
 234                 $oSearch = clone $this;
 235                 $oSearch->iSearchRank++;
 236                 $oSearch->sHouseNumber = trim($aSearchTerm['word_token']);
 237                 // sanity check: if the housenumber is not mainly made
 238                 // up of numbers, add a penalty
 239                 if (preg_match_all("/[^0-9]/", $oSearch->sHouseNumber, $aMatches) > 2) {
 240                     $oSearch->iSearchRank++;
 241                 }
 242                 // also must not appear in the middle of the address
 243                 if (sizeof($this->aAddress) || sizeof($this->aAddressNonSearch)) {
 244                     $oSearch->iSearchRank++;
 245                 }
 246                 $aNewSearches[] = $oSearch;
 247             }
 248         } elseif ($sPhraseType == ''
 249                   && $aSearchTerm['class'] !== '' && $aSearchTerm['class'] !== null
 250         ) {
 251             // require a normalized exact match of the term
 252             // if we have the normalizer version of the query
 253             // available
 254             if ($this->iOperator == Operator::NONE
 255                 && (isset($aSearchTerm['word']) && $aSearchTerm['word'])
 256                 && $bWordInQuery
 257             ) {
 258                 $oSearch = clone $this;
 259                 $oSearch->iSearchRank++;
 260
 261                 $iOp = Operator::NEAR; // near == in for the moment
 262                 if ($aSearchTerm['operator'] == '') {
 263                     if (sizeof($this->aName)) {
 264                         $iOp = Operator::NAME;
 265                     }
 266                     $oSearch->iSearchRank += 2;
 267                 }
 268
 269                 $oSearch->setPoiSearch($iOp, $aSearchTerm['class'], $aSearchTerm['type']);
 270                 $aNewSearches[] = $oSearch;
 271             }
 272         } elseif (isset($aSearchTerm['word_id']) && $aSearchTerm['word_id']) {
 273             $iWordID = $aSearchTerm['word_id'];
 274             if (sizeof($this->aName)) {
 275                 if (($sPhraseType == '' || !$bFirstPhrase)
 276                     && $sPhraseType != 'country'
 277                     && !$bHasPartial
 278                 ) {
 279                     $oSearch = clone $this;
 280                     $oSearch->iSearchRank++;
 281                     $oSearch->aAddress[$iWordID] = $iWordID;
 282                     $aNewSearches[] = $oSearch;
 283                 } else {
 284                     $this->aFullNameAddress[$iWordID] = $iWordID;
 285                 }
 286             } else {
 287                 $oSearch = clone $this;
 288                 $oSearch->iSearchRank++;
 289                 $oSearch->aName = array($iWordID => $iWordID);
 290                 $aNewSearches[] = $oSearch;
 291             }
 292         }
 293
 294         return $aNewSearches;
 295     }
 296
 297     public function extendWithPartialTerm($aSearchTerm, $bStructuredPhrases, $iPhrase, &$aWordFrequencyScores, $aFullTokens)
 298     {
 299         // Only allow name terms.
 300         if (!(isset($aSearchTerm['word_id']) && $aSearchTerm['word_id'])) {
 301             return array();
 302         }
 303
 304         $aNewSearches = array();
 305         $iWordID = $aSearchTerm['word_id'];
 306
 307         if ((!$bStructuredPhrases || $iPhrase > 0)
 308             && sizeof($this->aName)
 309             && strpos($aSearchTerm['word_token'], ' ') === false
 310         ) {
 311             if ($aWordFrequencyScores[$iWordID] < CONST_Max_Word_Frequency) {
 312                 $oSearch = clone $this;
 313                 $oSearch->iSearchRank++;
 314                 $oSearch->aAddress[$iWordID] = $iWordID;
 315                 $aNewSearches[] = $oSearch;
 316             } else {
 317                 $oSearch = clone $this;
 318                 $oSearch->iSearchRank++;
 319                 $oSearch->aAddressNonSearch[$iWordID] = $iWordID;
 320                 if (preg_match('#^[0-9]+$#', $aSearchTerm['word_token'])) {
 321                     $oSearch->iSearchRank += 2;
 322                 }
 323                 if (sizeof($aFullTokens)) {
 324                     $oSearch->iSearchRank++;
 325                 }
 326                 $aNewSearches[] = $oSearch;
 327
 328                 // revert to the token version?
 329                 foreach ($aFullTokens as $aSearchTermToken) {
 330                     if (empty($aSearchTermToken['country_code'])
 331                         && empty($aSearchTermToken['lat'])
 332                         && empty($aSearchTermToken['class'])
 333                     ) {
 334                         $oSearch = clone $this;
 335                         $oSearch->iSearchRank++;
 336                         $oSearch->aAddress[$aSearchTermToken['word_id']] = $aSearchTermToken['word_id'];
 337                         $aNewSearches[] = $oSearch;
 338                     }
 339                 }
 340             }
 341         }
 342
 343         if ((!$this->sPostcode && !$this->aAddress && !$this->aAddressNonSearch)
 344             && (!sizeof($this->aName) || $this->iNamePhrase == $iPhrase)
 345         ) {
 346             $oSearch = clone $this;
 347             $oSearch->iSearchRank++;
 348             if (!sizeof($this->aName)) {
 349                 $oSearch->iSearchRank += 1;
 350             }
 351             if (preg_match('#^[0-9]+$#', $aSearchTerm['word_token'])) {
 352                 $oSearch->iSearchRank += 2;
 353             }
 354             if ($aWordFrequencyScores[$iWordID] < CONST_Max_Word_Frequency) {
 355                 $oSearch->aName[$iWordID] = $iWordID;
 356             } else {
 357                 $oSearch->aNameNonSearch[$iWordID] = $iWordID;
 358             }
 359             $oSearch->iNamePhrase = $iPhrase;
 360             $aNewSearches[] = $oSearch;
 361         }
 362
 363         return $aNewSearches;
 364     }
 365
 366     /////////// Query functions
 367
 368
 369     public function queryCountry(&$oDB, $sViewboxSQL)
 370     {
 371         $sSQL = 'SELECT place_id FROM placex ';
 372         $sSQL .= "WHERE country_code='".$this->sCountryCode."'";
 373         $sSQL .= ' AND rank_search = 4';
 374         if ($sViewboxSQL) {
 375             $sSQL .= " AND ST_Intersects($sViewboxSQL, geometry)";
 376         }
 377         $sSQL .= " ORDER BY st_area(geometry) DESC LIMIT 1";
 378
 379         if (CONST_Debug) var_dump($sSQL);
 380
 381         return chksql($oDB->getCol($sSQL));
 382     }
 383
 384     public function queryNearbyPoi(&$oDB, $sCountryList, $sViewboxSQL, $sViewboxCentreSQL, $sExcludeSQL, $iLimit)
 385     {
 386         if (!$this->sClass) {
 387             return array();
 388         }
 389
 390         $sPoiTable = $this->poiTable();
 391
 392         $sSQL = 'SELECT count(*) FROM pg_tables WHERE tablename = \''.$sPoiTable."'";
 393         if (chksql($oDB->getOne($sSQL))) {
 394             $sSQL = 'SELECT place_id FROM '.$sPoiTable.' ct';
 395             if ($sCountryList) {
 396                 $sSQL .= ' JOIN placex USING (place_id)';
 397             }
 398             if ($this->oNearPoint) {
 399                 $sSQL .= ' WHERE '.$this->oNearPoint->withinSQL('ct.centroid');
 400             } else {
 401                 $sSQL .= " WHERE ST_Contains($sViewboxSQL, ct.centroid)";
 402             }
 403             if ($sCountryList) {
 404                 $sSQL .= " AND country_code in ($sCountryList)";
 405             }
 406             if ($sExcludeSQL) {
 407                 $sSQL .= ' AND place_id not in ('.$sExcludeSQL.')';
 408             }
 409             if ($sViewboxCentreSQL) {
 410                 $sSQL .= " ORDER BY ST_Distance($sViewboxCentreSQL, ct.centroid) ASC";
 411             } elseif ($this->oNearPoint) {
 412                 $sSQL .= ' ORDER BY '.$this->oNearPoint->distanceSQL('ct.centroid').' ASC';
 413             }
 414             $sSQL .= " limit $iLimit";
 415             if (CONST_Debug) var_dump($sSQL);
 416             return chksql($oDB->getCol($sSQL));
 417         }
 418
 419         if ($this->oNearPoint) {
 420             $sSQL = 'SELECT place_id FROM placex WHERE ';
 421             $sSQL .= 'class=\''.$this->sClass."' and type='".$this->sType."'";
 422             $sSQL .= ' AND '.$this->oNearPoint->withinSQL('geometry');
 423             $sSQL .= ' AND linked_place_id is null';
 424             if ($sCountryList) {
 425                 $sSQL .= " AND country_code in ($sCountryList)";
 426             }
 427             $sSQL .= ' ORDER BY '.$this->oNearPoint->distanceSQL('centroid')." ASC";
 428             $sSQL .= " LIMIT $iLimit";
 429             if (CONST_Debug) var_dump($sSQL);
 430             return chksql($oDB->getCol($sSQL));
 431         }
 432
 433         return array();
 434     }
 435
 436     public function queryPostcode(&$oDB, $sCountryList, $iLimit)
 437     {
 438         $sSQL = 'SELECT p.place_id FROM location_postcode p ';
 439
 440         if (sizeof($this->aAddress)) {
 441             $sSQL .= ', search_name s ';
 442             $sSQL .= 'WHERE s.place_id = p.parent_place_id ';
 443             $sSQL .= 'AND array_cat(s.nameaddress_vector, s.name_vector)';
 444             $sSQL .= '      @> '.getArraySQL($this->aAddress).' AND ';
 445         } else {
 446             $sSQL .= 'WHERE ';
 447         }
 448
 449         $sSQL .= "p.postcode = '".pg_escape_string(reset($this->aName))."'";
 450         $sCountryTerm = $this->countryCodeSQL('p.country_code', $sCountryList);
 451         if ($sCountryTerm) {
 452             $sSQL .= ' AND '.$sCountryTerm;
 453         }
 454         $sSQL .= " LIMIT $iLimit";
 455
 456         if (CONST_Debug) var_dump($sSQL);
 457
 458         return chksql($oDB->getCol($sSQL));
 459     }
 460
 461     public function queryNamedPlace(&$oDB, $aWordFrequencyScores, $sCountryList, $iMinAddressRank, $iMaxAddressRank, $sExcludeSQL, $sViewboxSmall, $sViewboxLarge, $iLimit)
 462     {
 463         $aTerms = array();
 464         $aOrder = array();
 465
 466         if ($this->sHouseNumber && sizeof($this->aAddress)) {
 467             $sHouseNumberRegex = '\\\\m'.$this->sHouseNumber.'\\\\M';
 468             $aOrder[] = ' (';
 469             $aOrder[0] .= 'EXISTS(';
 470             $aOrder[0] .= '  SELECT place_id';
 471             $aOrder[0] .= '  FROM placex';
 472             $aOrder[0] .= '  WHERE parent_place_id = search_name.place_id';
 473             $aOrder[0] .= "    AND transliteration(housenumber) ~* E'".$sHouseNumberRegex."'";
 474             $aOrder[0] .= '  LIMIT 1';
 475             $aOrder[0] .= ') ';
 476             // also housenumbers from interpolation lines table are needed
 477             if (preg_match('/[0-9]+/', $this->sHouseNumber)) {
 478                 $iHouseNumber = intval($this->sHouseNumber);
 479                 $aOrder[0] .= 'OR EXISTS(';
 480                 $aOrder[0] .= '  SELECT place_id ';
 481                 $aOrder[0] .= '  FROM location_property_osmline ';
 482                 $aOrder[0] .= '  WHERE parent_place_id = search_name.place_id';
 483                 $aOrder[0] .= '    AND startnumber is not NULL';
 484                 $aOrder[0] .= '    AND '.$iHouseNumber.'>=startnumber ';
 485                 $aOrder[0] .= '    AND '.$iHouseNumber.'<=endnumber ';
 486                 $aOrder[0] .= '  LIMIT 1';
 487                 $aOrder[0] .= ')';
 488             }
 489             $aOrder[0] .= ') DESC';
 490         }
 491
 492         if (sizeof($this->aName)) {
 493             $aTerms[] = 'name_vector @> '.getArraySQL($this->aName);
 494         }
 495         if (sizeof($this->aAddress)) {
 496             // For infrequent name terms disable index usage for address
 497             if (CONST_Search_NameOnlySearchFrequencyThreshold
 498                 && sizeof($this->aName) == 1
 499                 && $aWordFrequencyScores[$this->aName[reset($this->aName)]]
 500                      < CONST_Search_NameOnlySearchFrequencyThreshold
 501             ) {
 502                 $aTerms[] = 'array_cat(nameaddress_vector,ARRAY[]::integer[]) @> '.getArraySQL($this->aAddress);
 503             } else {
 504                 $aTerms[] = 'nameaddress_vector @> '.getArraySQL($this->aAddress);
 505             }
 506         }
 507
 508         $sCountryTerm = $this->countryCodeSQL('country_code', $sCountryList);
 509         if ($sCountryTerm) {
 510             $aTerms[] = $sCountryTerm;
 511         }
 512
 513         if ($this->sHouseNumber) {
 514             $aTerms[] = "address_rank between 16 and 27";
 515         } elseif (!$this->sClass || $this->iOperator == Operator::NAME) {
 516             if ($iMinAddressRank > 0) {
 517                 $aTerms[] = "address_rank >= ".$iMinAddressRank;
 518             }
 519             if ($iMaxAddressRank < 30) {
 520                 $aTerms[] = "address_rank <= ".$iMaxAddressRank;
 521             }
 522         }
 523
 524         if ($this->oNearPoint) {
 525             $aTerms[] = $this->oNearPoint->withinSQL('centroid');
 526             $aOrder[] = $this->oNearPoint->distanceSQL('centroid');
 527         } elseif ($this->sPostcode) {
 528             if (!sizeof($this->aAddress)) {
 529                 $aTerms[] = "EXISTS(SELECT place_id FROM location_postcode p WHERE p.postcode = '".$this->sPostcode."' AND ST_DWithin(search_name.centroid, p.geometry, 0.1))";
 530             } else {
 531                 $aOrder[] = "(SELECT min(ST_Distance(search_name.centroid, p.geometry)) FROM location_postcode p WHERE p.postcode = '".$this->sPostcode."')";
 532             }
 533         }
 534
 535         if ($sExcludeSQL) {
 536             $aTerms[] = 'place_id not in ('.$sExcludeSQL.')';
 537         }
 538
 539         if ($sViewboxSmall) {
 540             $aTerms[] = 'centroid && '.$sViewboxSmall;
 541         }
 542
 543         if ($this->oNearPoint) {
 544             $aOrder[] = $this->oNearPoint->distanceSQL('centroid');
 545         }
 546
 547         if ($this->sHouseNumber) {
 548             $sImportanceSQL = '- abs(26 - address_rank) + 3';
 549         } else {
 550             $sImportanceSQL = '(CASE WHEN importance = 0 OR importance IS NULL THEN 0.75-(search_rank::float/40) ELSE importance END)';
 551         }
 552         if ($sViewboxSmall) {
 553             $sImportanceSQL .= " * CASE WHEN ST_Contains($sViewboxSmall, centroid) THEN 1 ELSE 0.5 END";
 554         }
 555         if ($sViewboxLarge) {
 556             $sImportanceSQL .= " * CASE WHEN ST_Contains($sViewboxLarge, centroid) THEN 1 ELSE 0.5 END";
 557         }
 558         $aOrder[] = "$sImportanceSQL DESC";
 559
 560         if (sizeof($this->aFullNameAddress)) {
 561             $sExactMatchSQL = ' ( ';
 562             $sExactMatchSQL .= ' SELECT count(*) FROM ( ';
 563             $sExactMatchSQL .= '  SELECT unnest('.getArraySQL($this->aFullNameAddress).')';
 564             $sExactMatchSQL .= '    INTERSECT ';
 565             $sExactMatchSQL .= '  SELECT unnest(nameaddress_vector)';
 566             $sExactMatchSQL .= ' ) s';
 567             $sExactMatchSQL .= ') as exactmatch';
 568             $aOrder[] = 'exactmatch DESC';
 569         } else {
 570             $sExactMatchSQL = '0::int as exactmatch';
 571         }
 572
 573         if ($this->sHouseNumber || $this->sClass) {
 574             $iLimit = 20;
 575         }
 576
 577         if (sizeof($aTerms)) {
 578             $sSQL = 'SELECT place_id,'.$sExactMatchSQL;
 579             $sSQL .= ' FROM search_name';
 580             $sSQL .= ' WHERE '.join(' and ', $aTerms);
 581             $sSQL .= ' ORDER BY '.join(', ', $aOrder);
 582             $sSQL .= ' LIMIT '.$iLimit;
 583
 584             if (CONST_Debug) var_dump($sSQL);
 585
 586             return chksql(
 587                 $oDB->getAll($sSQL),
 588                 "Could not get places for search terms."
 589             );
 590         }
 591
 592         return array();
 593     }
 594
 595
 596     public function queryHouseNumber(&$oDB, $aRoadPlaceIDs, $sExcludeSQL, $iLimit)
 597     {
 598         $sPlaceIDs = join(',', $aRoadPlaceIDs);
 599
 600         $sHouseNumberRegex = '\\\\m'.$this->sHouseNumber.'\\\\M';
 601         $sSQL = 'SELECT place_id FROM placex ';
 602         $sSQL .= 'WHERE parent_place_id in ('.$sPlaceIDs.')';
 603         $sSQL .= "  AND transliteration(housenumber) ~* E'".$sHouseNumberRegex."'";
 604         if ($sExcludeSQL) {
 605             $sSQL .= ' AND place_id not in ('.$sExcludeSQL.')';
 606         }
 607         $sSQL .= " LIMIT $iLimit";
 608
 609         if (CONST_Debug) var_dump($sSQL);
 610
 611         $aPlaceIDs = chksql($oDB->getCol($sSQL));
 612
 613         if (sizeof($aPlaceIDs)) {
 614             return array('aPlaceIDs' => $aPlaceIDs, 'iHouseNumber' => -1);
 615         }
 616
 617         $bIsIntHouseNumber= (bool) preg_match('/[0-9]+/', $this->sHouseNumber);
 618         $iHousenumber = intval($this->sHouseNumber);
 619         if ($bIsIntHouseNumber) {
 620             // if nothing found, search in the interpolation line table
 621             $sSQL = 'SELECT distinct place_id FROM location_property_osmline';
 622             $sSQL .= ' WHERE startnumber is not NULL';
 623             $sSQL .= '  AND parent_place_id in ('.$sPlaceIDs.') AND (';
 624             if ($iHousenumber % 2 == 0) {
 625                 // If housenumber is even, look for housenumber in streets
 626                 // with interpolationtype even or all.
 627                 $sSQL .= "interpolationtype='even'";
 628             } else {
 629                 // Else look for housenumber with interpolationtype odd or all.
 630                 $sSQL .= "interpolationtype='odd'";
 631             }
 632             $sSQL .= " or interpolationtype='all') and ";
 633             $sSQL .= $iHousenumber.">=startnumber and ";
 634             $sSQL .= $iHousenumber."<=endnumber";
 635
 636             if ($sExcludeSQL) {
 637                 $sSQL .= ' AND place_id not in ('.$sExcludeSQL.')';
 638             }
 639             $sSQL .= " limit $iLimit";
 640
 641             if (CONST_Debug) var_dump($sSQL);
 642
 643             $aPlaceIDs = chksql($oDB->getCol($sSQL, 0));
 644
 645             if (sizeof($aPlaceIDs)) {
 646                 return array('aPlaceIDs' => $aPlaceIDs, 'iHouseNumber' => $iHousenumber);
 647             }
 648         }
 649
 650         // If nothing found try the aux fallback table
 651         if (CONST_Use_Aux_Location_data) {
 652             $sSQL = 'SELECT place_id FROM location_property_aux';
 653             $sSQL .= ' WHERE parent_place_id in ('.$sPlaceIDs.')';
 654             $sSQL .= " AND housenumber = '".$this->sHouseNumber."'";
 655             if ($sExcludeSQL) {
 656                 $sSQL .= " AND place_id not in ($sExcludeSQL)";
 657             }
 658             $sSQL .= " limit $iLimit";
 659
 660             if (CONST_Debug) var_dump($sSQL);
 661
 662             $aPlaceIDs = chksql($oDB->getCol($sSQL));
 663
 664             if (sizeof($aPlaceIDs)) {
 665                 return array('aPlaceIDs' => $aPlaceIDs, 'iHouseNumber' => -1);
 666             }
 667         }
 668
 669         // If nothing found then search in Tiger data (location_property_tiger)
 670         if (CONST_Use_US_Tiger_Data && $bIsIntHouseNumber) {
 671             $sSQL = 'SELECT distinct place_id FROM location_property_tiger';
 672             $sSQL .= ' WHERE parent_place_id in ('.$sPlaceIDs.') and (';
 673             if ($iHousenumber % 2 == 0) {
 674                 $sSQL .= "interpolationtype='even'";
 675             } else {
 676                 $sSQL .= "interpolationtype='odd'";
 677             }
 678             $sSQL .= " or interpolationtype='all') and ";
 679             $sSQL .= $iHousenumber.">=startnumber and ";
 680             $sSQL .= $iHousenumber."<=endnumber";
 681
 682             if ($sExcludeSQL) {
 683                 $sSQL .= ' AND place_id not in ('.$sExcludeSQL.')';
 684             }
 685             $sSQL .= " limit $iLimit";
 686
 687             if (CONST_Debug) var_dump($sSQL);
 688
 689             $aPlaceIDs = chksql($oDB->getCol($sSQL, 0));
 690
 691             if (sizeof($aPlaceIDs)) {
 692                 return array('aPlaceIDs' => $aPlaceIDs, 'iHouseNumber' => $iHousenumber);
 693             }
 694         }
 695
 696         return array();
 697     }
 698
 699
 700     public function queryPoiByOperator(&$oDB, $aParentIDs, $sExcludeSQL, $iLimit)
 701     {
 702         $sPlaceIDs = join(',', $aParentIDs);
 703         $aClassPlaceIDs = array();
 704
 705         if ($this->iOperator == Operator::TYPE || $this->iOperator == Operator::NAME) {
 706             // If they were searching for a named class (i.e. 'Kings Head pub')
 707             // then we might have an extra match
 708             $sSQL = 'SELECT place_id FROM placex ';
 709             $sSQL .= " WHERE place_id in ($sPlaceIDs)";
 710             $sSQL .= "   AND class='".$this->sClass."' ";
 711             $sSQL .= "   AND type='".$this->sType."'";
 712             $sSQL .= "   AND linked_place_id is null";
 713             $sSQL .= " ORDER BY rank_search ASC ";
 714             $sSQL .= " LIMIT $iLimit";
 715
 716             if (CONST_Debug) var_dump($sSQL);
 717
 718             $aClassPlaceIDs = chksql($oDB->getCol($sSQL));
 719         }
 720
 721         // NEAR and IN are handled the same
 722         if ($this->iOperator == Operator::TYPE || $this->iOperator == Operator::NEAR) {
 723             $sClassTable = $this->poiTable();
 724             $sSQL = "SELECT count(*) FROM pg_tables WHERE tablename = '$sClassTable'";
 725             $bCacheTable = (bool) chksql($oDB->getOne($sSQL));
 726
 727             $sSQL = "SELECT min(rank_search) FROM placex WHERE place_id in ($sPlaceIDs)";
 728             if (CONST_Debug) var_dump($sSQL);
 729             $iMaxRank = (int)chksql($oDB->getOne($sSQL));
 730
 731             // For state / country level searches the normal radius search doesn't work very well
 732             $sPlaceGeom = false;
 733             if ($iMaxRank < 9 && $bCacheTable) {
 734                 // Try and get a polygon to search in instead
 735                 $sSQL = 'SELECT geometry FROM placex';
 736                 $sSQL .= " WHERE place_id in ($sPlaceIDs)";
 737                 $sSQL .= "   AND rank_search < $iMaxRank + 5";
 738                 $sSQL .= "   AND ST_GeometryType(geometry) in ('ST_Polygon','ST_MultiPolygon')";
 739                 $sSQL .= " ORDER BY rank_search ASC ";
 740                 $sSQL .= " LIMIT 1";
 741                 if (CONST_Debug) var_dump($sSQL);
 742                 $sPlaceGeom = chksql($oDB->getOne($sSQL));
 743             }
 744
 745             if ($sPlaceGeom) {
 746                 $sPlaceIDs = false;
 747             } else {
 748                 $iMaxRank += 5;
 749                 $sSQL = 'SELECT place_id FROM placex';
 750                 $sSQL .= " WHERE place_id in ($sPlaceIDs) and rank_search < $iMaxRank";
 751                 if (CONST_Debug) var_dump($sSQL);
 752                 $aPlaceIDs = chksql($oDB->getCol($sSQL));
 753                 $sPlaceIDs = join(',', $aPlaceIDs);
 754             }
 755
 756             if ($sPlaceIDs || $sPlaceGeom) {
 757                 $fRange = 0.01;
 758                 if ($bCacheTable) {
 759                     // More efficient - can make the range bigger
 760                     $fRange = 0.05;
 761
 762                     $sOrderBySQL = '';
 763                     if ($this->oNearPoint) {
 764                         $sOrderBySQL = $this->oNearPoint->distanceSQL('l.centroid');
 765                     } elseif ($sPlaceIDs) {
 766                         $sOrderBySQL = "ST_Distance(l.centroid, f.geometry)";
 767                     } elseif ($sPlaceGeom) {
 768                         $sOrderBySQL = "ST_Distance(st_centroid('".$sPlaceGeom."'), l.centroid)";
 769                     }
 770
 771                     $sSQL = 'SELECT distinct i.place_id';
 772                     if ($sOrderBySQL) {
 773                         $sSQL .= ', i.order_term';
 774                     }
 775                     $sSQL .= ' from (SELECT l.place_id';
 776                     if ($sOrderBySQL) {
 777                         $sSQL .= ','.$sOrderBySQL.' as order_term';
 778                     }
 779                     $sSQL .= ' from '.$sClassTable.' as l';
 780
 781                     if ($sPlaceIDs) {
 782                         $sSQL .= ",placex as f WHERE ";
 783                         $sSQL .= "f.place_id in ($sPlaceIDs) ";
 784                         $sSQL .= " AND ST_DWithin(l.centroid, f.centroid, $fRange)";
 785                     } elseif ($sPlaceGeom) {
 786                         $sSQL .= " WHERE ST_Contains('$sPlaceGeom', l.centroid)";
 787                     }
 788
 789                     if ($sExcludeSQL) {
 790                         $sSQL .= ' AND l.place_id not in ('.$sExcludeSQL.')';
 791                     }
 792                     $sSQL .= 'limit 300) i ';
 793                     if ($sOrderBySQL) {
 794                         $sSQL .= 'order by order_term asc';
 795                     }
 796                     $sSQL .= " limit $iLimit";
 797
 798                     if (CONST_Debug) var_dump($sSQL);
 799
 800                     $aClassPlaceIDs = array_merge($aClassPlaceIDs, chksql($oDB->getCol($sSQL)));
 801                 } else {
 802                     if ($this->oNearPoint) {
 803                         $fRange = $this->oNearPoint->radius();
 804                     }
 805
 806                     $sOrderBySQL = '';
 807                     if ($this->oNearPoint) {
 808                         $sOrderBySQL = $this->oNearPoint->distanceSQL('l.geometry');
 809                     } else {
 810                         $sOrderBySQL = "ST_Distance(l.geometry, f.geometry)";
 811                     }
 812
 813                     $sSQL = 'SELECT distinct l.place_id';
 814                     if ($sOrderBySQL) {
 815                         $sSQL .= ','.$sOrderBySQL.' as orderterm';
 816                     }
 817                     $sSQL .= ' FROM placex as l, placex as f';
 818                     $sSQL .= " WHERE f.place_id in ($sPlaceIDs)";
 819                     $sSQL .= "  AND ST_DWithin(l.geometry, f.centroid, $fRange)";
 820                     $sSQL .= "  AND l.class='".$this->sClass."'";
 821                     $sSQL .= "  AND l.type='".$this->sType."'";
 822                     if ($sExcludeSQL) {
 823                         $sSQL .= " AND l.place_id not in (".$sExcludeSQL.")";
 824                     }
 825                     if ($sOrderBySQL) {
 826                         $sSQL .= "ORDER BY orderterm ASC";
 827                     }
 828                     $sSQL .= " limit $iLimit";
 829
 830                     if (CONST_Debug) var_dump($sSQL);
 831
 832                     $aClassPlaceIDs = array_merge($aClassPlaceIDs, chksql($oDB->getCol($sSQL)));
 833                 }
 834             }
 835         }
 836
 837         return $aClassPlaceIDs;
 838     }
 839
 840
 841     /////////// Sort functions
 842
 843
 844     public static function bySearchRank($a, $b)
 845     {
 846         if ($a->iSearchRank == $b->iSearchRank) {
 847             return $a->iOperator + strlen($a->sHouseNumber)
 848                      - $b->iOperator - strlen($b->sHouseNumber);
 849         }
 850
 851         return $a->iSearchRank < $b->iSearchRank ? -1 : 1;
 852     }
 853
 854     //////////// Debugging functions
 855
 856
 857     public function dumpAsHtmlTableRow(&$aWordIDs)
 858     {
 859         $kf = function ($k) use (&$aWordIDs) {
 860             return $aWordIDs[$k];
 861         };
 862
 863         echo "<tr>";
 864         echo "<td>$this->iSearchRank</td>";
 865         echo "<td>".join(', ', array_map($kf, $this->aName))."</td>";
 866         echo "<td>".join(', ', array_map($kf, $this->aNameNonSearch))."</td>";
 867         echo "<td>".join(', ', array_map($kf, $this->aAddress))."</td>";
 868         echo "<td>".join(', ', array_map($kf, $this->aAddressNonSearch))."</td>";
 869         echo "<td>".$this->sCountryCode."</td>";
 870         echo "<td>".Operator::toString($this->iOperator)."</td>";
 871         echo "<td>".$this->sClass."</td>";
 872         echo "<td>".$this->sType."</td>";
 873         echo "<td>".$this->sPostcode."</td>";
 874         echo "<td>".$this->sHouseNumber."</td>";
 875
 876         if ($this->oNearPoint) {
 877             echo "<td>".$this->oNearPoint->lat()."</td>";
 878             echo "<td>".$this->oNearPoint->lon()."</td>";
 879             echo "<td>".$this->oNearPoint->radius()."</td>";
 880         } else {
 881             echo "<td></td><td></td><td></td>";
 882         }
 883
 884         echo "</tr>";
 885     }
 886 }