himorogiの日記

主にプログラミングに関することなど。少々ハード（電子工作）についても。

PowerShell で GoogleStreetView Image を取得

仕事で GoogleStreetView Image を大量に取得する必要があり、PowerShell のスクリプトを作成した。
スクリプトで一括して取得するため StreetView Image は以下の条件とした。

  • 一つの場所につき、真北（0度）、真東（90度）、真南（180度）、真西（270度）の４つの方位角で StreetView Image を取得
  • いかなる場所も size（画素数）600x300、600fov（水平視野角）100度、pitch（俯角）10度

また、UI を簡潔にするため

  1. StreetView の撮影ポイントは、ID（ファイル名のベースとなる）、経度、緯度の値を、Unicode Text（UTF-16LE、Tab区切り、拡張子 .txt）のテキストファイルとして与える
  2. 上記テキストファイルは、スクリプトファイルと同じフォルダのサブフォルダ PointList に置かれるものとする
  3. 上記テキストファイルでは、先頭行は見出し、もしくは空行とする（スクリプトは読み飛ばす）
  4. 実行結果（テキストファイルの1レコードの情報に対して、４つの StreetView Image 取得）は、スクリプトファイルと同じフォルダのサブフォルダ Result に保存される

以上とした。

<#
    getStreetViewAtNEWS.ps1
    
    準備
    このスクリプトファイルと同じフォルダの子フォルダ：pointList に、
    1列目 pointId、2列目 latitude、3列目 longitude を記載し UnicodeText（UTF-16LE ）として保存する
    ※　Excel から保存する場合は、ファイル形式 UnicodeText を指定すると UTF-16LE で保存される
    ※　スクリプトからは一行目を無視するので、見出し行が無い場合は先頭に改行コードを置くこと
    
    実行結果
    このスクリプトファイルと同じフォルダの子フォルダ：result に、
    pointList.txt の id,lon の位置から東西南北の street view image を取得してそれぞれ
    pointId_[EWNS].jpg ファイルが保存される。
    
#>

function getStreetViewNEWS( $fileName, $lat, $lon ){
    function getAStreetView($head){
        $dstFolder = join-path $pwd result
        switch($head){
            0{ $fp = join-path $dstFolder $($fileName + "_N.jpg");break }
            90{ $fp = join-path $dstFolder $($fileName + "_E.jpg");break }
            180{ $fp = join-path $dstFolder $($fileName + "_S.jpg");break }
            default{ $fp = join-path $dstFolder $($fileName + "_W.jpg") }
        }
        if( test-path $fp){ remove-item $fp }
        $fov = 100; $pitch =10
        $url = "https://maps.googleapis.com/maps/api/streetview?size=600x300&location=$lat,$lon&fov=$fov&heading=$head&pitch=$pitch"
        [System.Net.ServicePointManager]::ServerCertificateValidationCallback = { $true }
        (New-Object System.Net.WebClient).DownloadFile($url, $fp) #>
        <# forDebug # write-host "$url`n$fp`n" #>
        $now = get-date -format g ;write-host "$now`t【処理開始】`t$fileName"
        start-sleep 1
        
    } # end of function
    (0,90,180,270)|%{ getAStreetView($_)}
}

$dummy = chcp 65001
$ScriptBase = Split-Path $MyInvocation.MyCommand.Path -parent
pushd $ScriptBase
    $xluniTxt = join-path $(join-path $pwd pointList ) pointList.txt
    $queue = gc $xluniTxt | ConvertFrom-CSV -delimiter "	" -header pointId,lat,lon | select -skip 1
     <# forDebug # $queue #>
    $queue | % { getStreetViewNEWS $_.pointId $_.lat $_.lon }
    $now = get-date -format g ;write-host "$now`t【処理完了】"
popd

Excel Workbook から SQLite データベースを構築するためのツールを PowerShell で整備中。

PowerShell SQLite

