geojson-points

Compare original and translation side by side

🇺🇸

Original

English

🇨🇳

Translation

Chinese

Points → GeoJSON (PostGIS)

点数据→GeoJSON（PostGIS）

Use this skill when you have tabular data (rows or JSON) that include latitude/longitude fields and you need a GeoJSON

FeatureCollection

for map display.

This skill documents the canonical raw SQL pattern for building GeoJSON from point data in PostGIS.

当你拥有包含纬度/经度字段的表格数据（行或JSON），且需要GeoJSON

FeatureCollection

用于地图展示时，可以使用该Skill。

本Skill记录了在PostGIS中从点数据构建GeoJSON的标准原生SQL模式。

When to use

适用场景

You have rows like
```
{ id, lat, lng, ... }
```
You want GeoJSON directly from Postgres
You want predictable geometry and property handling

你拥有类似
```
{ id, lat, lng, ... }
```
的行数据
你希望直接从Postgres获取GeoJSON
你需要可预测的几何图形和属性处理方式

Input assumptions

输入假设

Latitude and longitude are stored as numeric or castable to numeric
Longitude comes first in point construction
Geometry is output in WGS84 (EPSG:4326)

纬度和经度以数值类型存储或可转换为数值类型
构建点时经度在前
几何图形输出采用WGS84坐标系（EPSG:4326）

Canonical SQL pattern

标准SQL模式

From JSON input

从JSON输入转换

Use when your API passes rows as a JSON array.

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_Point(
          (row_data->>'lng')::float,
          (row_data->>'lat')::float
        )
      )::jsonb,
      'properties', row_data - 'lat' - 'lng'
    )
  )
)
FROM data_rows;

当你的API以JSON数组形式传递行数据时使用。

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_Point(
          (row_data->>'lng')::float,
          (row_data->>'lat')::float
        )
      )::jsonb,
      'properties', row_data - 'lat' - 'lng'
    )
  )
)
FROM data_rows;

From a table

从表中转换

Use when latitude/longitude are stored in columns.

SELECT jsonb_build_object(
  'type', 'FeatureCollection',
  'features', jsonb_agg(
    jsonb_build_object(
      'type', 'Feature',
      'geometry', ST_AsGeoJSON(
        ST_Point(lng, lat)
      )::jsonb,
      'properties', to_jsonb(t) - 'lat' - 'lng'
    )
  )
)
FROM my_table t;

当纬度/经度存储在表的列中时使用。

SELECT jsonb_build_object(
  'type', 'FeatureCollection',
  'features', jsonb_agg(
    jsonb_build_object(
      'type', 'Feature',
      'geometry', ST_AsGeoJSON(
        ST_Point(lng, lat)
      )::jsonb,
      'properties', to_jsonb(t) - 'lat' - 'lng'
    )
  )
)
FROM my_table t;

Coordinate rules (critical)

坐标规则（重点）

GeoJSON coordinates are [longitude, latitude]
Latitude range:
```
-90..90
```
Longitude range:
```
-180..180
```

Most incorrect maps come from swapped coordinates.

GeoJSON坐标格式为**
```
[经度, 纬度]
```
**
纬度范围：
```
-90..90
```
经度范围：
```
-180..180
```

大多数地图显示错误都源于坐标顺序颠倒。

Output shape

输出结构

```
type
```
:
```
"FeatureCollection"
```
```
features
```
: array of GeoJSON
```
Feature
```
objects

Each feature has:

```
geometry.type = "Point"
```
```
geometry.coordinates = [lng, lat]
```
```
properties
```
= remaining fields

```
type
```
:
```
"FeatureCollection"
```
```
features
```
: GeoJSON
```
Feature
```
对象数组

每个Feature包含：

```
geometry.type = "Point"
```
```
geometry.coordinates = [lng, lat]
```
```
properties
```
= 剩余字段

Implementation notes

实现说明

Use

ST_Point(lng, lat)

(not

ST_MakePoint(lat, lng)

)

```
ST_AsGeoJSON(... )::jsonb
```
avoids string parsing downstream
Removing coord fields keeps
```
properties
```
clean and map-safe

使用

ST_Point(lng, lat)

（而非

ST_MakePoint(lat, lng)

）

```
ST_AsGeoJSON(... )::jsonb
```
可避免下游的字符串解析
移除坐标字段可保持
```
properties
```
简洁且适用于地图展示

Guardrails

注意事项

Filter rows with missing or invalid coordinates before conversion
Enforce feature limits for browser safety
Explicitly select properties if payload size matters

在转换前过滤缺失或无效坐标的行数据
为保障浏览器安全，限制Feature的数量
若有效负载大小受限，可显式选择所需属性