实习日志

10月9日 第一天

工作内容：

postgis，今天主要了解空间数据类型有哪些；空间数据格式有哪些，不同格式之间的转换；空间数据的增删查改；常用基础空间查询函数的使用；常用分析函数的使用。

工作收获

空间函数的数据类型有哪些

PostGIS 作为 PostgreSQL 的空间扩展，定义了一系列用于处理空间数据的特殊数据类型，这些类型是空间函数操作的基础。以下是 PostGIS 中主要的空间数据类型分类及说明：

1. 基本几何类型（Geometry）

geometry 是 PostGIS 中最核心的空间数据类型，用于表示二维平面上的几何对象，遵循 OGC 简单要素规范（SFS），支持以下子类型：

  • 点（Point）：零维对象，表示单个坐标点（如 POINT(1 2)）。
  • 线串（LineString）：一维对象，由多个点连接成的线段（如 LINESTRING(0 0, 1 1, 2 0)）。
  • 多边形（Polygon）：二维对象，由闭合线串及内部区域组成（如 POLYGON((0 0, 0 1, 1 1, 1 0, 0 0))）。
  • 多点（MultiPoint）：多个点的集合（如 MULTIPOINT(1 2, 3 4)）。
  • 多线串（MultiLineString）：多个线串的集合（如 MULTILINESTRING((0 0,1 1), (2 2,3 3))）。
  • 多 polygon（MultiPolygon）：多个多边形的集合（如 MULTIPOLYGON(((0 0,0 1,1 1,1 0,0 0)), ((2 2,2 3,3 3,3 2,2 2)))）。
  • 几何集合（GeometryCollection）：多种几何类型的混合集合（如 GEOMETRYCOLLECTION(POINT(1 2), LINESTRING(0 0,1 1))）。

每个 geometry 对象都包含 空间参考系（SRID），用于定义坐标的投影和单位（如 WGS84 对应 SRID=4326）。

2. 地理类型（Geography）

geography 类型用于表示椭球面上的地理数据（如地球表面的经纬度坐标），与 geometry 的主要区别：

  • 基于球面计算距离、面积等（而非平面），结果更符合实际地理场景（如地球曲率影响）。
  • 支持的子类型与 geometry 一致（点、线串、多边形等），但隐含 SRID 为 WGS84（4326，经纬度坐标），也可指定其他椭球坐标系。
  • 计算成本高于 geometry，适合大范围（跨半球）数据，小范围数据常用 geometry。

3. 三维与测量类型

PostGIS 支持带 Z 坐标（三维）和 M 坐标（测量值，如时间、距离）的扩展类型：

  • 3D 几何：在基本类型后加 Z，如 PointZ（三维点）、LineStringZ（三维线串），表示包含高程信息的对象。
  • 测量几何：加 M，如 PointM（带测量值的点）、LineStringZM（三维 + 测量值的线串）。

4. 其他辅助类型

  • BOX2D：二维边界框，表示 geometry 的最小外接矩形（如 BOX2D(0 0, 1 1)）。
  • BOX3D：三维边界框，包含 Z 坐标的最小外接立方体。
  • GID：部分函数返回的几何 ID（非标准类型，较少直接使用）。
  • Raster：栅格数据类型（需启用 PostGIS Raster 扩展），用于处理影像、遥感数据等。

5. 总结

PostGIS 的空间函数主要操作 geometry 和 geography 两大核心类型，其中 geometry 适用于平面投影数据，geography 适用于球面地理数据，两者均支持点、线、面等基本几何形态及三维 / 测量扩展。选择类型时需根据数据的空间尺度、投影方式及计算需求决定。

6. 使用举例

geometry：平面坐标，适合小范围，计算快（如城市内建筑、道路）。

geography：球面坐标，适合大范围，结果更精确（如跨国距离、全球城市）。

Z/M 类型：扩展维度，用于高程、时间等附加信息（如山峰海拔、路线距离）。

边界框：辅助快速范围查询，优化性能。

1. 基本几何类型（geometry）

适用于平面坐标（如 utm 投影、地方坐标系），计算效率高，适合小范围（如城市、园区）数据。

示例 1：创建点（Point）并查询距离

假设某城市使用 utm 投影（SRID=32650，单位米），存储两个建筑的坐标：

-- 创建测试表（带SRID约束）
CREATE TABLE buildings (
    id INT PRIMARY KEY,
    name TEXT,
    geom geometry(Point, 32650)  -- 明确类型为Point，SRID=32650
);

-- 插入数据（坐标单位为米）
INSERT INTO buildings (id, name, geom) VALUES
(1, '图书馆', ST_SetSRID(ST_MakePoint(300000, 4000000), 32650)),  -- ST_MakePoint生成点，ST_SetSRID指定坐标系
(2, '体育馆', ST_SetSRID(ST_MakePoint(300100, 4000200), 32650));

-- 计算两点距离（平面距离，单位米）
SELECT ST_Distance(b1.geom, b2.geom) AS distance_m
FROM buildings b1, buildings b2
WHERE b1.id = 1 AND b2.id = 2;
-- 结果约为 223.607 米（勾股定理：√(100² + 200²)）

示例 2：创建多边形（Polygon）并判断包含关系

存储一个园区的边界，判断某点是否在园区内：

-- 创建园区表
CREATE TABLE parks (
    id INT PRIMARY KEY,
    name TEXT,
    geom geometry(Polygon, 32650)
);

-- 插入园区边界（闭合多边形，坐标按顺时针/逆时针顺序）
INSERT INTO parks (id, name, geom) VALUES
(1, '中央公园', ST_SetSRID(ST_MakePolygon(
    ST_GeomFromText('LINESTRING(299900 3999900, 300100 3999900, 300100 4000100, 299900 4000100, 299900 3999900)')
), 32650));