最初は try{}catch{} でエラー処理てんこ盛りにしようと思っていた。だけど PowerShell ISE 上でスクリプトを走らせるなら、余計な処理せずに ISE のエラーメッセージを素直に見てた方が状況把握しやすいのでエラー処理は省いた。

Unicode text ファイルを SQLIte デフォルトの text ファイルに変換する PowerShell スクリプト

Excel の Unicode text 出力は BOM が付くので SQLite からは読み込めない（はず）。
どうせ BOM 外しに変換するなら、ついでに encoding と separator も変更することにした。
このスクリプトは、chooseCSVFileで選択したtextファイルと同じフォルダにある全てのtextファイルをUTF8 BOM無し、かつファイル中のtabを'|'に全置換する。
unicode2UTF8nonBOM と chooseCSVFile は .psm1 としてそのうちモジュール化したい。

# toolUniTxt2SQLtxt.ps1

# Excel unicode text to SQLite default text
function unicode2UTF8nonBOM{
    param( $xlsU, $sqltDefault )
    $sw = New-Object System.IO.StreamWriter $sqltDefault, $( New-Object System.Text.UTF8Encoding($False) )
    gc $xlsU -encoding Unicode | % {  $sw.WriteLine( $_.replace( "`t","|" ) ) }
    $sw.close()
}

# choose of text file
# File 選択 Dialog が開き（ShowDialog）"OK" ボタンが押された場合：選択された filePath、そうでない場合：nullstring が返る
$chooseCSVFile = {
    # Sytem.Windows.Forms 参照 :: File 選択 Dialog の御膳立て
    Add-Type -Assembly System.Windows.Forms
    # File 選択 Dialog を参照
    $dlg = New-Object System.Windows.Forms.OpenFileDialog
    $dlg.Filter = "Text(*.csv;*.txt)|*.csv;*.txt"
    $dlg.InitialDirectory = "."
    $dlg.Title = "ファイル選択"
    return $( if($dlg.ShowDialog() -eq "OK"){ $dlg.FileNames }else{ "" }　)	
}

$ScriptBase = Split-Path ( & { $myInvocation.ScriptName } ) -parent
pushd $ScriptBase

$choosePath = &$chooseCSVFile
$chooseParent = split-path $choosePath -parent
gci $chooseParent | ? { $_.name -match ".txt" } | % {
    $srcPath = join-path $chooseParent $_.name; $srcPath
    $dstPath = join-path $chooseParent $([regex]::replace($_.name,".txt$",".sqltxt")); # $dstPath
    unicode2UTF8nonBOM -xlsU $srcPath -sqltDefault $dstPath
}
popd

PowerShell から Excel を操作して Workbook を Unicode text として保存するスクリプト

このスクリプトは、chooseExcelFileで選択したExcelWorkbookと同じフォルダにある全てのExcelWorkbookをUnicode textファイルに変換する。
xls2UnicodeTxt と chooseExcelFile は .psm1 としてそのうちモジュール化したい。

# toolXls2UnicodeTxt.ps1

# handling for Excel Workbook
function xls2UnicodeTxt{
    param( $xlsW, $xlsU )
    # Excel の機能を参照 :: Excel File を参照するための御膳立て
    $oXl = New-Object -ComObject Excel.Application
    # File 選択 Dialog で選択されたワークブックを開く
    $oBk = $oXl.Workbooks.Open( $xlsW )
    # 単一ワークシートのワークブックを想定している
    $oSh = $oBk.sheets.item(1)
    # SaveAs で確認Dialog が開かないようにする
    $oXl.displayAlerts = $false
    # Excel 定数 xlUnicodeText : 42 / CSV を指定するときはこちら xlCSV : 6 
    $oBk.SaveAs( $xlsU, 42 )
    $oBk.Close()
    $oXl.quit()
    [System.Runtime.InteropServices.Marshal]::FinalReleaseComObject($oXl) | Out-Null
}

