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

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 格式选择指南

格式优点缺点推荐场景
GeoJSONWeb 原生、人可读大文件效率低Web API、前端交互
WKB/HEX紧凑高效不可读数据库内部存储
WKT人可读、简洁不含 SRID调试、日志
KMLGoogle Earth 支持XML 冗余Google Earth 展示
ShapefileGIS 软件通用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 本章小结

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

扩展阅读