-- 判断图书馆是否在公园内（使用ST_Contains）
SELECT p.name, ST_Contains(p.geom, b.geom) AS is_inside
FROM parks p, buildings b
WHERE p.id = 1 AND b.id = 1;
-- 结果：t（true，图书馆在公园内）
2. 地理类型（geography）

适用于球面坐标（经纬度，SRID=4326），计算结果考虑地球曲率，适合大范围（如跨国、全球）数据。

示例：计算两地球面距离（经纬度）

存储两个城市的经纬度，计算实际地表距离：

-- 创建城市表（geography类型默认SRID=4326，单位度）
CREATE TABLE cities (
    id INT PRIMARY KEY,
    name TEXT,
    geog geography(Point)  -- 无需显式指定SRID，默认WGS84经纬度
);

-- 插入数据（经度,纬度）
INSERT INTO cities (id, name, geog) VALUES
(1, '北京', ST_MakePoint(116.4074, 39.9042)::geography),  -- 经纬度转geography
(2, '上海', ST_MakePoint(121.4737, 31.2304)::geography);

-- 计算球面距离（单位米，自动考虑地球曲率）
SELECT ST_Distance(c1.geog, c2.geog) / 1000 AS distance_km
FROM cities c1, cities c2
WHERE c1.id = 1 AND c2.id = 2;
-- 结果约为 1317 公里（接近实际公路距离）
3. 三维与测量类型（Z/M 坐标）

用于包含高程（Z）或附加测量值（如时间、温度）的场景。

示例：三维点（PointZ）存储海拔

记录山峰的经纬度和海拔：

CREATE TABLE mountains (
    id INT PRIMARY KEY,
    name TEXT,
    geom geometry(PointZ, 4326)  -- 三维点，SRID=4326（经纬度+海拔）
);

-- 插入数据（经度,纬度,海拔米）
INSERT INTO mountains (id, name, geom) VALUES
(1, '珠穆朗玛峰', ST_SetSRID(ST_MakePoint(86.9250, 27.9881, 8848.86), 4326));

-- 查询海拔（提取Z坐标）
SELECT name, ST_Z(geom) AS elevation_m FROM mountains WHERE id = 1;
-- 结果：8848.86 米

示例：带测量值的线串（LineStringM）

记录一条 hiking 路线的经纬度和累计距离（M 值）：

CREATE TABLE hiking_routes (
    id INT PRIMARY KEY,
    name TEXT,
    geom geometry(LineStringM, 4326)  -- 线串+测量值（距离）
);

-- 插入路线：点依次为起点、中途点、终点，M值为累计距离（公里）
INSERT INTO hiking_routes (id, name, geom) VALUES
(1, '香山步道', ST_SetSRID(
    ST_GeomFromText('LINESTRINGM(116.191 39.998 0, 116.202 40.003 1.5, 116.210 40.009 3.2)'),
    4326
));

-- 提取中途点的距离（M值）
SELECT ST_M(ST_PointN(geom, 2)) AS distance_km  -- ST_PointN取第2个点，ST_M取M值
FROM hiking_routes WHERE id = 1;
-- 结果：1.5 公里
4. 边界框类型（BOX2D/BOX3D）

用于快速范围查询，避免复杂几何计算。

示例：用 BOX2D 筛选范围内的点

查询某矩形区域内的建筑：

-- 定义查询范围：左下角(299950, 3999950)，右上角(300050, 4000050)（utm坐标）
SELECT b.name, ST_AsText(b.geom) AS coordinates
FROM buildings b
WHERE ST_Intersects(b.geom, ST_MakeEnvelope(299950, 3999950, 300050, 4000050, 32650));
-- 结果：返回图书馆（坐标300000,4000000在范围内）

ST_MakeEnvelope 生成 BOX2D 边界框，ST_Intersects 判断几何是否与边界框相交，效率高于直接用多边形判断。

空间数据格式有哪些

在 PostGIS 中，空间数据的 “格式” 通常指数据的存储格式和序列化格式（用于数据输入、输出或交换）。以下是主要的空间数据格式分类及说明：

1. 存储格式（数据库内部存储）

PostGIS 在 PostgreSQL 中存储空间数据时，采用高效的二进制格式，主要包括：

  1. EWKB（Extended Well-Known Binary）

    • 是 PostGIS 内部存储空间数据的原生格式，基于 OGC 标准的 WKB（Well-Known Binary）扩展而来。
    • 相比 WKB，EWKB 增加了SRID（空间参考标识符） 的存储，能直接关联坐标系信息（而 WKB 本身不包含 SRID）。
    • 二进制格式紧凑，读写效率高，适合数据库内部存储和快速计算。
    • 示例：PostGIS 表中 geometry 或 geography 类型的字段值，本质上就是 EWKB 编码的二进制数据。

  2. GEOGRAPHY 类型的存储

    • geography 类型本质上也是以 EWKB 格式存储，但隐含默认 SRID=4326（WGS84 经纬度），且存储的坐标是经纬度（度）而非平面坐标（米）

2. 序列化格式（输入 / 输出 / 交换格式）

