MySQL+PHP+Google Map API, Google Script で「フランスの最も美しい村」マップを作成

これまでに訪問した「フランスの最も美しい村」マップを作ってみました。

右側に訪れた村のリストを総合評価順で並べてあります。それぞれの村をクリックすると、

このように実際に現地で撮影した写真と自分のblog記事、そして公式サイトへのハイパーリンクが表示されます。

それにしてもかなり広範囲にわたって旅したんだなーっとじみじみ。

MySQL + PHP + Google Maps APIを利用

どうやって作ったのか？

作るのにいろんな過程を経ていますが、基本はこちらのサイトで紹介されていた方法をもとに作成しました。

MySQLとかphpとかxml、そしてGoogle Map APIの知識が多少要りますが、なんとか頑張れば素人でも組み込めるレベルだと思います。

一番のハードルはMySQL テーブルへのデータアップロード

上記紹介のサイトをもとにして、MySQLのテーブルから位置情報を読込んでGoogle Map APIを利用して地図を表示させることはそれほど苦労はしなかったです。

しかし問題はデータ、美しい村のデータテーブルの作成に一苦労しました。

これについては以下に述べる方法で対処しました。

Google Scriptを利用して美しい村の位置情報を取得

特にネックになったのは、

美しい村の「緯度経度」情報の取得

もちろんひとつひとつ手作業でGoogle Mapで検索して調べるという方法もありますが、100箇所以上となるとかなり骨の折れる作業。

そこで利用したのが「Google Script」です。

以下のように適当にスプレッドシートを作成、A列に対象となる村のリストを作成します。

そして、次のようなGoogle Scriptを書きます。

function getLatLng() {
  var mySS = SpreadsheetApp.getActiveSpreadsheet();
  var mySheet = mySS.getActiveSheet();
  var i = 2;
  while (mySheet.getRange(i,1).getValue() != ""){
    var targetBVname = mySheet.getRange(i,1).getValue();
    var geocode = Maps.newGeocoder().setRegion("FR").
    　　setLanguage("fr").geocode(targetBVname);
    var geo = geocode.results[0];
    var geodata = geo.geometry;
    mySheet.getRange(i,2).setValue(geodata.location.lat);
    mySheet.getRange(i,3).setValue(geodata.location.lng);
    i++;
  }
}

こうするとB列、C列にそれぞれの村の緯度経度が次々と出力されていきます。

ちなみにGoogle Scriptに関しては、以下のサイト、

そして以下の本、

Google クラウドスクリプティング Google Apps ScriptによるGoogleパワーアップ活用ガイド
早坂 清志

を参考にしました。

確実に検索できるようにするための”県 (département)”

上で紹介したGoogleスプレッドーシートの村名リストですが、よくみてみると、

のように、それぞれ「フランスの県 (département)」の名前が入っています。
（ご参考：Liste des départements français – Wikipédia）

実はフランスの最も美しい村、村の名前だけで検索しても検索結果が出ないことがよくあります。しかし、県名さらには国名（France）をあわせて検索ワードとすると、ほぼ確実に検索結果を出すことができます。

それぞれの村の県名ですが、公式サイトの美しい村リスト、

List of The Most Beautiful Villages of France | Les plus beaux villages de France – Site officiel

から調べることができます。

このページのコピーしてテキストを適当に加工すれば、県名入り村リストは完成します。

こうすることで、無事に前述のGoogle Scriptを利用して、それぞれの村の位置情報を取得することができました。

マクロを利用してExcelからSQLのINSERT文を作成する

後もう少しです。

以上までの過程でこのようなデータテーブルをExcel上で作ることができました。

問題はこのデータをどうやってMySQLのテーブルにアップロードするかです。

これ関しては、以下の便利なマクロを利用させていただきました。

Option Explicit

Sub createInsertSql()
    Dim newbook As Workbook
    Dim currentCell As Range
    
    '前処理
    Dim srcSheet As Worksheet
    Set srcSheet = ActiveSheet
    
    Dim targetRange As Range
    Set targetRange = srcSheet.UsedRange


    'INSERT文の前半
    Dim head As String
    head = "REPLACE INTO " & srcSheet.Name & " ("
    
    Dim first As Boolean
    first = True
    
    Dim currentColumnIndex As Integer
    For currentColumnIndex = 1 To targetRange.Columns.Count
        If (first) Then
            first = False
        Else
            head = head & ","
        End If
        Set currentCell = srcSheet.Cells(1, currentColumnIndex)
        head = head & currentCell.Value
    Next
    head = head & ") "
    

    '新しいBook作成
    Set newbook = Workbooks.Add
    
    'INSERT文のvalues以降
    Dim currentRowIndex As Integer
    For currentRowIndex = 2 To targetRange.Rows.Count
        
        Dim sql As String
        sql = head & "values ("
        first = True
    
        For currentColumnIndex = 1 To targetRange.Columns.Count
            If (first) Then
                first = False
            Else
                sql = sql & ","
            End If
            Set currentCell = srcSheet.Cells(currentRowIndex, currentColumnIndex)
            If IsNull(currentCell) Or Trim(currentCell.Value) = "" Then
                sql = sql & "null"
            ElseIf IsNumeric(currentCell.Value) Then
                sql = sql & currentCell.Value
            Else
                sql = sql & "'" & currentCell.Value & "'"
            End If
        Next
        
        sql = sql & ");"
        
        newbook.ActiveSheet.Cells(currentRowIndex - 1, 1).Value = sql
    Next
End Sub

これ、とっても便利！

これで無事MySQLにデータを格納することができました！

作り方レビューは以上です。

この地図は美しい村だけでなく、他の旅行記にも応用してみたいですね。