geojson-wkt（geojson WKT 命令行）

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.

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

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)

Canonical SQL pattern

From JSON input (WKT field)

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;

From a table (WKT column)

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;

Handling SRID (recommended)

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

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>'

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