在与外部系统交互（如导入、导出、查询返回）时，PostGIS 支持多种文本或二进制格式，常用的有：

  1. WKT（Well-Known Text）

    • 人类可读的文本格式，用于直观表示空间几何。
    • 语法示例：
      • 点：POINT(116.4 39.9)（经度，纬度）
      • 线串：LINESTRING(0 0, 1 1, 2 0)
      • 多边形：POLYGON((0 0, 0 1, 1 1, 1 0, 0 0))
    • 缺点：不包含 SRID 信息，需额外指定坐标系。
    • PostGIS 函数：ST_AsText(geom) 可将内部格式转为 WKT；ST_GeomFromText(wkt, srid) 可将 WKT 转为内部格式（需指定 SRID）。

  2. WKB（Well-Known Binary）

    • 二进制格式，比 WKT 更紧凑，适合计算机传输和存储（但人类不可读）。
    • 与 WKT 对应，语法相同但以二进制编码。
    • 缺点：同样不包含 SRID，需单独处理坐标系。
    • PostGIS 函数：ST_AsBinary(geom) 转为 WKB；ST_GeomFromWKB(wkb, srid) 解析 WKB 并指定 SRID。

  3. EWKT（Extended Well-Known Text）

    • WKT 的扩展，在文本中直接包含 SRID 信息，格式为 SRID=srid;geometry。
    • 示例：SRID=4326;POINT(116.4 39.9)（明确指定该点属于 WGS84 坐标系）。
    • 解决了 WKT 缺少 SRID 的问题，简化了数据交换。
    • PostGIS 函数：ST_AsEWKT(geom) 转为 EWKT；ST_GeomFromEWKT(ewkt) 直接解析 EWKT（无需单独指定 SRID）。

  4. GeoJSON

    • 基于 JSON 的轻量级空间数据交换格式，广泛用于 Web 地图（如 Leaflet、OpenLayers）和 API 接口。

    • 包含几何类型、坐标、坐标系（可选，默认 WGS84）等信息。

    • 示例：

      {
        "type": "Point",
        "coordinates": [116.4, 39.9]  // [经度, 纬度]
      }

    • PostGIS 函数：ST_AsGeoJSON(geom) 转为 GeoJSON；ST_GeomFromGeoJSON(geojson) 解析 GeoJSON 为内部格式。

  5. KML

    • 由 Google 定义的 XML 格式，主要用于 Google Earth 等地理信息可视化工具。

    • 支持点、线、面、多边形等几何类型，包含样式信息。

    • 示例：

      <Point><coordinates>116.4,39.9</coordinates></Point>

    • PostGIS 函数：ST_AsKML(geom) 转为 KML；ST_GeomFromKML(kml) 解析 KML 为内部格式。

  6. 其他格式

    • Shapefile：PostGIS 不直接存储，但可通过 shp2pgsql 工具将 Shapefile 导入 PostGIS，或通过 pgsql2shp 导出。
    • SVG：可通过 ST_AsSVG 函数将几何转为 SVG 格式，用于网页矢量图形。

3. 总结

  • 内部存储：主要使用 EWKB（含 SRID，高效二进制）。
  • 外部交换：
    • 人类可读：WKT（无 SRID）、EWKT（含 SRID）、GeoJSON（Web 常用）、KML（Google 工具）。
    • 机器友好：WKB（二进制，无 SRID）、EWKB（二进制，含 SRID）。

实际使用中，可根据场景选择格式（如 Web 开发用 GeoJSON，数据导入用 EWKT，内部计算依赖 EWKB）。

4. 举例子

WKT/EWKT：适合手动输入、调试（文本易读，EWKT 带坐标系）。

WKB/EWKB：适合程序间传输、数据库内部存储（二进制高效）。

GeoJSON：Web 地图开发首选（JSON 格式易被前端解析）。

KML：与 Google Earth 等工具交互时使用。

Shapefile：通过工具导入导出，兼容传统 GIS 软件。

根据场景选择格式（如前端展示用 GeoJSON，数据备份用 EWKT，程序交互用 WKB）。

1. WKT（Well-Known Text）格式

人类可读的文本格式，无 SRID 信息，需手动指定坐标系。

示例：用 WKT 创建点并转换格式

-- 1. 从 WKT 文本创建 geometry 对象（需指定 SRID=4326）
SELECT ST_GeomFromText('POINT(116.4074 39.9042)', 4326) AS geom_wkt;

-- 2. 将内部存储的 geometry 转为 WKT 文本
SELECT ST_AsText(ST_GeomFromText('POINT(116.4074 39.9042)', 4326)) AS wkt_result;
-- 输出：POINT(116.4074 39.9042)（仅坐标，无坐标系信息）
2. EWKT（Extended WKT）格式

WKT 的扩展，文本中直接包含 SRID，无需额外指定。

示例：用 EWKT 创建线串并解析

-- 1. 从 EWKT 文本创建 geometry（自带 SRID=32650）
SELECT ST_GeomFromEWKT('SRID=32650;LINESTRING(300000 4000000, 300100 4000200)') AS geom_ewkt;

-- 2. 将 geometry 转为 EWKT 文本（包含 SRID）
SELECT ST_AsEWKT(ST_GeomFromEWKT('SRID=32650;LINESTRING(300000 4000000, 300100 4000200)')) AS ewkt_result;
-- 输出：SRID=32650;LINESTRING(300000 4000000,300100 4000200)
3. WKB/EWKB（二进制格式）

机器友好的二进制格式，WKB 无 SRID，EWKB 含 SRID（PostGIS 内部存储格式）。

示例：转换为 WKB/EWKB 并查看

-- 1. 将 geometry 转为 WKB（二进制，无 SRID）
SELECT ST_AsBinary(ST_GeomFromText('POINT(116.4 39.9)', 4326)) AS wkb_result;
-- 输出：二进制数据（人类不可读，类似 \x0101000000...）

-- 2. 将 geometry 转为 EWKB（二进制，含 SRID）
SELECT ST_AsEWKB(ST_GeomFromText('POINT(116.4 39.9)', 4326)) AS ewkb_result;
-- 输出：包含 SRID 的二进制数据（PostGIS 内部存储的就是这种格式）
4. GeoJSON 格式

Web 开发常用，基于 JSON，适合前端地图展示（如 Leaflet、OpenLayers）。

示例：生成 GeoJSON 并解析

-- 1. 将多边形转为 GeoJSON
SELECT ST_AsGeoJSON(
  ST_GeomFromText('POLYGON((116.3 39.8, 116.4 39.8, 116.4 39.9, 116.3 39.9, 116.3 39.8))', 4326)
) AS geojson_result;

-- 输出（格式化后）：
{
  "type": "Polygon",
  "coordinates": [
    [
      [116.3, 39.8],
      [116.4, 39.8],
      [116.4, 39.9],
      [116.3, 39.9],
      [116.3, 39.8]
    ]
  ]
}

-- 2. 从 GeoJSON 解析为 geometry
SELECT ST_GeomFromGeoJSON('{"type":"Point","coordinates":[116.4,39.9]}') AS geom_from_geojson;
5. KML格式

用于 Google Earth 等工具，基于 XML。

示例：转换为 KML 格式

-- 将点转为 KML
SELECT ST_AsKML(ST_GeomFromText('POINT(116.4074 39.9042)', 4326)) AS kml_result;
-- 输出：<Point><coordinates>116.4074,39.9042,0</coordinates></Point>
6. Shapefile 格式（导入 / 导出）

通过 PostGIS 提供的命令行工具处理（非 SQL 直接操作）。

示例：导入 Shapefile 到 PostGIS

# 命令行执行：将 buildings.shp 导入到数据库（需指定 SRID=32650）
shp2pgsql -s 32650 -I buildings.shp public.buildings | psql -d your_db -U your_user

示例：从 PostGIS 导出为 Shapefile

# 命令行执行：将表中数据导出为 Shapefile
pgsql2shp -f export_buildings -h localhost -u your_user your_db "SELECT * FROM buildings"

不同格式之间的转换

PostGIS 提供了丰富的函数用于不同空间数据格式之间的转换，以下是常见格式转换的示例，涵盖 WKT、EWKT、WKB、EWKB、GeoJSON、KML 等之间的相互转换：

核心转换逻辑

PostGIS 中所有空间格式的转换，通常以 geometry 或 geography 类型为中间载体：格式A → geometry → 格式B

1. 文本格式 ↔︎ 内部 geometry 类型

1. WKT ↔︎ geometry
  • ST_GeomFromText(wkt, srid)：从 WKT 文本创建 geometry（需指定 SRID）。
  • ST_AsText(geom)：将 geometry 转为 WKT 文本。
-- WKT → geometry
SELECT ST_GeomFromText('POINT(116.4 39.9)', 4326) AS geom;

-- geometry → WKT
SELECT ST_AsText(ST_GeomFromText('POINT(116.4 39.9)', 4326)) AS wkt;
-- 输出：POINT(116.4 39.9)
2. EWKT ↔︎ geometry
  • ST_GeomFromEWKT(ewkt)：从 EWKT 文本创建 geometry（EWKT 自带 SRID）。
  • ST_AsEWKT(geom)：将 geometry 转为 EWKT 文本（含 SRID）。
-- EWKT → geometry
SELECT ST_GeomFromEWKT('SRID=32650;LINESTRING(300000 4000000, 300100 4000200)') AS geom;

-- geometry → EWKT
SELECT ST_AsEWKT(ST_GeomFromEWKT('SRID=32650;LINESTRING(300000 4000000, 300100 4000200)')) AS ewkt;
-- 输出：SRID=32650;LINESTRING(300000 4000000,300100 4000200)

2. 二进制格式 ↔︎ 内部 geometry 类型

1. WKB ↔︎ geometry
  • ST_GeomFromWKB(wkb, srid)：从 WKB 二进制创建 geometry（需指定 SRID）。
  • ST_AsBinary(geom)：将 geometry 转为 WKB 二进制。
-- 先将 geometry 转为 WKB（二进制）
SELECT ST_AsBinary(ST_GeomFromText('POINT(116.4 39.9)', 4326)) AS wkb;

-- WKB → geometry（使用上面的 WKB 结果）
SELECT ST_GeomFromWKB(ST_AsBinary(ST_GeomFromText('POINT(116.4 39.9)', 4326)), 4326) AS geom;
2. EWKB ↔︎ geometry
  • ST_GeomFromEWKB(ewkb)：从 EWKB 二进制创建 geometry（EWKB 自带 SRID）。
  • ST_AsEWKB(geom)：将 geometry 转为 EWKB 二进制（PostGIS 内部存储格式）。
-- geometry → EWKB
SELECT ST_AsEWKB(ST_GeomFromText('POINT(116.4 39.9)', 4326)) AS ewkb;

-- EWKB → geometry
SELECT ST_GeomFromEWKB(ST_AsEWKB(ST_GeomFromText('POINT(116.4 39.9)', 4326))) AS geom;

3. Web 常用格式 ↔︎ geometry 类型

1. GeoJSON ↔︎ geometry
  • ST_GeomFromGeoJSON(geojson)：从 GeoJSON 字符串创建 geometry（默认 SRID=4326）。
  • ST_AsGeoJSON(geom)：将 geometry 转为 GeoJSON 字符串。
-- GeoJSON → geometry
SELECT ST_GeomFromGeoJSON('{"type":"Point","coordinates":[116.4,39.9]}') AS geom;

-- geometry → GeoJSON
SELECT ST_AsGeoJSON(ST_GeomFromText('POLYGON((116.3 39.8, 116.4 39.8, 116.4 39.9, 116.3 39.9, 116.3 39.8))', 4326)) AS geojson;
-- 输出：{"type":"Polygon","coordinates":[[[116.3,39.8],[116.4,39.8],[116.4,39.9],[116.3,39.9],[116.3,39.8]]]}
2. KML ↔︎ geometry
  • ST_GeomFromKML(kml)：从 KML 字符串创建 geometry（默认 SRID=4326）。
  • ST_AsKML(geom)：将 geometry 转为 KML 字符串。
-- KML → geometry
SELECT ST_GeomFromKML('<Point><coordinates>116.4,39.9</coordinates></Point>') AS geom;

-- geometry → KML
SELECT ST_AsKML(ST_GeomFromText('LINESTRING(116.3 39.8, 116.4 39.9)', 4326)) AS kml;
-- 输出：<LineString><coordinates>116.3,39.8,0 116.4,39.9,0</coordinates></LineString>

4. 跨格式间接转换（以 geometry 为中间层）

例如：WKT → GeoJSON、KML → EWKT 等，需先转为 geometry，再转目标格式。

1. 示例 1：WKT → GeoJSON
-- 步骤：WKT → geometry → GeoJSON
SELECT ST_AsGeoJSON(
  ST_GeomFromText('POINT(116.4 39.9)', 4326)  -- 先转 geometry
) AS wkt_to_geojson;
-- 输出：{"type":"Point","coordinates":[116.4,39.9]}
2. 示例 2：KML → EWKT
-- 步骤：KML → geometry → EWKT
SELECT ST_AsEWKT(
  ST_GeomFromKML('<LineString><coordinates>116.3,39.8 116.4,39.9</coordinates></LineString>')  -- 先转 geometry
) AS kml_to_ewkt;
-- 输出：SRID=4326;LINESTRING(116.3 39.8,116.4 39.9)

5. 坐标系转换（SRID 变更）

严格来说不属于 “格式转换”，但属于空间数据处理的常见操作（需先确保目标 SRID 已在 PostGIS 中注册）。

-- 将 SRID=4326（经纬度）转为 SRID=32650（UTM 50N，米）
SELECT ST_Transform(
  ST_GeomFromText('POINT(116.4 39.9)', 4326),  -- 原 geometry（WGS84 经纬度）
  32650  -- 目标 SRID
) AS transformed_geom;

-- 查看转换后的 EWKT（含新 SRID）
SELECT ST_AsEWKT(ST_Transform(ST_GeomFromText('POINT(116.4 39.9)', 4326), 32650)) AS ewkt;
-- 输出类似：SRID=32650;POINT(300000 4000000)（具体值取决于经纬度对应的 UTM 坐标）

6. 总结

  • 所有格式转换均以 geometry 类型为中间层，核心是掌握 ST_AsXXX（转为目标格式）和 ST_GeomFromXXX（从源格式创建 geometry）系列函数。
  • 带坐标系的格式（如 EWKT、GeoJSON 带 crs）转换时会自动保留 SRID，无坐标系的格式（如 WKT、WKB）需手动指定 SRID。
  • 实际开发中，根据场景选择转换方向（如前端展示用 ST_AsGeoJSON，数据入库用 ST_GeomFromEWKT）。

空间数据的增删查改

在 PostGIS 中，空间数据的增删查改（CRUD）操作与普通 PostgreSQL 数据类似，但需结合空间数据类型（geometry/geography）和相关函数。以下是具体示例：

1. 准备工作：创建带空间字段的表

-- 创建表（指定 SRID=4326，WGS84 经纬度）
CREATE TABLE cities (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    population INT,  -- 人口
    geom geometry(Point, 4326)  -- 空间字段：点类型，SRID=4326
);

-- 为空间字段创建空间索引（优化查询性能）
CREATE INDEX idx_cities_geom ON cities USING GIST (geom);

2. 增加（Create）空间数据

插入空间数据时，需用 PostGIS 函数生成 geometry 对象（如 ST_MakePoint+ST_SetSRID 或 ST_GeomFromText 等）。

1. 示例：插入城市点数据
-- 方法1：用 ST_MakePoint 生成点（经度, 纬度），并指定 SRID
INSERT INTO cities (name, population, geom)
VALUES 
('北京', 21540000, ST_SetSRID(ST_MakePoint(116.4074, 39.9042), 4326)),
('上海', 24870000, ST_SetSRID(ST_MakePoint(121.4737, 31.2304), 4326)),
('广州', 15300000, ST_SetSRID(ST_MakePoint(113.2644, 23.1291), 4326));

-- 方法2：用 ST_GeomFromText 从 WKT 格式插入（需指定 SRID）
INSERT INTO cities (name, population, geom)
VALUES ('深圳', 13030000, ST_GeomFromText('POINT(114.0579 22.5431)', 4326));

-- 方法3：用 ST_GeomFromEWKT 从 EWKT 格式插入（自带 SRID）
INSERT INTO cities (name, population, geom)
VALUES ('杭州', 10360000, ST_GeomFromEWKT('SRID=4326;POINT(120.1551 30.2741)'));

3. 查询（Read）空间数据

查询时可结合空间函数（如距离计算、范围筛选）和普通字段筛选。

1. 示例 1：查询所有城市的基本信息和空间坐标（WKT 格式）
SELECT 
    id, 
    name, 
    population, 
    ST_AsText(geom) AS coordinates  -- 转为人类可读的 WKT 格式
FROM cities;
2. 示例 2：查询距离北京 1000 公里以内的城市（用 geography 类型计算球面距离）
-- 先将 geometry 转为 geography 类型（支持球面距离计算）
SELECT 
    c.name, 
    ST_Distance(c.geom::geography, bj.geom::geography) / 1000 AS distance_km  -- 距离（公里）
FROM cities c, cities bj
WHERE bj.name = '北京' 
  AND c.name != '北京' 
  AND ST_Distance(c.geom::geography, bj.geom::geography) < 1000000;  -- 1000000 米 = 1000 公里
3. 示例 3：查询某矩形范围内的城市（如经度 110°120°，纬度 20°35°）
-- 用 ST_MakeEnvelope 创建矩形范围（minx, miny, maxx, maxy, srid）
SELECT name, ST_AsText(geom) AS coordinates
FROM cities
WHERE ST_Intersects(
    geom, 
    ST_MakeEnvelope(110, 20, 120, 35, 4326)  -- 矩形范围
);
-- 结果：广州、深圳（北京、上海、杭州不在此范围内）

