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を書きます。

[php]

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++;

}

}

[/php]

こうすると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のテーブルにアップロードするかです。

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

[php]

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

[/php]

これ、とっても便利！

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

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

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