From: Sarah Hoffmann Date: Thu, 2 May 2019 19:33:36 +0000 (+0200) Subject: Merge remote-tracking branch 'upstream/master' X-Git-Tag: deploy~286 X-Git-Url: https://git.openstreetmap.org/nominatim.git/commitdiff_plain/5f19b23f49aee9989267ac03b488049f9d681166?hp=170426b294955909bf2d56c1ea7bb07b89896675 Merge remote-tracking branch 'upstream/master' --- diff --git a/.travis.yml b/.travis.yml index fe4e0cbd..259b9d5b 100644 --- a/.travis.yml +++ b/.travis.yml @@ -11,6 +11,8 @@ git: env: - TEST_SUITE=tests - TEST_SUITE=monaco +before_install: + - phpenv global 7.1 install: - vagrant/install-on-travis-ci.sh before_script: @@ -19,7 +21,7 @@ script: - cd $TRAVIS_BUILD_DIR/ - if [[ $TEST_SUITE == "tests" ]]; then phpcs --report-width=120 . ; fi - cd $TRAVIS_BUILD_DIR/test/php - - if [[ $TEST_SUITE == "tests" ]]; then phpunit ./ ; fi + - if [[ $TEST_SUITE == "tests" ]]; then /usr/bin/phpunit ./ ; fi - cd $TRAVIS_BUILD_DIR/test/bdd - # behave --format=progress3 api - if [[ $TEST_SUITE == "tests" ]]; then behave -DREMOVE_TEMPLATE=1 --format=progress3 db ; fi diff --git a/CMakeLists.txt b/CMakeLists.txt index b02201fa..fcadffd6 100644 --- a/CMakeLists.txt +++ b/CMakeLists.txt @@ -19,7 +19,7 @@ list(APPEND CMAKE_MODULE_PATH "${CMAKE_SOURCE_DIR}/cmake") project(nominatim) set(NOMINATIM_VERSION_MAJOR 3) -set(NOMINATIM_VERSION_MINOR 2) +set(NOMINATIM_VERSION_MINOR 3) set(NOMINATIM_VERSION_PATCH 0) set(NOMINATIM_VERSION "${NOMINATIM_VERSION_MAJOR}.${NOMINATIM_VERSION_MINOR}.${NOMINATIM_VERSION_PATCH}") diff --git a/ChangeLog b/ChangeLog index 31c5f226..1927b590 100644 --- a/ChangeLog +++ b/ChangeLog @@ -1,3 +1,27 @@ +3.3.0 + + * zoom 17 in reverse now zooms in on minor streets + * fix use of postcode relations in address + * support for housenumber 0 on interpolations + * replace database abstraction DB with PDO and switch to using exceptions + * exclude line features at rank 30 from reverse geocoding + * remove self-reference and country from place_addressline + * make json output more readable (less escaping) + * update conversion scripts for postcodes + * scripts in utils/ are no longer executable (always use scripts in build dir) + * remove Natural Earth country fallback (OSM is complete enough) + * make rank assignments configurable + * allow accept languages with underscore + * new reverse-only import mode (without search index table) + * rely on boundaries only for states and countries + * update osm2pgsql, now using a configurable style + * provide multiple import styles + * improve search when house number and postcodes are dropped + * overhaul of setup code + * add support for PHPUnit 6 + * update test database + * various documentation improvements + 3.2.0 * complete rewrite of reverse search algorithm diff --git a/data-sources/gb-postcodes/README.md b/data-sources/gb-postcodes/README.md index 5e6cef2d..b5a0b9d8 100644 --- a/data-sources/gb-postcodes/README.md +++ b/data-sources/gb-postcodes/README.md @@ -25,41 +25,32 @@ If you forgot to download the file, or have a new version, you can import it sep 2. `unzip codepo_gb.zip` - Unpacked you'll see a directory of CSV files. + Unpacked you'll see a directory of CSV files. - ``` - $ more codepo_gb/Data/CSV/n.csv - "N1 0AA",10,530626,183961,"E92000001","E19000003","E18000007","","E09000019","E05000368" - "N1 0AB",10,530559,183978,"E92000001","E19000003","E18000007","","E09000019","E05000368" - ``` + $ more codepo_gb/Data/CSV/n.csv + "N1 0AA",10,530626,183961,"E92000001","E19000003","E18000007","","E09000019","E05000368" + "N1 0AB",10,530559,183978,"E92000001","E19000003","E18000007","","E09000019","E05000368" - The coordinates are "Northings" and "Eastings" in [OSGB 1936](http://epsg.io/1314) projection. They can be projected to WGS84 like this + The coordinates are "Northings" and "Eastings" in [OSGB 1936](http://epsg.io/1314) projection. They can be projected to WGS84 like this - ``` - SELECT ST_AsText(ST_Transform(ST_SetSRID('POINT(530626 183961)'::geometry,27700), 4326)); - POINT(-0.117872733220225 51.5394424719303) - ``` - [-0.117872733220225 51.5394424719303 on OSM map](https://www.openstreetmap.org/?mlon=-0.117872733220225&mlat=51.5394424719303&zoom=16) + SELECT ST_AsText(ST_Transform(ST_SetSRID('POINT(530626 183961)'::geometry,27700), 4326)); + POINT(-0.117872733220225 51.5394424719303) + + [-0.117872733220225 51.5394424719303 on OSM map](https://www.openstreetmap.org/?mlon=-0.117872733220225&mlat=51.5394424719303&zoom=16) 3. Create database, import CSV files, add geometry column, dump into file - ``` - DBNAME=create_gb_postcode_file - - createdb $DBNAME - echo 'CREATE EXTENSION postgis' | psql $DBNAME - - cat data/gb_postcode_table.sql | psql $DBNAME - - cat codepo_gb/Data/CSV/*.csv | ./data-sources/gb-postcodes/convert_codepoint.php | psql $DBNAME - - cat codepo_gb/Doc/licence.txt | iconv -f iso-8859-1 -t utf-8 | dos2unix | sed 's/^/-- /g' > gb_postcode_data.sql - pg_dump -a -t gb_postcode $DBNAME | grep -v '^--' >> gb_postcode_data.sql - - gzip -9 -f gb_postcode_data.sql - ls -lah gb_postcode_data.* - - # dropdb $DBNAME - ``` + DBNAME=create_gb_postcode_file + createdb $DBNAME + echo 'CREATE EXTENSION postgis' | psql $DBNAME + + cat data/gb_postcode_table.sql | psql $DBNAME + cat codepo_gb/Data/CSV/*.csv | ./data-sources/gb-postcodes/convert_codepoint.php | psql $DBNAME + cat codepo_gb/Doc/licence.txt | iconv -f iso-8859-1 -t utf-8 | dos2unix | sed 's/^/-- /g' > gb_postcode_data.sql + pg_dump -a -t gb_postcode $DBNAME | grep -v '^--' >> gb_postcode_data.sql + + gzip -9 -f gb_postcode_data.sql + ls -lah gb_postcode_data.* + # dropdb $DBNAME diff --git a/docs/admin/Migration.md b/docs/admin/Migration.md index 6ae7a927..0cfe2960 100644 --- a/docs/admin/Migration.md +++ b/docs/admin/Migration.md @@ -7,7 +7,7 @@ SQL statements should be executed from the postgres commandline. Execute `psql nominatim` to enter command line mode. -## 3.2.0 -> master +## 3.2.0 -> 3.3.0 ### New database connection string (DSN) format diff --git a/docs/api/Reverse.md b/docs/api/Reverse.md index 22e01331..82b4e6f3 100644 --- a/docs/api/Reverse.md +++ b/docs/api/Reverse.md @@ -80,7 +80,8 @@ In terms of address details the zoom levels are as follows: 8 | county 10 | city 14 | suburb - 16 | street + 16 | major streets + 17 | major and minor streets 18 | building diff --git a/lib/AddressDetails.php b/lib/AddressDetails.php index 2d40c84f..b62335d1 100644 --- a/lib/AddressDetails.php +++ b/lib/AddressDetails.php @@ -17,7 +17,7 @@ class AddressDetails $mLangPref = 'ARRAY['.join(',', array_map('getDBQuoted', $mLangPref)).']'; } - if (!$sHousenumber) { + if (!isset($sHousenumber)) { $sHousenumber = -1; } diff --git a/lib/DB.php b/lib/DB.php index 17dfe67d..51fd49fc 100644 --- a/lib/DB.php +++ b/lib/DB.php @@ -74,12 +74,7 @@ class DB 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); - } + $stmt = $this->getQueryStatement($sSQL, $aInputVars, $sErrMessage); $row = $stmt->fetch(); } catch (\PDOException $e) { throw new \Nominatim\DatabaseError($sErrMessage, 500, null, $e, $sSQL); @@ -98,12 +93,7 @@ class DB 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); - } + $stmt = $this->getQueryStatement($sSQL, $aInputVars, $sErrMessage); $row = $stmt->fetch(\PDO::FETCH_NUM); if ($row === false) return false; } catch (\PDOException $e) { @@ -123,12 +113,7 @@ class DB 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); - } + $stmt = $this->getQueryStatement($sSQL, $aInputVars, $sErrMessage); $rows = $stmt->fetchAll(); } catch (\PDOException $e) { throw new \Nominatim\DatabaseError($sErrMessage, 500, null, $e, $sSQL); @@ -148,12 +133,8 @@ class DB { $aVals = array(); try { - if (isset($aInputVars)) { - $stmt = $this->connection->prepare($sSQL); - $stmt->execute($aInputVars); - } else { - $stmt = $this->connection->query($sSQL); - } + $stmt = $this->getQueryStatement($sSQL, $aInputVars, $sErrMessage); + while ($val = $stmt->fetchColumn(0)) { // returns first column or false $aVals[] = $val; } @@ -174,12 +155,8 @@ class DB 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); - } + $stmt = $this->getQueryStatement($sSQL, $aInputVars, $sErrMessage); + $aList = array(); while ($aRow = $stmt->fetch(\PDO::FETCH_NUM)) { $aList[$aRow[0]] = $aRow[1]; @@ -190,6 +167,27 @@ class DB return $aList; } + /** + * Executes query. Returns a PDO statement to iterate over. + * + * @param string $sSQL + * + * @return PDOStatement + */ + public function getQueryStatement($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); + } + } catch (\PDOException $e) { + throw new \Nominatim\DatabaseError($sErrMessage, 500, null, $e, $sSQL); + } + return $stmt; + } /** * St. John's Way => 'St. John\'s Way' diff --git a/lib/PlaceLookup.php b/lib/PlaceLookup.php index cf3e4f7b..fce49701 100644 --- a/lib/PlaceLookup.php +++ b/lib/PlaceLookup.php @@ -428,6 +428,7 @@ class PlaceLookup $aPlaces = $this->oDB->getAll($sSQL, null, 'Could not lookup place'); foreach ($aPlaces as &$aPlace) { + $aPlace['importance'] = (float) $aPlace['importance']; if ($this->bAddressDetails) { // to get addressdetails for tiger data, the housenumber is needed $aPlace['address'] = new AddressDetails( diff --git a/lib/setup/SetupClass.php b/lib/setup/SetupClass.php index c14190c3..d3f59296 100755 --- a/lib/setup/SetupClass.php +++ b/lib/setup/SetupClass.php @@ -753,7 +753,10 @@ class SetupFunctions private function pgsqlRunDropAndRestore($sDumpFile) { - $sCMD = 'pg_restore -p '.$this->aDSNInfo['port'].' -d '.$this->aDSNInfo['database'].' -Fc --clean '.$sDumpFile; + $sCMD = 'pg_restore -p '.$this->aDSNInfo['port'].' -d '.$this->aDSNInfo['database'].' --no-owner -Fc --clean '.$sDumpFile; + if ($this->oDB->getPostgresVersion() >= 9.04) { + $sCMD .= ' --if-exists'; + } if (isset($this->aDSNInfo['hostspec'])) { $sCMD .= ' -h '.$this->aDSNInfo['hostspec']; } diff --git a/test/bdd/db/import/interpolation.feature b/test/bdd/db/import/interpolation.feature index 61e0f463..52971295 100644 --- a/test/bdd/db/import/interpolation.feature +++ b/test/bdd/db/import/interpolation.feature @@ -344,3 +344,23 @@ Feature: Import of address interpolations When importing Then W1 expands to no interpolation + Scenario: Two point interpolation starting at 0 + Given the places + | osm | class | type | housenr | geometry | + | N1 | place | house | 0 | 1 1 | + | N2 | place | house | 2 | 1 1.001 | + And the places + | osm | class | type | addr+interpolation | geometry | + | W1 | place | houses | even | 1 1, 1 1.001 | + And the ways + | id | nodes | + | 1 | 1,2 | + When importing + Then W1 expands to interpolation + | start | end | geometry | + | 0 | 2 | 1 1, 1 1.001 | + When sending jsonv2 reverse coordinates 1,1 + Then results contain + | ID | osm_type | osm_id | type | display_name | + | 0 | way | 1 | house | 0 | + diff --git a/utils/export.php b/utils/export.php index 9d3037aa..ef55aab2 100644 --- a/utils/export.php +++ b/utils/export.php @@ -116,10 +116,8 @@ $sOsmId = $aCMDResult['restrict-to-osm-relation']; } if ($sOsmType) { - $sSQL = 'select place_id from placex where'; - $sSQL .= ' osm_type = '.$oDB->getDBQuoted($sOsmType); - $sSQL .= ' and osm_id = '.$sOsmId; - $sParentId = $oDB->getOne($sSQL); + $sSQL = 'select place_id from placex where osm_type = :osm_type and osm_id = :osm_id'; + $sParentId = $oDB->getOne($sSQL, array('osm_type' => $sOsmType, 'osm_id' => $sOsmId)); if (!$sParentId) fail('Could not find place '.$sOsmType.' '.$sOsmId); } if ($sParentId) { @@ -131,15 +129,15 @@ // Iterate over placeids // to get further hierarchical information //var_dump($sPlacexSQL); - $aRes =& $oDB->query($sPlacexSQL); + $oResults = $oDB->getQueryStatement($sPlacexSQL); $fOutstream = fopen('php://output', 'w'); - while ($aRes->fetchInto($aRow)) { - //var_dump($aRow); + while ($aRow = $oResults->fetch()) { + //var_dump($aRow); $iPlaceID = $aRow['place_id']; - $sSQL = "select rank_address,get_name_by_language(name,$sLanguagePrefArraySQL) as localname from get_addressdata($iPlaceID, -1)"; + $sSQL = "select rank_address,get_name_by_language(name,$sLanguagePrefArraySQL) as localname from get_addressdata(:place_id, -1)"; $sSQL .= ' WHERE isaddress'; $sSQL .= ' order by rank_address desc,isaddress desc'; - $aAddressLines = $oDB->getAll($sSQL); + $aAddressLines = $oDB->getAll($sSQL, array('place_id' => $iPlaceID)); $aOutput = array_fill(0, $iNumCol, ''); // output address parts @@ -154,9 +152,9 @@ $sSQL = 'select array_agg(px.postcode) from placex px join place_addressline pa '; $sSQL .= 'on px.place_id = pa.address_place_id '; $sSQL .= 'where pa.cached_rank_address in (5,11) '; - $sSQL .= 'and pa.place_id in (select place_id from place_addressline where address_place_id in ('.substr($aRow['place_ids'], 1, -1).')) '; + $sSQL .= 'and pa.place_id in (select place_id from place_addressline where address_place_id in (:first_place_id)) '; $sSQL .= 'group by postcode order by count(*) desc limit 1'; - $sRes = $oDB->getOne($sSQL); + $sRes = $oDB->getOne($sSQL, array('first_place_id' => substr($aRow['place_ids'], 1, -1))); $aOutput[$aColumnMapping['postcode']] = substr($sRes, 1, -1); } else { diff --git a/vagrant/install-on-travis-ci.sh b/vagrant/install-on-travis-ci.sh index fa2a43e0..229a7ef1 100755 --- a/vagrant/install-on-travis-ci.sh +++ b/vagrant/install-on-travis-ci.sh @@ -26,6 +26,9 @@ pip3 install --quiet behave nose pytidylib psycopg2-binary composer global require "squizlabs/php_codesniffer=*" sudo ln -s /home/travis/.config/composer/vendor/bin/phpcs /usr/bin/ +composer global require "phpunit/phpunit=7.*" +sudo ln -s /home/travis/.config/composer/vendor/bin/phpunit /usr/bin/ + sudo -u postgres createuser -S www-data # Make sure that system servers can read from the home directory: