現在位置: ホーム / OSSブログ / Google MapでPostgreSQLのJSONBを使うサンプル

Google MapでPostgreSQLのJSONBを使うサンプル

本記事では、PHP と PostgreSQL の JSONB 型を使った Google Map のサンプルコードを紹介しております。

こんにちは。渡辺 です。

今回は PHP と PostgreSQL の JSONB 型を使った Google Map のサンプルコードを紹介します。サンプルの作成には PHP 5.6 と PostgreSQL 9.4 を利用しました。

■ Google Map のサンプル

元にする Google Map のコードは Google のサイトに掲載されている 「Remove marker」 の HTML + JavaScript のコードを利用します。このサンプルコードはサンフランシスコの Google 社にマーカーが設定されたページが表示され、クリックした場所に新しいマーカーを複数設定できます。

https://developers.google.com/maps/documentation/javascript/examples/marker-remove

スクリーンショット

HTML + JavaScript

<!DOCTYPE html>
<html>
  <head>
    <title>Remove Markers</title>
    <style>
      html, body, #map-canvas {
        height: 100%;
        margin: 0px;
        padding: 0px
      }
      #panel {
        position: absolute;
        top: 5px;
        left: 50%;
        margin-left: -180px;
        z-index: 5;
        background-color: #fff;
        padding: 5px;
        border: 1px solid #999;
      }
    </style>
    <script src="https://maps.googleapis.com/maps/api/js?v=3.exp&signed_in=true">⁢/script>
    <script>
// In the following example, markers appear when the user clicks on the map.
// The markers are stored in an array.
// The user can then click an option to hide, show or delete the markers.
var map;
var markers = [];

function initialize() {
  var haightAshbury = new google.maps.LatLng(37.7699298, -122.4469157);
  var mapOptions = {
    zoom: 12,
    center: haightAshbury,
    mapTypeId: google.maps.MapTypeId.TERRAIN
  };
  map = new google.maps.Map(document.getElementById('map-canvas'),
      mapOptions);

  // This event listener will call addMarker() when the map is clicked.
  google.maps.event.addListener(map, 'click', function(event) {
    addMarker(event.latLng);
  });

  // Adds a marker at the center of the map.
  addMarker(haightAshbury);
}

// Add a marker to the map and push to the array.
function addMarker(location) {
  var marker = new google.maps.Marker({
    position: location,
    map: map
  });
  markers.push(marker);
}

// Sets the map on all markers in the array.
function setAllMap(map) {
  for (var i = 0; i < markers.length; i++) {
    markers[i].setMap(map);
  }
}

// Removes the markers from the map, but keeps them in the array.
function clearMarkers() {
  setAllMap(null);
}

// Shows any markers currently in the array.
function showMarkers() {
  setAllMap(map);
}

// Deletes all markers in the array by removing references to them.
function deleteMarkers() {
  clearMarkers();
  markers = [];
}

google.maps.event.addDomListener(window, 'load', initialize);

    </script>
  </head>
  <body>
    <div id="panel">
      <input onclick="clearMarkers();" type=button value="Hide Markers">
      <input onclick="showMarkers();" type=button value="Show All Markers">
      <input onclick="deleteMarkers();" type=button value="Delete Markers">
    </div>
    <div id="map-canvas"></div>
    <p>Click on the map to add markers.</p>
  </body>
</html>

https://developers.google.com/maps/documentation/javascript/examples/marker-remove

このソースコードを見ると以下の関数でマーカーを追加 / 表示 / 非表示 / 全削除にしていることが分かります。

// Add a marker to the map and push to the array.
function addMarker(location) {
  var marker = new google.maps.Marker({
    position: location,
    map: map
  });
  markers.push(marker);
}

// Sets the map on all markers in the array.
function setAllMap(map) {
  for (var i = 0; i < markers.length; i++) {
    markers[i].setMap(map);
  }
}

// Removes the markers from the map, but keeps them in the array.
function clearMarkers() {
  setAllMap(null);
}

// Shows any markers currently in the array.
function showMarkers() {
  setAllMap(map);
}

// Deletes all markers in the array by removing references to them.
function deleteMarkers() {
  clearMarkers();
  markers = [];
}

■ PostgreSQL でマーカーを保存できるようにする

データベースのテーブル定義は単純に