4. 更新（Update）空间数据

可更新空间字段的坐标或普通字段，结合空间函数修改几何形状。

1. 示例 1：修改城市的坐标（如调整深圳的经纬度）
UPDATE cities
SET geom = ST_SetSRID(ST_MakePoint(114.0665, 22.5461), 4326)  -- 新坐标
WHERE name = '深圳';
2. 示例 2：批量更新坐标系（如从 SRID=4326 转为 UTM 50N 的 SRID=32650）
-- 先添加一个新的 geometry 字段存储 UTM 坐标
ALTER TABLE cities ADD COLUMN geom_utm geometry(Point, 32650);

-- 用 ST_Transform 转换坐标系并更新
UPDATE cities
SET geom_utm = ST_Transform(geom, 32650);  -- 从 4326 转为 32650

5. 删除（Delete）空间数据

1. 示例 1：删除指定城市
DELETE FROM cities
WHERE name = '杭州';
2. 示例 2：删除人口少于 1000 万的城市
DELETE FROM cities
WHERE population < 10000000;
-- 结果：删除人口小于 1000 万的城市（假设示例中只有杭州被删除，其他均超过）

6. 总结

  • 增加：用 ST_MakePoint、ST_GeomFromText 等函数生成 geometry 对象插入。
  • 查询：结合 ST_AsText（转换格式）、ST_Distance（距离）、ST_Intersects（范围）等空间函数。
  • 更新：通过空间函数修改 geometry 字段的值，或用 ST_Transform 转换坐标系。
  • 删除：与普通 SQL 一致，按条件删除记录。

核心是利用 PostGIS 函数处理空间数据的特殊性，同时为空间字段创建 GIST 索引以提升查询效率。

常用基础空间查询函数的使用

PostGIS 提供了丰富的空间查询函数，用于处理几何对象的关系、测量、转换等操作。以下是最常用的基础空间查询函数及其使用示例，基于 geometry 或 geography 类型：

一. 几何关系查询函数

用于判断两个几何对象之间的空间关系（如包含、相交、相邻等），遵循 OGC 简单要素规范。

1. ST_Contains(geomA, geomB)

判断 geomA 是否完全包含 geomB（geomB 的所有点都在 geomA 内）。

示例：查询 “广东省” 多边形完全包含的城市点

-- 假设有 provinces 表（多边形）和 cities 表（点），均为 SRID=4326
SELECT c.name 
FROM cities c, provinces p
WHERE p.name = '广东省' 
  AND ST_Contains(p.geom, c.geom);
2. ST_Intersects(geomA, geomB)

判断 geomA 和 geomB 是否存在交集（至少有一个共同点）。

示例：查询与 “珠江” 线串相交的城市

SELECT c.name 
FROM cities c, rivers r
WHERE r.name = '珠江' 
  AND ST_Intersects(c.geom, r.geom);
3. ST_DWithin(geomA, geomB, distance)

判断 geomA 和 geomB 之间的最短距离是否小于等于 distance（单位取决于 SRID：geometry 用坐标单位，geography 用米）。

示例：查询距离 “上海” 100 公里以内的城市（用 geography 类型计算球面距离）

SELECT c.name, 
       ST_Distance(c.geom::geography, sh.geom::geography)/1000 AS distance_km
FROM cities c, cities sh
WHERE sh.name = '上海' 
  AND c.name != '上海' 
  AND ST_DWithin(c.geom::geography, sh.geom::geography, 100000);  -- 100000米=100公里
4. ST_Touches(geomA, geomB)

判断 geomA 和 geomB 是否边界相交，但内部不重叠（如相邻的两个多边形共享一条边）。

示例：查询与 “北京市” 多边形边界相邻的城市

SELECT c.name 
FROM cities c, provinces p
WHERE p.name = '北京市' 
  AND ST_Touches(p.geom, c.geom);

二、 测量计算函数

用于计算几何对象的距离、长度、面积等。

1. ST_Distance(geomA, geomB)

计算 geomA 和 geomB 之间的最短距离（geometry 返回平面距离，geography 返回球面距离）。

示例：计算北京到上海的球面距离（公里）

SELECT ST_Distance(bj.geom::geography, sh.geom::geography)/1000 AS distance_km
FROM cities bj, cities sh
WHERE bj.name = '北京' AND sh.name = '上海';
2. ST_Length(geom)

计算线串（LineString）或多线串（MultiLineString）的长度（单位同 ST_Distance）。

示例：计算 “长江” 的总长度（公里）

SELECT ST_Length(r.geom::geography)/1000 AS length_km
FROM rivers r
WHERE r.name = '长江';
3. ST_Area(geom)

计算多边形（Polygon）或多多边形（MultiPolygon）的面积（geometry 返回平面面积，geography 返回球面面积）。

示例：计算 “青海省” 的面积（平方公里）

SELECT ST_Area(p.geom::geography)/1000000 AS area_km2  -- 1平方公里=1e6平方米
FROM provinces p
WHERE p.name = '青海省';
4. ST_Perimeter(geom)

计算多边形的周长（单位同长度）。

示例：计算 “海南岛” 多边形的周长（公里）

SELECT ST_Perimeter(i.geom::geography)/1000 AS perimeter_km
FROM islands i
WHERE i.name = '海南岛';

三、几何转换与提取函数

用于转换几何类型或提取几何对象的部分信息。

1. ST_AsText(geom)/ST_AsGeoJSON(geom)

将 geometry 转为人类可读的文本格式（WKT 或 GeoJSON）。

示例：查询城市坐标的 WKT 和 GeoJSON 格式

SELECT 
  name,
  ST_AsText(geom) AS wkt_coords,
  ST_AsGeoJSON(geom) AS geojson_coords
FROM cities
WHERE name = '北京';
2. ST_Centroid(geom)

计算多边形或线串的几何中心（质心）。

