✅ 空间数据库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 种用法背死语法无其他✔ 插入线几何LineStringST_GeomFromText(LineString(经度1 纬度1,经度2 纬度2),4326)→ 比如插入一条从 (116.3 39.9) 到 (116.4 39.9) 的道路中心线✔ 插入点几何PointST_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));场景 2road 表的 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)对应 SQLSELECT DISTINCT status FROM taxi;进阶例题投影道路表的道路名称和长度 → πrname,STL​ength(geom)​(road)对应 SQLSELECT DISTINCT rname, ST_Length(geom) FROM road;✅ 考点 2集合差运算的 SQL 转换 → 2 种写法全部必考关系代数R−S → 取「属于 R但不属于 S」的所有数据要求两个查询结果的「字段数、字段类型完全一致」考试有 2 种实现方式都要掌握大题会二选一考✔ 写法 1NOT EXISTS子查询考试首选效率高兼容性最好大题必写需求例题查询「没有载客的出租车编号」→ 出租车表 减去 载客状态的出租车表SELECT cid FROM taxi t1 WHERE NOT EXISTS ( SELECT * FROM taxi t2 WHERE t1.cid t2.cid AND t2.status 载客 );高频考题查询「未通行过的道路编号」→ 道路表 rid 减去 有车辆通行记录的道路表 ridSELECT rid FROM road r WHERE NOT EXISTS ( SELECT * FROM taxi_pass tp WHERE r.rid tp.rid );✔ 写法 2EXCEPT关键字标准 SQL 语法直接实现差运算简单易写选择题必考语法规则SELECT 查询语句1 EXCEPT SELECT 查询语句2→ 自动返回前者有、后者无的数据自动去重对应上面例题的 SQLSELECT rid FROM road EXCEPT SELECT rid FROM taxi_pass;✅ 考点 3补充 - 选择运算σ的 SQL 实现 →WHERE 条件考试一定会结合投影 选择一起考关系代数条件表名 → 过滤符合条件的行对应 SQL 的WHERE子句比如空车 → 对应 SQLSELECT 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.cid1 AND t2.cid2;✅ 考点 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 rid1), 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 BYORDER 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 rid1), 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的参数顺序先经度后纬度写反会查不到数据大题易错点。这张图片展示的是数据库查询的成本模型核心是通过公式估算 “单表查询” 和 “嵌套循环连接” 在不同存储 / 索引方式下的成本以下是解读先明确符号定义符号定义σAa​(R)对表R执行 “属性A等于a” 的选择查询T(R)表R的总行数V(R,A)表R中属性A的不同取值数基数B(R)表R占用的数据块数R⋈S表R与表S的连接查询嵌套循环连接。一、单表选择查询的成本估算目标估算σAa​(R)的查询成本以数据块访问数为衡量标准返回行数估计T(R)∗1/V(R,A)基于属性A的取值分布假设每个取值出现次数相等。不同存储 / 索引的成本堆文件无索引需扫描表R的所有数据块成本为B(R)。聚集索引有序文件数据按A排序仅需访问包含Aa的块成本为B(R)∗1/V(R,A)。非聚集索引需先通过索引找到Aa的行指针再访问对应数据块成本为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 成本、缓存命中率优化估算。