Merge pull request #1502 from mtmail/specialphrases-quotes

[nominatim.git] / utils / export.php

diff --git a/utils/export.php b/utils/export.php
old mode 100755 (executable)
new mode 100644 (file)
index 2175024..ef55aab
--- a/utils/export.php
+++ b/utils/export.php
@@ -1,10 +1,8 @@
-#!/usr/bin/php -Cq

 <?php
     // Script to extract structured city and street data
     // from a running nominatim instance as CSV data
 
 
 <?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');
     require_once(CONST_BasePath.'/lib/init-cmd.php');
     require_once(CONST_BasePath.'/lib/ParameterParser.php');
     ini_set('memory_limit', '800M');


@@ -43,7 +41,8 @@
                  'path' => 27
                 );
 
                  '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']);
 
     if (isset($aCMDResult['output-type'])) {
         if (!isset($aRankmap[$aCMDResult['output-type']])) fail('unknown output-type: '.$aCMDResult['output-type']);


@@ -57,7 +56,7 @@
     $oParams = new Nominatim\ParameterParser();
     if (!isset($aCMDResult['language'])) $aCMDResult['language'] = 'xx';
     $aLangPrefOrder = $oParams->getPreferredLanguages($aCMDResult['language']);
     $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();
 
     // output formatting: build up a lookup table that maps address ranks to columns
     $aColumnMapping = array();


@@ -84,14 +83,20 @@
     $sPlacexSQL = 'select min(place_id) as place_id, ';
     $sPlacexSQL .= 'array_agg(place_id) as place_ids, ';
     $sPlacexSQL .= 'country_code as cc, ';
     $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
     // 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 .= ' 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
     }
 
     // restriction to parent place id


@@ -111,34 +116,28 @@
         $sOsmId = $aCMDResult['restrict-to-osm-relation'];
     }
     if ($sOsmType) {
         $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)';
     }
 
         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);
 
     // 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');
     $fOutstream = fopen('php://output', 'w');

-    while ($aRes->fetchInto($aRow)) {
-    //var_dump($aRow);
+    while ($aRow = $oResults->fetch()) {
+        //var_dump($aRow);

         $iPlaceID = $aRow['place_id'];
         $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';
         $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
 
         $aOutput = array_fill(0, $iNumCol, '');
         // output address parts


@@ -151,25 +150,20 @@
         if (isset($aColumnMapping['postcode'])) {
             if ($aCMDResult['output-all-postcodes']) {
                 $sSQL = 'select array_agg(px.postcode) from placex px join place_addressline pa ';
         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']] = 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);
             }
         }
         if (isset($aColumnMapping['placeid'])) {
             $aOutput[$aColumnMapping['placeid']] = substr($aRow['place_ids'], 1, -1);
         }
         fputcsv($fOutstream, $aOutput);

-

     }
     fclose($fOutstream);
     }
     fclose($fOutstream);