CREATE TABLE map_marker (
  id bigserial NOT NULL,
  marker jsonb NOT NULL
);

とします。このサンプルではテーブルが無い場合、自動的にテーブルを作成します。

最終的には PHP のビルトイン Web サーバーで動作させるので、html ディレクトリなどを作成し、全てのファイルをこのディレクトリ以下に起きます。

Google Map を表示する html ファイルは map.html とします。上記のサンプル HTML + JavaScript コードを map.html として保存します。

データベースにマーカーを保存するには、保存機能が必要です。JavaScript ライブラリを追加せずにサーバーとやり取りするのは手間なので、jQuery を利用します。

<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.4/jquery.min.js"></script>

最初のマーカーは必要ないので削除します。

// Adds a marker at the center of the map.
addMarker(haightAshbury);

マーカーは作成と同時に PostgreSQL に保存し、リロードと同時に表示するよう にします。マーカー削除以外のボタンは必要なくなるので削除します。

<input onclick="clearMarkers();" type=button value="Hide Markers">
<input onclick="showMarkers();" type=button value="Show All Markers">

addMarker 関数に保存用のコードを追加します。

// Add a marker to the map and push to the array.
function addMarker(location) {
    console.log(location);
    var marker = new google.maps.Marker({
        position: location,
        map: map
    });
    markers.push(marker);
    // Save marker to PostgreSQL
    $.ajax({
        type: "POST",
        url: "add_marker.php",
        dataType: "json",
        data: {
            lat: location.lat(),
            lng: location.lng()
        },
        success: function(response) {
            console.log(response);
        },
        error: function(xhr, status, error) {
            alert("Something wrong while adding marker.");
            console.log(status+" "+error);
        }
    })
}

AJAX リクエストを処理する PHP スクリプトを追加します。このスクリプトでは POST で送信されたデータを JSONB の marker カラムに追加しています。

add_marker.php

<?php
header('Content-Type: application/json');
require_once 'pgconfig.php';

$sql = "INSERT INTO markers (marker) VALUES ('". pg_escape_string(json_encode($_POST)) . "');";
if (pg_query($sql)) {
 echo json_encode($_POST);
} else {
 echo json_encode(['status'=>'error', 'message'=>'Failed to save marker']);
}

setAllMap 関数はマーカーの表示/非表示を制御する関数です。setAllMap 関数の仕様をマーカー保存/削除用に変更します。このために保存したマーカーを取得するコードとマーカー削除用のコードを追加します。

マーカーを表示するだけでは面白くないので、マーカーをクリックするとマーカー情報 (このコードではレコード ID のみ表示) を表示するようにします。

修正後の setAllMap 関数は以下のようになります。

// Sets the map on all markers in the array.
function setAllMap(map) {
    if (map) {
        // Load markers from PostgreSQL
        $.ajax({
            type: "POST",
            url: "get_markers.php",
            dataType: "json",
            data: [], // Dummy. It may specify which markers to retrieve.
            success: function(response) {
                console.log(response);
                var infowindow = new google.maps.InfoWindow();
                for (var i = 0; i < response.length; i++) {
                    var m = JSON.parse(response[i].marker);
                    var location = new google.maps.LatLng(m.lat, m.lng)
                    var marker = new google.maps.Marker({
                        position: location,
                        map: map,
                        title: 'Marker ID: '+response[i].id
                    });
                    markers.push(marker);
                    google.maps.event.addListener(marker, 'click', function() {
                        infowindow.setContent(this.title);
                        infowindow.open(map, this);
                    });
                }
            },
            error: function(xhr, status, error) {
                alert("Something wrong while loading markers.");
                console.log(status+" "+error);
        }
        });
    } else {
        // Delete all markers in PostgreSQL
        $.ajax({
            type: "POST",
            url: "remove_markers.php",
            dataType: "json",
            data: [], // Dummy. It may specify which markers to delete.
            success: function(response) {
                console.log(response);
            },
            error: function(xhr, status, error) {
                alert("Something wrong while removing markers.");
                console.log(status+" "+error);
        }
        });
    }
    // Display markers
    for (var i = 0; i < markers.length; i++) {
        markers[i].setMap(map);
    }
}

マーカーデータロード用の get_markers.php とマーカーデータ削除用の remove_markers.php を追加します。

get_markers.php

<?php
header('Content-Type: application/json');
require_once 'pgconfig.php';

