MySQL+PHP+Google Map API, Google Script to create a map of « The most beautiful villages in France

googleThe most beautiful villages of France

I made a map of the « Most Beautiful Villages in France » that I have visited so far.

フランスの最も美しい村巡りMAP yuu-koma

On the right side is a list of the villages we visited, sorted by overall rating. Click on each village to see the

In this way, you can see the actual photos taken there, your own blog post, and a hyperlink to the official website.

I’ve traveled quite extensively, by the way.

MySQL + PHP + Google Maps API.

How did you do it?

I went through a lot of processes to make it, but I basically based it on the method described on this site.

It requires some knowledge of MySQL, php, xml, and Google Map API, but I think even a novice can build it if you manage to do your best.

The biggest hurdle is uploading data to a MySQL table

Based on the site introduced above, it was not so difficult to read location information from MySQL table and display the map using Google Map API.

But the problem is data, I had a hard time creating a data table for a beautiful village.

This was dealt with in the following way.

Get the location of beautiful villages using Google Script.

It was a particular bottleneck for me.


Of course, there is also a way to manually search for each location using Google Maps, but this is quite a painstaking task when you have more than 100 locations.

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

Create a spreadsheet as shown below, and create a list of target villages in column A.

Then write a Google Script that looks like this


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").


var geo = geocode.results[0];

var geodata = geo.geometry;







In this way, the latitude and longitude of each village will be output one after another in columns B and C.

By the way, you can learn more about Google Script at the following site

Googleマップを利用する(1/8):Google Apps Scriptプログラミング [中級編]

And the following books.

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

Kiyoshi Hayasaka


I referred to the

A « département » to make sure you can find it.

The list of village names in the Google Spreadsheet I mentioned above, but if you look closely

Eus, Pyrénées Orientales, France

each of which contains the name of a « French department (département) ».

(ご参考:Liste des départements français – Wikipédia

In fact, if you search only for the name of the most beautiful village in France, you will often get no results. However, if you add the name of the province or even the country (France) to your search, you will almost certainly get a result.

It is the name of the prefecture of each village, but the list of beautiful villages on the official site, the

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

You can find out from

If you copy this page and process the text appropriately, you can complete the list of villages with prefecture names.

By doing this, we were able to successfully get the location information of each village using the aforementioned Google Script.

Create an SQL INSERT statement from Excel using a macro

We’re almost there.

By the above process, we were able to create such a data table in Excel.

The question is how to upload this data to the MySQL table.

For this, I used the following useful macro.

ExcelからSQLのINSERT文を作成するマクロ – grachroブログ


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

‘The first half of the INSERT statement

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


head = head & ","

End If

Set currentCell = srcSheet.Cells(1, currentColumnIndex)

head = head & currentCell.Value


head = head & ") "

‘Create New Book

Set newbook = Workbooks.Add

‘After values in INSERT statement

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


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


sql = sql & "’" & currentCell.Value & "’"

End If


sql = sql & ");"

newbook.ActiveSheet.Cells(currentRowIndex – 1, 1).Value = sql


End Sub


This is so convenient!

Now we’ve successfully stored the data in MySQL!

That’s it for my review of how to make it.