Posted inMySQL

MySQL Spatial Data: GPS座標処理と最寄り地点検索の極意

MySQL tutorial - IT technology blog
MySQL tutorial - IT technology blog

地図アプリを「手動」で実装した時の悪夢

新人の頃、デリバリーアプリで「半径5km以内のコンビニを検索する」という、一見簡単そうなタスクを引き受けたことがあります。当時は深く考えず、緯度（latitude）と経度（longitude）をDECIMAL型で個別に保存していました。そして、SQL文の中にSinやCosが入り混じった複雑なHaversine公式をそのまま詰め込んで距離を計算していたのです。

結果は散々なものでした。数百件程度のデータなら問題ありませんでしたが、データが100万件に達し、DB容量が50GBを超えたあたりからシステムが「悲鳴」を上げ始めました。ユーザーが周辺検索ボタンを押すたびにサーバーのCPU使用率は90%に跳ね上がり、クエリの結果が返ってくるまでに5〜7秒もかかっていたのです。これは、地理データの扱いに適したツールを使わなかったことによる手痛い教訓となりました。

座標を保存する2つの一般的な方法：パフォーマンスか、手軽さか？

地図上の位置を保存する必要がある場合、通常は次の2つの選択肢に直面します。

方法1：緯度と経度を個別のカラムに保存する（数値型）

これは、DECIMAL(10, 8)を使用して保存する、最も直感的な方法です。

  • メリット: 可読性が高く、複雑なフォーマットを気にせず簡単にデータを挿入できる。
  • デメリット: MySQLは各行に対して計算を行うため、フルテーブルスキャン（Full Table Scan）を強制されます。100万行のテーブルでは、検索のたびに100万回のSin/Cos計算が実行され、システムは即座にボトルネックに陥ります。

方法2：Spatial Data Type（空間データ型）を使用する

MySQLは、POINT、LINESTRING、POLYGONなどの専用データ型を提供しています。

  • メリット: SPATIAL INDEXをサポートしています。これは空間検索を劇的に高速化するアクセラレータです。また、球面上の距離を極めて低い誤差で計算できるST_Distance_Sphere関数が用意されています。
  • デメリット: SRID（座標系）や、少し特殊なSQL構文を理解する必要があります。

なぜSpatial Dataが「死活問題」なのか？

単にWebサイト上で住所を表示するために座標を保存するだけなら、DECIMALで十分です。しかし、「最寄りの配達員を探す」や「周辺のレストランをレコメンドする」といった機能を実装する場合、Spatial Dataは必須です。実際に私が携わったプロジェクトでは、Spatial Indexに切り替えたことで、レイテンシが2000msから10ms以下にまで短縮されました. ユーザー体験において、これは天と地ほどの差です。

MySQL 8.0での標準的な実装ガイド

以下に、現在最も最適化された地点データの保存テーブルの設定方法を示します。

1. POINT型を使用したテーブルの作成

ポイントはlocationカラムです。POINT型を使い、SRID 4326（世界標準のGPS座標系であるWGS 84）を指定します。

CREATE TABLE stores (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    location POINT NOT NULL SRID 4326,
    SPATIAL INDEX(location)
) ENGINE=InnoDB;

注意: Spatial Indexは、カラムがNOT NULLとして定義されている場合にのみ機能します。これは、Check 制約と同様に、データの整合性を保つための重要なルールです。

2. 座標データの挿入

ST_GeomFromText関数を使用してデータを挿入します。MySQL 8.0のSRID 4326では、デフォルトの順序はPOINT(緯度 経度)となります。

-- Landmark 81を追加 (緯度: 10.7946, 経度: 106.7218)
INSERT INTO stores (name, location)
VALUES ('Landmark 81 Store', ST_GeomFromText('POINT(10.7946 106.7218)', 4326));

-- ベンタイン市場を追加 (緯度: 10.7719, 経度: 106.6983)
INSERT INTO stores (name, location)
VALUES ('ベンタイン市場店', ST_GeomFromText('POINT(10.7719 106.6983)', 4326));

3. 半径X km以内の地点を検索する

これは、半径2km圏内の地点をフィルタリングする際によく使われるクエリです。MySQLはIndexを自動的に使用し、詳細な計算を行う前に遠すぎる地点を除外します。

-- ビテクスコ・タワーにいるユーザーの位置 (10.7715, 106.7042)
SET @user_loc = ST_GeomFromText('POINT(10.7715 106.7042)', 4326);

SELECT name, 
       ST_Distance_Sphere(location, @user_loc) AS distance
FROM stores
WHERE ST_Distance_Sphere(location, @user_loc) <= 2000
ORDER BY distance ASC;

位置が配送エリア（Polygon）内にあるか判定する

複雑な形状の配送エリアを扱う場合は、ST_Containsを使用します。例えば、配達員が事前に定義された「1区」エリア内にいるかどうかを確認する場合などです。

-- Polygonで配送エリアを定義
SET @delivery_zone = ST_GeomFromText('POLYGON((10.7 106.6, 10.8 106.6, 10.8 106.8, 10.7 106.8, 10.7 106.6))', 4326);

SELECT name FROM stores 
WHERE ST_Contains(@delivery_zone, location);

パフォーマンス最適化のための「血の滲むような」教訓

大規模システムで空間データを扱う際、罠にハマりやすいポイントがあります。生き残るための3つの注意点を紹介します。

  1. SRIDを忘れないこと: SRID 4326を指定しないと、MySQLは距離を「メートル」ではなく「度」単位で計算する可能性があります。その結果、現実に即さない全く誤った値が返されてしまいます。
  2. 緯度・経度の順序は罠である: Leafletは(緯度, 経度)を使いますが、標準的なGeoJSONは(経度, 緯度)を使います。ホーチミン市の店舗がアフリカのど真ん中に表示されてしまうような事態を防ぐため、最初からこの規約を統一しておきましょう。
  3. 超大規模データにはBounding Boxを活用する: 数千万行のテーブルでは、ST_MakeEnvelopeを使用して位置を囲む矩形（長方形）を作成します。これにより、ST_Distance_Sphereを使用する前に、さらに高速に粗いフィルタリングを行うことができます。

Spatial Dataをマスターすれば、データベースをスムーズに稼働させながら、数千のリクエストを処理できるようになります。この記事が、地図関連の機能を構築する際の自信に繋がれば幸いです。

Share:
Tags: