From: Sarah Hoffmann Date: Sat, 25 Aug 2012 20:03:23 +0000 (+0200) Subject: initial version of an export script X-Git-Tag: v3.2.0~65 X-Git-Url: https://git.openstreetmap.org./nominatim.git/commitdiff_plain/dfb9579a73ea100ff0b0e1a629a52a01c9dcb1fb initial version of an export script So far supports type selection down to street level, restriction to country or an OSM place and postcode printing. Output is standard CSV. --- diff --git a/utils/export.php b/utils/export.php new file mode 100755 index 00000000..4984b6b6 --- /dev/null +++ b/utils/export.php @@ -0,0 +1,196 @@ +#!/usr/bin/php -Cq + 1, + 'country' => 4, + 'state' => 8, + 'county' => 12, + 'city' => 16, + 'suburb' => 20, + 'street' => 26, + 'path' => 27 + ); + + $oDB =& getDB(); + + if (isset($aCMDResult['output-type'])) + { + if (!isset($aRankmap[$aCMDResult['output-type']])) fail('unknown output-type: '.$aCMDResult['output-type']); + $iOutputRank = $aRankmap[$aCMDResult['output-type']]; + } + else + { + $iOutputRank = $aRankmap['street']; + } + + + // Preferred language + if (!isset($aCMDResult['language'])) $aCMDResult['language'] = 'xx'; + $aLangPrefOrder = getPreferredLanguages($aCMDResult['language']); + $sLanguagePrefArraySQL = "ARRAY[".join(',',array_map("getDBQuoted",$aLangPrefOrder))."]"; + + // output formatting: build up a lookup table that maps address ranks to columns + $aColumnMapping = array(); + $iNumCol = 0; + If (!isset($aCMDResult['output-format'])) $aCMDResult['output-format'] = 'street;suburb;city;county;state;country'; + foreach (preg_split('/\s*;\s*/',$aCMDResult['output-format']) as $sColumn) + { + $bHasData = false; + foreach (preg_split('/\s*,\s*/', $sColumn) as $sRank) + { + if ($sRank == 'postcode' || $sRank == 'placeid') + { + $aColumnMapping[$sRank] = $iNumCol; + $bHasData = true; + } + elseif (isset($aRankmap[$sRank])) + { + $iRank = $aRankmap[$sRank]; + if ($iRank <= $iOutputRank) { + $aColumnMapping[(string)$iRank] = $iNumCol; + $bHasData = true; + } + } + } + if ($bHasData) $iNumCol++; + } + + // build the query for objects + $sPlacexSQL = 'select min(place_id) as place_id, '; + $sPlacexSQL .= 'array_agg(place_id) as place_ids, '; + $sPlacexSQL .= 'calculated_country_code as cc, '; + // 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 .= " 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 calculated_country_code = '.getDBQuoted($aCMDResult['restrict-to-country']); + } + + // restriction to parent place id + $sParentId = false; + $sOsmType = false; + + if (isset($aCMDResult['restrict-to-osm-node'])) + { + $sOsmType = 'N'; + $sOsmId = $aCMDResult['restrict-to-osm-node']; + } + if (isset($aCMDResult['restrict-to-osm-way'])) + { + $sOsmType = 'W'; + $sOsmId = $aCMDResult['restrict-to-osm-way']; + } + if (isset($aCMDResult['restrict-to-osm-relation'])) + { + $sOsmType = 'R'; + $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)); + 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, calculated_country_code"; + + # 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)); + $fOutstream = fopen("php://output", 'w'); + while ($aRes->fetchInto($aRow)) + { + //var_dump($aRow); + $iPlaceID = $aRow['place_id']; + $sSQL = "select rank_address,get_name_by_language(name,$sLanguagePrefArraySQL) as localname from get_addressdata($iPlaceID)"; + $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)); + + + $aOutput = array_fill(0, $iNumCol, ''); + # output address parts + foreach ($aAddressLines as $aAddress) + { + if (isset($aColumnMapping[$aAddress['rank_address']])) + { + $aOutput[$aColumnMapping[$aAddress['rank_address']]] = $aAddress['localname']; + } + } + # output postcode + 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']) + { + $aOutput[$aColumnMapping['postcode']] = substr($sRes, 1, -1); + } + else + { + $aOutput[$aColumnMapping['postcode']] = $sRes; + } + } + if (isset($aColumnMapping['placeid'])) + { + $aOutput[$aColumnMapping['placeid']] = substr($aRow['place_ids'], 1, -1); + } + fputcsv($fOutstream, $aOutput); + + } + fclose($fOutstream);