ここではGoogleスプレッドシートを使ってGoogleマイマップを作る方法を説明します。GoogleスプレッドシートはGoogleマイマップとの相性が良くまた、大部分の操作をWeb上で行うことができます。なおDITSダウンロードには1500ったーを使います。
(ヒント1) Excelを使う時は1500ったーからダウンロードする時の文字コードをSJISにします。
(ヒント2) DITSダウンロードにWeb Tweet Crawlerも使えます。ただ日時がグリニッジ標準時なので変換が必要です。
手順
1500ったーの検索欄に検索キーワードを入れDITSデータを探します。ここでは「#トコラボDITS 東京都」とします。「#トコラボDITS」と「東京都」は半角スペースで区切ります。ここでは検索数を10とします。
(ヒント1) 実際の地図作成では検索数を100、500など選択して下さい。
(ヒント2) 神奈川県、東京都をダウンロード時は「#トコラボDITS 神奈川県 OR 東京都」とします。
(ヒント3) 狭い地域は「#トコラボDITS 瀬谷区三ツ境」など住所を詳細にして検索します。
CSV保存(UTF-8)をクリックすると10件のDITSデータが常にファイル名1500ttr.csvでパソコンにダウンロードされます。
クリックで拡大
ダウンロードしたcsvファイルをGoogleドライブにアップロードします。csvファイルは自動的にGoogleスプレッドシート形式で保存されます。ファイル名1500ttr.csvのままでは他のファイルと区別が付かないので、ここではGoogleスプレッドシート名を「2016xxxx東京都」(xxxxは地図を作成する月日)と変更しておきます。
(ヒント) もしGoogleスプレッドシート形式にならない時は、Googleドライブの設定をクリックし「アップロードしたファイルを変換する」にチェックを入れて下さい
クリックで拡大
4-1 このGoogleスプレッドシートにはシート1(シート名1500ttr.csv)だけ含まれ、そこにDITSデータが入っています。万一のデータ消失防止のため(1500ttr.csv)シートをコピーしておきます。コピーしたシート(シート名1500ttr.csvのコピー)の名称を「1500ttr生データ」に変更します。
(ヒント) 「1500ttr生データ」シートは万一データが無くなった時のための予備です。このシートは加工せずそのまま残しておきます。
4-2 コピー元のシート(1500ttr.csv)の名称を「DITSデータ取り込み」に変更します。この段階では「DITSデータ取り込み」シートと「1500ttr生データ」シーには同じDITSデータが入っています。今後「DITSデータ取り込み」シートを加工し、Googleマイマップ作成に必要なデータを準備していきます。
クリックで拡大
加工前の「DITSデータ取り込み」シートには項目名が付いていませんが、A列にNo.、B列は空欄、C列にツイッターユーザー名、D列に名前、E列に投稿日時、F列に投稿記事、G列にツイートIdが保存されています。この内、Googleマイマップ作成に必要なのは、C列のツイッタユーザー名、E列の投稿日時、F列の投稿記事です。以下の手順で加工していきます。
5-1 Deleteキーで不要なA1-A10、D1-D10、G1-G10を削除します。
5-2 C1-C10を切り取り、B1-B10に貼付けます。
5-3 E1-E10を切り取り、C1-C10に貼付けます。
5-4 F1-F10を切り取り、D1-D10に貼付けます。
5-5 1行目の上に1行挿入しタイトル行とします。
5-6 A1-P1にはそれぞれ以下のタイトルを記入します。
A1 | B1 | C1 | D1 | E1 | F1 | G1 | H1 | I1 | J1 | K1 | L1 | M1 |
name | username | date | post | address | text | tweetUrl | photoUrl | utm | lat | lon | map | sUrl |
5-1から5-6を行った後の「DITSデータ取り込み」シートは以下の図となります。E列-L列に表示されている文字はD列の一部が表示されているだけで、実際はまだ空欄です。
クリックで拡大
DITSでは、D列の投稿記事(post)内に、住所、記事本文、ツイートurl、UTMポイント情報が含まれています。これらのデータはGoogleスプレッドシート関数を組合せ取り出すことができます。なお、A列のnameはDITSコンテンツを表す名称です。ここでは内容が直ぐ分かるようF列の記事本文の始めから10文字を切り取って使うことにします。
(ヒント) Googleスプレッドシート関数とExcel関数は一部異なります。しかし以下7.1の処理に関しては共通です。
6-1 投稿記事から、名称、住所、テキスト、ツイッターurl、UTMポイントを抽出するため、 A2、E2、F2、G2、I2各セルに、それぞれ以下の関数を挿入する。
A2セル(名称) =left(mid(D2,find("周辺",D2)+2,find("UTM",D2)-find("周辺",D2)-2),10)
E2セル(住所) =mid(D2,find("「",D2)+1,find("」",D2)-find("「",D2)-1)
F2セル(テキスト) =mid(D2,find("周辺",D2)+2,find("UTM",D2)-find("周辺",D2)-2)
G2セル(ツイッタurl) =if(countif(D2,"*https://t.co*"),mid(D2,find("https",D2),23),"")
I2セル(UTMポイント) =mid(D2,find("UTM",D2)+4,13)
6-2 A2-L2をコピーし、A3-L10に貼り付ける。
貼り付け後「DITSデータ取り込み」シートの A1-A10には名称が、E1-E10には住所が、F1-F10にはテキストが、G1-G10にはツイートurlが、I1-I10にはUTMポイントが表示されます。この内、投稿者が記入するのはF列「テキスト」だけで、他はDITSが自動的に挿入したデータです。
クリックで拡大
DITS(ツイッター)には写真を添付することが出来ます。その写真の保存先(url)は以下の手順で取得します。
7-1 写真が添付されたDITSにはG列にツイートurl (tweetUrl) が表示されている。ツイートurlの上にカーソルを置くとそのurlが強調表示されるのでそのurlをクリックする。
7-2 元のツイッター文が表示されることを確認する。
7-3 写真上で右クリックするとピックアップメニューが表示されるので、その中の「画像アドレスをコピー」をクリックする。
7-4 右隣りH列のphotoUrl欄にペーストする。
画像url (photoUrl)取得後の「DITSデータ取り込み」シートは以下のようになります。
なお、DITS(ツイッター)に画像がない時はJ列(photoUrl)欄は空白となります。
クリックで拡大
UTMポイントの詳細は別ページを参照して下さい。UTM→緯度、経度変換方法は色々あり何を使っても構いませんが、ここでは(公開用)UTMポイント→緯度経度一括変換(東日本版)します。
8-1 「DITSデータ取り込み」シートのI2-I11列をコピーし、「(公開用)UTMポイント→緯度経度一括変換(東日本版)」の「UTMポイント→緯度経度」シート A8セルに貼り付けます。貼り付ける際、特殊貼り付けで「値のみ貼り付ける」を選択します。
(ヒント)単純にコピーペーストで貼り付けるとエラーになります。
8-2 「UTMポイント→緯度経度」シートのB2セルからK2セルまでをコピーし、B8セルーK17セルに貼り付けます。
(ヒント)通常の関数のコピーペースト処理と同じです。
クリックで拡大
8-3 J8-J17セルに緯度が、K8-K17セルに経度が表示されていることを確認します。
8-4 J8〜K17セルをコピーし、「DITSデータ取り出し」シートのJ2に貼り付けます。この時も「特殊貼り付け」を選択し「値のみ貼り付け」ます。
クリックで拡大
8-5 緯度、経度貼付け後の「DITSデータ取り出し」シートは以下のようになります。
クリックで拡大
8-6 変換終了後「(公開用)UTMポイント→緯度経度一括変換(東日本版)」の「UTMポイント→緯度経度」シートのA8-K17セルはクリヤーしておきます。
(ヒント1)地理院地図の検索欄にUTMポイントを入力して検索すると、地図左下にUTMポイント、緯度経度、住所が表示されます。一括変換は出来ませんが、この方法が一番簡単です。
(ヒント2) EarthPointを使ってWeb経由で一括変換することが出来ます。ただし事前登録が必要です。詳細は別資料を見て下さい。
(ヒント3)地理院マップシートを使って一括変換できます。Excelマクロを利用しています。詳細は地理院マップシートのマニュアルを見て下さい。地理院マップシートをダウンロードするとマニュアルも同時にダウンロードされます。
東京都のように広い範囲でDITSを検索すると、個々のDITS投稿場所を確認するには地図を拡大表示する必要があります。個々のDITSの緯度、経度は既に分かっているので、そのズームレベルを上げることで拡大地図を表示させることができます。手順は以下です。
9-1 「DITSデータ取り込み」シートのL2セルに以下の式を記入する。
="https://maps.google.co.jp/maps?ll="&j2&","&k2&"&z=16&q="&j2&","&k2
クリックで拡大
9-2 L2をクリックするとGoogleマップが拡大され投稿場所にマーカーが表示されることを確認する。
クリックで拡大
9-3 L2セルをコピーし、L3-L11セルに貼り付ける
クリックで拡大
9-4 L2セルの表示文字数は約90字と長いので短縮urlサービスを使って21文字に短縮する。DITS件数が多い時のために関数を使った一括変換サイトを用意したのでここではこれを利用する。具体的には「DITSデータ取り込み」シートのL2-L11セルをコピーし、短縮urlシートのA11セルに貼り付ける。B11-B20セルに短縮urlが表示されるので、これをコピーし「DITSデータ取り込み」シートのM2列に貼り付ける。
(ヒント)「DITSデータ取り込み」シートの拡大地図urlを短縮urlシートに貼り付ける時、逆に短縮urlを「DITSデータ取り込み」シートに貼り付ける時、どちらも特殊貼り付け「値のみを貼り付け」する点に注意。
短縮url一括取得 → https://goo.gl/eoorUc
クリックで拡大
クリックで拡大
5〜9を終えた段階でGoogleマイマップ作成に必要なデータは全て揃いました。このデータを用い、Googleマイマップを作成することが出来ます。ただ、残念ながらこのデータだけでは地図上にサムネイル写真を自動表示することはできません。サムネイル写真を表示するにはサムネイル写真urlの入ったkmlファイルを用意する必要があります。以下にkmlファイル作成手順を示します。
(ヒント)サムネイル写真を自動表示するには10-3〜10.-11の手順によりkmlファイルを作成する必要があります。10-1〜10.2の処理すればリンクをクリックする形で写真のサムネイル表示することが出来ます。
10-1 2016xxxx東京都スプレッドシートのシート数を1枚増やし「Googleマイマップ」シートとします。
10-2 「DITSデータ取り込み」シートの全データをコピーし、「Googleマイマップ」シートに「特殊貼り付け」→「値のみを貼り付け」で貼り付けます。その後、Googleマイマップに必要のない、D列(post)、G列(tweetUrl)、I列(utm)、L列(map)を削除します。「Googleマイマップ」シートは以下のようになります。
(ヒント)dateが42679.48925のような数字になった時は、Googleスプレッドシートの「表示形式」→「数字」→「日時」を選択すれば2016/11/05 11:44:31のように表示させます。
1クリックで拡大
10-3 J列、K列を使ってkmlファイルを作成します。まずJ2セルに以下の式を記入−します。
="<Placemark><name>"&A2&"</name>"&"<description><![CDATA[<img src="""&F2&""" height=""200"" width=""auto"""&"/>DITS]]></description><styleUrl>#icon-1899-0288D1</styleUrl><ExtendedData>"&"<Data name='"&$B$1&"'><value>"&B2&"</value></Data>"&"<Data name='"&$C$1&"'><value>"&text(C2,"yyyy.m.d HH:mm:ss")&"</value></Data>"&"<Data name='"&$D$1&"'><value>"&D2&"</value></Data>"&"<Data name='"&$E$1&"'><value>"&E2&"</value></Data>"&"<Data name='"&$I$1&"'><value>"&I2&"</value></Data>"&"<Data name='gx_media_links'><value>"&F2&"</value></Data></ExtendedData>"&"<Point><coordinates>"&H2&","&G2&",0.0</coordinates></Point></Placemark>"
クリックで拡大
10-4 J2セルをコピーし、J3-J11セルに貼り付けます。
クリックで拡大
10-5 K1セルに以下の値を貼り付けます。
<?xml version='1.0' encoding='UTF-8'?><kml xmlns='http://www.opengis.net/kml/2.2'><Document><name>Google</name>
10-6 J12セルに以下の値を貼り付けます。
</Document></kml>
10-7 K2セルに以下の式を記入します。
=K1&J2
10-8 K2セルをコピーし、K3〜K12セルに貼り付けます。
10-9 K12セル(=K11+J12)が、Googleマイマップ用kmlの値となります。
クリックで拡大
10-10 K12セルをコピーし、テキストエディターに貼り付けます。このファイルに例えば 2016xxxxtokyo.kml という名称でPCに保存します。
(ヒント)ファイル名は何でも構いませんが、拡張子は必ずkmlとして下さい。
クリックで拡大
10-11 このkmlファイルをGoogleマイマップにインポートします。Googleマイマップを共有するには、共有設定を「Web上で一般公開」にして下さい。アイコンはお好みのアイコンに変更できます。
クリックで拡大
(参考)Googleマイマップのkmlは以下の構成となっている。DITSデータ一件毎に,<placement>------ </placement>が追加されます。J2セルに挿入した式はこれを関数で置換えたものです。
<?xml version='1.0' encoding='UTF-8'?><kml xmlns='http://www.opengis.net/kml/2.2'><Document><name>Google</name>
<Placemark><name>清掃活動が終わった後</name>
<description><![CDATA[<img src="https://pbs.twimg.com/media/CtKrxuYUAAAdXVn.jpg" height="200" width="auto" />DITS]]></description><styleUrl>#icon-1899-0288D1</styleUrl><ExtendedData> <Data name='username'><value>tujikawa</value></Data>
<Data name='date'><value>9/25/2016</value></Data>
<Data name='address'><value>神奈川県横浜市瀬谷区宮沢1丁目2</value></Data>
<Data name='text'><value>清掃活動が終わった後のビールがおいしいです。</value></Data>
<Data name='shortenUrl'><value>https://goo.gl/dfQYnz</value></Data>
<Point><coordinates>139.4931912,35.4659352,0.0</coordinates></Point>
</Placemark>
</Document></kml>