https://zenn.dev/convers39/articles/e91124920ea379

最近は業務で、現在地周辺の施設候補を検索する機能のリクエストが出て、それを解決するために色々と調査してみました。

ロケーションテーブル

仮に下記のようなテーブルがあったとします。

CREATE TABLE IF NOT EXISTS `location`(
    `id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    `geo_hash` VARCHAR(12),
    `position` POINT NOT NULL SRID 4326,
    `latitude` DOUBLE(8,6) NOT NULL,
    `longitude` DOUBLE(9,6) NOT NULL,
    SPATIAL INDEX(`position`)
);

delimiter //
CREATE TRIGGER add_geometry_on_insert BEFORE INSERT ON location
    FOR EACH ROW
    BEGIN
        SET @lng = NEW.longitude;
        SET @lat = NEW.latitude;
        SET @point = CONCAT('POINT(',@lat,' ',@lng,')');
        SET NEW.position = ST_GeomFromText(@point,4326);
    END;//

CREATE TRIGGER add_geometry_on_update BEFORE UPDATE ON location
    FOR EACH ROW
    BEGIN
        SET @lng = NEW.longitude;
        SET @lat = NEW.latitude;
        SET @point = CONCAT('POINT(',@lat,' ',@lng,')');
        SET NEW.position = ST_GeomFromText(@point,4326);
    END;//

CREATE TRIGGER add_geohash_on_insert BEFORE INSERT ON location
    FOR EACH ROW FOLLOWS add_geometry_on_insert
        SET NEW.geo_hash = ST_GeoHash(NEW.position,12);//

CREATE TRIGGER add_geohash_on_update BEFORE UPDATE ON location
    FOR EACH ROW FOLLOWS add_geometry_on_update
        SET NEW.geo_hash = ST_GeoHash(NEW.position,12);//

delimiter ;

基本的に、フロントエンドとバックエンドでは経度と緯度をメインに扱うため、この二つのデータさえあれば動くように、geo_hashとpositionフィールドに対して、トリガーを追加しています。経度と緯度だけでいいじゃんとか思われる方もいるかもしれませんが、この二つのフィールドが必要かどうかは今回議論範囲外です。

GeoHash

latitudeとlongitudeを使ってハッシュ関数で生成された数字とアルファベットの文字列。文字列の長さは精度と正の相関があります。

上記の表（ 参照先 ）で表示されたように、生成された文字列の長さが8桁であれば、精度は+-0.019キロメートル＝19mとなります。また、こちらの回答によると、9桁以上になると精度がさらに増えます：

この通り、10桁までいくと、精度が1メートル以内になるので、保存時は10桁で十分なはずですが、今回は一旦12桁で保存してみます。

MySQLには、GeoHashのデータから経度緯度を抽出する関数があります。今回は直接経度緯度を保存しているので関心外となりますが、詳細 はこちら に参照。

精度は桁数と関係あるため、左から同じ文字が多ければ多いほど、二つの場所が近いとのことになります。次の表のように、MySQLのST_GeoHash関数を使用し、東京駅周辺と台東区のサンプルロケーションデータの経度緯度で計算した結果、上位数桁の文字列が同じとなっています。

Untitled

GEOMETRYデータタイプ

MySQLにはいくつかのGeometryデータタイプが用意されています。