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

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

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

[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]

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

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

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.

[php]

Option Explicit

Sub createInsertSql()

Dim newbook As Workbook

Dim currentCell As Range

‘Pretreatment.

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

Else

head = head & ","

End If

Set currentCell = srcSheet.Cells(1, currentColumnIndex)

head = head & currentCell.Value

next

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

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]

This is so convenient!

Now we’ve successfully stored the data in MySQL!

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

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