强曰为道
与天地相似，故不违。知周乎万物，而道济天下，故不过。旁行而不流，乐天知命，故不忧.
文档目录

PostGIS 完全指南 / 第 13 章：GeoJSON 与数据交换

第 13 章：GeoJSON 与数据交换

13.1 GeoJSON 概述

GeoJSON 是基于 JSON 的地理空间数据交换格式（RFC 7946），它是 Web GIS 应用中最常用的数据格式。

GeoJSON 结构

GeoJSON
├── Point                   -- 点
├── LineString              -- 线
├── Polygon                 -- 面
├── MultiPoint              -- 多点
├── MultiLineString         -- 多线
├── MultiPolygon            -- 多面
├── GeometryCollection       -- 几何集合
├── Feature                  -- 要素（几何 + 属性）
└── FeatureCollection        -- 要素集合

基本示例

{
    "type": "Feature",
    "geometry": {
        "type": "Point",
        "coordinates": [116.4074, 39.9042]
    },
    "properties": {
        "name": "天安门",
        "city": "北京"
    }
}

13.2 GeoJSON 坐标顺序

RFC 7946 规定 GeoJSON 坐标顺序为 [经度, 纬度, 高程]，即 [X, Y, Z]。

{
    "type": "Point",
    "coordinates": [116.4074, 39.9042, 44.0]
}
//     经度 lng ↑    纬度 lat ↑   高程 z ↑

注意: 某些旧工具或中国地图服务可能使用 [纬度, 经度] 的顺序，导入前务必确认。

13.3 PostGIS 与 GeoJSON 转换

从 GeoJSON 创建几何

-- 基本导入
SELECT ST_GeomFromGeoJSON('{
    "type": "Point",
    "coordinates": [116.4074, 39.9042]
}');

-- 导入 Feature（需要提取 geometry 部分）
CREATE TABLE geojson_import (
    id SERIAL PRIMARY KEY,
    properties JSONB,
    geom GEOMETRY(Geometry, 4326)
);

-- 从 JSONB 字段提取几何
INSERT INTO geojson_import (properties, geom)
SELECT
    '{"name": "天安门", "city": "北京"}'::jsonb,
    ST_GeomFromGeoJSON('{"type":"Point","coordinates":[116.4074,39.9042]}');

-- 批量导入 GeoJSON FeatureCollection
CREATE OR REPLACE FUNCTION import_geojson_featurecollection(fc JSONB)
RETURNS INTEGER AS $$
DECLARE
    feature JSONB;
    count INTEGER := 0;
BEGIN
    FOR feature IN SELECT jsonb_array_elements(fc->'features')
    LOOP
        INSERT INTO geojson_import (properties, geom)
        VALUES (
            feature->'properties',
            ST_GeomFromGeoJSON(feature->'geometry')
        );
        count := count + 1;
    END LOOP;
    RETURN count;
END;
$$ LANGUAGE plpgsql;

-- 使用
SELECT import_geojson_featurecollection('{
    "type": "FeatureCollection",
    "features": [
        {"type": "Feature", "geometry": {"type": "Point", "coordinates": [116.4074, 39.9042]}, "properties": {"name": "天安门"}},
        {"type": "Feature", "geometry": {"type": "Point", "coordinates": [121.4737, 31.2304]}, "properties": {"name": "外滩"}}
    ]
}'::jsonb);

从几何导出 GeoJSON

-- 导出单个几何
SELECT ST_AsGeoJSON(ST_SetSRID(ST_MakePoint(116.4074, 39.9042), 4326));
-- 输出: {"type":"Point","coordinates":[116.4074,39.9042]}

-- 导出带精度控制
SELECT ST_AsGeoJSON(geom, 6, 0) FROM cities LIMIT 1;
-- 参数: 几何, 小数位数, 选项 (0=默认, 1=短格式, 2=去除bbox, 4=Long_CRS, 8=Short_CRS)

