MariaDB 10.3 で GIS データを扱う

MariaDBのブログにある、MariaDB Server 10.2: JSON/GeoJSON & GIS
を MaraiDB 10.3 で試してみます。

テスト環境

• MariaDB 10.3.9 GA
• CentOS 7.5.1804

サンプルデータ

NASA Meteorite Landings のデータをJSON形式に変換した隕石落下記録のサンプルデータが以下からダウンロードできます。

なお、MariaDBの設定でデフォルトの文字コードが utf8 になっていない場合は、上記の json_gis_sample.sql を source すると、アクセント記号等で以下のエラーが発生します。

ERROR 1366 (22007) at line 12 in file: 'json_gis_sample.sql': Incorrect string value: '\xC5\x81owic...' for column 'jsonfield' at row 13334

サーバ設定を変更しない場合は、CREATE TABLE文の最後に DEFAULT CHARSET=utf8 を追加する必要があります。

CREATE TABLE gis_json (
  id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
  jsonfield VARCHAR(1024),
  name VARCHAR(255) as (JSON_VALUE(jsonfield,'$.name')),
  gis_point POINT as ( ST_GeomFromGeoJSON(JSON_QUERY( jsonfield, '$.geolocation')) ) PERSISTENT,
  rlat VARCHAR(20) as (JSON_VALUE(jsonfield,'$.reclat')),
  rlong VARCHAR(20) as (JSON_VALUE(jsonfield,'$.reclong')),
  KEY jsonkey (name),
  CHECK (JSON_VALID(jsonfield))
) DEFAULT CHARSET=utf8;

DEFAULT CHARSET=utf8 を追加した上で json_gis_sample.sql を読み込みます。

# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 10
Server version: 10.3.9-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> create database gis_test;
Query OK, 1 row affected (0.000 sec)

MariaDB [(none)]> use gis_test;
Database changed
MariaDB [gis_test]> source json_gis_sample.sql
Query OK, 0 rows affected, 1 warning (0.000 sec)

Query OK, 0 rows affected (0.005 sec)

Query OK, 38401 rows affected (0.554 sec)
Records: 38401  Duplicates: 0  Warnings: 0

正常に 38,401 レコード読み込みができました。

皇居から半径50km以内の隕石落下地点を検索

皇居(latitude: 35.685175, longitude: 139.7506108)から半径50km以内での過去の隕石落下記録を検索するには以下のようなクエリが使えます。
変数 @latpoint, @longpoint, @radius はそれぞれ、中心地点の緯度、経度、検索半径[km]です。

SET
  @latpoint = 35.685175,    -- Imperial Palace latidue
  @longpoint = 139.7506108, -- Imperial Palace longitude
  @radius = 50.0,           -- radius[km] to search
  @units = 111.045;         

SELECT name, impactyear, mass_g, Y(gis_point) as latitude, X(gis_point) as longitude, round(distance,2) as distance_km
  FROM (
    SELECT name, JSON_VALUE(jsonfield,'$.year') as impactyear, 
           JSON_VALUE(jsonfield,'$.mass') as mass_g, 
           gis_point, 
           @radius,
           @units * DEGREES( ACOS( COS(RADIANS(@latpoint)) *
                             COS(RADIANS(X(gis_point))) *
                             COS(RADIANS(@longpoint) - RADIANS(Y(gis_point))) +
                             SIN(RADIANS(@latpoint)) *
                             SIN(RADIANS(X(gis_point))))) AS distance
           FROM gis_json
           WHERE MbrContains(GeomFromText(
                   CONCAT('LINESTRING(', @latpoint -(@radius/@units),' ',
                                         @longpoint-(@radius/(@units* COS(RADIANS(@latpoint)))),
                                         ',',
                                         @latpoint +(@radius/@units) ,' ',
                                         @longpoint+(@radius/(@units * COS(RADIANS(@latpoint)))), ')')),
                                         gis_point)
       ) AS d
  WHERE distance <= @radius
  ORDER BY distance;

クエリ実行結果

以下の4地点のデータが検索できました。

+-----------+------------+--------+-----------+-----------+-------------+
| name      | impactyear | mass_g | latitude  | longitude | distance_km |
+-----------+------------+--------+-----------+-----------+-------------+
| Komagome  | 1926-01-01 | 238    |    139.75 |  35.73333 |        5.35 |
| Sayama    | 1986-01-01 | 430    |     139.4 |  35.86667 |       37.47 |
| Hachi-oji | 1817-01-01 | 0      | 139.33333 |     35.65 |       37.85 |
| Kamiomi   | 1913-01-01 | 448    | 139.95667 |  36.04167 |       43.71 |
+-----------+------------+--------+-----------+-----------+-------------+

なお、ひとつ見慣れない地名の Kamiomi は 茨城県猿島郡神大実村(現在の茨城県坂東市) だそうです。