design-postgis-tables

PostGIS 空間テーブルの設計

始める前に（5つの質問）

1. 地理的な範囲はどの程度ですか（単一の都市／地域ですか、それとも全世界ですか）？
2. 主なクエリパターンは何ですか（半径内、バウンディングボックス、交差、最近傍など）？
3. 距離／面積の単位は何が必要ですか（メートルですか、CRS の単位ですか）。また、どの程度の精度が必要ですか？
4. 想定される規模（行数、書き込み頻度）はどの程度ですか。また、データの大半は追記専用ですか？
5. 3D（Z）や計測値（M）が必要ですか。それとも2Dで十分ですか？

SQL インジェクションに関する注意： これらのパターンをアプリケーションコードに組み込む際は、ユーザーから提供される値（WKT／WKB、座標、ID、半径）にパラメーター化クエリを使用してください。信頼できない入力を SQL に文字列連結するのは避けてください。動的な識別子には、安全な識別子の引用またはホワイトリストを使用してください。

基本ルール

• PostgreSQL 組み込みの幾何型（POINT、LINE、POLYGON、CIRCLE）ではなく、必ず PostGIS の geometry／geography 型を使用してください。PostGIS の型は真の空間機能を提供します。
• 用途に応じて GEOMETRY と GEOGRAPHY を選択してください。GEOMETRY は直交座標系の計算を行う投影データ／ローカルデータに、GEOGRAPHY は正確な球面計算が必要な全世界のデータに適しています。
• geometry 列を作成する際は、必ず SRID（空間参照系識別子）を指定してください。GPS／全世界のデータには 4326（WGS84）を、地域データには適切なローカル投影法を使用してください。
• GiST（デフォルト）を使用して、すべての geometry／geography 列に空間インデックスを作成してください。ディスク上で行が自然な順序に並び、より粗いフィルタリングを許容できる大規模な GEOMETRY テーブルに限り、BRIN も検討してください。
• GEOMETRY(type, SRID) 構文を使用した制約ベースの型強制により、データの整合性を確保してください。

Geometry と Geography の比較

GEOMETRY を使用する場合

• 単一の座標系内に収まるローカル／地域データ
• 正確な面積／距離計算に使う投影座標（メートル、フィート）
• 複雑な空間操作（バッファー、和集合、交差）
• パフォーマンスが重要なクエリ（直交座標系の計算は高速です）
• すでに投影 CRS にあるデータ（UTM、State Plane など）

-- Regional data with projected coordinates (UTM Zone 10N for California)
CREATE TABLE local_parcels (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    parcel_number TEXT NOT NULL,
    boundary GEOMETRY(POLYGON, 26910),  -- UTM Zone 10N (meters)
    area_sqm DOUBLE PRECISION GENERATED ALWAYS AS (ST_Area(boundary)) STORED
);

GEOGRAPHY を使用する場合

• 複数の大陸／半球にまたがる全世界のデータ
• GPS 座標（十進度で表す緯度／経度）
• 地表上の正確な距離計算（大円距離）
• 単純な空間操作（距離、包含）
• GPS デバイス、ジオコーディングサービス、ウェブマップからのデータ

-- Global data with geodetic calculations
CREATE TABLE global_offices (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name TEXT NOT NULL,
    city TEXT NOT NULL,
    location GEOGRAPHY(POINT, 4326)  -- WGS84 (lat/lon)
);

-- Distance in meters (accurate spherical calculation)
SELECT
    a.name AS office_a,
    b.name AS office_b,
    ST_Distance(a.location, b.location) / 1000 AS distance_km
FROM global_offices a
CROSS JOIN global_offices b
WHERE a.id < b.id;

比較表

項目	GEOMETRY	GEOGRAPHY
座標系	任意の SRID（投影座標または測地座標）	WGS84（SRID 4326）のみ
距離の単位	CRS の単位（度、メートル、フィート）	メートル（常に）
距離の精度	投影法によって異なる	真の楕円体上の距離
面積の精度	投影 CRS で正確	球面上で正確
関数のサポート	完全（300以上の関数）	限定的（約40の関数）
パフォーマンス	高速（直交座標系の計算）	低速（球面計算）
インデックスの種類	GiST、BRIN、SP-GiST	GiST のみ
最適な用途	地域／ローカルデータ、複雑な分析	全世界のデータ、GPS 追跡

Geometry 型

Point 型

-- Single location (stores, sensors, events)
location GEOMETRY(POINT, 4326)

-- Multiple discrete locations (multi-branch business)
locations GEOMETRY(MULTIPOINT, 4326)

-- 3D point with elevation
location_3d GEOMETRY(POINTZ, 4326)

