✅ 空间数据库（PostGIS）表设计 + 查询 + 业务应用 知识点【超详细完整版】
一、空间数据库表设计与操作（建表 + 插入，考试大题必考，占分最高）
✔ 核心说明
这部分是PostGIS 空间数据库的基础操作，是所有空间业务的前提，本质是「在传统关系数据库表设计的基础上，新增空间字段的定义规则」，所有语法完全兼容 PostgreSQL，考试考「完整建表 SQL + 完整插入 SQL」，是必出大题的考点。
（一）表创建 - 空间数据表完整设计（全考点拆解，必考）
1. 核心：空间数据表的字段类型定义（3 个高频必考字段类型 + 语法）
空间数据表的字段 = 「普通属性字段」+「空间几何字段」，考试考的字段类型是固定的 3 类，必须记牢语法 + 含义 + 应用场景，缺一不可：
✅ ① 空间类型geometry(几何子类型, 空间参考系)【重中之重，必考】
- 定义：PostGIS 的专属空间字段类型，用来存储地理实体的坐标 / 形状，固定语法格式，不能修改
- 考试必考 2 种写法：✔
geometry(Point, 4326)：存储单点坐标，比如出租车实时位置、POI 点位、公交站，4326是全球通用经纬度坐标系（考试唯一考点坐标系，背死）；✔geometry(LineString, 4326)：存储线段 / 连续路径，比如城市道路中心线、河流走向、轨迹线路，考试大题常考这个类型构造道路数据。 - 补充：还有
geometry(MultiPolygon,4326)存储区域，是前序考点，会结合考察。
✅ ② 时间类型timestamp
- 定义：存储精确的「年月日 时分秒」时间数据，是空间业务的高频字段，无时区影响，比
date更精准 - 应用场景：出租车定位时间、订单开始 / 结束时间、道路通行时间，考试必加此字段。
✅ ③ 序列类型serial
- 定义：PostgreSQL 的自增整数类型，专门用来生成「无重复的唯一编号」，等价于 MySQL 的
auto_increment - 特点：插入数据时，该字段无需手动赋值，数据库会自动生成 1、2、3... 递增的数值
- 应用场景：道路编号
rid、车辆编号cid、订单编号oid，是主键的首选类型，考试必考。
2. 约束设置 + 默认值（和传统数据库一致，结合空间表必考）
约束是保证数据有效性的规则，默认值是给字段赋「默认填充值」，考试建表语句必须带约束 + 默认值，否则扣分，全部为必考考点，一一对应：
✅ ① 主键约束PRIMARY KEY
- 作用：唯一标识表中每一条数据，字段值非空 + 不重复，一个表只能有 1 个主键（可以是复合主键，如
(rid, name)） - 常用搭配：
serial类型的字段必设为主键，比如rid serial PRIMARY KEY（道路编号自增且为主键），这是考试固定写法。
✅ ② 非空约束NOT NULL
- 作用：限制字段必须赋值，插入数据时该字段不能写
NULL，否则报错 - 必考应用：核心业务字段必加，比如 车辆状态
status NOT NULL、车速speed NOT NULL、空间坐标geom NOT NULL。
✅ ③ 默认值DEFAULT 取值
- 作用：插入数据时，如果不给该字段赋值，数据库会自动填充默认值，无需手动写值
- 考试唯一必考默认值：
DEFAULT now()→ 搭配timestamp类型使用，now()表示「当前系统时间」 - 固定写法：
localtime timestamp DEFAULT now()（定位时间，不赋值则自动填充当前时间），大题必写！
✔ 建表考点 → 完整例题（考试原题题型，背会直接套用）
需求：创建道路表
road，包含道路编号、道路名称、道路中心线（线几何）、创建时间，带所有约束和默认值
CREATE TABLE road( rid serial PRIMARY KEY, -- 序列类型+主键，自增道路编号，必考 rname varchar(50) NOT NULL, -- 道路名称，非空约束 geom geometry(LineString,4326) NOT NULL, -- 空间字段，线几何+4326坐标系，必考 createtime timestamp DEFAULT now() -- 时间类型+默认当前时间，必考 );需求：创建出租车表
taxi，包含车辆编号、车辆位置（点几何）、定位时间、车辆状态，带约束
CREATE TABLE taxi( cid serial PRIMARY KEY, status varchar(20) NOT NULL, -- 空车/载客 geom geometry(Point,4326) NOT NULL, -- 单点坐标，存储车辆位置 localtime timestamp DEFAULT now() );（二）数据插入 - 空间数据表的 INSERT 语句（必考大题，2 个核心考点）
插入的核心：空间数据表的普通字段，插入方式和传统数据库一致；只有「空间字段」需要用 PostGIS 函数构造几何对象后插入，考试考 2 个高频插入场景，全部为重点，无其他考点！
✅ 考点 1：空间数据的插入 → 通过ST_GeomFromText()构造几何对象
- 核心原因：空间字段
geometry存储的是「二进制几何数据」，不能直接写坐标文本，必须用函数把「坐标文本」转为数据库能识别的空间类型，这是空间数据插入的唯一方式。 - 函数作用：
ST_GeomFromText('几何文本格式', 坐标系)→ 将 WKT 格式的几何文本，转为 PostGIS 的geometry类型 - 考试最高频 2 种用法（背死语法，无其他）：✔ 插入线几何（LineString）：
ST_GeomFromText('LineString(经度1 纬度1,经度2 纬度2)',4326)→ 比如插入一条从 (116.3 39.9) 到 (116.4 39.9) 的道路中心线✔ 插入点几何（Point）：ST_GeomFromText('Point(经度 纬度)',4326)→ 比如插入出租车位置：ST_GeomFromText('Point(116.39 39.92)',4326)
✅ 考点 2：自增字段的动态赋值 →max(字段名)+1获取新编号
- 适用场景：当字段不是
serial类型（比如是普通int类型），又需要实现「自增编号」时，必须用此方法，考试必考补充考点！ - 核心逻辑：用聚合函数
max(rid)查询表中当前最大的编号，再加 1，就是新的唯一编号，保证不重复。 - 语法格式：
(SELECT max(rid)+1 FROM road) - 注意：如果表中无数据，
max(rid)返回NULL，可优化为(SELECT COALESCE(max(rid),0)+1 FROM road)→COALESCE表示：如果值为 NULL，就取 0。
✔ 插入考点 → 完整例题（2 类必考场景，全部覆盖）
场景 1：给 road 表插入道路数据，用 ST_GeomFromText 构造线几何（考试大题原题）
INSERT INTO road(rname, geom) VALUES ('长安街', ST_GeomFromText('LineString(116.38 39.90,116.40 39.90)',4326));场景 2：road 表的 rid 是 int 类型，用 max (rid)+1 动态赋值，插入数据
INSERT INTO road(rid, rname, geom) VALUES ((SELECT max(rid)+1 FROM road), '朝阳路', ST_GeomFromText('LineString(116.45 39.95,116.50 39.95)',4326));场景 3：给 taxi 表插入出租车位置，构造点几何，时间用默认值
INSERT INTO taxi(status, geom) VALUES ('空车', ST_GeomFromText('Point(116.3972 39.9075)',4326));二、空间查询与关系运算（核心重点，选择 + 大题全考，考点最密集）
✔ 核心说明
这部分分为两大块：
- 「关系代数与 SQL 转换」：是传统关系数据库的考点，完全沿用你之前背的投影、差运算规则，只是结合空间数据表的字段进行查询，无新增知识点，但是考试必考结合题型；
- 「空间分析函数」：是PostGIS 专属考点，所有函数都是考试高频，是空间查询的核心，大题必用，必须记牢「函数名 + 作用 + 语法 + 应用场景」。
（一）关系代数与 SQL 转换（必考，3 个核心考点，无新增，结合空间表）
所有规则和你之前背诵的「关系代数投影、差运算」完全一致，只是查询的表变成了空间数据表，考点是「把关系代数表达式，转为对应的 SQL 语句」，全部为考试选择题 + 简答题考点，一一对应，精准无冗余：
✅ 考点 1：投影运算（π）的 SQL 实现 →SELECT DISTINCT 字段列表
- 关系代数：字段名表名 → 从表中「选择指定列」，自动去重
- 核心规则：关系代数的投影运算，天生自带「去重」功能，所以对应的 SQL 语句，必须用
SELECT DISTINCT，不能只写SELECT（只写 SELECT 会保留重复值，是考试高频易错点，扣分重！） - 空间表应用例题：对出租车表 taxi，投影出所有的车辆状态 → πstatus(taxi)对应 SQL：
SELECT DISTINCT status FROM taxi; - 进阶例题：投影道路表的道路名称和长度 → πrname,STLength(geom)(road)对应 SQL：
SELECT DISTINCT rname, ST_Length(geom) FROM road;
✅ 考点 2：集合差运算的 SQL 转换 → 2 种写法，全部必考
关系代数：R−S → 取「属于 R，但不属于 S」的所有数据，要求两个查询结果的「字段数、字段类型完全一致」，考试有 2 种实现方式，都要掌握，大题会二选一考：
✔ 写法 1：NOT EXISTS子查询（考试首选，效率高，兼容性最好，大题必写）
需求例题：查询「没有载客的出租车编号」→ 出租车表 减去 载客状态的出租车表
SELECT cid FROM taxi t1 WHERE NOT EXISTS ( SELECT * FROM taxi t2 WHERE t1.cid = t2.cid AND t2.status = '载客' );高频考题：查询「未通行过的道路编号」→ 道路表 rid 减去 有车辆通行记录的道路表 rid
SELECT rid FROM road r WHERE NOT EXISTS ( SELECT * FROM taxi_pass tp WHERE r.rid = tp.rid );✔ 写法 2：EXCEPT关键字（标准 SQL 语法，直接实现差运算，简单易写，选择题必考）
- 语法规则：
SELECT 查询语句1 EXCEPT SELECT 查询语句2→ 自动返回前者有、后者无的数据，自动去重 - 对应上面例题的 SQL：
SELECT rid FROM road EXCEPT SELECT rid FROM taxi_pass;✅ 考点 3：补充 - 选择运算（σ）的 SQL 实现 →WHERE 条件
考试一定会结合投影 + 选择一起考，关系代数：条件表名 → 过滤符合条件的行，对应 SQL 的WHERE子句，比如：空车 → 对应 SQL：SELECT DISTINCT cid FROM taxi WHERE status='空车';
（二）空间分析函数（PostGIS 核心，必考 TOP 级，4 个高频函数，无其他考点）
这是空间查询的灵魂，所有空间业务题都基于这些函数实现，考试考「函数作用 + 语法 + 应用场景」，全部是大题必考，必须记牢，一字不差，函数名大小写不敏感，放心背诵。
通用说明：所有空间函数的前缀都是
ST_（Spatial Type，空间类型），是 PostGIS 函数的统一标识。
✅ 考点 1：空间距离筛选 →ST_DWithin(A, B, 距离值)【考试第一高频函数，重中之重】
- 核心作用：判断两个空间对象的「最短直线距离」是否 ≤ 指定的距离值，返回 true/false
- 核心优势：效率极高，会走「空间索引」，比先算距离再判断快 100 倍，是范围筛选的首选函数，考试大题必用！
- 语法格式：
ST_DWithin(空间字段, 目标几何对象, 距离)，距离单位和坐标系一致（4326 为米，考试唯一单位） - 必考应用场景：所有「XX 范围内的 XXX」都是用这个函数，比如：查询 2 千米内的空车、查询某道路 100 米内的车辆、查询地铁站 500 米内的商铺。
- 例题（考试原题）：查询 坐标 (116.4 39.9) 周围 2000 米内的所有空车
SELECT * FROM taxi WHERE status='空车' AND ST_DWithin(geom, ST_MakePoint(116.4,39.9), 2000);✅ 考点 2：空间距离计算 →ST_Distance(A, B)
- 核心作用：精确计算两个空间对象的「最短直线距离」，返回具体的数值（单位：米），不是布尔值
- 语法格式：
ST_Distance(空间字段1, 空间字段2) - 必考应用场景：需要「精确距离值」的需求，比如：计算两辆出租车之间的距离、计算车辆到道路的距离、计算两个点位的直线距离。
- 例题：计算编号为 1 的出租车到编号为 2 的出租车的直线距离
SELECT ST_Distance(t1.geom, t2.geom) AS distance FROM taxi t1, taxi t2 WHERE t1.cid=1 AND t2.cid=2;✅ 考点 3：点对象快速构造 →ST_MakePoint(经度, 纬度)
- 核心作用：快速创建一个点几何对象，是构造点的「最优函数」，比
ST_GeomFromText('Point(经 纬)',4326)更简洁、效率更高 - 语法格式：
ST_MakePoint(lon, lat)→ 注意：先写经度，再写纬度，考试易错点，写反会查不到数据！ - 必考搭配：和
ST_DWithin/ST_Distance一起使用，比如上面的例题，就是用这个函数构造目标点。
✅ 考点 4：几何对象转文本 →ST_AsText(空间字段)
- 核心作用：将 PostGIS 存储的「二进制空间几何数据」，转为人类可读的「WKT 文本格式」，方便查看坐标信息
- 语法格式：
ST_AsText(geom) - 必考应用场景：查询时需要「查看具体坐标」，比如：查看出租车的具体经纬度、查看道路的坐标点集合。
- 例题：查询所有空车的编号和具体位置坐标
SELECT cid, ST_AsText(geom) AS position FROM taxi WHERE status='空车';✔ 补充 2 个高频函数（考试偶尔考，性价比高）：
ST_AsGeoJSON(geom)：转为 JSON 格式的坐标，适合前端展示；ST_Length(geom)：计算线几何的长度，比如道路长度，必考搭配聚合函数。
三、空间业务场景应用（综合大题必考，所有考点的最终落地，占分最高）
✔ 核心说明
这部分是考试的压轴大题，是「空间表设计 + 空间查询函数 + 传统 SQL 聚合 / 分组 / 排序」的综合应用，没有新增知识点，所有内容都是前面考点的组合，考试的题型是固定的「业务需求→写 SQL 语句」，考点非常集中，掌握解题思路，就能满分！
✔ 核心解题逻辑
空间业务 SQL 的通用写法：SELECT 聚合函数/字段 → FROM 表 → WHERE 空间条件+属性条件 → GROUP BY 分组字段 → ORDER BY 排序字段所有业务题都遵循这个逻辑，只是替换条件和函数，非常好记！
（一）位置关联查询（基础业务题，必考，无难点）
✅ 核心定义
基于「空间距离」作为筛选条件，结合属性条件，实现对空间数据的精准查询，本质是：WHERE 子句中使用 ST_DWithin + 普通属性条件，是所有空间业务的基础，考试考的场景全部固定，无其他变种！
✅ 必考 2 类业务场景（全部为考试原题，背会 SQL 模板，直接套用）
✔ 场景 1：正向筛选 → 基于空间距离的包含查询
需求模板：查询「XX 范围内 + 满足某属性」的对象，比如：查询 2 千米内的空车、查询某道路 500 米内的载客出租车、查询地铁站 1 公里内的 POI 点。SQL 模板（万能）：
SELECT * FROM 表名 WHERE 属性条件 AND ST_DWithin(空间字段, 目标点/目标几何, 距离值);例题：查询 道路编号为 1 的道路中心线 周围 100 米内的所有空车
SELECT * FROM taxi t WHERE t.status='空车' AND ST_DWithin(t.geom, (SELECT geom FROM road WHERE rid=1), 100);✔ 场景 2：反向筛选 → 基于空间距离的排除查询
需求模板：查询「满足某属性 + 排除 XX 范围内」的对象，比如：排除 100 米内的车辆、查询 2 千米外的载客出租车、查询不在道路周边的车辆。核心：在
ST_DWithin前加NOT即可，SQL 模板（万能）：
SELECT * FROM 表名 WHERE 属性条件 AND NOT ST_DWithin(空间字段, 目标点/目标几何, 距离值);例题（考试原题）：查询所有空车，排除距离坐标 (116.4 39.9) 100 米内的车辆
SELECT * FROM taxi WHERE status='空车' AND NOT ST_DWithin(geom, ST_MakePoint(116.4,39.9), 100);（二）空间统计分析（综合大题，必考 TOP 级，重中之重，无容错）
✅ 核心定义
在「位置关联查询」的基础上，结合SQL 聚合函数 + 分组 + 排序，实现对空间数据的统计分析，是考试的核心大题，所有考点都集中在这里，但是题型固定，解题思路固定，只要掌握「聚合函数 + GROUP BY+ORDER BY」的搭配，就能轻松得分！
✅ 前置必背：3 类基础知识点（无新增，都是之前的考点）
- 聚合函数（必考，固定 4 个，无其他）：
COUNT(字段)统计数量、AVG(字段)计算平均值、SUM(字段)求和、MAX(字段)求最大值； - 分组语句：
GROUP BY 字段→ 按指定字段对数据分组，聚合函数必须搭配 GROUP BY 使用，比如按道路分组统计车辆数； - 排序语句：
ORDER BY 字段 [ASC/DESC]→ 按指定字段排序，ASC升序（默认），DESC降序，比如按平均车速降序排列。
✅ 必考 3 类业务场景（全部为考试压轴大题，SQL 模板万能，背会直接套用）
✔ 场景 1：简单空间统计 → 无分组，直接聚合
需求模板：统计「满足空间条件 + 属性条件」的对象数量 / 平均值，比如：统计 2 千米内的空车数量、统计某道路周边车辆的平均车速。SQL 模板：
SELECT 聚合函数(字段) AS 别名 FROM 表名 WHERE 属性条件 AND ST_DWithin(空间字段, 目标几何, 距离值);例题 1：统计坐标 (116.4 39.9) 周围 2000 米内的空车数量
SELECT COUNT(cid) AS empty_taxi_num FROM taxi WHERE status='空车' AND ST_DWithin(geom, ST_MakePoint(116.4,39.9), 2000);例题 2：统计道路编号为 1 的周边 100 米内车辆的平均车速
SELECT AVG(speed) AS avg_speed FROM taxi WHERE ST_DWithin(geom, (SELECT geom FROM road WHERE rid=1), 100);✔ 场景 2：分组空间统计 → 按空间 / 属性字段分组 + 聚合（考试最高频大题）
需求模板：按「某字段分组」，统计每组的空间数据，比如：按道路分组，统计每条道路周边的车辆数；按车辆状态分组，统计不同状态的车辆在某区域的数量。核心规则：GROUP BY 分组的字段，必须出现在 SELECT 中，聚合函数和分组字段搭配使用，这是考试的核心规则，不遵守会报错！SQL 模板（万能，背死）：
SELECT 分组字段, 聚合函数(字段) AS 别名 FROM 表名1 JOIN 表名2 ON 关联条件 -- 可选，多表关联时用 WHERE 空间条件+属性条件 GROUP BY 分组字段;例题（考试原题，必考）：按道路分组，统计每条道路周边 100 米内的车辆数量
SELECT r.rid, r.rname, COUNT(t.cid) AS taxi_num FROM road r LEFT JOIN taxi t ON ST_DWithin(t.geom, r.geom, 100) GROUP BY r.rid, r.rname;例题 2：按车辆状态分组，统计坐标 (116.4 39.9) 周围 2 千米内的各类车辆数量
SELECT status, COUNT(cid) AS num FROM taxi WHERE ST_DWithin(geom, ST_MakePoint(116.4,39.9), 2000) GROUP BY status;✔ 场景 3：分组统计 + 排序 → GROUP BY + ORDER BY（压轴大题，满分考点）
需求模板：在分组统计的基础上，对统计结果进行排序，比如：按道路分组统计车辆数，按车辆数降序排列；按道路分组计算平均车速，按车速升序排列。核心规则：
ORDER BY必须写在GROUP BY之后，排序的字段可以是「聚合函数的别名」，这是考试的固定写法！SQL 模板（万能，压轴大题唯一模板）：
SELECT 分组字段, 聚合函数(字段) AS 别名 FROM 表名 WHERE 空间条件+属性条件 GROUP BY 分组字段 ORDER BY 别名 [ASC/DESC];例题（考试压轴大题，必考原题）：按道路分组，统计每条道路周边 100 米内的车辆平均车速，并按平均车速降序排列
SELECT r.rid, r.rname, AVG(t.speed) AS avg_speed FROM road r LEFT JOIN taxi t ON ST_DWithin(t.geom, r.geom, 100) GROUP BY r.rid, r.rname ORDER BY avg_speed DESC;例题 2：按道路分组，统计每条道路的通行车辆数，按车辆数升序排列
SELECT r.rid, COUNT(t.cid) AS taxi_num FROM road r LEFT JOIN taxi t ON ST_DWithin(t.geom, r.geom, 100) GROUP BY r.rid ORDER BY taxi_num ASC;✅ 最后补充：考试 3 大高频易错点（避坑必看，扣分重灾区，全部避开 = 多拿 10 分）
- 空间字段定义的语法：
geometry(Point,4326)中间无空格，坐标系 4326 不能写错，考试常考拼写错误； - 投影运算必须加
DISTINCT：关系代数的 π 自带去重，SQL 不加就是错，选择题必考； ST_MakePoint的参数顺序：先经度，后纬度，写反会查不到数据，大题易错点。
这张图片展示的是数据库查询的成本模型，核心是通过公式估算 “单表查询” 和 “嵌套循环连接” 在不同存储 / 索引方式下的成本，以下是解读（先明确符号定义）：
符号定义
- σA=a(R)：对表R执行 “属性A等于a” 的选择查询；
- T(R)：表R的总行数；
- V(R,A)：表R中属性A的不同取值数（基数）；
- B(R)：表R占用的数据块数；
- R⋈S：表R与表S的连接查询（嵌套循环连接）。
一、单表选择查询的成本估算
目标：估算σA=a(R)的查询成本（以数据块访问数为衡量标准）：
- 返回行数估计：T(R)∗1/V(R,A)（基于属性A的取值分布，假设每个取值出现次数相等）。
- 不同存储 / 索引的成本：
- 堆文件（无索引）：需扫描表R的所有数据块，成本为B(R)。
- 聚集索引（有序文件）：数据按A排序，仅需访问包含A=a的块，成本为B(R)∗1/V(R,A)。
- 非聚集索引：需先通过索引找到A=a的行指针，再访问对应数据块，成本为T(R)∗1/V(R,A)（与返回行数一致）。
二、嵌套循环连接的成本估算
目标：估算R⋈S（嵌套循环连接）的成本：
- 无索引：
- 以R为外层表：成本为B(R)+B(R)∗B(S)（扫描R的所有块，每块R的行扫描S的所有块）；
- 以S为外层表：成本为B(S)+B(S)∗B(R)。
- 聚集索引：
- 假设连接属性为B，以R为外层表：成本为B(R)+T(R)∗B(S)∗1/V(S,B)（扫描R的块，每行R通过S的聚集索引访问S中B匹配的块）。
- 非聚集索引：
- 以R为外层表：成本为B(R)+T(R)∗T(S)∗1/V(S,B)（扫描R的块，每行R通过S的非聚集索引访问S中B匹配的行）。
成本模型的核心价值
成本模型是数据库查询优化器的核心依据：通过估算不同查询方式的成本，选择成本最低的执行计划（如选择 “聚集索引” 而非 “无索引” 执行单表查询）。
需注意：这是简化的成本模型，实际数据库会结合更多因素（如 CPU 成本、缓存命中率）优化估算。
)