# choose of ExcelWorkbook
# File 選択 Dialog が開き（ShowDialog） "OK" ボタンが押された場合：選択された filePath、そうでない場合：nullstring が返る
$chooseExcelFile = {
    # Sytem.Windows.Forms 参照 :: File 選択 Dialog の御膳立て
    Add-Type -Assembly System.Windows.Forms
    # File 選択 Dialog を参照
    $dlg = New-Object System.Windows.Forms.OpenFileDialog
    $dlg.Filter = "Excel(*.xls;*.xlsx;*.xlsm)|*.xls;*.xlsx;*.xlsm"
    $dlg.InitialDirectory = "."
    $dlg.Title = "ファイル選択"
    return $( if($dlg.ShowDialog() -eq "OK"){ $dlg.FileNames }else{ "" }　)	
}

$ScriptBase = Split-Path ( & { $myInvocation.ScriptName } ) -parent
pushd $ScriptBase

$choosePath = &$chooseExcelFile
$chooseParent = split-path $choosePath -parent
gci $chooseParent | ? { $_.name -match ".xls" } | % {
    $srcPath = join-path $chooseParent $_.name; $srcPath
    $dstPath = join-path $chooseParent $([regex]::replace($_.name,".xls.$|.xls$",".txt")); # $dstPath
    xls2UnicodeTxt -xlsW $srcPath -xlsU $dstPath
}
popd

MS Access では扱えない 255 以上のカラム数のテーブルを SQLite を使って管理することになった。

PowerShell SQLite

データは Excel ファイルで展開されてくるため、Excel のファイルを SQLite で取り込めるように変換する手順をまとめた。
ただExcelが吐き出すテキストファイルフォーマットは、データ透過性が保証されるものが一つもないため、本来ならデータ交換に推奨できる代物ではない。
だからといってXMLやJSONでデータ交換やろうとすると、社内調整がとても面倒なので。

PowerShellからExcelが出力したUnicodeテキストをUFT8 BOM無し '|' 区切りに変換

SQLiteデフォルト設定のインポートテキスト形式として保存する方法

SQLite のエンコーディングと区切り文字をデフォルトから変更しない場合は、ExcelからUnicodeテキストとして保存されたファイルを、さらにUTF8 BOM無し'|' 区切りに変更する必要がある。
UTF8 BOM無しの定数が無いので、エンコーディング指定のパラメータは以下のようにして渡す。

$( New-Object System.Text.UTF8Encoding($False) )

エンコーディングの変更と同時に、区切り文字の置換を行う必要があるので、StreamWiriteを使って書き出す。

$sw = New-Object System.IO.StreamWriter $sqlitePath, $( New-Object System.Text.UTF8Encoding($False) )  
gc $xlsPath -encoding Unicode | % { $sw.WriteLine( $_.replace( "`t","|" ) ) }
$sw.close()

PowerShell から ExcelWorkBook を テキストとして保存

PowerShell から Excel.Application オブジェクトを開き、WorkBook を Unicode テキストとして保存する

SQLite のデフォルトエンコーディングは UTF8 なので、以下のように

PRAGMA encoding = "UFT-16le"

エンコーディングをUnicode(LittelEndian)に変更（ATTACH DATABASE する前でのみ可能）し、区切り文字も

.saparetor "\t"

に変更すれば、Excel のUnicode テキスト(*.txt)出力をそのままSQLiteから .import により読み込める[はず※未検証]。

# Excel の機能を参照 :: Excel File を参照するための御膳立て
$oXl = New-Object -ComObject Excel.Application
# File 選択 Dialog で選択されたワークブックを開く
$oBk = $oXl.Workbooks.Open( $xlsW )
# 単一ワークシートの場合
$oSh = $oBk.sheets.item(1)
# SaveAs で確認Dialog が開かないようにする
$oXl.displayAlerts = $false
#Unicodeテキストとして保存(xlUnicodeText : 42 / xlCSV : 6)
$oBk.SaveAs( $xlsU, 42 )
$oBk.Close()
$oXl.quit()
[System.Runtime.InteropServices.Marshal]::FinalReleaseComObject($oXl) | Out-Null