$sSQL .= ' FROM get_addressdata('.$iPlaceID.','.$sHousenumber.')';
$sSQL .= ' ORDER BY rank_address DESC, isaddress DESC';
- $this->aAddressLines = chksql($oDB->getAll($sSQL));
+ $this->aAddressLines = $oDB->getAll($sSQL);
}
private static function isAddress($aLine)
--- /dev/null
+<?php
+
+namespace Nominatim;
+
+require_once(CONST_BasePath.'/lib/DatabaseError.php');
+
+/**
+ * Uses PDO to access the database specified in the CONST_Database_DSN
+ * setting.
+ */
+class DB
+{
+ protected $connection;
+
+ public function __construct($sDSN = CONST_Database_DSN)
+ {
+ $this->sDSN = $sDSN;
+ }
+
+ public function connect($bNew = false, $bPersistent = true)
+ {
+ if (isset($this->connection) && !$bNew) {
+ return true;
+ }
+ $aConnOptions = array(
+ \PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION,
+ \PDO::ATTR_DEFAULT_FETCH_MODE => \PDO::FETCH_ASSOC,
+ \PDO::ATTR_PERSISTENT => $bPersistent
+ );
+
+ // https://secure.php.net/manual/en/ref.pdo-pgsql.connection.php
+ try {
+ $conn = new \PDO($this->sDSN, null, null, $aConnOptions);
+ } catch (\PDOException $e) {
+ $sMsg = 'Failed to establish database connection:' . $e->getMessage();
+ throw new \Nominatim\DatabaseError($sMsg, 500, null, $e->getMessage());
+ }
+
+ $conn->exec("SET DateStyle TO 'sql,european'");
+ $conn->exec("SET client_encoding TO 'utf-8'");
+ $iMaxExecution = ini_get('max_execution_time');
+ if ($iMaxExecution > 0) $conn->setAttribute(\PDO::ATTR_TIMEOUT, $iMaxExecution); // seconds
+
+ $this->connection = $conn;
+ return true;
+ }
+
+ // returns the number of rows that were modified or deleted by the SQL
+ // statement. If no rows were affected returns 0.
+ public function exec($sSQL, $aInputVars = null, $sErrMessage = 'Database query failed')
+ {
+ $val = null;
+ try {
+ if (isset($aInputVars)) {
+ $stmt = $this->connection->prepare($sSQL);
+ $stmt->execute($aInputVars);
+ } else {
+ $val = $this->connection->exec($sSQL);
+ }
+ } catch (\PDOException $e) {
+ $sErrMessage = $e->message();
+ throw new \Nominatim\DatabaseError($sErrMessage, 500, null, $e, $sSQL);
+ }
+ return $val;
+ }
+
+ /**
+ * Executes query. Returns first row as array.
+ * Returns false if no result found.
+ *
+ * @param string $sSQL
+ *
+ * @return array[]
+ */
+ public function getRow($sSQL, $aInputVars = null, $sErrMessage = 'Database query failed')
+ {
+ try {
+ if (isset($aInputVars)) {
+ $stmt = $this->connection->prepare($sSQL);
+ $stmt->execute($aInputVars);
+ } else {
+ $stmt = $this->connection->query($sSQL);
+ }
+ $row = $stmt->fetch();
+ } catch (\PDOException $e) {
+ throw new \Nominatim\DatabaseError($sErrMessage, 500, null, $e, $sSQL);
+ }
+ return $row;
+ }
+
+ /**
+ * Executes query. Returns first value of first result.
+ * Returns false if no results found.
+ *
+ * @param string $sSQL
+ *
+ * @return array[]
+ */
+ public function getOne($sSQL, $aInputVars = null, $sErrMessage = 'Database query failed')
+ {
+ try {
+ if (isset($aInputVars)) {
+ $stmt = $this->connection->prepare($sSQL);
+ $stmt->execute($aInputVars);
+ } else {
+ $stmt = $this->connection->query($sSQL);
+ }
+ $row = $stmt->fetch(\PDO::FETCH_NUM);
+ if ($row === false) return false;
+ } catch (\PDOException $e) {
+ throw new \Nominatim\DatabaseError($sErrMessage, 500, null, $e, $sSQL);
+ }
+ return $row[0];
+ }
+
+ /**
+ * Executes query. Returns array of results (arrays).
+ * Returns empty array if no results found.
+ *
+ * @param string $sSQL
+ *
+ * @return array[]
+ */
+ public function getAll($sSQL, $aInputVars = null, $sErrMessage = 'Database query failed')
+ {
+ try {
+ if (isset($aInputVars)) {
+ $stmt = $this->connection->prepare($sSQL);
+ $stmt->execute($aInputVars);
+ } else {
+ $stmt = $this->connection->query($sSQL);
+ }
+ $rows = $stmt->fetchAll();
+ } catch (\PDOException $e) {
+ throw new \Nominatim\DatabaseError($sErrMessage, 500, null, $e, $sSQL);
+ }
+ return $rows;
+ }
+
+ /**
+ * Executes query. Returns array of the first value of each result.
+ * Returns empty array if no results found.
+ *
+ * @param string $sSQL
+ *
+ * @return array[]
+ */
+ public function getCol($sSQL, $aInputVars = null, $sErrMessage = 'Database query failed')
+ {
+ $aVals = array();
+ try {
+ if (isset($aInputVars)) {
+ $stmt = $this->connection->prepare($sSQL);
+ $stmt->execute($aInputVars);
+ } else {
+ $stmt = $this->connection->query($sSQL);
+ }
+ while ($val = $stmt->fetchColumn(0)) { // returns first column or false
+ $aVals[] = $val;
+ }
+ } catch (\PDOException $e) {
+ throw new \Nominatim\DatabaseError($sErrMessage, 500, null, $e, $sSQL);
+ }
+ return $aVals;
+ }
+
+ /**
+ * Executes query. Returns associate array mapping first value to second value of each result.
+ * Returns empty array if no results found.
+ *
+ * @param string $sSQL
+ *
+ * @return array[]
+ */
+ public function getAssoc($sSQL, $aInputVars = null, $sErrMessage = 'Database query failed')
+ {
+ try {
+ if (isset($aInputVars)) {
+ $stmt = $this->connection->prepare($sSQL);
+ $stmt->execute($aInputVars);
+ } else {
+ $stmt = $this->connection->query($sSQL);
+ }
+ $aList = array();
+ while ($aRow = $stmt->fetch(\PDO::FETCH_NUM)) {
+ $aList[$aRow[0]] = $aRow[1];
+ }
+ } catch (\PDOException $e) {
+ throw new \Nominatim\DatabaseError($sErrMessage, 500, null, $e, $sSQL);
+ }
+ return $aList;
+ }
+
+
+ /**
+ * St. John's Way => 'St. John\'s Way'
+ *
+ * @param string $sVal Text to be quoted.
+ *
+ * @return string
+ */
+ public function getDBQuoted($sVal)
+ {
+ return $this->connection->quote($sVal);
+ }
+
+ /**
+ * Like getDBQuoted, but takes an array.
+ *
+ * @param array $aVals List of text to be quoted.
+ *
+ * @return array[]
+ */
+ public function getDBQuotedList($aVals)
+ {
+ return array_map(function ($sVal) {
+ return $this->getDBQuoted($sVal);
+ }, $aVals);
+ }
+
+ /**
+ * [1,2,'b'] => 'ARRAY[1,2,'b']''
+ *
+ * @param array $aVals List of text to be quoted.
+ *
+ * @return string
+ */
+ public function getArraySQL($a)
+ {
+ return 'ARRAY['.join(',', $a).']';
+ }
+
+ public function getLastError()
+ {
+ // https://secure.php.net/manual/en/pdo.errorinfo.php
+ return $this->connection->errorInfo();
+ }
+
+ /**
+ * Check if a table exists in the database. Returns true if it does.
+ *
+ * @param string $sTableName
+ *
+ * @return boolean
+ */
+ public function tableExists($sTableName)
+ {
+ $sSQL = 'SELECT count(*) FROM pg_tables WHERE tablename = :tablename';
+ return ($this->getOne($sSQL, array(':tablename' => $sTableName)) == 1);
+ }
+
+ /**
+ * Since the DSN includes the database name, checks if the connection works.
+ *
+ * @return boolean
+ */
+ public function databaseExists()
+ {
+ $bExists = true;
+ try {
+ $this->connect(true);
+ } catch (\Nominatim\DatabaseError $e) {
+ $bExists = false;
+ }
+ return $bExists;
+ }
+
+ /**
+ * e.g. 9.6, 10, 11.2
+ *
+ * @return float
+ */
+ public function getPostgresVersion()
+ {
+ $sVersionString = $this->getOne('SHOW server_version_num');
+ preg_match('#([0-9]?[0-9])([0-9][0-9])[0-9][0-9]#', $sVersionString, $aMatches);
+ return (float) ($aMatches[1].'.'.$aMatches[2]);
+ }
+
+ /**
+ * e.g. 2, 2.2
+ *
+ * @return float
+ */
+ public function getPostgisVersion()
+ {
+ $sVersionString = $this->getOne('select postgis_lib_version()');
+ preg_match('#^([0-9]+)[.]([0-9]+)[.]#', $sVersionString, $aMatches);
+ return (float) ($aMatches[1].'.'.$aMatches[2]);
+ }
+
+ public static function parseDSN($sDSN)
+ {
+ // https://secure.php.net/manual/en/ref.pdo-pgsql.connection.php
+ $aInfo = array();
+ if (preg_match('/^pgsql:(.+)/', $sDSN, $aMatches)) {
+ foreach (explode(';', $aMatches[1]) as $sKeyVal) {
+ list($sKey, $sVal) = explode('=', $sKeyVal, 2);
+ if ($sKey == 'host') $sKey = 'hostspec';
+ if ($sKey == 'dbname') $sKey = 'database';
+ if ($sKey == 'user') $sKey = 'username';
+ $aInfo[$sKey] = $sVal;
+ }
+ }
+ return $aInfo;
+ }
+}
if ($sSpecialTerm && !$aSearches[0]->hasOperator()) {
$sSpecialTerm = pg_escape_string($sSpecialTerm);
- $sToken = chksql(
- $this->oDB->getOne("SELECT make_standard_name('$sSpecialTerm')"),
+ $sToken = $this->oDB->getOne(
+ 'SELECT make_standard_name(:term)',
+ array(':term' => $sSpecialTerm),
'Cannot decode query. Wrong encoding?'
);
$sSQL = 'SELECT class, type FROM word ';
$sSQL .= ' AND class is not null AND class not in (\'place\')';
Debug::printSQL($sSQL);
- $aSearchWords = chksql($this->oDB->getAll($sSQL));
+ $aSearchWords = $this->oDB->getAll($sSQL);
$aNewSearches = array();
foreach ($aSearches as $oSearch) {
foreach ($aSearchWords as $aSearchTerm) {
$aTokens = array();
$aPhrases = array();
foreach ($aInPhrases as $iPhrase => $sPhrase) {
- $sPhrase = chksql(
- $this->oDB->getOne('SELECT make_standard_name('.$this->oDB->getDBQuoted($sPhrase).')'),
+ $sPhrase = $this->oDB->getOne(
+ 'SELECT make_standard_name(:phrase)',
+ array(':phrase' => $sPhrase),
'Cannot normalize query string (is it a UTF-8 string?)'
);
if (trim($sPhrase)) {
if ($aFilterSql) {
$sSQL = join(' UNION ', $aFilterSql);
Debug::printSQL($sSQL);
- $aFilteredIDs = chksql($this->oDB->getCol($sSQL));
+ $aFilteredIDs = $this->oDB->getCol($sSQL);
}
$tempIDs = array();
public function lookupOSMID($sType, $iID)
{
- $sSQL = "select place_id from placex where osm_type = '".$sType."' and osm_id = ".$iID;
- $iPlaceID = chksql($this->oDB->getOne($sSQL));
+ $sSQL = 'select place_id from placex where osm_type = :type and osm_id = :id';
+ $iPlaceID = $this->oDB->getOne($sSQL, array(':type' => $sType, ':id' => $iID));
if (!$iPlaceID) {
return null;
$sSQL = join(' UNION ', $aSubSelects);
Debug::printSQL($sSQL);
- $aPlaces = chksql($this->oDB->getAll($sSQL), 'Could not lookup place');
+ $aPlaces = $this->oDB->getAll($sSQL, null, 'Could not lookup place');
foreach ($aPlaces as &$aPlace) {
if ($this->bAddressDetails) {
$sSQL .= $sFrom;
}
- $aPointPolygon = chksql($this->oDB->getRow($sSQL), 'Could not get outline');
+ $aPointPolygon = $this->oDB->getRow($sSQL, null, 'Could not get outline');
if ($aPointPolygon && $aPointPolygon['place_id']) {
if ($aPointPolygon['centrelon'] !== null && $aPointPolygon['centrelat'] !== null) {
$sSQL .= ' and indexed_status = 0 and startnumber is not NULL ';
$sSQL .= ' ORDER BY distance ASC limit 1';
- return chksql(
- $this->oDB->getRow($sSQL),
+ return $this->oDB->getRow(
+ $sSQL,
+ null,
'Could not determine closest housenumber on an osm interpolation line.'
);
}
$sSQL = 'SELECT country_code FROM country_osm_grid';
$sSQL .= ' WHERE ST_CONTAINS(geometry, '.$sPointSQL.') LIMIT 1';
- $sCountryCode = chksql(
- $this->oDB->getOne($sSQL),
+ $sCountryCode = $this->oDB->getOne(
+ $sSQL,
+ null,
'Could not determine country polygon containing the point.'
);
if ($sCountryCode) {
$sSQL .= ' LIMIT 1';
if (CONST_Debug) var_dump($sSQL);
- $aPlace = chksql(
- $this->oDB->getRow($sSQL),
- 'Could not determine place node.'
- );
+ $aPlace = $this->oDB->getRow($sSQL, null, 'Could not determine place node.');
if ($aPlace) {
return new Result($aPlace['place_id']);
}
$sSQL .= ' ORDER BY distance ASC';
if (CONST_Debug) var_dump($sSQL);
- $aPlace = chksql(
- $this->oDB->getRow($sSQL),
- 'Could not determine place node.'
- );
+ $aPlace = $this->oDB->getRow($sSQL, null, 'Could not determine place node.');
if ($aPlace) {
return new Result($aPlace['place_id']);
}
$sSQL .= ' WHERE ST_CONTAINS(geometry, '.$sPointSQL.' )';
$sSQL .= ' ORDER BY rank_address DESC LIMIT 1';
- $aPoly = chksql(
- $this->oDB->getRow($sSQL),
- 'Could not determine polygon containing the point.'
- );
+ $aPoly = $this->oDB->getRow($sSQL, null, 'Could not determine polygon containing the point.');
+
if ($aPoly) {
// if a polygon is found, search for placenodes begins ...
$iParentPlaceID = $aPoly['parent_place_id'];
$sSQL .= ' LIMIT 1';
if (CONST_Debug) var_dump($sSQL);
- $aPlacNode = chksql(
- $this->oDB->getRow($sSQL),
- 'Could not determine place node.'
- );
+ $aPlacNode = $this->oDB->getRow($sSQL, null, 'Could not determine place node.');
if ($aPlacNode) {
return $aPlacNode;
}
$sSQL .= ' OR ST_DWithin('.$sPointSQL.', centroid, '.$fSearchDiam.'))';
$sSQL .= ' ORDER BY distance ASC limit 1';
if (CONST_Debug) var_dump($sSQL);
- $aPlace = chksql(
- $this->oDB->getRow($sSQL),
- 'Could not determine closest place.'
- );
+ $aPlace = $this->oDB->getRow($sSQL, null, 'Could not determine closest place.');
if (CONST_Debug) var_dump($aPlace);
if ($aPlace) {
$sSQL .= ' and indexed_status = 0 and linked_place_id is null';
$sSQL .= ' ORDER BY distance ASC limit 1';
if (CONST_Debug) var_dump($sSQL);
- $aStreet = chksql(
- $this->oDB->getRow($sSQL),
- 'Could not determine closest place.'
- );
+ $aStreet = $this->oDB->getRow($sSQL, null, 'Could not determine closest place.');
if ($aStreet) {
if (CONST_Debug) var_dump($aStreet);
$oResult = new Result($aStreet['place_id']);
$sSQL .= ' AND ST_DWithin('.$sPointSQL.', linegeo, 0.001)';
$sSQL .= ' ORDER BY distance ASC limit 1';
if (CONST_Debug) var_dump($sSQL);
- $aPlaceTiger = chksql(
- $this->oDB->getRow($sSQL),
- 'Could not determine closest Tiger place.'
- );
+ $aPlaceTiger = $this->oDB->getRow($sSQL, null, 'Could not determine closest Tiger place.');
if ($aPlaceTiger) {
if (CONST_Debug) var_dump('found Tiger housenumber', $aPlaceTiger);
$oResult = new Result($aPlaceTiger['place_id'], Result::TABLE_TIGER);
* The viewbox may be bounded which means that no search results
* must be outside the viewbox.
*
- * @param object $oDB DB connection to use for computing the box.
+ * @param object $oDB Nominatim::DB instance to use for computing the box.
* @param string[] $aRoutePoints List of x,y coordinates along a route.
* @param float $fRouteWidth Buffer around the route to use.
* @param bool $bBounded True if the viewbox bounded.
$this->sqlViewboxCentre .= ")'::geometry,4326)";
$sSQL = 'ST_BUFFER('.$this->sqlViewboxCentre.','.($fRouteWidth/69).')';
- $sGeom = chksql($oDB->getOne('select '.$sSQL), 'Could not get small viewbox');
+ $sGeom = $oDB->getOne('select '.$sSQL, null, 'Could not get small viewbox');
$this->sqlViewboxSmall = "'".$sGeom."'::geometry";
$sSQL = 'ST_BUFFER('.$this->sqlViewboxCentre.','.($fRouteWidth/30).')';
- $sGeom = chksql($oDB->getOne('select '.$sSQL), 'Could not get large viewbox');
+ $sGeom = $oDB->getOne('select '.$sSQL, null, 'Could not get large viewbox');
$this->sqlViewboxLarge = "'".$sGeom."'::geometry";
}
/**
* Query database for places that match this search.
*
- * @param object $oDB Database connection to use.
+ * @param object $oDB Nominatim::DB instance to use.
* @param integer $iMinRank Minimum address rank to restrict search to.
* @param integer $iMaxRank Maximum address rank to restrict search to.
* @param integer $iLimit Maximum number of results.
$sSQL .= ' WHERE place_id in ('.$sPlaceIds.')';
$sSQL .= " AND postcode != '".$this->sPostcode."'";
Debug::printSQL($sSQL);
- $aFilteredPlaceIDs = chksql($oDB->getCol($sSQL));
+ $aFilteredPlaceIDs = $oDB->getCol($sSQL);
if ($aFilteredPlaceIDs) {
foreach ($aFilteredPlaceIDs as $iPlaceId) {
$aResults[$iPlaceId]->iResultRank++;
$aDBResults = array();
$sPoiTable = $this->poiTable();
- $sSQL = 'SELECT count(*) FROM pg_tables WHERE tablename = \''.$sPoiTable."'";
- if (chksql($oDB->getOne($sSQL))) {
+ if ($oDB->tableExists($sPoiTable)) {
$sSQL = 'SELECT place_id FROM '.$sPoiTable.' ct';
if ($this->oContext->sqlCountryList) {
$sSQL .= ' JOIN placex USING (place_id)';
} elseif ($this->oContext->hasNearPoint()) {
$sSQL .= ' ORDER BY '.$this->oContext->distanceSQL('ct.centroid').' ASC';
}
- $sSQL .= " limit $iLimit";
+ $sSQL .= " LIMIT $iLimit";
Debug::printSQL($sSQL);
- $aDBResults = chksql($oDB->getCol($sSQL));
+ $aDBResults = $oDB->getCol($sSQL);
}
if ($this->oContext->hasNearPoint()) {
$sSQL = 'SELECT place_id FROM placex WHERE ';
- $sSQL .= 'class=\''.$this->sClass."' and type='".$this->sType."'";
+ $sSQL .= 'class = :class and type = :type';
$sSQL .= ' AND '.$this->oContext->withinSQL('geometry');
$sSQL .= ' AND linked_place_id is null';
if ($this->oContext->sqlCountryList) {
$sSQL .= ' ORDER BY '.$this->oContext->distanceSQL('centroid').' ASC';
$sSQL .= " LIMIT $iLimit";
Debug::printSQL($sSQL);
- $aDBResults = chksql($oDB->getCol($sSQL));
+ $aDBResults = $oDB->getCol(
+ $sSQL,
+ array(':class' => $this->sClass, ':type' => $this->sType)
+ );
}
$aResults = array();
Debug::printSQL($sSQL);
$aResults = array();
- foreach (chksql($oDB->getCol($sSQL)) as $iPlaceId) {
+ foreach ($oDB->getCol($sSQL) as $iPlaceId) {
$aResults[$iPlaceId] = new Result($iPlaceId, Result::TABLE_POSTCODE);
}
Debug::printSQL($sSQL);
- $aDBResults = chksql(
- $oDB->getAll($sSQL),
- 'Could not get places for search terms.'
- );
+ $aDBResults = $oDB->getAll($sSQL, null, 'Could not get places for search terms.');
foreach ($aDBResults as $aResult) {
$oResult = new Result($aResult['place_id']);
Debug::printSQL($sSQL);
// XXX should inherit the exactMatches from its parent
- foreach (chksql($oDB->getCol($sSQL)) as $iPlaceId) {
+ foreach ($oDB->getCol($sSQL) as $iPlaceId) {
$aResults[$iPlaceId] = new Result($iPlaceId);
}
Debug::printSQL($sSQL);
- foreach (chksql($oDB->getCol($sSQL)) as $iPlaceId) {
+ foreach ($oDB->getCol($sSQL) as $iPlaceId) {
$oResult = new Result($iPlaceId, Result::TABLE_OSMLINE);
$oResult->iHouseNumber = $iHousenumber;
$aResults[$iPlaceId] = $oResult;
Debug::printSQL($sSQL);
- foreach (chksql($oDB->getCol($sSQL)) as $iPlaceId) {
+ foreach ($oDB->getCol($sSQL) as $iPlaceId) {
$aResults[$iPlaceId] = new Result($iPlaceId, Result::TABLE_AUX);
}
}
Debug::printSQL($sSQL);
- foreach (chksql($oDB->getCol($sSQL)) as $iPlaceId) {
+ foreach ($oDB->getCol($sSQL) as $iPlaceId) {
$oResult = new Result($iPlaceId, Result::TABLE_TIGER);
$oResult->iHouseNumber = $iHousenumber;
$aResults[$iPlaceId] = $oResult;
Debug::printSQL($sSQL);
- foreach (chksql($oDB->getCol($sSQL)) as $iPlaceId) {
+ foreach ($oDB->getCol($sSQL) as $iPlaceId) {
$aResults[$iPlaceId] = new Result($iPlaceId);
}
}
// NEAR and IN are handled the same
if ($this->iOperator == Operator::TYPE || $this->iOperator == Operator::NEAR) {
$sClassTable = $this->poiTable();
- $sSQL = "SELECT count(*) FROM pg_tables WHERE tablename = '$sClassTable'";
- $bCacheTable = (bool) chksql($oDB->getOne($sSQL));
+ $bCacheTable = $oDB->tableExists($sClassTable);
$sSQL = "SELECT min(rank_search) FROM placex WHERE place_id in ($sPlaceIDs)";
Debug::printSQL($sSQL);
- $iMaxRank = (int)chksql($oDB->getOne($sSQL));
+ $iMaxRank = (int) $oDB->getOne($sSQL);
// For state / country level searches the normal radius search doesn't work very well
$sPlaceGeom = false;
$sSQL .= ' ORDER BY rank_search ASC ';
$sSQL .= ' LIMIT 1';
Debug::printSQL($sSQL);
- $sPlaceGeom = chksql($oDB->getOne($sSQL));
+ $sPlaceGeom = $oDB->getOne($sSQL);
}
if ($sPlaceGeom) {
$sSQL = 'SELECT place_id FROM placex';
$sSQL .= " WHERE place_id in ($sPlaceIDs) and rank_search < $iMaxRank";
Debug::printSQL($sSQL);
- $aPlaceIDs = chksql($oDB->getCol($sSQL));
+ $aPlaceIDs = $oDB->getCol($sSQL);
$sPlaceIDs = join(',', $aPlaceIDs);
}
Debug::printSQL($sSQL);
- foreach (chksql($oDB->getCol($sSQL)) as $iPlaceId) {
+ foreach ($oDB->getCol($sSQL) as $iPlaceId) {
$aResults[$iPlaceId] = new Result($iPlaceId);
}
} else {
Debug::printSQL($sSQL);
- foreach (chksql($oDB->getCol($sSQL)) as $iPlaceId) {
+ foreach ($oDB->getCol($sSQL) as $iPlaceId) {
$aResults[$iPlaceId] = new Result($iPlaceId);
}
}
/**
* Add token information from the word table in the database.
*
- * @param object $oDB Database connection.
+ * @param object $oDB Nominatim::DB instance.
* @param string[] $aTokens List of tokens to look up in the database.
* @param string[] $aCountryCodes List of country restrictions.
* @param string $sNormQuery Normalized query string.
Debug::printSQL($sSQL);
- $aDBWords = chksql($oDB->getAll($sSQL), 'Could not get word tokens.');
+ $aDBWords = $oDB->getAll($sSQL, null, 'Could not get word tokens.');
foreach ($aDBWords as $aWord) {
$oToken = null;
exit;
}
-function chksql($oSql, $sMsg = false)
-{
- return $oSql;
-}
-
function info($sMsg)
{
echo date('Y-m-d H:i:s == ').$sMsg."\n";
+++ /dev/null
-<?php
-
-namespace Nominatim;
-
-require_once(CONST_BasePath.'/lib/DatabaseError.php');
-
-class DB
-{
- public $connection;
-
- public function __construct($sDSN = CONST_Database_DSN)
- {
- $this->sDSN = $sDSN;
- }
-
- public function connect($bNew = false, $bPersistent = true)
- {
- $aConnOptions = array(
- \PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION,
- \PDO::ATTR_DEFAULT_FETCH_MODE => \PDO::FETCH_ASSOC,
- \PDO::ATTR_PERSISTENT => $bPersistent
- );
-
- // https://secure.php.net/manual/en/ref.pdo-pgsql.connection.php
- try {
- $conn = new \PDO($this->sDSN, null, null, $aConnOptions);
- } catch (\PDOException $e) {
- $sMsg = 'Failed to establish database connection:' . $e->getMessage();
- throw new \Nominatim\DatabaseError($sMsg, 500, null, $e->getMessage());
- }
-
- $conn->exec("SET DateStyle TO 'sql,european'");
- $conn->exec("SET client_encoding TO 'utf-8'");
- $iMaxExecution = ini_get('max_execution_time');
- if ($iMaxExecution > 0) $conn->setAttribute(\PDO::ATTR_TIMEOUT, $iMaxExecution); // seconds
-
- $this->connection = $conn;
- return true;
- }
-
- // returns the number of rows that were modified or deleted by the SQL
- // statement. If no rows were affected returns 0.
- public function exec($sSQL)
- {
- $val = null;
- try {
- $val = $this->connection->exec($sSQL);
- } catch (\PDOException $e) {
- throw new \Nominatim\DatabaseError('Database query failed', 500, null, $e, $sSQL);
- }
- return $val;
- }
-
- public function getRow($sSQL)
- {
- try {
- $stmt = $this->connection->query($sSQL);
- $row = $stmt->fetch();
- } catch (\PDOException $e) {
- throw new \Nominatim\DatabaseError('Database query failed', 500, null, $e, $sSQL);
- }
- return $row;
- }
-
- public function getOne($sSQL)
- {
- try {
- $stmt = $this->connection->query($sSQL);
- $row = $stmt->fetch(\PDO::FETCH_NUM);
- if ($row === false) return false;
- } catch (\PDOException $e) {
- throw new \Nominatim\DatabaseError('Database query failed', 500, null, $e, $sSQL);
- }
- return $row[0];
- }
-
- public function getAll($sSQL)
- {
- try {
- $stmt = $this->connection->query($sSQL);
- $rows = $stmt->fetchAll();
- } catch (\PDOException $e) {
- throw new \Nominatim\DatabaseError('Database query failed', 500, null, $e, $sSQL);
- }
- return $rows;
- }
-
- public function getCol($sSQL)
- {
- $aVals = array();
- try {
- $stmt = $this->connection->query($sSQL);
- while ($val = $stmt->fetchColumn(0)) { // returns first column or false
- $aVals[] = $val;
- }
- } catch (\PDOException $e) {
- throw new \Nominatim\DatabaseError('Database query failed', 500, null, $e, $sSQL);
- }
- return $aVals;
- }
-
- public function getAssoc($sSQL)
- {
- try {
- $stmt = $this->connection->query($sSQL);
- $aList = array();
- while ($aRow = $stmt->fetch(\PDO::FETCH_NUM)) {
- $aList[$aRow[0]] = $aRow[1];
- }
- } catch (\PDOException $e) {
- throw new \Nominatim\DatabaseError('Database query failed', 500, null, $e, $sSQL);
- }
- return $aList;
- }
-
-
- // St. John's Way => 'St. John\'s Way'
- public function getDBQuoted($sVal)
- {
- return $this->connection->quote($sVal);
- }
-
- public function getDBQuotedList($aVals)
- {
- return array_map(function ($sVal) {
- return $this->getDBQuoted($sVal);
- }, $aVals);
- }
-
- public function getArraySQL($a)
- {
- return 'ARRAY['.join(',', $a).']';
- }
-
- public function getLastError()
- {
- // https://secure.php.net/manual/en/pdo.errorinfo.php
- return $this->connection->errorInfo();
- }
-
- public function tableExists($sTableName)
- {
- $sSQL = 'SELECT count(*) FROM pg_tables WHERE tablename = '.$this->getDBQuoted($sTableName);
- return ($this->getOne($sSQL) == 1);
- }
-
- public function getPostgresVersion()
- {
- $sVersionString = $this->getOne('SHOW server_version_num');
- preg_match('#([0-9]?[0-9])([0-9][0-9])[0-9][0-9]#', $sVersionString, $aMatches);
- return (float) ($aMatches[1].'.'.$aMatches[2]);
- }
-
- public function getPostgisVersion()
- {
- $sVersionString = $this->getOne('select postgis_lib_version()');
- preg_match('#^([0-9]+)[.]([0-9]+)[.]#', $sVersionString, $aMatches);
- return (float) ($aMatches[1].'.'.$aMatches[2]);
- }
-
- public static function parseDSN($sDSN)
- {
- // https://secure.php.net/manual/en/ref.pdo-pgsql.connection.php
- $aInfo = array();
- if (preg_match('/^pgsql:(.+)/', $sDSN, $aMatches)) {
- foreach (explode(';', $aMatches[1]) as $sKeyVal) {
- list($sKey, $sVal) = explode('=', $sKeyVal, 2);
- if ($sKey == 'host') $sKey = 'hostspec';
- if ($sKey == 'dbname') $sKey = 'database';
- if ($sKey == 'user') $sKey = 'username';
- $aInfo[$sKey] = $sVal;
- }
- }
- return $aInfo;
- }
-}
*
*/
-
-function chksql($oSql, $sMsg = 'Database request failed')
-{
- return $oSql;
-}
-
-
function userError($sMsg)
{
throw new Exception($sMsg, 400);
<?php
require_once(CONST_BasePath.'/lib/lib.php');
-require_once(CONST_BasePath.'/lib/db.php');
+require_once(CONST_BasePath.'/lib/DB.php');
if (get_magic_quotes_gpc()) {
echo "Please disable magic quotes in your php.ini configuration\n";
*/
public function createTable($oDB, $sTable)
{
- chksql($oDB->exec('DROP TABLE IF EXISTS '.$sTable));
+ $oDB->exec('DROP TABLE IF EXISTS '.$sTable);
$sSql = 'CREATE TABLE '.$sTable;
$sSql .= '(country_code varchar(2), class TEXT, type TEXT,';
$sSql .= ' rank_search SMALLINT, rank_address SMALLINT)';
- chksql($oDB->exec($sSql));
+ $oDB->exec($sSql);
- $sSql = 'CREATE UNIQUE INDEX ON '.$sTable.'(country_code, class, type)';
- chksql($oDB->exec($sSql));
+ $sSql = 'CREATE UNIQUE INDEX ON '.$sTable.' (country_code, class, type)';
+ $oDB->exec($sSql);
$sSql = 'INSERT INTO '.$sTable.' VALUES ';
foreach ($this->aLevels as $aLevel) {
}
}
}
- chksql($oDB->exec(rtrim($sSql, ',')));
+ $oDB->exec(rtrim($sSql, ','));
}
}
public function createDB()
{
info('Create DB');
- $bExists = true;
- try {
- $oDB = new \Nominatim\DB;
- $oDB->connect();
- } catch (\Nominatim\DatabaseError $e) {
- $bExists = false;
- }
+ $oDB = new \Nominatim\DB;
- if ($bExists) {
+ if ($oDB->databaseExists()) {
fail('database already exists ('.CONST_Database_DSN.')');
}
// For extratags and namedetails the hstore_to_json converter is
// needed which is only available from Postgresql 9.3+. For older
// versions add a dummy function that returns nothing.
- $iNumFunc = chksql($this->oDB->getOne("select count(*) from pg_proc where proname = 'hstore_to_json'"));
+ $iNumFunc = $this->oDB->getOne("select count(*) from pg_proc where proname = 'hstore_to_json'");
if ($iNumFunc == 0) {
$this->pgsqlRunScript("create function hstore_to_json(dummy hstore) returns text AS 'select null::text' language sql immutable");
$this->pgsqlRunScript('ALTER FUNCTION ST_Distance_Spheroid(geometry, geometry, spheroid) RENAME TO ST_DistanceSpheroid');
}
- $i = chksql($this->oDB->getOne("select count(*) from pg_user where usename = '".CONST_Database_Web_User."'"));
+ $i = $this->oDB->getOne("select count(*) from pg_user where usename = '".CONST_Database_Web_User."'");
if ($i == 0) {
echo "\nERROR: Web user '".CONST_Database_Web_User."' does not exist. Create it with:\n";
echo "\n createuser ".CONST_Database_Web_User."\n\n";
$this->runWithPgEnv($osm2pgsql);
- if (!$this->sIgnoreErrors && !chksql($this->oDB->getRow('select * from place limit 1'))) {
+ if (!$this->sIgnoreErrors && !$this->oDB->getRow('select * from place limit 1')) {
fail('No Data');
}
}
echo '.';
$sSQL = 'select distinct partition from country_name';
- $aPartitions = chksql($this->oDB->getCol($sSQL));
+ $aPartitions = $this->oDB->getCol($sSQL);
if (!$this->bNoPartitions) $aPartitions[] = 0;
foreach ($aPartitions as $sPartition) {
);
$aDropTables = array();
- $aHaveTables = chksql($this->oDB->getCol("SELECT tablename FROM pg_tables WHERE schemaname='public'"));
+ $aHaveTables = $this->oDB->getCol("SELECT tablename FROM pg_tables WHERE schemaname='public'");
foreach ($aHaveTables as $sTable) {
$bFound = false;
namespace Nominatim;
require_once(CONST_BasePath.'/lib/lib.php');
-require_once(CONST_BasePath.'/lib/db.php');
+require_once(CONST_BasePath.'/lib/DB.php');
+// subclassing so we can set the protected connection variable
+class NominatimSubClassedDB extends \Nominatim\DB
+{
+ public function setConnection($oConnection)
+ {
+ $this->connection = $oConnection;
+ }
+}
+
+// phpcs:ignore PSR1.Classes.ClassDeclaration.MultipleClasses
class DBTest extends \PHPUnit\Framework\TestCase
{
+ public function testReusingConnection()
+ {
+ $oDB = new NominatimSubClassedDB('');
+ $oDB->setConnection('anything');
+ $this->assertTrue($oDB->connect());
+ }
+
+ public function testDatabaseExists()
+ {
+ $oDB = new \Nominatim\DB('');
+ $this->assertFalse($oDB->databaseExists());
+ }
public function testErrorHandling()
{
throw new \PDOException('ERROR: syntax error at or near "FROM"');
}));
- $oDB = new \Nominatim\DB('');
- $oDB->connection = $oPDOStub;
- $oDB->tableExists('abc');
+ $oDB = new NominatimSubClassedDB('');
+ $oDB->setConnection($oPDOStub);
+ $oDB->getOne('SELECT name FROM');
+ }
+
+ public function testGetPostgresVersion()
+ {
+ $oDBStub = $this->getMockBuilder(\Nominatim\DB::class)
+ ->disableOriginalConstructor()
+ ->setMethods(array('getOne'))
+ ->getMock();
+
+ $oDBStub->method('getOne')
+ ->willReturn('100006');
+
+ $this->assertEquals(10, $oDBStub->getPostgresVersion());
+ }
+
+ public function testGetPostgisVersion()
+ {
+ $oDBStub = $this->getMockBuilder(\Nominatim\DB::class)
+ ->disableOriginalConstructor()
+ ->setMethods(array('getOne'))
+ ->getMock();
+
+ $oDBStub->method('getOne')
+ ->willReturn('2.4.4');
+
+ $this->assertEquals(2.4, $oDBStub->getPostgisVersion());
}
public function testParseDSN()
namespace Nominatim;
-require_once(CONST_BasePath.'/lib/db.php');
+require_once(CONST_BasePath.'/lib/DB.php');
require_once(CONST_BasePath.'/lib/Status.php');
namespace Nominatim;
-// require_once(CONST_BasePath.'/lib/db.php');
-// require_once(CONST_BasePath.'/lib/cmd.php');
require_once(CONST_BasePath.'/lib/TokenList.php');
}
if ($aResult['check-for-updates']) {
- $aLastState = chksql($oDB->getRow('SELECT sequence_id FROM import_status'));
+ $aLastState = $oDB->getRow('SELECT sequence_id FROM import_status');
if (!$aLastState['sequence_id']) {
fail('Updates not set up. Please run ./utils/update.php --init-updates.');
}
$sSQL = 'select partition from country_name order by country_code';
- $aPartitions = chksql($oDB->getCol($sSQL));
+ $aPartitions = $oDB->getCol($sSQL);
$aPartitions[] = 0;
// we don't care about empty search_name_* partitions, they can't contain mentions of duplicates
foreach ($aPartitions as $i => $sPartition) {
$sSQL = 'select count(*) from search_name_'.$sPartition;
- $nEntries = chksql($oDB->getOne($sSQL));
+ $nEntries = $oDB->getOne($sSQL);
if ($nEntries == 0) {
unset($aPartitions[$i]);
}
$sSQL = "select word_token,count(*) from word where substr(word_token, 1, 1) = ' '";
$sSQL .= ' and class is null and type is null and country_code is null';
$sSQL .= ' group by word_token having count(*) > 1 order by word_token';
- $aDuplicateTokens = chksql($oDB->getAll($sSQL));
+ $aDuplicateTokens = $oDB->getAll($sSQL);
foreach ($aDuplicateTokens as $aToken) {
if (trim($aToken['word_token']) == '' || trim($aToken['word_token']) == '-') continue;
echo 'Deduping '.$aToken['word_token']."\n";
$sSQL .= ' (select count(*) from search_name where nameaddress_vector @> ARRAY[word_id]) as num';
$sSQL .= " from word where word_token = '".$aToken['word_token'];
$sSQL .= "' and class is null and type is null and country_code is null order by num desc";
- $aTokenSet = chksql($oDB->getAll($sSQL));
+ $aTokenSet = $oDB->getAll($sSQL);
$aKeep = array_shift($aTokenSet);
$iKeepID = $aKeep['word_id'];
$sSQL .= ' name_vector = array_replace(name_vector,'.$aRemove['word_id'].','.$iKeepID.'),';
$sSQL .= ' nameaddress_vector = array_replace(nameaddress_vector,'.$aRemove['word_id'].','.$iKeepID.')';
$sSQL .= ' where name_vector @> ARRAY['.$aRemove['word_id'].']';
- chksql($oDB->exec($sSQL));
+ $oDB->exec($sSQL);
$sSQL = 'update search_name set';
$sSQL .= ' nameaddress_vector = array_replace(nameaddress_vector,'.$aRemove['word_id'].','.$iKeepID.')';
$sSQL .= ' where nameaddress_vector @> ARRAY['.$aRemove['word_id'].']';
- chksql($oDB->exec($sSQL));
+ $oDB->exec($sSQL);
$sSQL = 'update location_area_country set';
$sSQL .= ' keywords = array_replace(keywords,'.$aRemove['word_id'].','.$iKeepID.')';
$sSQL .= ' where keywords @> ARRAY['.$aRemove['word_id'].']';
- chksql($oDB->exec($sSQL));
+ $oDB->exec($sSQL);
foreach ($aPartitions as $sPartition) {
$sSQL = 'update search_name_'.$sPartition.' set';
$sSQL .= ' name_vector = array_replace(name_vector,'.$aRemove['word_id'].','.$iKeepID.')';
$sSQL .= ' where name_vector @> ARRAY['.$aRemove['word_id'].']';
- chksql($oDB->exec($sSQL));
+ $oDB->exec($sSQL);
$sSQL = 'update location_area_country set';
$sSQL .= ' keywords = array_replace(keywords,'.$aRemove['word_id'].','.$iKeepID.')';
$sSQL .= ' where keywords @> ARRAY['.$aRemove['word_id'].']';
- chksql($oDB->exec($sSQL));
+ $oDB->exec($sSQL);
}
$sSQL = 'delete from word where word_id = '.$aRemove['word_id'];
- chksql($oDB->exec($sSQL));
+ $oDB->exec($sSQL);
}
}
}
while (true) {
$fStartTime = time();
- $aLastState = chksql($oDB->getRow('SELECT *, EXTRACT (EPOCH FROM lastimportdate) as unix_ts FROM import_status'));
+ $aLastState = $oDB->getRow('SELECT *, EXTRACT (EPOCH FROM lastimportdate) as unix_ts FROM import_status');
if (!$aLastState['sequence_id']) {
echo "Updates not set up. Please run ./utils/update.php --init-updates.\n";
$sSQL .= date('Y-m-d H:i:s', $fCMDStartTime)."','";
$sSQL .= date('Y-m-d H:i:s')."','import')";
var_Dump($sSQL);
- chksql($oDB->exec($sSQL));
+ $oDB->exec($sSQL);
// update the status
$sSQL = "UPDATE import_status SET lastimportdate = '$sBatchEnd', indexed=false, sequence_id = $iEndSequence";
var_Dump($sSQL);
- chksql($oDB->exec($sSQL));
+ $oDB->exec($sSQL);
echo date('Y-m-d H:i:s')." Completed download step for $sBatchEnd in ".round((time()-$fCMDStartTime)/60, 2)." minutes\n";
}
$sSQL .= " name->'name' as name, i.* from placex, import_polygon_delete i";
$sSQL .= ' where placex.osm_id = i.osm_id and placex.osm_type = i.osm_type';
$sSQL .= ' and placex.class = i.class and placex.type = i.type';
-$aPolygons = chksql($oDB->getAll($sSQL), 'Could not get list of deleted OSM elements.');
+$aPolygons = $oDB->getAll($sSQL, null, 'Could not get list of deleted OSM elements.');
if (CONST_Debug) {
var_dump($aPolygons);
$sLanguagePrefArraySQL = $oDB->getArraySQL($oDB->getDBQuotedList($aLangPrefOrder));
if ($sOsmType && $iOsmId > 0) {
- $sSQL = sprintf(
- "SELECT place_id FROM placex WHERE osm_type='%s' AND osm_id=%d",
- $sOsmType,
- $iOsmId
- );
+ $sSQL = 'SELECT place_id FROM placex WHERE osm_type = :type AND osm_id = :id';
// osm_type and osm_id are not unique enough
if ($sClass) {
$sSQL .= " AND class='".$sClass."'";
}
$sSQL .= ' ORDER BY class ASC';
- $sPlaceId = chksql($oDB->getOne($sSQL));
+ $sPlaceId = $oDB->getOne($sSQL, array(':type' => $sOsmType, ':id' => $iOsmId));
// Be nice about our error messages for broken geometry
$sSQL .= ' ST_AsText(prevgeometry) AS prevgeom, ';
$sSQL .= ' ST_AsText(newgeometry) AS newgeom';
$sSQL .= ' FROM import_polygon_error ';
- $sSQL .= " WHERE osm_type = '".$sOsmType."'";
- $sSQL .= ' AND osm_id = '.$iOsmId;
+ $sSQL .= ' WHERE osm_type = :type';
+ $sSQL .= ' AND osm_id = :id';
$sSQL .= ' ORDER BY updated DESC';
$sSQL .= ' LIMIT 1';
- $aPointDetails = chksql($oDB->getRow($sSQL));
+ $aPointDetails = $oDB->getRow($sSQL, array(':type' => $sOsmType, ':id' => $iOsmId));
if ($aPointDetails) {
if (preg_match('/\[(-?\d+\.\d+) (-?\d+\.\d+)\]/', $aPointDetails['errormessage'], $aMatches)) {
$aPointDetails['error_x'] = $aMatches[1];
$iPlaceID = (int)$sPlaceId;
if (CONST_Use_US_Tiger_Data) {
- $iParentPlaceID = chksql($oDB->getOne('SELECT parent_place_id FROM location_property_tiger WHERE place_id = '.$iPlaceID));
+ $iParentPlaceID = $oDB->getOne('SELECT parent_place_id FROM location_property_tiger WHERE place_id = '.$iPlaceID);
if ($iParentPlaceID) $iPlaceID = $iParentPlaceID;
}
// interpolated house numbers
-$iParentPlaceID = chksql($oDB->getOne('SELECT parent_place_id FROM location_property_osmline WHERE place_id = '.$iPlaceID));
+$iParentPlaceID = $oDB->getOne('SELECT parent_place_id FROM location_property_osmline WHERE place_id = '.$iPlaceID);
if ($iParentPlaceID) $iPlaceID = $iParentPlaceID;
// artificial postcodes
-$iParentPlaceID = chksql($oDB->getOne('SELECT parent_place_id FROM location_postcode WHERE place_id = '.$iPlaceID));
+$iParentPlaceID = $oDB->getOne('SELECT parent_place_id FROM location_postcode WHERE place_id = '.$iPlaceID);
if ($iParentPlaceID) $iPlaceID = $iParentPlaceID;
if (CONST_Use_Aux_Location_data) {
- $iParentPlaceID = chksql($oDB->getOne('SELECT parent_place_id FROM location_property_aux WHERE place_id = '.$iPlaceID));
+ $iParentPlaceID = $oDB->getOne('SELECT parent_place_id FROM location_property_aux WHERE place_id = '.$iPlaceID);
if ($iParentPlaceID) $iPlaceID = $iParentPlaceID;
}
$sSQL .= ' FROM placex ';
$sSQL .= " WHERE place_id = $iPlaceID";
-$aPointDetails = chksql($oDB->getRow($sSQL), 'Could not get details of place object.');
+$aPointDetails = $oDB->getRow($sSQL, null, 'Could not get details of place object.');
if (!$aPointDetails) {
userError('Unknown place id.');
if ($sOutputFormat=='html') {
$sSQL = "SELECT TO_CHAR(lastimportdate,'YYYY/MM/DD HH24:MI')||' GMT' FROM import_status LIMIT 1";
- $sDataDate = chksql($oDB->getOne($sSQL));
+ $sDataDate = $oDB->getOne($sSQL);
$sTileURL = CONST_Map_Tile_URL;
$sTileAttribution = CONST_Map_Tile_Attribution;
}
$sLanguagePrefArraySQL = $oDB->getArraySQL($oDB->getDBQuotedList($aLangPrefOrder));
if ($sOsmType && $iOsmId > 0) {
- $sPlaceId = chksql($oDB->getOne("select place_id from placex where osm_type = '".$sOsmType."' and osm_id = ".$iOsmId." order by type = 'postcode' asc"));
+ $sPlaceId = $oDB->getOne("select place_id from placex where osm_type = '".$sOsmType."' and osm_id = ".$iOsmId." order by type = 'postcode' asc");
// Be nice about our error messages for broken geometry
if (!$sPlaceId) {
$sSQL .= ' ST_AsText(prevgeometry) as prevgeom, ST_AsText(newgeometry) as newgeom';
$sSQL .= " from import_polygon_error where osm_type = '".$sOsmType;
$sSQL .= "' and osm_id = ".$iOsmId.' order by updated desc limit 1';
- $aPointDetails = chksql($oDB->getRow($sSQL));
+ $aPointDetails = $oDB->getRow($sSQL);
if ($aPointDetails) {
if (preg_match('/\[(-?\d+\.\d+) (-?\d+\.\d+)\]/', $aPointDetails['errormessage'], $aMatches)) {
$aPointDetails['error_x'] = $aMatches[1];
$iPlaceID = (int)$sPlaceId;
if (CONST_Use_US_Tiger_Data) {
- $iParentPlaceID = chksql($oDB->getOne('select parent_place_id from location_property_tiger where place_id = '.$iPlaceID));
+ $iParentPlaceID = $oDB->getOne('select parent_place_id from location_property_tiger where place_id = '.$iPlaceID);
if ($iParentPlaceID) $iPlaceID = $iParentPlaceID;
}
if (CONST_Use_Aux_Location_data) {
- $iParentPlaceID = chksql($oDB->getOne('select parent_place_id from location_property_aux where place_id = '.$iPlaceID));
+ $iParentPlaceID = $oDB->getOne('select parent_place_id from location_property_aux where place_id = '.$iPlaceID);
if ($iParentPlaceID) $iPlaceID = $iParentPlaceID;
}
exit;
}
-$aRelatedPlaceIDs = chksql($oDB->getCol($sSQL = "select place_id from placex where linked_place_id = $iPlaceID or place_id = $iPlaceID"));
+$aRelatedPlaceIDs = $oDB->getCol("select place_id from placex where linked_place_id = $iPlaceID or place_id = $iPlaceID");
$sSQL = 'select obj.place_id, osm_type, osm_id, class, type, housenumber, admin_level,';
$sSQL .= " rank_address, ST_GeometryType(geometry) in ('ST_Polygon','ST_MultiPolygon') as isarea, st_area(geometry) as area, ";
$sSQL .= ' from (select placex.place_id, osm_type, osm_id, class, type, housenumber, admin_level, rank_address, rank_search, geometry, name from placex ';
$sSQL .= ' where parent_place_id in ('.join(',', $aRelatedPlaceIDs).') and name is not null order by rank_address asc,rank_search asc limit 500) as obj';
$sSQL .= ' order by rank_address asc,rank_search asc,localname,class, type,housenumber';
-$aParentOfLines = chksql($oDB->getAll($sSQL));
+$aParentOfLines = $oDB->getAll($sSQL);
if (!empty($aParentOfLines)) {
echo '<h2>Parent Of:</h2>';
$oDB = new Nominatim\DB();
$oDB->connect();
-$iTotalBroken = (int) chksql($oDB->getOne('select count(*) from import_polygon_error'));
+$iTotalBroken = (int) $oDB->getOne('select count(*) from import_polygon_error');
$aPolygons = array();
while ($iTotalBroken && empty($aPolygons)) {
}
$sSQL .= ' order by updated desc limit 1000';
- $aPolygons = chksql($oDB->getAll($sSQL));
+ $aPolygons = $oDB->getAll($sSQL);
}
if (CONST_Debug) {
}
if ($sOutputFormat == 'html') {
- $sDataDate = chksql($oDB->getOne("select TO_CHAR(lastimportdate,'YYYY/MM/DD HH24:MI')||' GMT' from import_status limit 1"));
+ $sDataDate = $oDB->getOne("select TO_CHAR(lastimportdate,'YYYY/MM/DD HH24:MI')||' GMT' from import_status limit 1");
$sTileURL = CONST_Map_Tile_URL;
$sTileAttribution = CONST_Map_Tile_Attribution;
} elseif ($sOutputFormat == 'geocodejson') {
$sQuery = $fLat.','.$fLon;
if (isset($aPlace['place_id'])) {
- $fDistance = chksql($oDB->getOne('SELECT ST_Distance(ST_SetSRID(ST_Point('.$fLon.','.$fLat.'),4326), centroid) FROM placex where place_id='.$aPlace['place_id']));
+ $fDistance = $oDB->getOne(
+ 'SELECT ST_Distance(ST_SetSRID(ST_Point(:lon,:lat),4326), centroid) FROM placex where place_id = :placeid',
+ array(':lon' => $fLon, ':lat' => $fLat, ':placeid' => $aPlace['place_id'])
+ );
}
}
$aSearchResults = $oGeocode->lookup();
if ($sOutputFormat=='html') {
- $sDataDate = chksql($oDB->getOne("select TO_CHAR(lastimportdate,'YYYY/MM/DD HH24:MI')||' GMT' from import_status limit 1"));
+ $sDataDate = $oDB->getOne("select TO_CHAR(lastimportdate,'YYYY/MM/DD HH24:MI')||' GMT' from import_status limit 1");
}
logEnd($oDB, $hLog, count($aSearchResults));
projects / nominatim.git / commitdiff