$sql = "INSERT INTO markers (marker) VALUES ('". pg_escape_string(json_encode($_POST)) . "');";
if (pg_query($sql)) {
 echo json_encode($_POST);
} else {
 echo json_encode(['status'=>'error', 'message'=>'Failed to save marker']);
}

remove_markers.php

<?php
header('Content-Type: application/json');
require_once 'pgconfig.php';

$sql = 'DELETE FROM markers;';
$res = pg_query($sql);
$num = pg_affected_rows($res);

echo json_encode(['status'=>'success', 'removed'=>$num]);

最後に add_marker.php, get_markers.php, remove_markers.phpで利用している pgconfig.php を追加します。

pgconfig.php

<?php
// 設定に合わせて修正。パスワードが必要な場合、パスワードも追加。
$host = '127.0.0.1';
$port = 5432;
$dbname = 'mydb';
$user = 'myname';

$db = pg_connect("host=${host} port=${port} dbname=${dbname} user=${user}")
 or die(json_encode(['status'=>'Database connection error']));

if (!@pg_query('SELECT * FROM markers LIMIT 1')) {
 // テーブルが存在しないので作成
 $ddl = <<< EOD
CREATE TABLE markers (
 id bigserial NOT NULL PRIMARY KEY,
 marker jsonb NOT NULL
);
EOD;
 if (!@pg_query($ddl)) {
  trigger_error('Failed to create marker table');
  exit(1);
 }
}

完成したサンプルコードのファイル一覧は以下のようになります。

$ ls
add_marker.php  get_markers.php  map.html  map.orig.html  pgconfig.php  remove_markers.php

上記説明した以外にも多少修正があります。編集後の map.html は以下のようになります。

map.html

<!DOCTYPE html>
<html>
  <head>
    <title>Remove Markers</title>
    <style>
      html, body, #map-canvas {
        height: 100%;
        margin: 0px;
        padding: 0px
      }
      #panel {
        position: absolute;
        top: 5px;
        left: 50%;
        margin-left: -180px;
        z-index: 5;
        background-color: #fff;
        padding: 5px;
        border: 1px solid #999;
      }
    </style>
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.4/jquery.min.js"></script>
    <script src="https://maps.googleapis.com/maps/api/js?v=3.exp&signed_in=true"></script>
    <script>
// In the following example, markers appear when the user clicks on the map.
// The markers are stored in an array.
// The user can then click an option to hide, show or delete the markers.
var map;
var markers = [];

function initialize() {
    var haightAshbury = new google.maps.LatLng(37.7699298, -122.4469157);
    var mapOptions = {
        zoom: 12,
        center: haightAshbury,
        mapTypeId: google.maps.MapTypeId.TERRAIN
    };
    map = new google.maps.Map(document.getElementById('map-canvas'),
                              mapOptions);
    // This event listener will call addMarker() when the map is clicked.
    google.maps.event.addListener(map, 'click', function(event) {
        addMarker(event.latLng);
    });
    showMarkers();
}

// Add a marker to the map and push to the array.
function addMarker(location) {
    console.log(location);
    var marker = new google.maps.Marker({
        position: location,
        map: map
    });
    markers.push(marker);
    // Save marker to PostgreSQL
    $.ajax({
        type: "POST",
        url: "add_marker.php",
        dataType: "json",
        data: {
            lat: location.lat(),
            lng: location.lng()
        },
        success: function(response) {
            console.log(response);
        },
        error: function(xhr, status, error) {
            alert("Something wrong while adding marker.");
            console.log(status+" "+error);
        }
    })
}

