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