示例：计算 “浙江省” 的省会（质心点）

SELECT 
  p.name,
  ST_AsText(ST_Centroid(p.geom)) AS centroid
FROM provinces p
WHERE p.name = '浙江省';
3. ST_Transform(geom, target_srid)

将几何对象从当前 SRID 转换为目标 SRID（需确保目标 SRID 已注册）。

示例：将北京的 WGS84 经纬度（SRID=4326）转为 UTM 50N（SRID=32650）坐标

SELECT 
  ST_AsText(geom) AS wgs84_coords,
  ST_AsText(ST_Transform(geom, 32650)) AS utm50n_coords
FROM cities
WHERE name = '北京';
4. ST_Buffer(geom, distance)

以 geom 为中心，生成指定距离的缓冲区（单位同 ST_Distance）。

示例：生成以上海为中心、半径 50 公里的缓冲区（用于范围筛选）

-- 创建上海 50 公里缓冲区，并查询缓冲区内的城市
SELECT c.name
FROM cities c, cities sh
WHERE sh.name = '上海'
  AND ST_Intersects(c.geom, ST_Buffer(sh.geom::geography, 50000)::geometry);

四、几何属性查询函数

用于获取几何对象的基本属性。

1. ST_GeometryType(geom)

返回几何对象的具体类型（如 POINT、POLYGON）。

示例：查询表中所有几何类型

SELECT DISTINCT ST_GeometryType(geom) AS geom_type FROM spatial_table;
2. ST_SRID(geom)

返回几何对象的 SRID（空间参考标识符）。

示例：检查城市表的 SRID 是否为 4326

SELECT name, ST_SRID(geom) AS srid FROM cities;
3. ST_NPoints(geom)

返回线串或多边形的顶点数量。

示例：查询某条道路线串的顶点数

SELECT r.name, ST_NPoints(r.geom) AS point_count
FROM roads r
WHERE r.name = '长安街';

五、总结

  • 关系查询：ST_Contains、ST_Intersects、ST_DWithin 是判断空间关系的核心函数，常用于筛选符合条件的对象。
  • 测量计算：ST_Distance、ST_Length、ST_Area 用于量化几何属性，注意 geometry 和 geography 类型的单位差异。
  • 转换提取：ST_Transform 用于坐标系转换，ST_AsText/ST_AsGeoJSON 用于格式转换，ST_Buffer 用于范围扩展。
  • 属性查询：ST_GeometryType、ST_SRID 用于验证几何对象的基本信息。

使用时需根据数据的 SRID 类型（平面 / 球面）选择合适的函数，并结合空间索引（GIST）提升查询效率。

常用分析函数的使用

PostGIS 提供了一系列用于空间分析的函数，可用于解决缓冲区分析、叠加分析、网络分析、密度分析等常见空间问题。以下是常用分析函数的分类及使用示例：

一、缓冲区分析（Buffer Analysis）

通过 ST_Buffer 生成几何对象的缓冲区，用于范围筛选、影响区域分析等。

1. 函数：ST_Buffer(geom, distance)
  • 功能：以 geom 为中心，生成半径为 distance 的缓冲区（geometry 类型用坐标单位，geography 类型用米）。
  • 示例：分析某地铁站 500 米范围内的公交站（假设坐标单位为米，SRID=32650）
-- 地铁站表（points）和公交站表（bus_stops）
SELECT bs.name AS bus_stop_name
FROM subway_stations ss, bus_stops bs
WHERE ss.name = '人民广场站'
  -- 地铁站的 500 米缓冲区与公交站相交
  AND ST_Intersects(ST_Buffer(ss.geom, 500), bs.geom);

二、叠加分析

对多个几何对象进行叠加计算，生成新的几何或属性（如交集、并集、差异等）。

1. ST_Intersection(geomA, geomB)
  • 功能：返回 geomA 和 geomB 的交集（重叠部分的几何）。
  • 示例：计算 “上海市” 与 “长江” 的重叠区域（河流穿过城市的部分）
SELECT 
  ST_AsText(ST_Intersection(c.geom, r.geom)) AS overlap_geom
FROM cities c, rivers r
WHERE c.name = '上海市' AND r.name = '长江';
2. ST_Union(geomA, geomB)
  • 功能：返回 geomA 和 geomB 的并集（合并后的几何）。
  • 示例：合并 “东城区” 和 “西城区” 的多边形为一个整体
SELECT 
  ST_AsText(ST_Union(dongcheng.geom, xicheng.geom)) AS merged_district
FROM districts dongcheng, districts xicheng
WHERE dongcheng.name = '东城区' AND xicheng.name = '西城区';
3. ST_Difference(geomA, geomB)
  • 功能：返回 geomA 中减去与 geomB 重叠的部分（A 独有的区域）。
  • 示例：计算 “北京市” 除去 “密云水库” 后的区域
SELECT 
  ST_Area(ST_Difference(c.geom, r.geom))/1000000 AS area_km2  -- 剩余面积（平方公里）
FROM cities c, reservoirs r
WHERE c.name = '北京市' AND r.name = '密云水库';

三、最近邻分析（Nearest Neighbor）

查找距离目标对象最近的几何，常用于 “最近设施” 查询。

1. 函数组合：ST_Distance + 排序
  • 示例：查找距离 “天安门” 最近的 3 个地铁站
SELECT 
  ss.name AS subway_name,
  ST_Distance(tm.geom::geography, ss.geom::geography)/1000 AS distance_km  -- 球面距离（公里）
FROM subway_stations ss, landmarks tm
WHERE tm.name = '天安门'
ORDER BY distance_km
LIMIT 3;  -- 取最近的3个

优化：ST_DWithin 先过滤范围（减少计算量）

SELECT 
  ss.name, 
  ST_Distance(tm.geom::geography, ss.geom::geography)/1000 AS distance_km