-- 导出完整的 Feature
SELECT jsonb_build_object(
    'type', 'Feature',
    'id', id,
    'geometry', ST_AsGeoJSON(geom)::jsonb,
    'properties', jsonb_build_object(
        'name', name,
        'province', province,
        'population', population
    )
) AS feature
FROM cities
WHERE name = '北京';

13.4 导出完整 FeatureCollection

-- 完整的 FeatureCollection 导出函数
CREATE OR REPLACE FUNCTION export_geojson(
    p_table TEXT,
    p_geom_col TEXT DEFAULT 'geom',
    p_id_col TEXT DEFAULT 'id',
    p_props_cols TEXT[] DEFAULT NULL
) RETURNS JSONB AS $$
DECLARE
    result JSONB;
    props_expr TEXT;
BEGIN
    -- 构建属性表达式
    IF p_props_cols IS NULL THEN
        props_expr := 'NULL';
    ELSE
        props_expr := 'jsonb_build_object(' ||
            string_agg(quote_literal(unnest) || ', ' || unnest, ', ') ||
        ')';
    END IF;

    EXECUTE format($q$
        SELECT jsonb_build_object(
            'type', 'FeatureCollection',
            'features', COALESCE(jsonb_agg(feature), '[]'::jsonb)
        )
        FROM (
            SELECT jsonb_build_object(
                'type', 'Feature',
                'id', %I,
                'geometry', ST_AsGeoJSON(%I)::jsonb,
                'properties', %s
            ) AS feature
            FROM %I
        ) sub
    $q$, p_id_col, p_geom_col, props_expr, p_table)
    INTO result;

    RETURN result;
END;
$$ LANGUAGE plpgsql;

-- 使用示例（简化版）
SELECT jsonb_build_object(
    'type', 'FeatureCollection',
    'features', jsonb_agg(
        jsonb_build_object(
            'type', 'Feature',
            'id', id,
            'geometry', ST_AsGeoJSON(geom)::jsonb,
            'properties', jsonb_build_object(
                'name', name,
                'province', province,
                'population', population
            )
        )
    )
) AS geojson
FROM cities;

13.5 GeoJSON API 设计

RESTful 空间 API

-- 创建 API 查询函数
CREATE OR REPLACE FUNCTION api_nearby_pois(
    lng DOUBLE PRECISION,
    lat DOUBLE PRECISION,
    radius_m INTEGER DEFAULT 1000,
    category TEXT DEFAULT NULL,
    limit_n INTEGER DEFAULT 20
) RETURNS JSONB AS $$
DECLARE
    result JSONB;
BEGIN
    SELECT jsonb_build_object(
        'type', 'FeatureCollection',
        'features', COALESCE(jsonb_agg(feature), '[]'::jsonb),
        'meta', jsonb_build_object(
            'query_point', jsonb_build_object('lng', lng, 'lat', lat),
            'radius_m', radius_m,
            'total', count(*)
        )
    )
    INTO result
    FROM (
        SELECT jsonb_build_object(
            'type', 'Feature',
            'id', p.id,
            'geometry', ST_AsGeoJSON(p.geom)::jsonb,
            'properties', jsonb_build_object(
                'name', p.name,
                'category', p.category,
                'address', p.address,
                'distance_m', ROUND(ST_Distance(
                    p.geom::geography,
                    ST_SetSRID(ST_MakePoint(lng, lat), 4326)::geography
                ))
            )
        ) AS feature
        FROM pois p
        WHERE ST_DWithin(
            p.geom::geography,
            ST_SetSRID(ST_MakePoint(lng, lat), 4326)::geography,
            radius_m
        )
        AND (category IS NULL OR p.category = category)
        ORDER BY p.geom <-> ST_SetSRID(ST_MakePoint(lng, lat), 4326)
        LIMIT limit_n
    ) sub;

    RETURN result;
END;
$$ LANGUAGE plpgsql STABLE;

-- 调用
SELECT api_nearby_pois(116.4074, 39.9042, 3000, '餐厅', 10);

分页查询