-- Point with measure value (linear referencing)
location_m GEOMETRY(POINTM, 4326)

POINT の用途： 店舗の場所、センサーの位置、イベントの座標、住所、POI の保存 MULTIPOINT の用途： 関連する複数の場所を単一のフィーチャとして保存

Line 型

-- Single path (road segment, river, route)
path GEOMETRY(LINESTRING, 4326)

-- Multiple paths (road network, transit lines)
network GEOMETRY(MULTILINESTRING, 4326)

-- 3D line with elevation profile
trail_3d GEOMETRY(LINESTRINGZ, 4326)

LINESTRING の用途： 道路、河川、パイプライン、GPS トラック、ルート MULTILINESTRING の用途： 途切れた道路区間、河川網

Polygon 型

-- Single area (parcel, building footprint, zone)
boundary GEOMETRY(POLYGON, 4326)

-- Multiple areas (archipelago, fragmented habitat)
territories GEOMETRY(MULTIPOLYGON, 4326)

-- 3D polygon (building with height)
footprint_3d GEOMETRY(POLYGONZ, 4326)

POLYGON の用途： 土地の境界、行政区域、サービスエリア MULTIPOLYGON の用途： 島を含む国、分断された地域

汎用型

-- Any geometry type (flexible schema)
geom GEOMETRY(GEOMETRY, 4326)

-- Collection of mixed types
features GEOMETRY(GEOMETRYCOLLECTION, 4326)

GEOMETRY を使用する場面： 複数の型を受け入れる柔軟なスキーマ GEOMETRYCOLLECTION は避ける： インデックスの効率を高めるため、同種の型を優先してください

座標系（SRID）

一般的な SRID

SRID	名称	用途	単位
4326	WGS84	GPS、グローバルデータ、ウェブマップ	度
3857	Web Mercator	ウェブマップタイル（表示専用）	メートル
26910-26919	UTM Zone（米国）	地域分析	メートル
32601-32660	UTM Zone（北半球）	地域分析	メートル
32701-32760	UTM Zone（南半球）	地域分析	メートル

SRID のベストプラクティス

• 相互運用性と GPS データのため、WGS84（4326）で保存します
• 正確な測定には、投影 CRS に変換します
• 明示的な変換を行わずに、空間操作で SRID を混在させないでください
• 高精度が必要な面積・距離計算には、適切なローカル CRS を使用します

-- Store in WGS84, calculate in UTM
CREATE TABLE survey_points (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    location GEOMETRY(POINT, 4326),  -- Storage: WGS84
    CONSTRAINT valid_location CHECK (ST_IsValid(location))
);

-- Calculate distance in meters using UTM projection
SELECT
    a.id AS point_a,
    b.id AS point_b,
    ST_Distance(
        ST_Transform(a.location, 26910),  -- Transform to UTM
        ST_Transform(b.location, 26910)
    ) AS distance_meters
FROM survey_points a
CROSS JOIN survey_points b
WHERE a.id < b.id;

空間インデックス

GiST インデックス（デフォルト）

最も汎用性の高い空間インデックスです。すべての geometry/geography 列に使用してください。

-- Geometry (most common)
CREATE INDEX idx_your_table_geom_gist ON your_table_name USING GIST (geom);

-- Geography (GiST is the supported option)
CREATE INDEX idx_your_table_geog_gist ON your_table_name USING GIST (geog);

-- Analyze after index creation
VACUUM ANALYZE your_table_name;

対応： すべての空間演算子（&&、@>、<@、~=、<->） 最適な用途： 汎用的な空間クエリ、さまざまなクエリパターン

BRIN インデックス

自然に順序付けされた非常に大きなデータセット向けのブロック範囲インデックスです。

-- BRIN for very large, append-only GEOMETRY tables (geography uses GiST)
CREATE INDEX idx_your_table_geom_brin
    ON your_table_name
    USING BRIN (geom)
    WITH (pages_per_range = 128);

対応： バウンディングボックス演算子（&&、@>、<@） 最適な用途： 追記専用テーブル、時系列空間データ、非常に大きなデータセット（1 億行超） トレードオフ： GiST よりはるかに小さい一方、フィルタリングの精度は低くなります

SP-GiST インデックス

特定の分布を持つポイントデータ向けの、空間分割型 GiST です。

-- SP-GiST for GEOMETRY(POINT, ...) only
CREATE INDEX idx_sensors_location_spgist
    ON sensors
    USING SPGIST (location);

最適な用途： ポイントのみのデータ、クアッドツリーに適した分布 不向きな用途： 複雑なジオメトリ、混在型

インデックス選択ガイド