FROM subway_stations ss, landmarks tm
WHERE tm.name = '天安门'
  -- 先筛选1公里内的候选，再排序（效率更高）
  AND ST_DWithin(tm.geom::geography, ss.geom::geography, 1000)
ORDER BY distance_km
LIMIT 3;

四、密度分析（Density Analysis）

通过 ST_HexGrid 或 ST_Count 等函数分析要素在空间上的分布密度（如人口密度、设施密度）。

1. 生成网格并统计密度

示例：在上海市范围内生成 1km×1km 的六边形网格，统计每个网格内的便利店数量

-- 生成覆盖上海市的 1km 六边形网格（SRID=32650，单位米）
WITH hex_grid AS (
  SELECT hex.geom AS hex_geom
  FROM ST_HexGrid(1000, ST_Transform(sh.geom, 32650)) AS hex  -- 1000米网格，基于上海范围
  CROSS JOIN cities sh
  WHERE sh.name = '上海市'
    AND ST_Intersects(hex.geom, ST_Transform(sh.geom, 32650))
)
-- 统计每个网格内的便利店数量
SELECT 
  ST_AsText(h.hex_geom) AS hexagon,
  COUNT(c.id) AS convenience_count
FROM hex_grid h
LEFT JOIN convenience_stores c 
  ON ST_Intersects(h.hex_geom, ST_Transform(c.geom, 32650))  -- 转换便利店坐标到网格SRID
GROUP BY h.hex_geom;
2. 核密度估计（需 PostGIS 3.0+）

函数：ST_KDE（基于点要素生成连续密度表面）

-- 计算上海市范围内的人口核密度（假设人口点数据在 population_points 表）
SELECT ST_AsRaster(
  ST_KDE(pp.geom, bandwidth := 5000),  -- 带宽5000米（影响范围）
  100, 100  -- 输出 raster 分辨率（100×100像素）
) AS population_density_raster
FROM population_points pp, cities sh
WHERE ST_Intersects(pp.geom, sh.geom) AND sh.name = '上海市';

五、网络分析（Network Analysis）

基于线串网络（如道路、管线）的分析，需结合 pgrouting 扩展，PostGIS 基础函数可辅助预处理。

1. 线串拆分与节点提取
  • 函数：ST_Split（拆分线串）、ST_Nodes（提取线串所有节点）
  • 示例：将 “主干道” 线串按交叉点拆分为路段
-- 提取所有道路的交叉点
WITH road_nodes AS (
  SELECT ST_Nodes(r.geom) AS node_geom
  FROM roads r
  WHERE r.type = '主干道'
)
-- 按交叉点拆分主干道为路段
SELECT ST_AsText(ST_Split(r.geom, ST_Collect(n.node_geom))) AS split_segments
FROM roads r, road_nodes n
WHERE r.type = '主干道'
GROUP BY r.geom;
2. 最短路径
-- 需先安装 pgrouting 扩展，创建网络拓扑
-- 示例：从 A 点到 B 点的最短路径（基于道路长度）
SELECT * 
FROM pgr_dijkstra(
  'SELECT id, source, target, length AS cost FROM roads',  -- 道路网络（带长度成本）
  (SELECT source FROM roads_vertices_pgr WHERE ST_DWithin(geom, ST_SetSRID(ST_MakePoint(116.4, 39.9), 4326), 100)),  -- 起点最近节点
  (SELECT target FROM roads_vertices_pgr WHERE ST_DWithin(geom, ST_SetSRID(ST_MakePoint(116.5, 39.9), 4326), 100)),  -- 终点最近节点
  directed := false
);

六、空间聚合分析

对空间数据按几何范围或属性进行聚合（如按行政区统计数量、平均值）。

函数：ST_Collect（聚合几何）、ST_Union（合并几何）结合 GROUP BY

SELECT 
  p.name AS province,
  COUNT(c.id) AS city_count,  -- 城市数量
  AVG(c.population) AS avg_population,  -- 平均人口
  ST_Union(c.geom) AS city_points  -- 聚合所有城市点为多点几何
FROM provinces p
LEFT JOIN cities c ON ST_Contains(p.geom, c.geom)  -- 城市在省份内
GROUP BY p.name;

七、总结

  • 缓冲区分析：ST_Buffer 是核心，用于范围影响评估。
  • 叠加分析：ST_Intersection、ST_Union、ST_Difference 处理多几何间的空间关系。
  • 最近邻分析：通过 ST_Distance 排序或 ST_DWithin 筛选，高效查找最近要素。
  • 密度分析：ST_HexGrid 生成网格统计密度，ST_KDE 生成连续密度表面。
  • 网络分析：基础函数辅助预处理，结合 pgrouting 实现路径计算。
  • 聚合分析：结合 GROUP BY 和几何聚合函数，实现空间维度的统计分析。

实际应用中，需根据数据类型（geometry/geography）和场景选择函数，并注意坐标系单位对计算结果的影响（如米 vs 度）。

10月10日 第二天

晓磊哥帮忙部署了代码，目前主要是熟悉一下公司项目代码

项目经理说让我改bug，不过电脑不知道怎么回事宕机了，偷偷玩了一会

晚上项目经理问我项目怎么样了

挂机了

10月11日 第三天

今天项目经理说解决一下bug问题（熟悉一下代码），加油加油

感觉自己晕乎乎的不知道干嘛

解决了一个BUG，

10月12日 第四天

放假休息一天

10月13日 第五天

重新使用IDEA拉取了项目代码，然后部署了第一版的代码结果有BUG，然后改了一下午的BUG

10月14日 第六天

为京山的项目的会商模块添加了一些功能，然后让我负责另外一个项目了

10月15日 第七天

今天来写一个需求功能，要新建表，设计接口，编写接口文档

11月13日 第N天

我学到了一定要少问多学