-- 分页导出 GeoJSON
CREATE OR REPLACE FUNCTION api_pois_page(
    page_num INTEGER DEFAULT 1,
    page_size INTEGER DEFAULT 100,
    bbox TEXT DEFAULT NULL
) RETURNS JSONB AS $$
DECLARE
    result JSONB;
    filter_geom GEOMETRY;
BEGIN
    -- 解析 bbox
    IF bbox IS NOT NULL THEN
        filter_geom := ST_MakeEnvelope(
            split_part(bbox, ',', 1)::DOUBLE PRECISION,
            split_part(bbox, ',', 2)::DOUBLE PRECISION,
            split_part(bbox, ',', 3)::DOUBLE PRECISION,
            split_part(bbox, ',', 4)::DOUBLE PRECISION,
            4326
        );
    END IF;

    SELECT jsonb_build_object(
        'type', 'FeatureCollection',
        'features', COALESCE(jsonb_agg(feature), '[]'::jsonb),
        'pagination', jsonb_build_object(
            'page', page_num,
            'page_size', page_size,
            'total', (SELECT count(*) FROM pois WHERE filter_geom IS NULL OR geom && filter_geom)
        )
    )
    INTO result
    FROM (
        SELECT jsonb_build_object(
            'type', 'Feature',
            'id', id,
            'geometry', ST_AsGeoJSON(geom)::jsonb,
            'properties', jsonb_build_object(
                'name', name,
                'category', category,
                'address', address
            )
        ) AS feature
        FROM pois
        WHERE filter_geom IS NULL OR geom && filter_geom
        ORDER BY id
        LIMIT page_size OFFSET (page_num - 1) * page_size
    ) sub;

    RETURN result;
END;
$$ LANGUAGE plpgsql STABLE;

13.6 其他空间数据格式

WKT / WKB

-- WKT (Well-Known Text)
SELECT ST_AsText(geom) FROM cities LIMIT 1;
-- 输出: POINT(116.4074 39.9042)

SELECT ST_GeomFromText('POINT(116.4074 39.9042)', 4326);

-- WKB (Well-Known Binary)
SELECT ST_AsBinary(geom) FROM cities LIMIT 1;
SELECT ST_AsEWKB(geom) FROM cities LIMIT 1;  -- 含 SRID

-- HEX 格式
SELECT ST_AsHexEWKB(geom) FROM cities LIMIT 1;

KML

-- 从 KML 导入
SELECT ST_GeomFromKML('<Point><coordinates>116.4074,39.9042</coordinates></Point>');

-- 导出为 KML
SELECT ST_AsKML(geom) FROM cities LIMIT 1;
-- 输出: <Point><coordinates>116.4074,39.9042</coordinates></Point>

GML

-- 从 GML 导入
SELECT ST_GeomFromGML('<gml:Point><gml:coordinates>116.4074,39.9042</gml:coordinates></gml:Point>');

-- 导出为 GML
SELECT ST_AsGML(geom) FROM cities LIMIT 1;

SVG

-- 导出为 SVG 路径
SELECT ST_AsSVG(geom) FROM cities LIMIT 1;
-- 输出: cx="116.4074" cy="-39.9042" (注意 Y 轴翻转)

13.7 使用 ogr2ogr 格式转换

# PostgreSQL -> GeoJSON
ogr2ogr -f "GeoJSON" /output/cities.geojson \
  PG:"dbname=gisdb" -sql "SELECT name, population, geom FROM cities"

# GeoJSON -> PostgreSQL
ogr2ogr -f "PostgreSQL" \
  PG:"dbname=gisdb user=postgres" \
  /data/input.geojson -nln imported_features

# PostgreSQL -> GeoPackage (推荐的多图层格式)
ogr2ogr -f "GPKG" /output/data.gpkg \
  PG:"dbname=gisdb" -sql "SELECT * FROM cities"

# PostgreSQL -> Shapefile
ogr2ogr -f "ESRI Shapefile" /output/shp/ \
  PG:"dbname=gisdb" -sql "SELECT name, geom FROM cities"

