【MySQL】MySQLにFeatureCollection型のGeoJSONの情報を保持させる

　MySQL には空間データを扱う仕組みがあり、この中には地理データである GeoJSON を扱う仕組みも含まれます。

MySQL ：： MySQL 8.0 リファレンスマニュアル ：： 12.17.11 空間 GeoJSON 関数
MySQL ：： MySQL 8.0 Reference Manual ：： 12.17.11 Spatial GeoJSON Functions

　MySQL8.0 は GeoJSON の概ねの型に対応しているのですが、残念ながら Feature と FeatureCollection には対応していません。

GeoJSON では、MySQL でサポートされているものと同じジオメトリデータ型または地理データ型がサポートされます。 ジオメトリオブジェクトが抽出される点を除き、フィーチャおよび FeatureCollection オブジェクトはサポートされていません。 CRS のサポートは SRID を識別する値に制限されています。

MySQL ：： MySQL 8.0 リファレンスマニュアル ：： 12.17.11 空間 GeoJSON 関数　より引用

　FeatureCollection は次の様に地理情報に密に情報を紐づけられる形式であり、これが使えると表現可能なものが増えます。

{
    "type": "FeatureCollection",
    "features": [
        {
            "type": "Feature",
            "geometry": {
                "type": "Polygon",
                "coordinates": [
                    [
                        [100.0, 0.0],
                        [101.0, 0.0],
                        [101.0, 1.0],
                        [100.0, 1.0],
                        [100.0, 0.0]
                    ]
                ]
            },
            "properties": {
                "fill": "rgba(0, 128, 255, 0.5)",
                "border": "rgba(0, 128, 255, 0.5)"
            }
        },{
            "type": "Feature",
            "geometry": {
                "type": "Point",
                "coordinates": [100.5, 0.5]
            },
            "properties": {
                "markerImg": "/img.example.com/marker.webp"
            }
        }
    ]
}

　この FeatureCollection を情報量を落とさず MySQL 内に地理情報として保存する方法を紹介します。

　MySQL は FeatureCollection に対応していませんが、これは完全に対応していないといった程度で FeatureCollection 型の GeoJSON を読み込んで GeometryCollection として保存することはできます。これを利用して FeatureCollection をJSON型カラムに格納し、格納した FeatureCollection を元に GeometryCollection として空間データを保存する方法が使えます。これを実現できるカラムの定義が次です。

geojson_raw      json not null comment 'GeoJSONをそのまま格納',
geojson_geometry geometrycollection as (ST_GEOMFROMGEOJSON(`geojson_raw`)) stored
        comment 'ST_GEOMFROMGEOJSON 関数でGeoJSONから空間データを生成して格納'

　これにより FeatureCollection としての様々な情報を残したまま空間データとして地理情報保持することができます。これは例えば次の様に実装できます。

create table leaflet_draws
(
    leaflet_layer_id bigint unsigned auto_increment primary key
        comment '主キー',
    geojson_raw      json not null
        comment 'GeoJSONをそのまま保持する。Featureとしての情報もここが持つ',
    # ST_GEOMFROMGEOJSON 関数でGeoJSONから空間データを生成
    geojson_geometry geometrycollection as (ST_GEOMFROMGEOJSON(`geojson_raw`)) stored
        comment 'geojson_rawカラムが持つGeoJSONを元にした地理情報を保持する。各空間分析関数はここを介して使う'
) comment 'leaflet の地図上に配置した色々';
# データ挿入例
INSERT INTO leaflet_draws (geojson_raw) VALUES ('{
  "type": "FeatureCollection",
  "features": [
    {
      "type": "Feature",
      "geometry": {
        "type": "Polygon",
        "coordinates": [
          [
            [100.0, 0.0],
            [101.0, 0.0],
            [101.0, 1.0],
            [100.0, 1.0],
            [100.0, 0.0]
          ]
        ]
      },
      "properties": {
        "fill": "rgba(0, 128, 255, 0.5)",
        "border": "rgba(0, 128, 255, 0.5)"
      }
    },{
      "type": "Feature",
      "geometry": {
        "type": "Point",
        "coordinates": [100.5, 0.5]
      },
      "properties": {
        "markerImg": "/img.example.com/marker.webp"
      }
    }
  ]
}');
INSERT INTO leaflet_draws (geojson_raw) VALUES ('{
  "type": "FeatureCollection",
  "features": [
    {
      "type": "Feature",
      "geometry": {
        "type": "Polygon",
        "coordinates": [
          [
            [-100.0, 0.0],
            [-101.0, 0.0],
            [-101.0, 2.0],
            [-100.0, 2.0],
            [-100.0, 0.0]
          ]
        ]
      },
      "properties": {
        "comment": "離れた場所にある面"
      }
    }
  ]
}');
# 空間関数使用例
# [[100.5, 0.5], [120.5, 0.5]] の線が交差する空間データを持つレコードのみを抜き出し
select leaflet_layer_id, geojson_raw from leaflet_draws
where ST_Intersects(geojson_geometry, st_geomfromgeojson('{
        "type": "LineString",
        "coordinates": [[100.5, 0.5], [120.5, 0.5]]
      }'));
# FeatureCollection部使用例
# JSON関数を使ってコメントを読み取る 
select
    leaflet_layer_id,
    JSON_UNQUOTE(`geojson_raw` -> "$.features[0].properties.comment") as comment
from leaflet_draws;

　こんな感じでリレーショナルデータベース上に空間データをそれに紐づく細かい情報込みで扱いやすい形で永続化できます。これは特にGIS（Geographic Information System）で使っているデータを過不足なく永続化するのに便利です。