GIS データの管理と分析に欠かせない！MariaDB Information Schema GEOMETRY_COLUMNS テーブルの詳細解説

テーブル構造

GEOMETRY_COLUMNS (
  F_TABLE_SCHEMA VARCHAR(255) NOT NULL,
  F_TABLE_NAME VARCHAR(255) NOT NULL,
  F_GEOMETRY_COLUMN VARCHAR(255) NOT NULL,
  TYPE VARCHAR(255) NOT NULL,
  SRID INT NOT NULL,
  GTYPE VARCHAR(255) NOT NULL,
  DIM INT NOT NULL,
  F_GEOMETRY_TYPE VARCHAR(255) NOT NULL,
  F_GEOMETRY_COLLATION VARCHAR(255),
  MPS INTEGER NOT NULL,
  F_DATA_TYPE VARCHAR(30) NOT NULL,
  F_SRS_ID INT NOT NULL
);

主な列の説明

• F_SRS_ID
  空間参照システム定義の識別番号
• F_DATA_TYPE
  空間データの基底データ型 (例: BLOB, VARCHAR)
• MPS
  空間データの最大精度
• F_GEOMETRY_COLLATION
  空間データの照合順序
• F_GEOMETRY_TYPE
  空間データの型 (例: MULTIPOINT, MULTIPOLYGON, MULTIGEOMETRY)
• DIM
  空間データの次元数 (例: 2D, 3D)
• GTYPE
  空間データの幾何学タイプ (例: GEOMETRY, GEOGRAPHY)
• SRID
  空間参照システム識別番号
• TYPE
  空間データ型 (例: POINT, POLYGON, GEOMETRYCOLLECTION)
• F_GEOMETRY_COLUMN
  空間列名
• F_TABLE_NAME
  空間列を含むテーブル名
• F_TABLE_SCHEMA
  空間列を含むテーブルのスキーマ名

特定のデータベース内のすべての空間列を取得する

SELECT * FROM INFORMATION_SCHEMA.GEOMETRY_COLUMNS
WHERE F_TABLE_SCHEMA = 'your_database_name';

特定のテーブル内のすべての空間列を取得する

SELECT * FROM INFORMATION_SCHEMA.GEOMETRY_COLUMNS
WHERE F_TABLE_SCHEMA = 'your_database_name'
AND F_TABLE_NAME = 'your_table_name';

SELECT * FROM INFORMATION_SCHEMA.GEOMETRY_COLUMNS
WHERE F_TABLE_SCHEMA = 'your_database_name'
AND F_TABLE_NAME = 'your_table_name'
AND F_GEOMETRY_COLUMN = 'your_geometry_column_name';

SELECT SRID FROM INFORMATION_SCHEMA.GEOMETRY_COLUMNS
WHERE F_TABLE_SCHEMA = 'your_database_name'
AND F_TABLE_NAME = 'your_table_name'
AND F_GEOMETRY_COLUMN = 'your_geometry_column_name';

---

特定のデータベース内のすべての空間列を取得

-- サンプルデータベース名: 'my_database'

SELECT * FROM INFORMATION_SCHEMA.GEOMETRY_COLUMNS
WHERE F_TABLE_SCHEMA = 'my_database';

特定のテーブル内のすべての空間列を取得

-- サンプルテーブル名: 'my_table'

SELECT * FROM INFORMATION_SCHEMA.GEOMETRY_COLUMNS
WHERE F_TABLE_SCHEMA = 'my_database'
AND F_TABLE_NAME = 'my_table';

-- サンプル空間列名: 'my_geometry_column'

SELECT * FROM INFORMATION_SCHEMA.GEOMETRY_COLUMNS
WHERE F_TABLE_SCHEMA = 'my_database'
AND F_TABLE_NAME = 'my_table'
AND F_GEOMETRY_COLUMN = 'my_geometry_column';

空間データの空間参照システムを確認

-- サンプル空間列名: 'my_geometry_column'

SELECT SRID FROM INFORMATION_SCHEMA.GEOMETRY_COLUMNS
WHERE F_TABLE_SCHEMA = 'my_database'
AND F_TABLE_NAME = 'my_table'
AND F_GEOMETRY_COLUMN = 'my_geometry_column';

空間列を含むテーブルの作成

CREATE TABLE my_spatial_table (
  id INT PRIMARY KEY AUTO_INCREMENT,
  location POINT NOT NULL,
  spatial_index SPATIAL INDEX(location)
) ENGINE = InnoDB;

空間データの挿入

INSERT INTO my_spatial_table (location)
VALUES (POINT(40.7128, -74.0060)); -- ニューヨーク市の座標

空間データの検索

SELECT * FROM my_spatial_table
WHERE ST_CONTAINS(location, POINT(40.7142, -73.9981)); -- ニューヨーク市周辺のデータを取得

空間データの更新

UPDATE my_spatial_table
SET location = ST_SETSRID(ST_POINTFROMTEXT('POINT(40.7128 -74.0060)'), 4326)
WHERE id = 1; -- 空間データの座標を更新

DELETE FROM my_spatial_table
WHERE ST_CONTAINS(location, POINT(40.7142, -73.9981)); -- ニューヨーク市周辺のデータを削除

• 詳細については、MariaDB の公式ドキュメントを参照してください。
• 空間データの操作には、ST_Geometry 関数などの空間関数が必要です。

---

• 以下の例では、GeoJSON フォーマットに変換して、Web ブラウザで可視化します。
• 空間データの可視化には、GeoJSON や Leaflet などのライブラリを使用できます。

-- サンプル空間列名: 'my_geometry_column'

SELECT ST_AsGeoJSON(my_geometry_column) AS geojson
FROM my_spatial_table;

空間データの分析

• 以下の例では、特定の空間列内のすべてのデータの面積を計算します。
• 空間データの分析には、ST_Geometry 関数や空間解析ライブラリを使用できます。

-- サンプル空間列名: 'my_geometry_column'

SELECT ST_Area(my_geometry_column) AS area
FROM my_spatial_table;

空間データのインデックス作成

• 以下の例では、R-tree インデックスを作成します。
• 空間データのインデックスを作成することで、空間クエリの処理速度を向上させることができます。

CREATE SPATIAL INDEX my_spatial_index ON my_spatial_table (my_geometry_column);

空間データの結合

• 以下の例では、別のテーブルのデータと空間的に一致するレコードを取得します。
• 空間データの結合には、ST_Intersects や ST_Within などの空間関数を使用できます。

SELECT * FROM my_spatial_table AS s
JOIN other_table AS o ON ST_Intersects(s.my_geometry_column, o.geometry_column);

空間データの更新

• 以下の例では、空間列内のすべてのデータの座標をオフセットします。
• 空間データの更新には、ST_Geometry 関数を使用できます。

UPDATE my_spatial_table
SET my_geometry_column = ST_Translate(my_geometry_column, 0.1, 0.2);

• 詳細については、MariaDB の公式ドキュメントを参照してください。
• 空間データの操作には、ST_Geometry 関数などの空間関数が必要です。
• 上記の例はあくまでも高度な操作の一例であり、他にも様々な操作が可能です。