# PostgreSQL -> CSV (含 WKT)
ogr2ogr -f "CSV" /output/cities.csv \
  PG:"dbname=gisdb" \
  -sql "SELECT name, population, ST_AsText(geom) AS wkt FROM cities" \
  -lco GEOMETRY=AS_WKT

13.8 格式选择指南

格式 优点 缺点 推荐场景
GeoJSON Web 原生、人可读 大文件效率低 Web API、前端交互
WKB/HEX 紧凑高效 不可读 数据库内部存储
WKT 人可读、简洁 不含 SRID 调试、日志
KML Google Earth 支持 XML 冗余 Google Earth 展示
Shapefile GIS 软件通用 2GB 限制、字段名 10 字符 GIS 数据交换
GeoPackage 多图层、无大小限制 较新 推荐的通用格式
MVT 瓦片级优化 二进制不可读 Web 地图瓦片

13.9 MVT 矢量瓦片

-- 生成 Mapbox Vector Tile (MVT)
SELECT ST_AsMVT(tile, 'cities') AS mvt
FROM (
    SELECT
        name,
        population,
        ST_AsMVTGeom(
            geom,
            ST_MakeEnvelope(116, 39, 117, 40, 4326),
            4096,   -- 瓦片大小
            256,    -- 缓冲区
            true    -- 按几何裁剪
        ) AS mvt_geom
    FROM cities
    WHERE geom && ST_MakeEnvelope(116, 39, 117, 40, 4326)
) tile;

-- XYZ 瓦片生成函数
CREATE OR REPLACE FUNCTION mvt_tile(z integer, x integer, y integer)
RETURNS bytea AS $$
    SELECT ST_AsMVT(tile, 'layer_name', 4096, 'mvt_geom')
    FROM (
        SELECT
            name,
            ST_AsMVTGeom(geom, ST_TileEnvelope(z, x, y)) AS mvt_geom
        FROM cities
        WHERE geom && ST_TileEnvelope(z, x, y)
    ) AS tile;
$$ LANGUAGE sql STABLE PARALLEL SAFE;

13.10 大规模数据导出优化

-- 使用 COPY 导出为 CSV（含 WKT）
COPY (
    SELECT id, name, population, ST_AsText(geom) AS wkt
    FROM cities
) TO '/tmp/cities.csv' WITH (FORMAT csv, HEADER true);

-- 分块导出大表
CREATE OR REPLACE FUNCTION export_geojson_chunks(
    p_table TEXT,
    p_chunk_size INTEGER DEFAULT 10000
) RETURNS SETOF JSONB AS $$
DECLARE
    total INTEGER;
    i INTEGER;
BEGIN
    EXECUTE format('SELECT count(*) FROM %I', p_table) INTO total;

    FOR i IN 0..CEIL(total::float / p_chunk_size)::INTEGER - 1 LOOP
        RETURN QUERY
        EXECUTE format($q$
            SELECT jsonb_build_object(
                'type', 'FeatureCollection',
                'features', jsonb_agg(feature)
            )
            FROM (
                SELECT jsonb_build_object(
                    'type', 'Feature',
                    'geometry', ST_AsGeoJSON(geom)::jsonb,
                    'properties', to_jsonb(t) - 'geom'
                ) AS feature
                FROM %I t
                ORDER BY id
                LIMIT %s OFFSET %s
            ) sub
        $q$, p_table, p_chunk_size, i * p_chunk_size);
    END LOOP;
END;
$$ LANGUAGE plpgsql;

13.11 本章小结

要点 说明
GeoJSON Web GIS 最通用的数据格式
ST_AsGeoJSON PostGIS 几何 → GeoJSON
ST_GeomFromGeoJSON GeoJSON → PostGIS 几何
ST_AsMVT 生成矢量瓦片
ogr2ogr 多格式空间数据转换工具
坐标顺序 GeoJSON 标准: [经度, 纬度, 高程]

扩展阅读