シナリオ	インデックス型	理由
一般的な空間クエリ	GiST	最も汎用性が高く、すべての演算子に対応
非常に大きな追記専用データ	BRIN	フットプリントが小さく、時間順データに適している
ポイントのみ、均一な分布	SP-GiST	ポイント検索に効率的
geography 列	GiST	唯一の対応オプション
空間属性と通常属性の複合	GiST + B-tree	個別のインデックスまたは式インデックス

テーブル設計例

施設・スポット（POI）

CREATE TABLE pois (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name TEXT NOT NULL,
    category TEXT NOT NULL,
    location GEOGRAPHY(POINT, 4326) NOT NULL,
    address TEXT,
    metadata JSONB DEFAULT '{}',
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    CONSTRAINT valid_category CHECK (category IN (
        'restaurant', 'hotel', 'gas_station', 'hospital', 'school'
    ))
);

-- Spatial index
CREATE INDEX idx_pois_location ON pois USING GIST (location);

-- Category + location for filtered spatial queries
CREATE INDEX idx_pois_category ON pois (category);

-- Find restaurants within 1km
SELECT name, address,
       ST_Distance(
         location,
         ST_SetSRID(ST_MakePoint(-122.4194, 37.7749), 4326)::GEOGRAPHY
       ) AS distance_m
FROM pois
WHERE category = 'restaurant'
  AND ST_DWithin(
    location,
    ST_SetSRID(ST_MakePoint(-122.4194, 37.7749), 4326)::GEOGRAPHY,
    1000
  )
ORDER BY distance_m;

土地区画

CREATE TABLE parcels (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    parcel_id TEXT NOT NULL UNIQUE,
    owner_name TEXT,
    boundary GEOMETRY(MULTIPOLYGON, 4326) NOT NULL,
    centroid GEOMETRY(POINT, 4326) GENERATED ALWAYS AS (ST_Centroid(boundary)) STORED,
    area_sqm DOUBLE PRECISION GENERATED ALWAYS AS (
        ST_Area(boundary::GEOGRAPHY)
    ) STORED,
    perimeter_m DOUBLE PRECISION GENERATED ALWAYS AS (
        ST_Perimeter(boundary::GEOGRAPHY)
    ) STORED,
    CONSTRAINT valid_boundary CHECK (ST_IsValid(boundary)),
    CONSTRAINT closed_boundary CHECK (ST_IsClosed(ST_ExteriorRing(ST_GeometryN(boundary, 1))))
);

CREATE INDEX idx_parcels_boundary ON parcels USING GIST (boundary);
CREATE INDEX idx_parcels_centroid ON parcels USING GIST (centroid);

-- Find parcels intersecting a search area
SELECT parcel_id, owner_name, area_sqm
FROM parcels
WHERE ST_Intersects(boundary, ST_MakeEnvelope(-122.5, 37.7, -122.4, 37.8, 4326));

GPS トラッキング

CREATE TABLE gps_tracks (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    device_id TEXT NOT NULL,
    recorded_at TIMESTAMPTZ NOT NULL,
    location GEOGRAPHY(POINT, 4326) NOT NULL,
    speed_kmh DOUBLE PRECISION,
    heading DOUBLE PRECISION,
    accuracy_m DOUBLE PRECISION
);

-- Composite index for device + time queries
CREATE INDEX idx_gps_device_time ON gps_tracks (device_id, recorded_at DESC);

-- Spatial index for location queries
CREATE INDEX idx_gps_location ON gps_tracks USING GIST (location);

-- Note: GEOGRAPHY supports GiST; BRIN is for GEOMETRY (when appropriate).

-- Create linestring from track points
SELECT
    device_id,
    ST_MakeLine(location::GEOMETRY ORDER BY recorded_at) AS track_line,
    MIN(recorded_at) AS start_time,
    MAX(recorded_at) AS end_time
FROM gps_tracks
WHERE device_id = 'device_001'
  AND recorded_at >= '2024-01-01'
GROUP BY device_id;

サービスエリア／カバレッジゾーン

CREATE TABLE service_zones (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    zone_name TEXT NOT NULL,
    zone_type TEXT NOT NULL,
    boundary GEOMETRY(POLYGON, 4326) NOT NULL,
    population INTEGER,
    active BOOLEAN NOT NULL DEFAULT true,
    CONSTRAINT valid_zone_type CHECK (zone_type IN ('delivery', 'service', 'coverage')),
    CONSTRAINT valid_boundary CHECK (ST_IsValid(boundary))
);

CREATE INDEX idx_zones_boundary ON service_zones USING GIST (boundary);
CREATE INDEX idx_zones_active ON service_zones (active) WHERE active = true;

