-#!/usr/bin/php -Cq
<?php
// Script to extract structured city and street data
// from a running nominatim instance as CSV data
- require_once(dirname(dirname(__FILE__)).'/settings/settings.php');
require_once(CONST_BasePath.'/lib/init-cmd.php');
require_once(CONST_BasePath.'/lib/ParameterParser.php');
ini_set('memory_limit', '800M');
'path' => 27
);
- $oDB =& getDB();
+ $oDB = new Nominatim\DB();
+ $oDB->connect();
if (isset($aCMDResult['output-type'])) {
if (!isset($aRankmap[$aCMDResult['output-type']])) fail('unknown output-type: '.$aCMDResult['output-type']);
$oParams = new Nominatim\ParameterParser();
if (!isset($aCMDResult['language'])) $aCMDResult['language'] = 'xx';
$aLangPrefOrder = $oParams->getPreferredLanguages($aCMDResult['language']);
- $sLanguagePrefArraySQL = 'ARRAY['.join(',', array_map('getDBQuoted', $aLangPrefOrder)).']';
+ $sLanguagePrefArraySQL = $oDB->getArraySQL($oDB->getDBQuotedList($aLangPrefOrder));
// output formatting: build up a lookup table that maps address ranks to columns
$aColumnMapping = array();
$sPlacexSQL = 'select min(place_id) as place_id, ';
$sPlacexSQL .= 'array_agg(place_id) as place_ids, ';
$sPlacexSQL .= 'country_code as cc, ';
+ $sPlacexSQL .= 'postcode, ';
// get the address places excluding postcodes
- $sPlacexSQL .= 'array(select address_place_id from place_addressline a where a.place_id = placex.place_id and isaddress and address_place_id != placex.place_id and not cached_rank_address in (5,11) and cached_rank_address > 2 order by cached_rank_address) as address';
+ $sPlacexSQL .= 'array(select address_place_id from place_addressline a';
+ $sPlacexSQL .= ' where a.place_id = placex.place_id and isaddress';
+ $sPlacexSQL .= ' and address_place_id != placex.place_id';
+ $sPlacexSQL .= ' and not cached_rank_address in (5,11)';
+ $sPlacexSQL .= ' and cached_rank_address > 2 order by cached_rank_address)';
+ $sPlacexSQL .= ' as address';
$sPlacexSQL .= ' from placex where name is not null and linked_place_id is null';
$sPlacexSQL .= ' and rank_address = '.$iOutputRank;
if (isset($aCMDResult['restrict-to-country'])) {
- $sPlacexSQL .= ' and country_code = '.getDBQuoted($aCMDResult['restrict-to-country']);
+ $sPlacexSQL .= ' and country_code = '.$oDB->getDBQuoted($aCMDResult['restrict-to-country']);
}
// restriction to parent place id
$sOsmId = $aCMDResult['restrict-to-osm-relation'];
}
if ($sOsmType) {
- $sSQL = 'select place_id from placex where';
- $sSQL .= ' osm_type = '.getDBQuoted($sOsmType);
- $sSQL .= ' and osm_id = '.$sOsmId;
- $sParentId = $oDB->getOne($sSQL);
- if (PEAR::isError($sParentId)) fail(pg_last_error($oDB->connection));
+ $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) {
$sPlacexSQL .= ' and place_id in (select place_id from place_addressline where address_place_id = '.$sParentId.' and isaddress)';
}
- $sPlacexSQL .= " group by name->'name', address, country_code, placex.place_id";
+ $sPlacexSQL .= " group by name->'name', address, postcode, country_code, placex.place_id";
// Iterate over placeids
// to get further hierarchical information
//var_dump($sPlacexSQL);
- $aRes =& $oDB->query($sPlacexSQL);
- if (PEAR::isError($aRes)) fail(pg_last_error($oDB->connection));
+ $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);
- if (PEAR::IsError($aAddressLines)) fail(pg_last_error($oDB->connection));
-
+ $aAddressLines = $oDB->getAll($sSQL, array('place_id' => $iPlaceID));
$aOutput = array_fill(0, $iNumCol, '');
// output address parts
if (isset($aColumnMapping['postcode'])) {
if ($aCMDResult['output-all-postcodes']) {
$sSQL = 'select array_agg(px.postcode) from placex px join place_addressline pa ';
- } else {
- $sSQL = 'select 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 .= 'group by postcode order by count(*) desc limit 1';
- $sRes = $oDB->getOne($sSQL);
- if (PEAR::IsError($sRes)) fail(pg_last_error($oDB->connection));
- if ($aCMDResult['output-all-postcodes']) {
+ $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 (:first_place_id)) ';
+ $sSQL .= 'group by postcode order by count(*) desc limit 1';
+ $sRes = $oDB->getOne($sSQL, array('first_place_id' => substr($aRow['place_ids'], 1, -1)));
+
$aOutput[$aColumnMapping['postcode']] = substr($sRes, 1, -1);
} else {
- $aOutput[$aColumnMapping['postcode']] = $sRes;
+ $aOutput[$aColumnMapping['postcode']] = $aRow['postcode'];
}
}
if (isset($aColumnMapping['placeid'])) {
$aOutput[$aColumnMapping['placeid']] = substr($aRow['place_ids'], 1, -1);
}
fputcsv($fOutstream, $aOutput);
-
}
fclose($fOutstream);
projects / nominatim.git / blobdiff