3 * SPDX-License-Identifier: GPL-2.0-only
5 * This file is part of Nominatim. (https://nominatim.org)
7 * Copyright (C) 2022 by the Nominatim developer community.
8 * For a full list of authors see the git log.
13 require_once(CONST_LibDir.'/AddressDetails.php');
14 require_once(CONST_LibDir.'/Result.php');
20 protected $aLangPrefOrderSql = "''";
22 protected $bAddressDetails = false;
23 protected $bExtraTags = false;
24 protected $bNameDetails = false;
26 protected $bIncludePolygonAsText = false;
27 protected $bIncludePolygonAsGeoJSON = false;
28 protected $bIncludePolygonAsKML = false;
29 protected $bIncludePolygonAsSVG = false;
30 protected $fPolygonSimplificationThreshold = 0.0;
32 protected $sAnchorSql = null;
33 protected $sAddressRankListSql = null;
34 protected $sAllowedTypesSQLList = null;
35 protected $bDeDupe = true;
38 public function __construct(&$oDB)
43 public function doDeDupe()
45 return $this->bDeDupe;
48 public function setIncludeAddressDetails($b)
50 $this->bAddressDetails = $b;
53 public function loadParamArray($oParams, $sGeomType = null)
55 $aLangs = $oParams->getPreferredLanguages();
56 $this->aLangPrefOrderSql =
57 'ARRAY['.join(',', $this->oDB->getDBQuotedList($aLangs)).']';
59 $this->bExtraTags = $oParams->getBool('extratags', false);
60 $this->bNameDetails = $oParams->getBool('namedetails', false);
62 $this->bDeDupe = $oParams->getBool('dedupe', $this->bDeDupe);
64 if ($sGeomType === null || $sGeomType == 'geojson') {
65 $this->bIncludePolygonAsGeoJSON = $oParams->getBool('polygon_geojson');
68 if ($oParams->getString('format', '') !== 'geojson') {
69 if ($sGeomType === null || $sGeomType == 'text') {
70 $this->bIncludePolygonAsText = $oParams->getBool('polygon_text');
72 if ($sGeomType === null || $sGeomType == 'kml') {
73 $this->bIncludePolygonAsKML = $oParams->getBool('polygon_kml');
75 if ($sGeomType === null || $sGeomType == 'svg') {
76 $this->bIncludePolygonAsSVG = $oParams->getBool('polygon_svg');
79 $this->fPolygonSimplificationThreshold
80 = $oParams->getFloat('polygon_threshold', 0.0);
83 ($this->bIncludePolygonAsText ? 1 : 0) +
84 ($this->bIncludePolygonAsGeoJSON ? 1 : 0) +
85 ($this->bIncludePolygonAsKML ? 1 : 0) +
86 ($this->bIncludePolygonAsSVG ? 1 : 0);
87 if ($iWantedTypes > CONST_PolygonOutput_MaximumTypes) {
88 if (CONST_PolygonOutput_MaximumTypes) {
89 userError('Select only '.CONST_PolygonOutput_MaximumTypes.' polgyon output option');
91 userError('Polygon output is disabled');
96 public function getMoreUrlParams()
100 if ($this->bAddressDetails) {
101 $aParams['addressdetails'] = '1';
103 if ($this->bExtraTags) {
104 $aParams['extratags'] = '1';
106 if ($this->bNameDetails) {
107 $aParams['namedetails'] = '1';
110 if ($this->bIncludePolygonAsText) {
111 $aParams['polygon_text'] = '1';
113 if ($this->bIncludePolygonAsGeoJSON) {
114 $aParams['polygon_geojson'] = '1';
116 if ($this->bIncludePolygonAsKML) {
117 $aParams['polygon_kml'] = '1';
119 if ($this->bIncludePolygonAsSVG) {
120 $aParams['polygon_svg'] = '1';
123 if ($this->fPolygonSimplificationThreshold > 0.0) {
124 $aParams['polygon_threshold'] = $this->fPolygonSimplificationThreshold;
127 if (!$this->bDeDupe) {
128 $aParams['dedupe'] = '0';
134 public function setAnchorSql($sPoint)
136 $this->sAnchorSql = $sPoint;
139 public function setAddressRankList($aList)
141 $this->sAddressRankListSql = '('.join(',', $aList).')';
144 public function setAllowedTypesSQLList($sSql)
146 $this->sAllowedTypesSQLList = $sSql;
149 public function setLanguagePreference($aLangPrefOrder)
151 $this->aLangPrefOrderSql = $this->oDB->getArraySQL(
152 $this->oDB->getDBQuotedList($aLangPrefOrder)
156 private function addressImportanceSql($sGeometry, $sPlaceId)
158 if ($this->sAnchorSql) {
159 $sSQL = 'ST_Distance('.$this->sAnchorSql.','.$sGeometry.')';
161 $sSQL = '(SELECT max(ai_p.importance * (ai_p.rank_address + 2))';
162 $sSQL .= ' FROM place_addressline ai_s, placex ai_p';
163 $sSQL .= ' WHERE ai_s.place_id = '.$sPlaceId;
164 $sSQL .= ' AND ai_p.place_id = ai_s.address_place_id ';
165 $sSQL .= ' AND ai_s.isaddress ';
166 $sSQL .= ' AND ai_p.importance is not null)';
169 return $sSQL.' AS addressimportance,';
172 private function langAddressSql($sHousenumber)
174 if ($this->bAddressDetails) {
175 return ''; // langaddress will be computed from address details
178 return 'get_address_by_language(place_id,'.$sHousenumber.','.$this->aLangPrefOrderSql.') AS langaddress,';
181 public function lookupOSMID($sType, $iID)
183 $sSQL = 'select place_id from placex where osm_type = :type and osm_id = :id';
184 $iPlaceID = $this->oDB->getOne($sSQL, array(':type' => $sType, ':id' => $iID));
190 $aResults = $this->lookup(array($iPlaceID => new Result($iPlaceID)));
192 return empty($aResults) ? null : reset($aResults);
195 public function lookup($aResults, $iMinRank = 0, $iMaxRank = 30)
197 Debug::newFunction('Place lookup');
199 if (empty($aResults)) {
202 $aSubSelects = array();
204 $sPlaceIDs = Result::joinIdsByTable($aResults, Result::TABLE_PLACEX);
206 Debug::printVar('Ids from placex', $sPlaceIDs);
208 $sSQL .= ' osm_type,';
212 $sSQL .= ' admin_level,';
213 $sSQL .= ' rank_search,';
214 $sSQL .= ' rank_address,';
215 $sSQL .= ' min(place_id) AS place_id,';
216 $sSQL .= ' min(parent_place_id) AS parent_place_id,';
217 $sSQL .= ' -1 as housenumber,';
218 $sSQL .= ' country_code,';
219 $sSQL .= $this->langAddressSql('-1');
220 $sSQL .= ' get_name_by_language(name,'.$this->aLangPrefOrderSql.') AS placename,';
221 $sSQL .= " get_name_by_language(name, ARRAY['ref']) AS ref,";
222 if ($this->bExtraTags) {
223 $sSQL .= 'hstore_to_json(extratags)::text AS extra,';
225 if ($this->bNameDetails) {
226 $sSQL .= 'hstore_to_json(name)::text AS names,';
228 $sSQL .= ' avg(ST_X(centroid)) AS lon, ';
229 $sSQL .= ' avg(ST_Y(centroid)) AS lat, ';
230 $sSQL .= ' COALESCE(importance,0.75-(rank_search::float/40)) AS importance, ';
231 $sSQL .= $this->addressImportanceSql(
232 'ST_Collect(centroid)',
233 'min(CASE WHEN placex.rank_search < 28 THEN placex.place_id ELSE placex.parent_place_id END)'
235 $sSQL .= " COALESCE(extratags->'place', extratags->'linked_place') AS extra_place ";
236 $sSQL .= ' FROM placex';
237 $sSQL .= " WHERE place_id in ($sPlaceIDs) ";
239 $sSQL .= " placex.rank_address between $iMinRank and $iMaxRank ";
240 if (14 >= $iMinRank && 14 <= $iMaxRank) {
241 $sSQL .= " OR (extratags->'place') = 'city'";
243 if ($this->sAddressRankListSql) {
244 $sSQL .= ' OR placex.rank_address in '.$this->sAddressRankListSql;
247 if ($this->sAllowedTypesSQLList) {
248 $sSQL .= 'AND placex.class in '.$this->sAllowedTypesSQLList;
250 $sSQL .= ' AND linked_place_id is null ';
251 $sSQL .= ' GROUP BY ';
252 $sSQL .= ' osm_type, ';
253 $sSQL .= ' osm_id, ';
256 $sSQL .= ' admin_level, ';
257 $sSQL .= ' rank_search, ';
258 $sSQL .= ' rank_address, ';
259 $sSQL .= ' housenumber,';
260 $sSQL .= ' country_code, ';
261 $sSQL .= ' importance, ';
262 if (!$this->bDeDupe) {
263 $sSQL .= 'place_id,';
265 if (!$this->bAddressDetails) {
266 $sSQL .= 'langaddress, ';
268 $sSQL .= ' placename, ';
270 if ($this->bExtraTags) {
271 $sSQL .= 'extratags, ';
273 if ($this->bNameDetails) {
276 $sSQL .= ' extra_place ';
278 $aSubSelects[] = $sSQL;
282 $sPlaceIDs = Result::joinIdsByTable($aResults, Result::TABLE_POSTCODE);
284 Debug::printVar('Ids from location_postcode', $sPlaceIDs);
286 $sSQL .= " 'P' as osm_type,";
287 $sSQL .= ' (SELECT osm_id from placex p WHERE p.place_id = lp.parent_place_id) as osm_id,';
288 $sSQL .= " 'place' as class, 'postcode' as type,";
289 $sSQL .= ' null::smallint as admin_level, rank_search, rank_address,';
290 $sSQL .= ' place_id, parent_place_id,';
291 $sSQL .= ' -1 as housenumber,';
292 $sSQL .= ' country_code,';
293 $sSQL .= $this->langAddressSql('-1');
294 $sSQL .= ' postcode as placename,';
295 $sSQL .= ' postcode as ref,';
296 if ($this->bExtraTags) {
297 $sSQL .= 'null::text AS extra,';
299 if ($this->bNameDetails) {
300 $sSQL .= 'null::text AS names,';
302 $sSQL .= ' ST_x(geometry) AS lon, ST_y(geometry) AS lat,';
303 $sSQL .= ' (0.75-(rank_search::float/40)) AS importance, ';
304 $sSQL .= $this->addressImportanceSql('geometry', 'lp.parent_place_id');
305 $sSQL .= ' null::text AS extra_place ';
306 $sSQL .= 'FROM location_postcode lp';
307 $sSQL .= " WHERE place_id in ($sPlaceIDs) ";
308 $sSQL .= " AND lp.rank_address between $iMinRank and $iMaxRank";
310 $aSubSelects[] = $sSQL;
313 // All other tables are rank 30 only.
314 if ($iMaxRank == 30) {
316 if (CONST_Use_US_Tiger_Data) {
317 $sPlaceIDs = Result::joinIdsByTable($aResults, Result::TABLE_TIGER);
319 Debug::printVar('Ids from Tiger table', $sPlaceIDs);
320 $sHousenumbers = Result::sqlHouseNumberTable($aResults, Result::TABLE_TIGER);
321 // Tiger search only if a housenumber was searched and if it was found
322 // (realized through a join)
324 $sSQL .= " 'T' AS osm_type, ";
325 $sSQL .= ' (SELECT osm_id from placex p WHERE p.place_id=blub.parent_place_id) as osm_id, ';
326 $sSQL .= " 'place' AS class, ";
327 $sSQL .= " 'house' AS type, ";
328 $sSQL .= ' null::smallint AS admin_level, ';
329 $sSQL .= ' 30 AS rank_search, ';
330 $sSQL .= ' 30 AS rank_address, ';
331 $sSQL .= ' place_id, ';
332 $sSQL .= ' parent_place_id, ';
333 $sSQL .= ' housenumber_for_place as housenumber,';
334 $sSQL .= " 'us' AS country_code, ";
335 $sSQL .= $this->langAddressSql('housenumber_for_place');
336 $sSQL .= ' null::text AS placename, ';
337 $sSQL .= ' null::text AS ref, ';
338 if ($this->bExtraTags) {
339 $sSQL .= 'null::text AS extra,';
341 if ($this->bNameDetails) {
342 $sSQL .= 'null::text AS names,';
344 $sSQL .= ' st_x(centroid) AS lon, ';
345 $sSQL .= ' st_y(centroid) AS lat,';
346 $sSQL .= ' -1.15 AS importance, ';
347 $sSQL .= $this->addressImportanceSql('centroid', 'blub.parent_place_id');
348 $sSQL .= ' null::text AS extra_place ';
350 $sSQL .= ' SELECT place_id, '; // interpolate the Tiger housenumbers here
351 $sSQL .= ' ST_LineInterpolatePoint(linegeo, (housenumber_for_place-startnumber::float)/(endnumber-startnumber)::float) AS centroid, ';
352 $sSQL .= ' parent_place_id, ';
353 $sSQL .= ' housenumber_for_place';
355 $sSQL .= ' location_property_tiger ';
356 $sSQL .= ' JOIN (values '.$sHousenumbers.') AS housenumbers(place_id, housenumber_for_place) USING(place_id)) ';
358 $sSQL .= ' housenumber_for_place >= startnumber';
359 $sSQL .= ' AND housenumber_for_place <= endnumber';
360 $sSQL .= ' ) AS blub'; //postgres wants an alias here
362 $aSubSelects[] = $sSQL;
366 // osmline - interpolated housenumbers
367 $sPlaceIDs = Result::joinIdsByTable($aResults, Result::TABLE_OSMLINE);
369 Debug::printVar('Ids from interpolation', $sPlaceIDs);
370 $sHousenumbers = Result::sqlHouseNumberTable($aResults, Result::TABLE_OSMLINE);
371 // interpolation line search only if a housenumber was searched
372 // (realized through a join)
374 $sSQL .= " 'W' AS osm_type, ";
375 $sSQL .= ' osm_id, ';
376 $sSQL .= " 'place' AS class, ";
377 $sSQL .= " 'house' AS type, ";
378 $sSQL .= ' null::smallint AS admin_level, ';
379 $sSQL .= ' 30 AS rank_search, ';
380 $sSQL .= ' 30 AS rank_address, ';
381 $sSQL .= ' place_id, ';
382 $sSQL .= ' parent_place_id, ';
383 $sSQL .= ' housenumber_for_place as housenumber,';
384 $sSQL .= ' country_code, ';
385 $sSQL .= $this->langAddressSql('housenumber_for_place');
386 $sSQL .= ' null::text AS placename, ';
387 $sSQL .= ' null::text AS ref, ';
388 if ($this->bExtraTags) {
389 $sSQL .= 'null::text AS extra, ';
391 if ($this->bNameDetails) {
392 $sSQL .= 'null::text AS names, ';
394 $sSQL .= ' st_x(centroid) AS lon, ';
395 $sSQL .= ' st_y(centroid) AS lat, ';
396 // slightly smaller than the importance for normal houses
397 $sSQL .= ' -0.1 AS importance, ';
398 $sSQL .= $this->addressImportanceSql('centroid', 'blub.parent_place_id');
399 $sSQL .= ' null::text AS extra_place ';
402 $sSQL .= ' osm_id, ';
403 $sSQL .= ' place_id, ';
404 $sSQL .= ' country_code, ';
405 $sSQL .= ' CASE '; // interpolate the housenumbers here
406 $sSQL .= ' WHEN startnumber != endnumber ';
407 $sSQL .= ' THEN ST_LineInterpolatePoint(linegeo, (housenumber_for_place-startnumber::float)/(endnumber-startnumber)::float) ';
408 $sSQL .= ' ELSE linegeo ';
409 $sSQL .= ' END as centroid, ';
410 $sSQL .= ' parent_place_id, ';
411 $sSQL .= ' housenumber_for_place ';
413 $sSQL .= ' location_property_osmline ';
414 $sSQL .= ' JOIN (values '.$sHousenumbers.') AS housenumbers(place_id, housenumber_for_place) USING(place_id)';
416 $sSQL .= ' WHERE housenumber_for_place >= 0 ';
417 $sSQL .= ' ) as blub'; //postgres wants an alias here
419 $aSubSelects[] = $sSQL;
423 if (empty($aSubSelects)) {
427 $sSQL = join(' UNION ', $aSubSelects);
428 Debug::printSQL($sSQL);
429 $aPlaces = $this->oDB->getAll($sSQL, null, 'Could not lookup place');
431 foreach ($aPlaces as &$aPlace) {
432 $aPlace['importance'] = (float) $aPlace['importance'];
433 if ($this->bAddressDetails) {
434 // to get addressdetails for tiger data, the housenumber is needed
435 $aPlace['address'] = new AddressDetails(
438 $aPlace['housenumber'],
439 $this->aLangPrefOrderSql
441 $aPlace['langaddress'] = $aPlace['address']->getLocaleAddress();
444 if ($this->bExtraTags) {
445 if ($aPlace['extra']) {
446 $aPlace['sExtraTags'] = json_decode($aPlace['extra']);
448 $aPlace['sExtraTags'] = (object) array();
452 if ($this->bNameDetails) {
453 if ($aPlace['names']) {
454 $aPlace['sNameDetails'] = json_decode($aPlace['names']);
456 $aPlace['sNameDetails'] = (object) array();
460 $aPlace['addresstype'] = ClassTypes\getLabelTag(
462 $aPlace['country_code']
465 $aResults[$aPlace['place_id']] = $aPlace;
468 $aResults = array_filter(
471 return !($v instanceof Result);
475 Debug::printVar('Places', $aResults);
480 /* returns an array which will contain the keys
482 * and may also contain one or more of the keys
492 public function getOutlines($iPlaceID, $fLon = null, $fLat = null, $fRadius = null, $fLonReverse = null, $fLatReverse = null)
495 $aOutlineResult = array();
497 return $aOutlineResult;
500 // Get the bounding box and outline polygon
501 $sSQL = 'select place_id,0 as numfeatures,st_area(geometry) as area,';
502 if ($fLonReverse != null && $fLatReverse != null) {
503 $sSQL .= ' ST_Y(closest_point) as centrelat,';
504 $sSQL .= ' ST_X(closest_point) as centrelon,';
506 $sSQL .= ' ST_Y(centroid) as centrelat, ST_X(centroid) as centrelon,';
508 $sSQL .= ' ST_YMin(geometry) as minlat,ST_YMax(geometry) as maxlat,';
509 $sSQL .= ' ST_XMin(geometry) as minlon,ST_XMax(geometry) as maxlon';
510 if ($this->bIncludePolygonAsGeoJSON) {
511 $sSQL .= ',ST_AsGeoJSON(geometry) as asgeojson';
513 if ($this->bIncludePolygonAsKML) {
514 $sSQL .= ',ST_AsKML(geometry) as askml';
516 if ($this->bIncludePolygonAsSVG) {
517 $sSQL .= ',ST_AsSVG(geometry) as assvg';
519 if ($this->bIncludePolygonAsText) {
520 $sSQL .= ',ST_AsText(geometry) as astext';
522 if ($fLonReverse != null && $fLatReverse != null) {
523 $sFrom = ' from (SELECT * , CASE WHEN (class = \'highway\') AND (ST_GeometryType(geometry) = \'ST_LineString\') THEN ';
524 $sFrom .=' ST_ClosestPoint(geometry, ST_SetSRID(ST_Point('.$fLatReverse.','.$fLonReverse.'),4326))';
525 $sFrom .=' ELSE centroid END AS closest_point';
526 $sFrom .= ' from placex where place_id = '.$iPlaceID.') as plx';
528 $sFrom = ' from placex where place_id = '.$iPlaceID;
530 if ($this->fPolygonSimplificationThreshold > 0) {
531 $sSQL .= ' from (select place_id,centroid,ST_SimplifyPreserveTopology(geometry,'.$this->fPolygonSimplificationThreshold.') as geometry'.$sFrom.') as plx';
536 $aPointPolygon = $this->oDB->getRow($sSQL, null, 'Could not get outline');
538 if ($aPointPolygon && $aPointPolygon['place_id']) {
539 if ($aPointPolygon['centrelon'] !== null && $aPointPolygon['centrelat'] !== null) {
540 $aOutlineResult['lat'] = $aPointPolygon['centrelat'];
541 $aOutlineResult['lon'] = $aPointPolygon['centrelon'];
544 if ($this->bIncludePolygonAsGeoJSON) {
545 $aOutlineResult['asgeojson'] = $aPointPolygon['asgeojson'];
547 if ($this->bIncludePolygonAsKML) {
548 $aOutlineResult['askml'] = $aPointPolygon['askml'];
550 if ($this->bIncludePolygonAsSVG) {
551 $aOutlineResult['assvg'] = $aPointPolygon['assvg'];
553 if ($this->bIncludePolygonAsText) {
554 $aOutlineResult['astext'] = $aPointPolygon['astext'];
557 if (abs($aPointPolygon['minlat'] - $aPointPolygon['maxlat']) < 0.0000001) {
558 $aPointPolygon['minlat'] = $aPointPolygon['minlat'] - $fRadius;
559 $aPointPolygon['maxlat'] = $aPointPolygon['maxlat'] + $fRadius;
562 if (abs($aPointPolygon['minlon'] - $aPointPolygon['maxlon']) < 0.0000001) {
563 $aPointPolygon['minlon'] = $aPointPolygon['minlon'] - $fRadius;
564 $aPointPolygon['maxlon'] = $aPointPolygon['maxlon'] + $fRadius;
567 $aOutlineResult['aBoundingBox'] = array(
568 (string)$aPointPolygon['minlat'],
569 (string)$aPointPolygon['maxlat'],
570 (string)$aPointPolygon['minlon'],
571 (string)$aPointPolygon['maxlon']
575 // as a fallback we generate a bounding box without knowing the size of the geometry
576 if ((!isset($aOutlineResult['aBoundingBox'])) && isset($fLon)) {
578 'minlat' => $fLat - $fRadius,
579 'maxlat' => $fLat + $fRadius,
580 'minlon' => $fLon - $fRadius,
581 'maxlon' => $fLon + $fRadius
584 $aOutlineResult['aBoundingBox'] = array(
585 (string)$aBounds['minlat'],
586 (string)$aBounds['maxlat'],
587 (string)$aBounds['minlon'],
588 (string)$aBounds['maxlon']
591 return $aOutlineResult;