geojson-wkt

Compare original and translation side by side

🇺🇸

Original

English

🇨🇳

Translation

Chinese

WKT → GeoJSON (PostGIS)

Use this skill when your data includes geometries as WKT strings (e.g.,

"POINT(-87.6 41.8)"

"LINESTRING(...)"

"POLYGON(...)"

) and you want a GeoJSON

FeatureCollection

for map display.

This skill documents the canonical raw SQL pattern for building GeoJSON from WKT using PostGIS.

当你的数据中包含以WKT字符串形式存储的几何图形（例如

"POINT(-87.6 41.8)"

、

"LINESTRING(...)"

、

"POLYGON(...)"

），且你需要将其转换为用于地图展示的GeoJSON

FeatureCollection

时，可以使用该方法。

本文档介绍了使用PostGIS从WKT构建GeoJSON的标准原生SQL方案。

When to use

使用场景

You receive an array of objects like
```
{ id, wkt, ... }
```
You want GeoJSON directly from Postgres
You want consistent property shaping and GeoJSON output

你收到类似
```
{ id, wkt, ... }
```
的对象数组
希望直接从Postgres获取GeoJSON
需要统一的属性格式和GeoJSON输出

Input assumptions

输入前提

WKT is valid (or you’ll get PostGIS parse errors)
The WKT may or may not include an SRID (this pattern assumes no SRID unless you add it)
Output should be web-map friendly (commonly EPSG:4326)

WKT格式有效（否则会触发PostGIS解析错误）
WKT可能包含或不包含SRID（本方案默认无SRID，除非你手动添加）
输出需适配Web地图（通常为EPSG:4326坐标系）

Canonical SQL pattern

标准SQL方案

From JSON input (WKT field)

从JSON输入（含WKT字段）

Use when your API passes rows as a JSON array, and one field contains WKT.

WITH data_rows AS (
  SELECT jsonb_array_elements($1::jsonb) AS row_data
)
SELECT jsonb_build_object(
  'type', 'FeatureCollection',
  'features', jsonb_agg(
    jsonb_build_object(
      'type', 'Feature',
      'geometry', ST_AsGeoJSON(
        ST_GeomFromText(row_data->>'wkt')
      )::jsonb,
      'properties', row_data - 'wkt'
    )
  )
)
FROM data_rows;

当你的API以JSON数组形式传递数据行，且其中一个字段为WKT时使用。

WITH data_rows AS (
  SELECT jsonb_array_elements($1::jsonb) AS row_data
)
SELECT jsonb_build_object(
  'type', 'FeatureCollection',
  'features', jsonb_agg(
    jsonb_build_object(
      'type', 'Feature',
      'geometry', ST_AsGeoJSON(
        ST_GeomFromText(row_data->>'wkt')
      )::jsonb,
      'properties', row_data - 'wkt'
    )
  )
)
FROM data_rows;

From a table (WKT column)

从表中读取（含WKT列）

Use when WKT is stored in a column.

SELECT jsonb_build_object(
  'type', 'FeatureCollection',
  'features', jsonb_agg(
    jsonb_build_object(
      'type', 'Feature',
      'geometry', ST_AsGeoJSON(
        ST_GeomFromText(t.wkt)
      )::jsonb,
      'properties', to_jsonb(t) - 'wkt'
    )
  )
)
FROM my_table t;

当WKT存储在表的某一列中时使用。

SELECT jsonb_build_object(
  'type', 'FeatureCollection',
  'features', jsonb_agg(
    jsonb_build_object(
      'type', 'Feature',
      'geometry', ST_AsGeoJSON(
        ST_GeomFromText(t.wkt)
      )::jsonb,
      'properties', to_jsonb(t) - 'wkt'
    )
  )
)
FROM my_table t;

Handling SRID (recommended)

处理SRID（推荐）

If your WKT is intended to be EPSG:4326, set it explicitly:

ST_SetSRID(ST_GeomFromText(row_data->>'wkt'), 4326)

If you need to transform to 4326 for web clients:

ST_AsGeoJSON(ST_Transform(
  ST_SetSRID(ST_GeomFromText(row_data->>'wkt'), <source_srid>),
  4326
))::jsonb

如果你的WKT目标坐标系为EPSG:4326，请显式设置：

ST_SetSRID(ST_GeomFromText(row_data->>'wkt'), 4326)

如果需要为Web客户端转换到4326坐标系：

ST_AsGeoJSON(ST_Transform(
  ST_SetSRID(ST_GeomFromText(row_data->>'wkt'), <source_srid>),
  4326
))::jsonb

Example input

示例输入

[ { "id": 1, "name": "A", "wkt": "POINT(-87.6298 41.8781)", "status": "active" }, { "id": 2, "name": "B", "wkt": "POINT(-95.3698 29.7604)", "status": "inactive" } ]

Example output (shape)

示例输出（格式）

{ "type": "FeatureCollection", "features": [ { "type": "Feature", "geometry": { "type": "Point", "coordinates": [-87.6298, 41.8781] }, "properties": { "id": 1, "name": "A", "status": "active" } } ] }

Implementation notes

实现说明

Parse WKT with
```
ST_GeomFromText(...)
```
Serialize with
```
ST_AsGeoJSON(...)::jsonb
```
Properties are computed by removing the WKT field:
- ```
row_data - '<wkt_field>'
```

使用
```
ST_GeomFromText(...)
```
解析WKT
使用
```
ST_AsGeoJSON(...)::jsonb
```
序列化
通过移除WKT字段来生成属性：
- ```
row_data - '<wkt_field>'
```

Guardrails

注意事项

Validate/clean WKT upstream when possible (invalid WKT will error)
Enforce feature limits for browser safety
Prefer explicit SRID assignment (
```
ST_SetSRID
```
) when SRID is known

尽可能在数据上游验证/清理WKT（无效WKT会导致错误）
为保障浏览器安全，限制Feature数量
当已知SRID时，优先显式指定（使用
```
ST_SetSRID
```
）