-- Check if location is within any active service zone
SELECT zone_name, zone_type
FROM service_zones
WHERE active = true
  AND ST_Contains(boundary, ST_SetSRID(ST_MakePoint(-122.4194, 37.7749), 4326));

パフォーマンスパターン

ST_Distance の代わりに ST_DWithin を使用する

-- SLOW: calculates distance for all rows
SELECT * FROM pois
WHERE ST_Distance(location, ref_point) < 1000;

-- FAST: uses spatial index
SELECT * FROM pois
WHERE ST_DWithin(location, ref_point, 1000);

バウンディングボックスによる事前フィルタリングに && を使用する

-- Bounding box operator leverages spatial index
SELECT * FROM parcels
WHERE boundary && ST_MakeEnvelope(-122.5, 37.7, -122.4, 37.8, 4326)
  AND ST_Intersects(boundary, search_polygon);

インデックス対象列への関数適用を避ける

-- SLOW: function prevents index usage
SELECT * FROM parcels WHERE ST_Area(boundary) > 10000;

-- FAST: use generated column with regular index
ALTER TABLE parcels ADD COLUMN area_sqm DOUBLE PRECISION
    GENERATED ALWAYS AS (ST_Area(boundary::GEOGRAPHY)) STORED;
CREATE INDEX idx_parcels_area ON parcels (area_sqm);
SELECT * FROM parcels WHERE area_sqm > 10000;

表示用にジオメトリを単純化する

-- Reduce complexity for web display (tolerance in CRS units)
SELECT
    id,
    name,
    ST_AsGeoJSON(ST_Simplify(boundary, 0.0001)) AS geojson
FROM parcels;

適切な精度を使用する

-- Reduce coordinate precision for storage efficiency
UPDATE locations SET geom = ST_ReducePrecision(geom, 0.000001);

-- GeoJSON with limited decimal places
SELECT ST_AsGeoJSON(location, 6) AS geojson FROM pois;

データ検証

ジオメトリの妥当性チェック

-- Add validity constraint
ALTER TABLE parcels ADD CONSTRAINT valid_geom CHECK (ST_IsValid(boundary));

-- Find and fix invalid geometries
SELECT id, ST_IsValidReason(boundary) AS reason
FROM parcels
WHERE NOT ST_IsValid(boundary);

-- Attempt to fix invalid geometries
UPDATE parcels
SET boundary = ST_MakeValid(boundary)
WHERE NOT ST_IsValid(boundary);

SRID の一貫性

-- Verify SRID consistency
SELECT DISTINCT ST_SRID(geom) FROM spatial_table;

-- Enforce SRID with constraint
ALTER TABLE locations ADD CONSTRAINT enforce_srid
    CHECK (ST_SRID(location) = 4326);

座標範囲の検証

-- Ensure coordinates are within valid WGS84 bounds
ALTER TABLE global_locations ADD CONSTRAINT valid_coords CHECK (
    ST_X(location::GEOMETRY) BETWEEN -180 AND 180 AND
    ST_Y(location::GEOMETRY) BETWEEN -90 AND 90
);

使用しないもの

• PostgreSQL 組み込み型（POINT、LINE、POLYGON、CIRCLE）— 代わりに PostGIS 型を使用してください
• SRID 0（未定義）— 常に正しい SRID を指定してください
• フィルタリングでの ST_Distance — インデックス対応の距離クエリには ST_DWithin を使用してください
• 操作での SRID の混在 — 常に最初に共通の SRID に変換してください
• 複雑な分析での GEOGRAPHY — 適切な投影法を使用した GEOMETRY を使用してください
• 過剰に精密な座標 — GPS の精度は約 3～5 m であり、小数点以下 6 桁（0.1 m）で十分です

よくある落とし穴

1. 経度／緯度の順序：PostGIS では (longitude, latitude) = (X, Y) を使用します。(lat, lon) ではありません
2. GEOGRAPHY の距離単位：表示方法にかかわらず、常にメートル単位です
3. インデックスが使用されない：EXPLAIN ANALYZE を実行して、空間インデックスが使用されていることを確認してください
4. 変換のパフォーマンス：クエリを繰り返す場合は、変換済みのジオメトリをキャッシュしてください
5. 大きなジオメトリ：非常に複雑なポリゴンには、ST_Subdivide の使用を検討してください
6. SQL インジェクション／安全でない動的 SQL：信頼できない入力を SQL に連結しないでください。値はパラメーター化してください。動的な識別子には、安全な引用符付け（quote_ident、format('%I', ...)）または厳格な許可リストを使用してください。