// Sets the map on all markers in the array.
function setAllMap(map) {
    if (map) {
        // Load markers from PostgreSQL
        $.ajax({
            type: "POST",
            url: "get_markers.php",
            dataType: "json",
            data: [], // Dummy. It may specify which markers to retrieve.
            success: function(response) {
                console.log(response);
                var infowindow = new google.maps.InfoWindow();
                for (var i = 0; i < response.length; i++) {
                    var m = JSON.parse(response[i].marker);
                    var location = new google.maps.LatLng(m.lat, m.lng)
                    var marker = new google.maps.Marker({
                        position: location,
                        map: map,
                        title: 'Marker ID: '+response[i].id
                    });
                    markers.push(marker);
                    google.maps.event.addListener(marker, 'click', function() {
                        infowindow.setContent(this.title);
                        infowindow.open(map, this);
                    });
                }
            },
            error: function(xhr, status, error) {
                alert("Something wrong while loading markers.");
                console.log(status+" "+error);
        }
        });
    } else {
        // Delete all markers in PostgreSQL
        $.ajax({
            type: "POST",
            url: "remove_markers.php",
            dataType: "json",
            data: [], // Dummy. It may specify which markers to delete.
            success: function(response) {
                console.log(response);
            },
            error: function(xhr, status, error) {
                alert("Something wrong while removing markers.");
                console.log(status+" "+error);
        }
        });
    }
    // Display markers
    for (var i = 0; i < markers.length; i++) {
        markers[i].setMap(map);
    }
}

// Removes the markers from the map, but keeps them in the array.
function clearMarkers() {
    setAllMap(null);
}

// Shows any markers currently in the array.
function showMarkers() {
    setAllMap(map);
}

// Deletes all markers in the array by removing references to them.
function deleteMarkers() {
    clearMarkers();
    markers = [];
}

google.maps.event.addDomListener(window, 'load', initialize);

    </script>
  </head>
  <body>
    <div id="panel">
       <input onclick="deleteMarkers();" type=button value="Delete Markers">
    </div>
    <div id="map-canvas"></div>
    <p>Click on the map to add markers.</p>
  </body>
</html>

■ サンプルの試用

サンプルプログラムを利用するには、ここで作ったファイルが保存されているディレクトリから PHP のビルトイン Web サーバーを利用します。

$ php -S 127.0.0.1:8888

とするとビルトイン Web サーバーが起動します。Web ブラウザから

http://127.0.0.1:8888/map.html

にアクセスすると、サンフランシスコの地図が表示されます。クリックするとマーカーが追加されます。オリジナルのサンプルファイルは HTML + JavaScript だけなのでリロードするとマーカーが消えますが、このサンプルプログラムはマーカーデータを PostgreSQL に保存しているので、リロードしても同じマーカーが表示されます。

改造後のスクリーンショット

■ サンプルの拡張

今回のサンプルは緯度経度のみを保存しています。サンプルの拡張は詳しく解説しませんが、Google Map には非常に多くの機能が組み込まれています。今回利用した InfoWindow には自由に HTML を組み込めます。つまり

- 場所の写真 / 情報を追加 / 削除するフォームを配置する

といったことが InfoWindow で行えます。JSONB を利用しているので、これらのフィールドを追加してもデータベーススキーマを修正することなく、アプリケーションの拡張が行えます。これらのデータを PostgreSQL で保存すると簡単な地図アプリケーションのできあがりです。

■ データベース設計

テーブルが 1つしかないデータベースでデータベース設計ということはほとんどありません。しかし、サンプルを拡張するにあたって考慮すべきことがあります。

- 緯度経度をJSONBとして保存している

JSONB は比較的効率良く検索できますが、データの保存場所として JSONB が適しているか考慮しなければなりません。

PostgreSQL には cube や earthdistance といった拡張モジュールがあります。これを使うと、緯度経度を使って自分の半径 1km以内のマーカーを探す、といったことが簡単できます。この機能を利用するには緯度経度は浮動小数点型として保存した方が効率的です。

場所の情報なども JSONB に保存していても検索可能ですが、専用カラムに保存した方が効率が良い場合も多いです。JOIN を行う場合や制約を付けたい場合には専用カラムを利用すべきです。

■ まとめ

とても簡単なプログラムで Google Map に表示するマーカーをデータベースに保存可能であることが分かったと思います。アプリケーションを拡張する場合にも JSONB を利用していればスキーマの変更なく、新しい情報を保存できます。プロトタイプを作る場合にはとても助かります。

しかし、データベース設計の部分で記述したように JSONB やハッシュなどのデータ型は必ずしも最適化されたデータベース設計ではありません。

リレーショナルデータベースに適したデータである場合、通常のテーブルのカラムとしてデータを保存した方が良い場合が多くあります。データベース設計を誤るとシステムの性能や拡張性、メンテナンスに大きな影響を与えます。JSONB / JSON 型はとても便利ですが、十分注意して利用しましょう。

サイオスOSSよろず相談室

サイオスOSSよろず相談室(1)

問い合わせボタン