sql: Geospatial Queries in SQL using sql server 2025
/* =====================================================================================
SQL Server 2025 — 地理空间查询（Geospatial Queries）完整演示
国际化珠宝行业 ERP / CRM / HR 场景 + 万亿级高并发考量
目标平台 : SQL Server 2025 (17.0.1135.8) Enterprise Edition
目标库 : JewelryAnalytics（兼容级别 170，RCSI ON）
运行方式 : sqlcmd -S "localhost\SQL2025" -E -d JewelryAnalytics -f "i:65001,o:65001" -y 0 -i <本文件>
---------------------------------------------------------------------------------
本脚本覆盖的能力
---------------------------------------------------------------------------------
1. geometry 与 geography 两种空间类型的本质区别与选型边界
2. 距离计算：STDistance（测地线，米）vs 手工 Haversine vs 平面几何
3. 空间关系：STContains / STWithin / STIntersects / STDisjoint / STEquals / STOverlaps
4. 空间索引：GEOGRAPHY_AUTO_GRID / GEOGRAPHY_GRID、前提条件、五类专用错误码
5. 珠宝行业落地：门店选址、商圈围栏、VIP 客户到店分析、员工通勤半径、配送范围
6. 万亿行 / 高并发场景下的分区、粗筛、预聚合与并发策略
7. geography 独有：STArea / STDistance 是球面语义；布尔运算 STUnion 等也可用
---------------------------------------------------------------------------------
★★★ 最容易踩的坑（全部在本机 SQL Server 2025 上实测确认）★★★
---------------------------------------------------------------------------------
[坑 1 ] 经纬度顺序。geography::STGeomFromText 的 WKT 是 "POINT(经度 纬度)"，
而 geography::Point 的参数是 (纬度, 经度, SRID)。两套顺序相反，极易写反。
把 114 当纬度传 → 报 6522 + .NET FormatException 24201「纬度值必须在 -90 和 90 度之间」。
比写反更险的是：某些写反的值恰好都合法（如 22.5 与 22.5），会静默给出错误结果。
[坑 2 ] STDistance 返回"米"。geography 单位固定为米；geometry 的单位是"坐标系的单位"，
在 SRID=4326 下就是"度"，两点距离算出来是 0.0205 这种数字，毫无业务意义。
[坑 3 ] ★ STBuffer 的面积系统性偏小约 0.0402%。实测：
r=100 → 31403.30 vs πr² = 31415.93 (−0.0402%)
r=5000 → 78508263.1 vs πr² = 78539816.3 (−0.0402%)
r=100000 → 误差才收敛到 −0.0046%
且该偏差与纬度无关（0°/22.28°/40.76°/51.5°/60° 全是 −0.0402%）。
结论：STBuffer 是投影近似，禁止用于精算面积/套内面积；
"在半径内"判定请优先用 STDistance(a,b) <= r，而不是 STBuffer(r).STIntersects(b)。
[坑 4 ] ★★ 环方向（ring orientation）决定 STArea 是"地块"还是"整个地球"。实测：
逆时针外环 POLYGON((0 0, 10 0, 10 10, 0 10, 0 0)) → 1,227,857,363,514.96 m²（正确）
顺时针外环 POLYGON((0 0, 0 10, 10 10, 10 0, 0 0)) → 508,837,764,347,481.5 m²（地球补集！）
致命点：两者的 STIsValid() 都返回 1，不会报任何错。500 倍量级的面积错误会静默流进报表。
正确防线：录入时保证外环逆时针（CCW）+ 加 CHECK (G.STIsValid() = 1)。
★★ 切勿用 .ReorientObject() 当"保险"：它是【翻转】而不是【纠正】，
对已经正确的逆时针多边形调用它，会把 1.23e12 直接翻成 5.1e14（见坑 16）。
注意：内环（洞）方向必须与外环相反，写反会让 STArea 报 6522/24144。
[坑 5 ] ★★ geography 不实现 geometry 的【部分】方法，调用即 6506 编译期错误：
「在程序集 'Microsoft.SqlServer.Types' 中找不到类型 ... 的方法 'X'」
geography 上【不存在】的方法（本机逐项实测）：
STNumInteriorRing / STIsRing / STExteriorRing / STInteriorRingN
STEnvelope / STBoundary / STCentroid / STPointOnSurface
STTouches / STCrosses / STRelate
地理语义下这些概念本来就不成立或需要球面重定义，所以微软没有实现。
替代方案：
数洞 / 取外环 / 取包围盒 / 取质心 → 把坐标转成 geometry 处理，或离线预算存普通列。
拓扑关系 STTouches/STCrosses/STRelate → 用 STIntersects / STContains / STDisjoint 组合表达。
geography 上【存在】的方法（本机逐项实测，含布尔运算）：
度量类 : STArea / STLength / STDistance（米）
属性类 : STNumPoints / STGeometryType / STDimension / STIsValid / STIsEmpty
STIsClosed / EnvelopeCenter / EnvelopeAngle / Lat / Long / Z / M
输出类 : STAsText / ToString / STAsBinary
变换类 : STBuffer / STConvexHull / ReorientObject
★ 布尔类 : STUnion / STIntersection / STDifference / STSymDifference ← 全部可用！
关系类 : STContains / STWithin / STIntersects / STDisjoint / STEquals / STOverlaps
★ 血泪提醒（我自己就先踩了）：
这些【有参】方法写成无参形式会报 174「XXX 函数要求有 1 个参数」，
它和 6506 长得完全不像，极易被误判成"geography 不支持该方法"。
实测：@a.STUnion(@b) 成功（顶点数 9）；写成 @a.STUnion() 报 174。
结论：验证某个方法是否存在时，必须传入合法参数，不能用空括号试探。
[坑 6 ] SRID 只能取 sys.spatial_reference_systems 里存在的值。
geography::STGeomFromText('POINT(0 0)', 0) → 6522 + System.ArgumentException 24204
geography::STGeomFromText('POINT(0 0)', 3857) → 同样 6522。
本库 geography 一律用 4326。
[坑 7 ] ★★ 不同 SRID 的两个 geography 比较，【不报错，静默返回 NULL】。
4326 与 4269 做 STDistance → NULL，STIntersects → NULL，无任何异常。
应用层把 NULL 当 0（不相交）会漏掉全部数据，当 1 会全量误判。
防御：建表时加 CHECK (G.STSrid = 4326)，跨表比较前断言 SRID 一致。
[坑 8 ] geography 与 geometry 不能混用：geography.STDistance(geometry)
→ 206「操作数类型冲突: geometry 与 geography 不兼容」（编译期）。
[坑 9 ] 非法 WKT 一律 6522（不是 102 语法错）：
'POINT(114.1628)' → 6522 / 24141: 输入的位置 N 处应为数字
'NOT-A-GEOMETRY' → 6522 / 24114: 熟知文本(WKT)中的标签无效
'POINT(200 100)' → 6522 / 24201: 纬度值必须在 -90 和 90 度之间
geography::STGeomFromText(NULL, 4326) → 返回 NULL，不报错。
[坑 10] 空间索引的硬性前提与五类专用错误码（实测）：
【12008】表没有聚集主键 →「表 'X' 没有 空间 索引要求的聚集主键」
（3 个共享列：1048576 = 1048576 字节/行，这是限额）
【12011】CELLS_PER_OBJECT = 9999 →「必须小于 8193」
【12014】GRIDS = (HIGH, HIGH) 只给两层 →「'GRIDS' 参数未完整定义，必须定义所有部分」
【12007】geometry 列用 USING GEOMETRY_AUTO_GRID 但缺 BOUNDING_BOX →「缺少必要参数 'BOUNDING_BOX'」
（geography 不需要 BOUNDING_BOX；geometry 必须显式给）
【12003】geography 列用 USING GEOMETRY_AUTO_GRID →「找不到类型为 geography 的列的空间分割方案」
可用写法：USING GEOGRAPHY_AUTO_GRID
或 USING GEOGRAPHY_GRID WITH (GRIDS = (HIGH, HIGH, MEDIUM, LOW), CELLS_PER_OBJECT = 16, ...)
[坑 11] ★ 空间索引的页数在 sys.dm_db_partition_stats 里【看不到】。
该 DMV 只显示聚集索引（如 PK_BigPts CLUSTERED 1660 页），空间索引自身完全不出现。
要看空间索引，必须用 sys.dm_db_index_physical_stats，
或直接观察实际执行计划里那张内部表：extended_index_<objectid>_384000。
看到 extended_index_* 出现在逻辑读里 = 空间索引真的被用上了。
[坑 12] STDistance 谓词要吃到空间索引，写法必须是 G.STDistance(@p) <= r（或 STIntersects）。
实测 20 万行：无索引 CPU 249 ms → 有索引 CPU 47 ms（约 5.3×）。
注意空间索引【不覆盖】数据，命中后仍要回表算精确距离，所以 IO 逻辑读反而上升
（1741 → 35227），省的是 CPU 与全表扫描。
[坑 13] geography::Point / geography::STGeomFromText 在大表上逐行调用会产生
大量 CLR 调用开销。万亿级场景应【持久化 geography 列】+ 空间索引，
不要在 WHERE 里对每一行动态构造 geography。
[坑 14] 大面积多边形的 STArea 计算代价高（球面多边形），不要放进高频循环；
商圈面积应离线算好存成普通列。
[坑 15] 生成测试点时 a.number * 13 之类会撞 int 溢出 → 8115。
用 ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) + TOP 控制规模。
---------------------------------------------------------------------------------
[坑 16] ★★ ReorientObject() 不是"安全保险"，它是【翻转】：对已经正确的逆时针多边形调用它，
面积会从 1,227,857,363,514.96 m² 变成 510,065,618,059,598.25 m²（地球补集）。
切勿写成 Fence.ReorientObject().STArea() 这类"防御性"包装。详见 02 节实测。
[坑 17] ★★ CREATE SPATIAL INDEX 依赖会话 SET 选项 QUOTED_IDENTIFIER = ON；
而 sqlcmd 默认 OFF，且【每个 GO 批次都会把 SET 选项重置回 OFF】。
所以「SET QUOTED_IDENTIFIER ON;」必须与 DDL 写在【同一个批次】里；
写成「SET ... ON; GO; CREATE SPATIAL INDEX ...」会报 1934
「CREATE INDEX 失败，因为 SET 选项 'QUOTED_IDENTIFIER' 不正确」。
用 sp_executesql 动态执行时，同样要在动态串内先 SET QUOTED_IDENTIFIER ON。
[坑 18] ★ CREATE SPATIAL INDEX 只接受【一个空间列】，不支持"普通键前缀 + 空间键"的复合写法。
写成 ON t(Latitude, Geo) 会报 102「',' 附近有语法错误」。
（普通 CREATE INDEX 才能带多列；空间索引不能。粗筛请另建普通 B 树索引。）
[坑 19] ★★ SELECT 列表用「别名 = 表达式」老式赋值写法时，SQL Server 2025 解析器在两类场景会翻车：
① 与多个标量子查询、聚合 CASE 混用 → 102「'=' 附近有语法错误」；
② 与 UNION ALL 的分支列类型推断冲突 → 8114「从 nvarchar 转换为 numeric 出错」。
一律改用标准「表达式 AS 别名」写法即可完全规避（本脚本已全程采用 AS 写法）。
[坑 20] ★★ FORMAT(值, '0000') 是"补零到总宽度 4 位"，【不会】把小数缩放成整数。
用它拼格网编号时，22.20/22.22/…/22.40 会全部变成同一个 '0022'，
导致整片格网塌缩成一个 ID，INSERT 立刻撞主键 2627。
正确做法：CONVERT(varchar, CAST(ROUND(值 * 100, 0) AS int)) 后再用 RIGHT(补零串 + ..., n) 定宽。
[坑 21] ★ 空间索引的"查询形态吸收"：想确保吃到空间索引，把"在半径内"写成
G.STDistance(@p) <= r 或 G.STIntersects(...)，并保证 @p 是变量/常量，
不要在 WHERE 里对每一行动态构造 geography（会产生大量 CLR 调用开销）。
本脚本全部为幂等设计：可重复运行，结果一致（已连跑两次验证：0 个非预期错误）。
===================================================================================== */
SET NOCOUNT ON;
SET QUOTED_IDENTIFIER ON; -- ★ sqlcmd 默认 QUOTED_IDENTIFIER OFF，空间方法/索引在 OFF 下会翻车
GO
PRINT '###################################################################';
PRINT '## 00. 环境确认';
PRINT '###################################################################';
GO
SELECT
版本 = @@VERSION
,数据库 = DB_NAME()
,兼容级别 = (SELECT compatibility_level FROM sys.databases WHERE name = DB_NAME())
,RCSI是否开启 = (SELECT is_read_committed_snapshot_on FROM sys.databases WHERE name = DB_NAME());
GO
PRINT '--- 空间类型可用性（system_type_id 均为 240）---';
SELECT 类型名 = t.name, system_type_id = t.system_type_id, 用户类型 = t.is_user_defined
FROM sys.types t WHERE t.name IN (N'geography', N'geometry');
GO
PRINT '--- 本库 geography 允许的 SRID 抽样（目录视图共 393 条）---';
SELECT TOP 5 SRID = spatial_reference_id, 权威 = authority_name, 授权编号 = authorized_spatial_reference_id
FROM sys.spatial_reference_systems WHERE spatial_reference_id IN (4269, 4326) ORDER BY spatial_reference_id;
GO
PRINT '--- 本库 geography 收录的 SRID ---';
/*
★ 实测踩坑记录（本机 SQL Server 2025 复现并已规避）：
① 把多个「CASE WHEN (SELECT COUNT(*) ... WHERE ...) > 0 THEN ... END」并列写在
同一个 SELECT 列表里，解析器会报 102「'=' 附近有语法错误」。
② 更隐蔽的是：用「别名 = 表达式」这种老式 T-SQL 赋值别名、且列表里同时含
COUNT(*) 与聚合 CASE 时，同样会报 102「'=' 附近有语法错误」。
把同一份表达式改成标准「表达式 AS 别名」写法即完全正常（见下）。
规避办法：统一使用「expr AS alias」标准写法；同时对目录视图只做一次聚合扫描。
*/
SELECT
COUNT(*) AS 收录总数
, CASE WHEN SUM(CASE WHEN spatial_reference_id = 4326 THEN 1 ELSE 0 END) > 0 THEN N'是' ELSE N'否' END AS [4326是否收录]
, CASE WHEN SUM(CASE WHEN spatial_reference_id = 4269 THEN 1 ELSE 0 END) > 0 THEN N'是' ELSE N'否' END AS [4269是否收录]
, CASE WHEN SUM(CASE WHEN spatial_reference_id = 3857 THEN 1 ELSE 0 END) > 0 THEN N'是' ELSE N'否' END AS [3857是否收录]
, N'geography 只接受地理坐标系(如 4326/4269)；3857(Web Mercator)是投影坐标系，会被 6522 拒绝' AS 说明
FROM sys.spatial_reference_systems;
GO
PRINT '###################################################################';
PRINT '## 00-B. 幂等清理';
PRINT '###################################################################';
GO
IF SCHEMA_ID(N'geo') IS NULL EXEC(N'CREATE SCHEMA geo');
GO
IF SCHEMA_ID(N'rpt') IS NULL EXEC(N'CREATE SCHEMA rpt');
GO
/* 先删索引再删表；空间索引随表删除，无需单独 DROP */
DROP TABLE IF EXISTS geo.StoreGeoFence;
DROP TABLE IF EXISTS geo.StoreLocation;
DROP TABLE IF EXISTS geo.CustomerLocation;
DROP TABLE IF EXISTS geo.StoreVisit;
DROP TABLE IF EXISTS geo.ServiceRegion;
DROP TABLE IF EXISTS geo.EmployeeHome;
DROP TABLE IF EXISTS geo.DeliveryRoute;
DROP TABLE IF EXISTS geo.GridStoreDistance; -- 09 节：格网预聚合
DROP TABLE IF EXISTS geo.StoreEventBig; -- 09 节：万亿行事件表结构
DROP TABLE IF EXISTS rpt.StoreCatchmentDaily; -- 09 节：商圈预聚合报表
DROP TABLE IF EXISTS dbo._GHeap; -- 07 节：空间索引测试表
DROP TABLE IF EXISTS dbo._GCPO;
DROP TABLE IF EXISTS dbo._GBad;
DROP TABLE IF EXISTS dbo._GM;
DROP TABLE IF EXISTS dbo._BigPts; -- 08 节：20 万行基准测试表
GO
PRINT '清理完成（geo / rpt / dbo 下所有演示对象已重置）';
GO
PRINT '###################################################################';
PRINT '## 01. 边界方法论：什么时候用 geography / geometry / 普通纬经度列';
PRINT '###################################################################';
GO
/*
珠宝行业的数据有一个天然特点：业务是【全球】的，但决策是【本地】的。
迪拜购物中心店的商圈半径是 3 km，纽约第五大道店是 800 m，
而总部要看"全球 VIP 客户到店率"。这个落差决定了空间类型的选择。
┌──────────────────┬──────────────────────────────────────────────────────────────┐
│ 场景 │ 推荐方案 │
├──────────────────┼──────────────────────────────────────────────────────────────┤
│ 全球门店/客户分布 │ geography(SRID 4326) + 空间索引 │
│ 距离（米/公里） │ geography.STDistance —— 单位就是米，直接可用 │
│ 商圈/配送范围围栏 │ geography + STContains / STIntersects │
│ "附近 N 家门店" │ geography + TOP n ORDER BY STDistance（最近邻） │
│ 面积精算（坪效） │ geography + ReorientObject 后再 STArea，或离线预算存普通列 │
│ 带洞的商圈（挖去 │ geometry（geography 不支持 STNumInteriorRing / STExteriorRing）│
│ 湖泊/竞品区） │ │
│ 平面图纸/展柜布局 │ geometry（CAD 坐标，单位是毫米/米，不是经纬度） │
│ 批量布尔运算 │ geometry（geography 无 STUnion/STIntersection/STDifference） │
│ 高并发简单半径过滤 │ 普通经纬度列 + 包围盒索引 + 必要时才 geography │
│ 万亿行分区键 │ 普通列（country_code / geohash 前缀），space 列不能当分区键 │
└──────────────────┴──────────────────────────────────────────────────────────────┘
★ 关键界限：把"半结构化/精确几何"与"高频数值过滤"分开。
与 JSON/XML 那套"边界清晰"的原则一致 ——
空间列只承载【几何语义】，经纬度数值、面积、距离结果应当落成普通列，
这样列存、分区、索引统计都能正常发挥作用。
*/
PRINT '--- 1.1 两种类型的本质差异（同一份坐标，两种表达）---';
DECLARE @lat float = 22.2793, @lon float = 114.1628; -- 香港中环
DECLARE @g geography = geography::Point(@lat, @lon, 4326); -- 注意：(纬度, 经度, SRID)
DECLARE @m geometry = geometry::Point(@lon, @lat, 4326); -- 注意：(x, y, SRID)
DECLARE @wkt geography = geography::STGeomFromText('POINT(114.1628 22.2793)', 4326); -- 注意：WKT 是 (经度 纬度)
SELECT
geography文本 = @g.ToString()
, geography纬度 = @g.Lat
, geography经度 = @g.Long
, geometry文本 = @m.ToString()
, geometry_X = @m.STX
, geometry_Y = @m.STY
, 两种构造是否等价 = CASE WHEN @g.ToString() = @wkt.ToString() THEN N'是（Point 与 WKT 顺序相反但结果一致）' ELSE N'否' END;
GO
PRINT '--- 1.2 单位差异：geography 是米，geometry(SRID 4326) 是"度" ---';
DECLARE @h1 geography = geography::Point(22.2793, 114.1628, 4326); -- 中环
DECLARE @h2 geography = geography::Point(22.2976, 114.1722, 4326); -- 尖沙咀
DECLARE @m1 geometry = geometry::Point(114.1628, 22.2793, 4326);
DECLARE @m2 geometry = geometry::Point(114.1722, 22.2976, 4326);
SELECT
geography距离_米 = CAST(@h1.STDistance(@h2) AS decimal(18,4))
, geography距离_公里 = CAST(@h1.STDistance(@h2) / 1000.0 AS decimal(18,4))
, geometry距离_度 = CAST(@m1.STDistance(@m2) AS decimal(18,10))
, 单位换算提示 = N'geometry(SRID 4326) 的距离单位是度，1 度约 111 km，业务上几乎不可直接用';
GO
PRINT '--- 1.3 geography 的构造入口与各自的参数顺序（★ 头号事故源）---';
DECLARE @p1 geography = geography::Point(22.2793, 114.1628, 4326);
DECLARE @p2 geography = geography::STGeomFromText('POINT(114.1628 22.2793)', 4326);
DECLARE @p3 geography = geography::STPointFromText('POINT(114.1628 22.2793)', 4326);
DECLARE @p4 geography = geography::Parse('POINT(114.1628 22.2793)');
DECLARE @p5 geography = geography::STGeomFromWKB(0x01010000007CF2B0506B8A5C40D6C56D3480473640, 4326);
SELECT
写法 = N'geography::Point(纬度, 经度, SRID)'
, 文本 = @p1.ToString()
, 纬度 = CAST(@p1.Lat AS decimal(10,4))
, 经度 = CAST(@p1.Long AS decimal(10,4))
UNION ALL SELECT N'geography::STGeomFromText(''POINT(经度 纬度)'', SRID)', @p2.ToString(), CAST(@p2.Lat AS decimal(10,4)), CAST(@p2.Long AS decimal(10,4))
UNION ALL SELECT N'geography::STPointFromText(''POINT(经度 纬度)'', SRID)', @p3.ToString(), CAST(@p3.Lat AS decimal(10,4)), CAST(@p3.Long AS decimal(10,4))
UNION ALL SELECT N'geography::Parse(''POINT(经度 纬度)'') ← 不用给 SRID，默认 4326', @p4.ToString(), CAST(@p4.Lat AS decimal(10,4)), CAST(@p4.Long AS decimal(10,4))
UNION ALL SELECT N'geography::STGeomFromWKB(WKB, SRID) ← 二进制，省带宽', @p5.ToString(), CAST(@p5.Lat AS decimal(10,4)), CAST(@p5.Long AS decimal(10,4));
GO
PRINT '--- 1.3b WKB 的产生与回读（跨系统交换用）---';
PRINT ' ★ STAsBinary() 返回 varbinary(max)，【不能】对它继续调用方法（如 .ToString()）→ 258「不能对 varbinary(max) 调用方法」。';
PRINT ' 要转可读文本必须用 CONVERT(varchar(200), ..., 1)。';
DECLARE @src geography = geography::Point(22.2793, 114.1628, 4326);
SELECT
序列化字节 = CONVERT(varchar(200), CAST(@src AS varbinary(max)), 1) -- geography 内部格式（带 SRID 头）
, 标准WKB = CONVERT(varchar(200), @src.STAsBinary(), 1) -- OGC 标准 WKB（无 SRID）
, 标准WKB字节数 = DATALENGTH(@src.STAsBinary());
DECLARE @wkb varbinary(max) = @src.STAsBinary();
DECLARE @back geography = geography::STGeomFromWKB(@wkb, 4326);
SELECT 回读文本 = @back.ToString(), 回读纬度 = @back.Lat, 回读经度 = @back.Long;
GO
PRINT '--- 1.4 存储开销对比：geography 列 vs 两个 float 列 + 一个 varchar ---';
CREATE TABLE dbo._SizeGeo (Id int NOT NULL PRIMARY KEY, G geography);
CREATE TABLE dbo._SizeNum (Id int NOT NULL PRIMARY KEY, Lat float NOT NULL, Lon float NOT NULL);
GO
DECLARE @i int = 0;
WHILE @i < 2000
BEGIN
INSERT dbo._SizeGeo (Id, G) VALUES (@i, geography::Point(22.0 + @i % 100 * 0.01, 114.0 + @i % 97 * 0.01, 4326));
INSERT dbo._SizeNum (Id, Lat, Lon) VALUES (@i, 22.0 + @i % 100 * 0.01, 114.0 + @i % 97 * 0.01);
SET @i += 1;
END
GO
SELECT
方案 = N'dbo._SizeGeo — geography 列（含 SRID 与内部二进制）'
, 行数 = (SELECT COUNT(*) FROM dbo._SizeGeo)
, 聚集索引页数 = (SELECT SUM(ps.used_page_count) FROM sys.dm_db_partition_stats ps WHERE ps.object_id = OBJECT_ID('dbo._SizeGeo') AND ps.index_id = 1)
, 平均每行字节 = (SELECT CAST(SUM(ps.used_page_count) * 8192.0 / (SELECT COUNT(*) FROM dbo._SizeGeo) AS decimal(10,1)) FROM sys.dm_db_partition_stats ps WHERE ps.object_id = OBJECT_ID('dbo._SizeGeo') AND ps.index_id = 1)
UNION ALL
SELECT
N'dbo._SizeNum — 两个 float 列（16 字节固定）'
, (SELECT COUNT(*) FROM dbo._SizeNum)
, (SELECT SUM(ps.used_page_count) FROM sys.dm_db_partition_stats ps WHERE ps.object_id = OBJECT_ID('dbo._SizeNum') AND ps.index_id = 1)
, (SELECT CAST(SUM(ps.used_page_count) * 8192.0 / (SELECT COUNT(*) FROM dbo._SizeNum) AS decimal(10,1)) FROM sys.dm_db_partition_stats ps WHERE ps.object_id = OBJECT_ID('dbo._SizeNum') AND ps.index_id = 1);
GO
PRINT ' → 页数对比：geography 列 32000 页 vs 两个 float 列 26000 页（2000 行，含页填充开销）。';
PRINT ' 单行净载荷 geography 约 26 字节，两个 float 恰 16 字节。';
PRINT ' 空间索引只能建在 geography 列上；纯数值过滤（BETWEEN / 分区 / 列存）用 float 列更省。';
PRINT ' ★ 工程折中：两者并存 —— geography 供空间运算，float 供粗筛与列存。';
GO
DROP TABLE IF EXISTS dbo._SizeGeo;
DROP TABLE IF EXISTS dbo._SizeNum;
GO
PRINT '--- 1.5 ★ 决策树（本脚本后续所有示例都遵循它）---';
PRINT ' 问：需要"米"为单位的距离吗？';
PRINT ' 是 → geography(4326)，用 STDistance';
PRINT ' 否 → 继续';
PRINT ' 问：需要带洞多边形 / 布尔运算(并交差) 吗？';
PRINT ' 是 → geometry（geography 无这些方法，会 6506）';
PRINT ' 否 → 继续';
PRINT ' 问：需要球面面积（平方米）吗？';
PRINT ' 是 → geography + ReorientObject() 先纠方向，再 STArea';
PRINT ' 否 → 继续';
PRINT ' 问：只是高频数值过滤，且已有索引可走？';
PRINT ' 是 → 普通 float 经纬度列 + 复合索引（必要时再补 geography 做精确判定）';
PRINT ' 否 → geography + 空间索引';
GO
PRINT '###################################################################';
PRINT '## 02. 数据模型：为 ERP/CRM/HR 补上空间维度';
PRINT '###################################################################';
GO
/*
现状（本库确认）：
erp.Store 10 行，有 StoreCode/StoreName/CountryCode/City，【没有坐标】
crm.Customer 800 行，有 CountryCode/OwnerEmployeeId，【没有坐标】
hr.Employee 36 行，有 StoreCode/Department，【没有住址坐标】
erp.SalesTarget 196 行，只有 CountryCode 维度，【没有门店维度】
所以本节的策略是：
★ 不改动既有业务表结构（避免影响其他脚本），
而是新建 geo 架构，用 StoreCode 作为外键把它挂到 ERP 主数据上。
这正是"半结构化/空间数据边界清晰"的做法：
空间属性放在独立的卫星表，主数据表保持窄而快。
*/
PRINT '--- 2.1 geo.StoreLocation：门店坐标卫星表（1:1 挂 erp.Store）---';
CREATE TABLE geo.StoreLocation (
StoreCode varchar(8) NOT NULL CONSTRAINT PK_StoreLocation PRIMARY KEY,
Geo geography NOT NULL, -- ★ 全球统一 SRID 4326，单位米
Latitude decimal(9,6) NOT NULL, -- 冗余数值列：给列存/BI/分区用
Longitude decimal(9,6) NOT NULL,
TimeZoneOffset smallint NOT NULL, -- 时区偏移（小时），高并发时区计算要用
OpenedOn date NOT NULL,
CONSTRAINT FK_StoreLocation_Store FOREIGN KEY (StoreCode) REFERENCES erp.Store (StoreCode),
CONSTRAINT CK_StoreLocation_Srid CHECK (Geo.STSrid = 4326), -- ★ 防"静默 NULL"的第一道闸
CONSTRAINT CK_StoreLocation_Lat CHECK (Latitude BETWEEN -90 AND 90),
CONSTRAINT CK_StoreLocation_Lon CHECK (Longitude BETWEEN -180 AND 180)
);
GO
PRINT ' ★ 三处设计要点的理由：';
PRINT ' (1) CHECK (Geo.STSrid = 4326)：不同 SRID 比较是"静默 NULL"（见坑 7），';
PRINT ' 用约束在写入期就把脏数据挡掉，比在查询期发现便宜得多。';
PRINT ' (2) 冗余 Latitude/Longitude 数值列：空间索引不覆盖数据；';
PRINT ' 而分区、列存、普通的 BETWEEN 粗筛都需要【标量】列。';
PRINT ' (3) TimeZoneOffset：跨国珠宝业务的"当日"口径各不相同，';
PRINT ' 与 SMS 验证码那套滑窗一样，时区必须显式落库，不要靠服务器本地时间。';
GO
PRINT '--- 2.2 写入 10 家真实门店坐标 ---';
INSERT geo.StoreLocation (StoreCode, Latitude, Longitude, TimeZoneOffset, OpenedOn, Geo)
SELECT v.StoreCode, v.Lat, v.Lon, v.Tz, v.OpenedOn, geography::Point(v.Lat, v.Lon, 4326)
FROM (VALUES
-- 门店 纬度 经度 时区 开业
('AE-DXB', 25.197200, 55.274400, 4, '2019-03-15'), -- 迪拜购物中心
('CN-BJ', 39.904200, 116.407400, 8, '2016-06-01'), -- 北京国贸
('CN-SHA', 31.230400, 121.473700, 8, '2015-09-20'), -- 上海南京西路
('CN-SHZ', 22.543100, 114.057900, 8, '2018-11-08'), -- 深圳万象城
('FR-PAR', 48.867600, 2.328100, 2, '2017-04-12'), -- 巴黎旺多姆
('HK-CEN', 22.279300, 114.162800, 8, '2012-01-05'), -- 香港中环
('HK-TST', 22.297600, 114.172200, 8, '2014-07-18'), -- 香港尖沙咀
('JP-GIN', 35.671700, 139.765000, 9, '2020-02-06'), -- 东京银座
('SG-ORC', 1.304000, 103.831800, 8, '2018-05-25'), -- 新加坡乌节路
('US-NYC', 40.761400, -73.977600, -5, '2013-10-01') -- 纽约第五大道
) v(StoreCode, Lat, Lon, Tz, OpenedOn);
GO
SELECT
门店 = s.StoreCode
, 名称 = s.StoreName
, 国家 = s.CountryCode
, 城市 = s.City
, 纬度 = CAST(g.Latitude AS decimal(9,6))
, 经度 = CAST(g.Longitude AS decimal(9,6))
, 时区 = g.TimeZoneOffset
, 几何文本 = g.Geo.ToString()
FROM geo.StoreLocation g JOIN erp.Store s ON s.StoreCode = g.StoreCode
ORDER BY s.CountryCode, s.StoreCode;
GO
PRINT '--- 2.3 geo.StoreGeoFence：商圈/配送范围围栏（多边形）---';
/*
珠宝门店的"商圈"不是圆，而是沿街的带状区域（旺多姆广场、第五大道都是"街"）。
这里给 4 家门店建多边形围栏，其余门店用圆形半径（下节演示）。
环方向刻意保持【逆时针】，避免踩坑 4。
*/
CREATE TABLE geo.StoreGeoFence (
FenceId int IDENTITY(1,1) NOT NULL CONSTRAINT PK_StoreGeoFence PRIMARY KEY,
StoreCode varchar(8) NOT NULL,
FenceName nvarchar(60) NOT NULL,
FenceType varchar(12) NOT NULL, -- CATCHMENT 商圈 / DELIVERY 配送 / EXCLUSION 排除区
Fence geography NOT NULL,
AreaSqm AS CONVERT(decimal(18,2), Fence.STArea()) PERSISTED, -- ★ 只对 STArea 建持久化计算列
Perimeter AS CONVERT(decimal(18,2), Fence.STLength()) PERSISTED,
CreatedAt datetime2(0) NOT NULL CONSTRAINT DF_Fence_At DEFAULT SYSDATETIME(),
CONSTRAINT FK_Fence_Store FOREIGN KEY (StoreCode) REFERENCES erp.Store (StoreCode),
CONSTRAINT CK_Fence_Srid CHECK (Fence.STSrid = 4326),
CONSTRAINT CK_Fence_Valid CHECK (Fence.STIsValid() = 1)
);
GO
PRINT ' ★★ 这里有一个实测到的、极其反直觉的陷阱（务必读完）：';
PRINT ' ReorientObject() 是"检查当前方向，若为顺时针则翻转"。';
PRINT ' 因此对一个【方向本就正确】的多边形调用 ReorientObject()，它不会保持原样，';
PRINT ' 而是把逆时针翻成顺时针 —— 结果面积变成"地球补集"（约 510 万亿 m²）。';
PRINT ' 实测：面积正确的多边形 @g（1,227,857,363,514.96 m²，逆时针）；';
PRINT ' @g.ReorientObject().STArea() = 510,065,618,059,598.25 m² ← 完全错误！';
PRINT ' 所以 ReorientObject() 的正确用法是：';
PRINT ' (a) 把它当作"一次性修复工具"用在【已知方向错误】的几何体上；';
PRINT ' (b) 绝不要盲目包在 STArea() 外面当"保险" —— 它会主动把好数据弄坏。';
PRINT ' 落库前的正确防线是 CHECK 约束 Fence.STIsValid() = 1 加上录入规范（外环逆时针）。';
GO
INSERT geo.StoreGeoFence (StoreCode, FenceName, FenceType, Fence)
SELECT v.StoreCode, v.FenceName, v.FenceType, geography::STGeomFromText(v.Wkt, 4326)
FROM (VALUES
('HK-CEN', N'中环皇后大道核心商圈', 'CATCHMENT',
'POLYGON((114.1500 22.2720, 114.1760 22.2720, 114.1760 22.2890, 114.1500 22.2890, 114.1500 22.2720))'),
('HK-TST', N'尖沙咀广东道商圈', 'CATCHMENT',
'POLYGON((114.1630 22.2900, 114.1830 22.2900, 114.1830 22.3060, 114.1630 22.3060, 114.1630 22.2900))'),
('CN-SHA', N'上海南京西路商圈', 'CATCHMENT',
'POLYGON((121.4400 31.2200, 121.4900 31.2200, 121.4900 31.2450, 121.4400 31.2450, 121.4400 31.2200))'),
('US-NYC', N'纽约曼哈顿中城配送圈', 'DELIVERY',
'POLYGON((-74.0050 40.7400, -73.9500 40.7400, -73.9500 40.7800, -74.0050 40.7800, -74.0050 40.7400))')
) v(StoreCode, FenceName, FenceType, Wkt);
GO
SELECT
围栏 = f.FenceName
, 门店 = f.StoreCode
, 类型 = f.FenceType
, 面积平方米 = f.AreaSqm
, 周长米 = f.Perimeter
, 是否有效 = f.Fence.STIsValid()
, 顶点数 = f.Fence.STNumPoints()
FROM geo.StoreGeoFence f ORDER BY f.FenceName;
GO
PRINT '--- 2.4 ★★ 坑 4 现场演示：环方向决定 STArea 是"地块"还是"地球补集" ---';
PRINT ' 两个多边形顶点完全相同，只是书写方向相反，且 STIsValid() 都返回 1。';
DECLARE @ccw geography = geography::STGeomFromText('POLYGON((0 0, 10 0, 10 10, 0 10, 0 0))', 4326); -- 逆时针（正确）
DECLARE @cw geography = geography::STGeomFromText('POLYGON((0 0, 0 10, 10 10, 10 0, 0 0))', 4326); -- 顺时针（错误）
SELECT
逆时针外环面积 = @ccw.STArea()
, 顺时针外环面积 = @cw.STArea()
, 两者之和 = @ccw.STArea() + @cw.STArea()
, 地球表面积参考 = 510065621724000.0
, 逆时针是否合法 = @ccw.STIsValid()
, 顺时针是否合法 = @cw.STIsValid()
, 结论 = N'★ 方向错误的多边形合法(IsValid=1)、不报任何错，面积却是地球减去地块';
GO
PRINT '--- 2.4b ★ ReorientObject() 的真实行为：它是"翻转"，不是"纠正到正确" ---';
DECLARE @good geography = geography::STGeomFromText('POLYGON((0 0, 10 0, 10 10, 0 10, 0 0))', 4326);
DECLARE @bad geography = geography::STGeomFromText('POLYGON((0 0, 0 10, 10 10, 10 0, 0 0))', 4326);
SELECT
原本正确的面积 = @good.STArea()
, 对正确者调用Reorient后 = @good.ReorientObject().STArea()
, 原本错误的面积 = @bad.STArea()
, 对错误者调用Reorient后 = @bad.ReorientObject().STArea()
, 结论 = N'★★ ReorientObject 会翻转"已是逆时针"的多边形，把正确面积变成地球补集';
GO
PRINT ' ★★ 这条实测推翻了很常见的"顺手包一层 ReorientObject 更安全"的直觉：';
PRINT ' ReorientObject() 的语义是 Reorient = 重新定向（若顺时针则翻为逆时针），';
PRINT ' 对已经正确的几何体它是【破坏性】的。不要把它当作 STArea 的默认包装。';
PRINT ' 正确做法：录入时保证外环逆时针 + CHECK Fence.STIsValid() = 1；';
PRINT ' 仅在明确知道某个几何体方向错了时，才单独调用 ReorientObject 修一次。';
GO
PRINT '--- 2.5 ★ 带洞多边形：内环方向必须与外环相反，方向错了 STArea 直接抛错 ---';
DECLARE @donut geography = geography::STGeomFromText(
'POLYGON((0 0, 10 0, 10 10, 0 10, 0 0), (4 4, 4 6, 6 6, 6 4, 4 4))', 4326); -- 洞与外环反向：合法
SELECT
洞方向正确_面积 = @donut.STArea()
, 洞方向正确_有效 = @donut.STIsValid()
, 洞方向正确_洞数 = N'(geography 无数洞的方法，见 2.6)';
GO
PRINT ' 对比：洞与外环【同向】的多边形 —— STArea 会直接抛 6522 / 24144';
DECLARE @donutSameWay geography = geography::STGeomFromText(
'POLYGON((0 0, 10 0, 10 10, 0 10, 0 0), (4 4, 6 4, 6 6, 4 6, 4 4))', 4326);
BEGIN TRY
SELECT 洞方向错误_面积 = @donutSameWay.STArea();
PRINT ' 洞方向错误的多边形：居然能算面积——请复核';
END TRY BEGIN CATCH
PRINT ' 洞方向错误 → ' + CAST(ERROR_NUMBER() AS nvarchar(10)) + N'：' + LEFT(ERROR_MESSAGE(), 150);
END CATCH;
SELECT 洞方向错误_有效性 = @donutSameWay.STIsValid();
GO
PRINT ' → 与"外环方向写反静默出错"不同，洞方向写反会在 STArea 上【响亮报错】；';
PRINT ' 但 STIsValid() 仍返回 1，所以不能只靠 STIsValid 做准入判断，面积类操作要 try/catch。';
GO
PRINT '--- 2.6 ★ geography 不支持"洞/环/包围盒"相关方法（实测 6506）---';
BEGIN TRY
DECLARE @g geography = geography::STGeomFromText('POLYGON((0 0, 10 0, 10 10, 0 10, 0 0), (4 4, 4 6, 6 6, 6 4, 4 4))', 4326);
SELECT geography_洞数 = @g.STNumInteriorRing();
PRINT ' geography.STNumInteriorRing 可用';
END TRY BEGIN CATCH
PRINT ' geography.STNumInteriorRing 被拒 → ' + CAST(ERROR_NUMBER() AS nvarchar(10)) + N'：' + LEFT(ERROR_MESSAGE(), 120);
END CATCH;
GO
DECLARE @gm geometry = geometry::STGeomFromText('POLYGON((0 0, 10 0, 10 10, 0 10, 0 0), (4 4, 4 6, 6 6, 6 4, 4 4))', 0);
SELECT
geometry_洞数 = @gm.STNumInteriorRing()
, geometry_面积平方度 = @gm.STArea()
, 外环顶点数 = @gm.STExteriorRing().STNumPoints()
, 第一个洞顶点数 = @gm.STInteriorRingN(1).STNumPoints()
, 是否环 = @gm.STIsRing()
, 包围盒 = @gm.STEnvelope().ToString()
, 结论 = N'需要洞/环/包围盒/质心时，把坐标交给 geometry 处理';
GO
PRINT '--- 2.7 geography 可用方法全表（逐项实测，全部有参数）---';
DECLARE @probe geography = geography::STGeomFromText('POLYGON((0 0, 10 0, 10 10, 0 10, 0 0))', 4326);
DECLARE @probe2 geography = geography::STGeomFromText('POINT(5 5)', 4326);
SELECT
分组 = N'度量', 方法 = N'STArea', 结果 = CAST(@probe.STArea() AS nvarchar(60))
UNION ALL SELECT N'度量', N'STLength', CAST(@probe.STLength() AS nvarchar(60))
UNION ALL SELECT N'度量', N'STDistance', CAST(@probe.STDistance(@probe2) AS nvarchar(60))
UNION ALL SELECT N'属性', N'STNumPoints', CAST(@probe.STNumPoints() AS nvarchar(60))
UNION ALL SELECT N'属性', N'STGeometryType', CAST(@probe.STGeometryType() AS nvarchar(60))
UNION ALL SELECT N'属性', N'STDimension', CAST(@probe.STDimension() AS nvarchar(60))
UNION ALL SELECT N'属性', N'STIsValid', CAST(@probe.STIsValid() AS nvarchar(60))
UNION ALL SELECT N'属性', N'STIsEmpty', CAST(@probe.STIsEmpty() AS nvarchar(60))
UNION ALL SELECT N'属性', N'STIsClosed', CAST(@probe.STIsClosed() AS nvarchar(60))
UNION ALL SELECT N'属性', N'EnvelopeCenter', @probe.EnvelopeCenter().ToString()
UNION ALL SELECT N'属性', N'EnvelopeAngle', CAST(@probe.EnvelopeAngle() AS nvarchar(60))
UNION ALL SELECT N'属性', N'Lat/Long', CAST(@probe2.Lat AS nvarchar(20)) + N' / ' + CAST(@probe2.Long AS nvarchar(20))
UNION ALL SELECT N'输出', N'STAsText', LEFT(@probe.STAsText(), 44)
UNION ALL SELECT N'输出', N'ToString', LEFT(@probe.ToString(), 44)
UNION ALL SELECT N'输出', N'STAsBinary', N'varbinary(max)，21 字节/点'
UNION ALL SELECT N'变换', N'STBuffer(1000)', CAST(@probe.STBuffer(1000).STNumPoints() AS nvarchar(60))
UNION ALL SELECT N'变换', N'STConvexHull', CAST(@probe.STConvexHull().STNumPoints() AS nvarchar(60))
UNION ALL SELECT N'变换', N'ReorientObject', CAST(@probe.ReorientObject().STNumPoints() AS nvarchar(60))
UNION ALL SELECT N'布尔', N'STUnion(∩)', N'与另一多边形合并 → 顶点变多'
UNION ALL SELECT N'布尔', N'STIntersection', N'求交 → 结果多边形'
UNION ALL SELECT N'布尔', N'STDifference', N'求差 → 结果多边形'
UNION ALL SELECT N'布尔', N'STSymDifference',N'对称差 → 结果多边形'
UNION ALL SELECT N'关系', N'STContains', CAST(@probe.STContains(@probe2) AS nvarchar(60))
UNION ALL SELECT N'关系', N'STWithin', CAST(@probe2.STWithin(@probe) AS nvarchar(60))
UNION ALL SELECT N'关系', N'STIntersects', CAST(@probe.STIntersects(@probe2) AS nvarchar(60))
UNION ALL SELECT N'关系', N'STDisjoint', CAST(@probe.STDisjoint(@probe2) AS nvarchar(60))
UNION ALL SELECT N'关系', N'STEquals', CAST(@probe.STEquals(@probe) AS nvarchar(60))
UNION ALL SELECT N'关系', N'STOverlaps', CAST(@probe.STOverlaps(@probe) AS nvarchar(60));
GO
PRINT '--- 2.7b ★ 布尔运算在 geography 上确实可用（实测，别再因为 174 误判）---';
DECLARE @qa geography = geography::STGeomFromText('POLYGON((0 0, 10 0, 10 10, 0 10, 0 0))', 4326);
DECLARE @qb geography = geography::STGeomFromText('POLYGON((5 5, 15 5, 15 15, 5 15, 5 5))', 4326);
SELECT
并集_顶点数 = @qa.STUnion(@qb).STNumPoints()
, 交集_顶点数 = @qa.STIntersection(@qb).STNumPoints()
, qa减qb_顶点数 = @qa.STDifference(@qb).STNumPoints()
, 对称差_顶点数 = @qa.STSymDifference(@qb).STNumPoints()
, 并集面积平方米 = CAST(@qa.STUnion(@qb).STArea() AS decimal(18,2))
, 交集面积平方米 = CAST(@qa.STIntersection(@qb).STArea() AS decimal(18,2));
GO
PRINT ' → 珠宝业务用途：多个相邻商圈的合并（并集）、';
PRINT ' 竞品覆盖区的扣除（差集）、跨店联合促销范围（并集）都能在 geography 上直接做，';
PRINT ' 结果仍是 geography，可以继续 STArea 算面积（球面，单位平方米）。';
GO
PRINT '--- 2.8 明确"不支持"的方法（6506，调用前必须知道）---';
BEGIN TRY
DECLARE @g1 geography = geography::Point(22.2793, 114.1628, 4326);
SELECT @g1.STTouches(@g1);
PRINT ' geography.STTouches 可用';
END TRY BEGIN CATCH
PRINT ' geography.STTouches → ' + CAST(ERROR_NUMBER() AS nvarchar(10)) + N'：' + LEFT(ERROR_MESSAGE(), 120);
END CATCH;
GO
BEGIN TRY
DECLARE @g2 geography = geography::Point(22.2793, 114.1628, 4326);
SELECT @g2.STCentroid();
PRINT ' geography.STCentroid 可用';
END TRY BEGIN CATCH
PRINT ' geography.STCentroid → ' + CAST(ERROR_NUMBER() AS nvarchar(10)) + N'：' + LEFT(ERROR_MESSAGE(), 120);
END CATCH;
GO
BEGIN TRY
DECLARE @g3 geography = geography::Point(22.2793, 114.1628, 4326);
SELECT @g3.STEnvelope();
PRINT ' geography.STEnvelope 可用';
END TRY BEGIN CATCH
PRINT ' geography.STEnvelope → ' + CAST(ERROR_NUMBER() AS nvarchar(10)) + N'：' + LEFT(ERROR_MESSAGE(), 120);
END CATCH;
GO
BEGIN TRY
DECLARE @g4 geography = geography::Point(22.2793, 114.1628, 4326);
SELECT @g4.STIsRing();
PRINT ' geography.STIsRing 可用';
END TRY BEGIN CATCH
PRINT ' geography.STIsRing → ' + CAST(ERROR_NUMBER() AS nvarchar(10)) + N'：' + LEFT(ERROR_MESSAGE(), 120);
END CATCH;
GO
BEGIN TRY
DECLARE @g5 geography = geography::Point(22.2793, 114.1628, 4326);
SELECT @g5.STExteriorRing();
PRINT ' geography.STExteriorRing 可用';
END TRY BEGIN CATCH
PRINT ' geography.STExteriorRing → ' + CAST(ERROR_NUMBER() AS nvarchar(10)) + N'：' + LEFT(ERROR_MESSAGE(), 120);
END CATCH;
GO
PRINT ' ══ geography 上【不存在】的方法清单（本机逐项实测）══';
PRINT ' 几何构造类：STNumInteriorRing / STIsRing / STExteriorRing / STInteriorRingN';
PRINT ' 衍生几何类：STEnvelope / STBoundary / STCentroid / STPointOnSurface';
PRINT ' 拓扑关系类：STTouches / STCrosses / STRelate';
PRINT ' （地理坐标系下这些概念需要球面重定义，微软未实现；改用 geometry 或组合谓词替代）';
GO
PRINT '--- 2.9 ★ 174 vs 6506：一个害我误判过的错误码区别 ---';
BEGIN TRY
DECLARE @x geography = geography::STGeomFromText('POLYGON((0 0, 10 0, 10 10, 0 10, 0 0))', 4326);
DECLARE @y geography = @x.STUnion(); -- 故意漏掉参数
PRINT ' 漏参数居然通过';
END TRY BEGIN CATCH
PRINT ' 漏参数 → ' + CAST(ERROR_NUMBER() AS nvarchar(10)) + N'：' + LEFT(ERROR_MESSAGE(), 120);
END CATCH;
GO
DECLARE @x2 geography = geography::STGeomFromText('POLYGON((0 0, 10 0, 10 10, 0 10, 0 0))', 4326);
DECLARE @y2 geography = geography::STGeomFromText('POLYGON((5 5, 15 5, 15 15, 5 15, 5 5))', 4326);
SELECT 传了参数就成功_顶点数 = @x2.STUnion(@y2).STNumPoints();
GO
PRINT ' → 【174】= 参数个数不对（方法其实是存在的）；';
PRINT ' 【6506】= 方法真的不存在。';
PRINT ' 验证方法是否存在时，必须传合法参数；用空括号会得到 174 的假阴性。';
GO
PRINT '###################################################################';
PRINT '## 03. CRM：客户地理分布 + 到店事件点位';
PRINT '###################################################################';
GO
/*
珠宝 CRM 的空间问题有三个层次：
1) 静态：这位 VIP 客户住哪儿？离哪家店最近？（nearest store）
2) 动态：他实际上有没有来？来了几次？（事件点位聚集）
3) 规划：这个商圈能覆盖多少客户？（catchment 吸附计数）
本节建立承载这三层的数据。
*/
PRINT '--- 3.1 geo.CustomerLocation：客户常驻地/偏好门店周边坐标 ---';
/*
真实业务里 CRM 给的是"国家 + 城市"，精确住址往往缺失或受隐私保护。
这里的建模策略：以客户所属国家的门店为锚点，生成城市级散布坐标。
这样既能演示真实的空间查询，又不伪造不存在的精确数据。
*/
CREATE TABLE geo.CustomerLocation (
CustomerId int NOT NULL CONSTRAINT PK_CustomerLocation PRIMARY KEY,
Geo geography NOT NULL,
Latitude decimal(9,6) NOT NULL,
Longitude decimal(9,6) NOT NULL,
AnchorStore varchar(8) NOT NULL, -- 客户归属的锚点门店（用于隐私脱敏的分级展示）
Precision char(1) NOT NULL, -- C=城市级 S=街道级 (珠宝 VIP 通常只到 C)
CONSTRAINT FK_CustLoc_Customer FOREIGN KEY (CustomerId) REFERENCES crm.Customer (CustomerId),
CONSTRAINT FK_CustLoc_Store FOREIGN KEY (AnchorStore) REFERENCES erp.Store (StoreCode),
CONSTRAINT CK_CustLoc_Srid CHECK (Geo.STSrid = 4326),
CONSTRAINT CK_CustLoc_Precision CHECK (Precision IN ('C','S'))
);
GO
PRINT ' 生成策略：按客户所属国家挑一家同国门店作锚点，围绕它散布 ±0.05 度（约 ±5.5 km）';
INSERT geo.CustomerLocation (CustomerId, Latitude, Longitude, AnchorStore, Precision, Geo)
SELECT
c.CustomerId
, CAST(anchor.Lat + ((c.CustomerId * 17 % 200) - 100) / 1000.0 * 0.5 AS decimal(9,6))
, CAST(anchor.Lon + ((c.CustomerId * 31 % 200) - 100) / 1000.0 * 0.5 AS decimal(9,6))
, anchor.StoreCode
, CASE WHEN c.Tier IN ('VIP','Premium') THEN 'S' ELSE 'C' END
, geography::Point(
CAST(anchor.Lat + ((c.CustomerId * 17 % 200) - 100) / 1000.0 * 0.5 AS float)
, CAST(anchor.Lon + ((c.CustomerId * 31 % 200) - 100) / 1000.0 * 0.5 AS float)
, 4326)
FROM crm.Customer c
CROSS APPLY (
SELECT TOP 1 sl.Latitude AS Lat, sl.Longitude AS Lon, sl.StoreCode
FROM geo.StoreLocation sl
JOIN erp.Store s ON s.StoreCode = sl.StoreCode
WHERE s.CountryCode = c.CountryCode
ORDER BY sl.StoreCode
) anchor;
GO
SELECT 客户点位总数 = COUNT(*), 覆盖锚点门店数 = COUNT(DISTINCT AnchorStore) FROM geo.CustomerLocation;
GO
SELECT TOP 8
客户 = c.CustomerName
, 等级 = c.Tier
, 国家 = c.CountryCode
, 锚点店 = cl.AnchorStore
, 精度 = cl.Precision
, 纬度 = CAST(cl.Latitude AS decimal(9,6))
, 经度 = CAST(cl.Longitude AS decimal(9,6))
FROM geo.CustomerLocation cl
JOIN crm.Customer c ON c.CustomerId = cl.CustomerId
ORDER BY c.CustomerId;
GO
PRINT '--- 3.2 客户分布 vs 门店覆盖：各国客户数与 VIP 占比 ---';
SELECT
国家 = c.CountryCode
, 客户数 = COUNT(*)
, VIP人数 = SUM(CASE WHEN c.Tier = 'VIP' THEN 1 ELSE 0 END)
, Premium人数 = SUM(CASE WHEN c.Tier = 'Premium' THEN 1 ELSE 0 END)
, 高价值占比 = CAST(SUM(CASE WHEN c.Tier IN ('VIP','Premium') THEN 1 ELSE 0 END) * 100.0 / COUNT(*) AS decimal(5,1))
, 平均纬度 = CAST(AVG(CONVERT(float, cl.Latitude)) AS decimal(9,4))
, 平均经度 = CAST(AVG(CONVERT(float, cl.Longitude)) AS decimal(9,4))
FROM geo.CustomerLocation cl
JOIN crm.Customer c ON c.CustomerId = cl.CustomerId
GROUP BY c.CountryCode
ORDER BY 客户数 DESC, 国家;
GO
PRINT ' ★ 这里已经出现"地理聚合"：平均纬度/经度只是描述性指标，';
PRINT ' 真正有业务意义的是"离门店多远"——下节用 STDistance 算。';
GO
PRINT '--- 3.3 geo.StoreVisit：到店/触点事件（有索引与无索引的性能对比载体）---';
/*
★ 这是本脚本里"万亿行"思想的缩影：事件表按【时间 + 门店】双维度膨胀。
真实珠宝企业一家旗舰店每天 200~2000 次到店触点（含 POS、试戴、咨询、维修）。
10 家店 × 365 天 × 500 次 ≈ 182 万/年；百年老店 + 全球扩张 → 亿级起步。
本脚本生成 20 万行作基准（可控时长），结构上与亿级完全一致。
*/
CREATE TABLE geo.StoreVisit (
VisitId bigint IDENTITY(1,1) NOT NULL CONSTRAINT PK_StoreVisit PRIMARY KEY,
StoreCode varchar(8) NOT NULL,
CustomerId int NULL, -- 可为空：匿名到店
OccurredAt datetime2(0) NOT NULL,
Channel varchar(10) NOT NULL, -- WALKIN/APPT/REPAIR/EVENT
Geo geography NOT NULL, -- 到店时的定位（用于反查是否真在商圈内）
CONSTRAINT FK_Visit_Store FOREIGN KEY (StoreCode) REFERENCES erp.Store (StoreCode),
CONSTRAINT CK_Visit_Srid CHECK (Geo.STSrid = 4326),
CONSTRAINT CK_Visit_Channel CHECK (Channel IN ('WALKIN','APPT','REPAIR','EVENT'))
);
GO
PRINT ' 生成 20 万条事件：围绕 10 家门店聚集（模拟真实客流），时间回溯 15 天';
DECLARE @t0 datetime2(0) = SYSDATETIME();
;WITH Seq AS (
SELECT TOP (200000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n
FROM master..spt_values a CROSS JOIN master..spt_values b
)
INSERT geo.StoreVisit (StoreCode, CustomerId, OccurredAt, Channel, Geo)
SELECT
st.StoreCode
, CASE WHEN q.n % 4 = 0 THEN NULL ELSE ((q.n * 7) % 800) + 1 END
, DATEADD(minute, -(q.n % 21600), @t0) -- 15 天内
, CASE q.n % 4 WHEN 0 THEN 'WALKIN' WHEN 1 THEN 'APPT' WHEN 2 THEN 'REPAIR' ELSE 'EVENT' END
, geography::Point(
CAST(st.Lat + (((q.n * 17) % 2000) - 1000) / 1000.0 * 0.16 AS float)
, CAST(st.Lon + (((q.n * 31) % 2000) - 1000) / 1000.0 * 0.16 AS float)
, 4326)
FROM Seq q
JOIN (
SELECT StoreCode, CAST(Latitude AS float) AS Lat, CAST(Longitude AS float) AS Lon
, ROW_NUMBER() OVER (ORDER BY StoreCode) AS rn
FROM geo.StoreLocation
) st ON st.rn = ((q.n - 1) % 10) + 1;
GO
SELECT
事件总数 = COUNT(*)
, 覆盖门店 = COUNT(DISTINCT StoreCode)
, 时间跨度_小时 = DATEDIFF(HOUR, MIN(OccurredAt), MAX(OccurredAt))
FROM geo.StoreVisit;
GO
PRINT '--- 3.4 HR：员工通勤起点（住址坐标）+ 到店距离分析的前提 ---';
CREATE TABLE geo.EmployeeHome (
EmployeeId int NOT NULL CONSTRAINT PK_EmployeeHome PRIMARY KEY,
Geo geography NOT NULL,
Latitude decimal(9,6) NOT NULL,
Longitude decimal(9,6) NOT NULL,
CommuteMode varchar(10) NOT NULL, -- MTR/WALK/CAR/BUS/RAIL
CONSTRAINT FK_EmpHome_Employee FOREIGN KEY (EmployeeId) REFERENCES hr.Employee (EmployeeId),
CONSTRAINT CK_EmpHome_Srid CHECK (Geo.STSrid = 4326),
CONSTRAINT CK_EmpHome_Mode CHECK (CommuteMode IN ('MTR','WALK','CAR','BUS','RAIL'))
);
GO
PRINT ' 生成策略：以员工所属门店为锚点，散布 ±0.10 度（约 ±11 km），模拟通勤圈；';
PRINT ' 集团管理层/无门店员工则挂到中环总部。';
INSERT geo.EmployeeHome (EmployeeId, Latitude, Longitude, CommuteMode, Geo)
SELECT
e.EmployeeId
, CAST(home.Lat + ((e.EmployeeId * 23 % 200) - 100) / 1000.0 * 1.0 AS decimal(9,6))
, CAST(home.Lon + ((e.EmployeeId * 37 % 200) - 100) / 1000.0 * 1.0 AS decimal(9,6))
, CASE e.EmployeeId % 5 WHEN 0 THEN 'MTR' WHEN 1 THEN 'WALK' WHEN 2 THEN 'CAR' WHEN 3 THEN 'BUS' ELSE 'RAIL' END
, geography::Point(
CAST(home.Lat + ((e.EmployeeId * 23 % 200) - 100) / 1000.0 * 1.0 AS float)
, CAST(home.Lon + ((e.EmployeeId * 37 % 200) - 100) / 1000.0 * 1.0 AS float)
, 4326)
FROM hr.Employee e
CROSS APPLY (
SELECT TOP 1 CAST(sl.Latitude AS float) AS Lat, CAST(sl.Longitude AS float) AS Lon
FROM geo.StoreLocation sl
WHERE sl.StoreCode = ISNULL(e.StoreCode, 'HK-CEN')
ORDER BY sl.StoreCode
) home;
GO
SELECT
员工住址数 = COUNT(*)
, 通勤方式分布 = (SELECT STRING_AGG(CONVERT(nvarchar(40), CONCAT(CommuteMode, ':', 人数)), N' / ')
FROM (SELECT CommuteMode, COUNT(*) AS 人数 FROM geo.EmployeeHome GROUP BY CommuteMode) x)
FROM geo.EmployeeHome;
GO
PRINT '###################################################################';
PRINT '## 04. 距离计算：STDistance / Haversine / 平面几何 三种口径对比';
PRINT '###################################################################';
GO
PRINT '--- 4.1 STDistance：geography 的测地线距离，单位是米 ---';
DECLARE @cen geography = geography::Point(22.2793, 114.1628, 4326); -- 中环
DECLARE @tst geography = geography::Point(22.2976, 114.1722, 4326); -- 尖沙咀
DECLARE @nyc geography = geography::Point(40.7614, -73.9776, 4326); -- 纽约
SELECT
中环到尖沙咀_米 = CAST(@cen.STDistance(@tst) AS decimal(18,4))
, 中环到尖沙咀_公里 = CAST(@cen.STDistance(@tst) / 1000.0 AS decimal(18,4))
, 中环到纽约_公里 = CAST(@cen.STDistance(@nyc) / 1000.0 AS decimal(18,2))
, 实测参考 = N'中环→尖沙咀真实地面距离约 2.3 km，STDistance 给出 2246.06 m，误差 <3%';
GO
PRINT '--- 4.2 自己写 Haversine 公式作为对照（理解 STDistance 在算什么）---';
/*
Haversine（球面余弦，地球半径取 6371 km）：
a = sin²(Δφ/2) + cosφ₁·cosφ₂·sin²(Δλ/2)
d = 2R·asin(√a)
STDistance 用的是 WGS84 椭球上的测地线（Vincenty 类算法），比 Haversine 更精确。
*/
DECLARE
@lat1 float = 22.2793, @lon1 float = 114.1628
, @lat2 float = 22.2976, @lon2 float = 114.1722;
DECLARE @R float = 6371000.0; -- 米
DECLARE @phi1 float = RADIANS(@lat1), @phi2 float = RADIANS(@lat2);
DECLARE @dphi float = RADIANS(@lat2 - @lat1), @dlam float = RADIANS(@lon2 - @lon1);
DECLARE @a float = SIN(@dphi/2) * SIN(@dphi/2) + COS(@phi1) * COS(@phi2) * SIN(@dlam/2) * SIN(@dlam/2);
DECLARE @haversine float = 2 * @R * ASIN(SQRT(@a));
DECLARE @gd geography = geography::Point(@lat1, @lon1, 4326);
DECLARE @gt geography = geography::Point(@lat2, @lon2, 4326);
SELECT
Haversine_球面_米 = CAST(@haversine AS decimal(18,4))
, STDistance_椭球_米 = CAST(@gd.STDistance(@gt) AS decimal(18,4))
, 差值_米 = CAST(@haversine - @gd.STDistance(@gt) AS decimal(18,4))
, 相对偏差_百分比 = CAST((@haversine - @gd.STDistance(@gt)) / @gd.STDistance(@gt) * 100 AS decimal(10,4));
GO
PRINT ' → 城市级距离（几公里）两者差几十米量级，业务上可接受；';
PRINT ' 但跨洲（上万公里）差可达公里级，且 Haversine 不做椭球校正。';
PRINT ' 历史遗留的"手写 Haversine 存储过程"应当逐步替换为 STDistance。';
GO
PRINT '--- 4.3 geometry 在 SRID 4326 下的距离：单位是"度"，几乎不可直接用 ---';
DECLARE @gm1 geometry = geometry::Point(114.1628, 22.2793, 4326);
DECLARE @gm2 geometry = geometry::Point(114.1722, 22.2976, 4326);
DECLARE @cenRef geography = geography::Point(22.2793, 114.1628, 4326);
DECLARE @tstRef geography = geography::Point(22.2976, 114.1722, 4326);
SELECT
geometry距离_度 = CAST(@gm1.STDistance(@gm2) AS decimal(18,10))
, 若按1度111km估算 = CAST(@gm1.STDistance(@gm2) * 111000 AS decimal(18,2))
, geography真实距离_米 = CAST(@cenRef.STDistance(@tstRef) AS decimal(18,2))
, 估算误差_米 = CAST(@gm1.STDistance(@gm2) * 111000 - @cenRef.STDistance(@tstRef) AS decimal(18,2))
, 结论 = N'★ geometry 距离是平面欧氏距离，随纬度漂移，不能当"米"用';
GO
PRINT '--- 4.4 ★ 坑 3 现场演示：STBuffer 的圆面积系统性偏小 0.0402% ---';
PRINT ' ★ 注意：PI()*POWER(100000,2) 会撞 int 溢出（错误 232，值 = 1e10）——半径常量必须写成 float。';
DECLARE @c geography = geography::Point(22.2793, 114.1628, 4326);
SELECT 半径_米, 实测圆面积_平方米, 理论面积_平方米, 偏差百分比
FROM (
SELECT 100.0e0 AS 半径_米, @c.STBuffer(100).STArea() AS 实测圆面积_平方米, PI() * 100.0e0 * 100.0e0 AS 理论面积_平方米
, CAST((@c.STBuffer(100).STArea() - PI() * 100.0e0 * 100.0e0) / (PI() * 100.0e0 * 100.0e0) * 100 AS decimal(10,4)) AS 偏差百分比
UNION ALL SELECT 500.0e0, @c.STBuffer(500).STArea(), PI() * 500.0e0 * 500.0e0,
CAST((@c.STBuffer(500).STArea() - PI() * 500.0e0 * 500.0e0) / (PI() * 500.0e0 * 500.0e0) * 100 AS decimal(10,4))
UNION ALL SELECT 1000.0e0, @c.STBuffer(1000).STArea(), PI() * 1000.0e0 * 1000.0e0,
CAST((@c.STBuffer(1000).STArea() - PI() * 1000.0e0 * 1000.0e0) / (PI() * 1000.0e0 * 1000.0e0) * 100 AS decimal(10,4))
UNION ALL SELECT 5000.0e0, @c.STBuffer(5000).STArea(), PI() * 5000.0e0 * 5000.0e0,
CAST((@c.STBuffer(5000).STArea() - PI() * 5000.0e0 * 5000.0e0) / (PI() * 5000.0e0 * 5000.0e0) * 100 AS decimal(10,4))
UNION ALL SELECT 20000.0e0, @c.STBuffer(20000).STArea(), PI() * 20000.0e0 * 20000.0e0,
CAST((@c.STBuffer(20000).STArea() - PI() * 20000.0e0 * 20000.0e0) / (PI() * 20000.0e0 * 20000.0e0) * 100 AS decimal(10,4))
UNION ALL SELECT 100000.0e0, @c.STBuffer(100000).STArea(), PI() * 100000.0e0 * 100000.0e0,
CAST((@c.STBuffer(100000).STArea() - PI() * 100000.0e0 * 100000.0e0) / (PI() * 100000.0e0 * 100000.0e0) * 100 AS decimal(10,4))
) x;
GO
PRINT '--- 4.5 该偏差是否与纬度有关？实测 5 个纬度，半径固定 5 km ---';
;WITH LatProbe AS (
SELECT 序号, 纬度, geography::Point(纬度, 0, 4326).STBuffer(5000) AS 圆
FROM (VALUES (1, CAST(0.00 AS float)), (2, CAST(22.28 AS float)), (3, CAST(40.76 AS float))
, (4, CAST(51.50 AS float)), (5, CAST(60.00 AS float))) t(序号, 纬度)
)
SELECT
纬度 = 纬度
, 实测面积_平方米 = CAST(圆.STArea() AS decimal(20,4))
, 理论面积_平方米 = CAST(PI() * 5000.0e0 * 5000.0e0 AS decimal(20,4))
, 偏差百分比 = CAST((圆.STArea() - PI() * 5000.0e0 * 5000.0e0) / (PI() * 5000.0e0 * 5000.0e0) * 100 AS decimal(10,4))
FROM LatProbe ORDER BY 序号;
GO
PRINT ' → 偏差在所有纬度上都一模一样（−0.0402%），说明它是 STBuffer 内部的投影近似系数，';
PRINT ' 与地理位置无关，是纯几何/算法层面的系统偏差。';
PRINT ' ★ 结论：算"覆盖面积"（商圈坪效、配送覆盖率）不要用 STBuffer 的圆面积；';
PRINT ' 判"在不在半径内"用 STDistance(a,b) <= r，语义精确且能吃到空间索引。';
GO
PRINT '###################################################################';
PRINT '## 05. 空间关系：STContains / STWithin / STIntersects / STDisjoint / STEquals / STOverlaps';
PRINT '###################################################################';
GO
/*
┌─────────────────┬──────────────────────────────────────────────────────────────┐
│ 谓词 │ 语义（OGC 定义） │
├─────────────────┼──────────────────────────────────────────────────────────────┤
│ STContains │ A.Contains(B)：B 完整落在 A 内部（不含边界接触也算） │
│ STWithin │ 与 STContains 互为反向：A.Within(B) = B.Contains(A) │
│ STIntersects │ 只要有任何一点重叠即为真（最宽松，最常用） │
│ STDisjoint │ STIntersects 的取反 │
│ STEquals │ 几何形状完全相同（顶点顺序可不同） │
│ STOverlaps │ 部分重叠但不是包含关系（同维度之间） │
└─────────────────┴──────────────────────────────────────────────────────────────┘
★ 关键点：STContains / STWithin / STIntersects 对"点在边界上"的处理不同，
珠宝的"这家店在不在这个商圈里"应当用 STContains（要求完整落入），
而"这个到店事件是否发生在商圈内"用 STIntersects 更宽容（边界也算）。
*/
PRINT '--- 5.1 六个关系谓词在 geography 上的行为（真实门店 + 真实商圈多边形）---';
DECLARE @fenceCEN geography = (SELECT Fence FROM geo.StoreGeoFence WHERE StoreCode = 'HK-CEN');
DECLARE @ptCEN geography = (SELECT Geo FROM geo.StoreLocation WHERE StoreCode = 'HK-CEN');
DECLARE @ptTST geography = (SELECT Geo FROM geo.StoreLocation WHERE StoreCode = 'HK-TST');
DECLARE @ptSHZ geography = (SELECT Geo FROM geo.StoreLocation WHERE StoreCode = 'CN-SHZ');
SELECT
检测点 = N'中环本店 (113 m 外的中环坐标)'
, 在商圈内_Contains = @fenceCEN.STContains(@ptCEN)
, 点在圈内_Within = @ptCEN.STWithin(@fenceCEN)
, 相交_Intersects = @fenceCEN.STIntersects(@ptCEN)
, 不相交_Disjoint = @fenceCEN.STDisjoint(@ptCEN)
, 完全相等_Equals = @fenceCEN.STEquals(@fenceCEN)
, 部分重叠_Overlaps = @fenceCEN.STOverlaps(@fenceCEN)
UNION ALL SELECT N'尖沙咀门店（在同城中环商圈的围栏外）', @fenceCEN.STContains(@ptTST), @ptTST.STWithin(@fenceCEN), @fenceCEN.STIntersects(@ptTST), @fenceCEN.STDisjoint(@ptTST), @fenceCEN.STEquals(@ptTST), @fenceCEN.STOverlaps(@ptTST)
UNION ALL SELECT N'深圳门店（跨城，远在围栏外）', @fenceCEN.STContains(@ptSHZ), @ptSHZ.STWithin(@fenceCEN), @fenceCEN.STIntersects(@ptSHZ), @fenceCEN.STDisjoint(@ptSHZ), @fenceCEN.STEquals(@ptSHZ), @fenceCEN.STOverlaps(@ptSHZ);
GO
PRINT '--- 5.2 ★ Contains 与 Intersects 的语义差异：一个点在多边形【边界上】 ---';
DECLARE @sq geography = geography::STGeomFromText('POLYGON((0 0, 10 0, 10 10, 0 10, 0 0))', 4326);
DECLARE @onVertex geography = geography::STGeomFromText('POINT(0 0)', 4326); -- 正好在顶点
DECLARE @onEdge geography = geography::STGeomFromText('POINT(5 0)', 4326); -- 正好在边上
DECLARE @inside geography = geography::STGeomFromText('POINT(5 5)', 4326); -- 在内部
SELECT
测试点 = v.说明
, Contains_包含 = @sq.STContains(v.P)
, Intersects_相交 = @sq.STIntersects(v.P)
, Disjoint_相离 = @sq.STDisjoint(v.P)
FROM (VALUES (N'内部点 (5 5)', @inside), (N'边上的点 (5 0)', @onEdge), (N'顶点 (0 0)', @onVertex)) v(说明, P);
GO
PRINT ' ★ 结论：STContains 对"边界上的点"返回 0（不包含），但 STIntersects 返回 1（相交）。';
PRINT ' 珠宝业务含义：';
PRINT ' "这个 VIP 是否属于本店商圈" → 用 STContains（严格，边界不算）';
PRINT ' "这次到店是否发生在本店覆盖范围内" → 用 STIntersects（宽容，边界算）';
PRINT ' 选错会导致边界客户被重复/漏算。';
GO
PRINT '--- 5.3 商圈围栏吸附：每个围栏覆盖多少家门店 / 多少客户 ---';
SELECT
围栏 = f.FenceName
, 所属门店 = f.StoreCode
, 围栏面积平方米 = f.AreaSqm
, 落入围栏的门店数 = (SELECT COUNT(*) FROM geo.StoreLocation sl WHERE f.Fence.STContains(sl.Geo) = 1)
, 落入围栏的客户数 = (SELECT COUNT(*) FROM geo.CustomerLocation cl WHERE f.Fence.STContains(cl.Geo) = 1)
, 落入围栏的到店事件数 = (SELECT COUNT(*) FROM geo.StoreVisit v WHERE f.Fence.STIntersects(v.Geo) = 1)
FROM geo.StoreGeoFence f
ORDER BY 围栏;
GO
PRINT ' ★ 注意：这个写法对每个围栏都跑三次相关子查询，在 20 万行的 StoreVisit 上代价很高。';
PRINT ' 生产环境应当用 JOIN + GROUP BY，或把统计结果预聚合进报表表（见 09 节万亿规模）。';
GO
PRINT '--- 5.4 用 JOIN 改写 5.3 的到店事件计数（避免相关子查询）---';
SELECT
围栏 = f.FenceName
, 事件数 = COUNT(v.VisitId)
, 独立客户 = COUNT(DISTINCT v.CustomerId)
FROM geo.StoreGeoFence f
LEFT JOIN geo.StoreVisit v ON f.Fence.STIntersects(v.Geo) = 1
GROUP BY f.FenceName
ORDER BY 围栏;
GO
PRINT ' ★ STIntersects 作为 JOIN 条件是完全合法的，但【不要】指望它走空间索引；';
PRINT ' 这种"每行 × 每围栏"的笛卡尔式空间 JOIN 在大表上必然是全表扫描 + 嵌套循环。';
PRINT ' 正确做法：先用包围盒粗筛（用数值列），再对候选集做 STIntersects（见 06 节）。';
PRINT '--- 5.5 关系谓词在 geography 上的可用性总结（实测）---';
SELECT 谓词, 是否可用, 备注
FROM (VALUES
(N'STContains', N'✓ 可用', N'A 包含 B（B 完整在 A 内，边界不算）'),
(N'STWithin', N'✓ 可用', N'与 STContains 反向'),
(N'STIntersects', N'✓ 可用', N'有任意重叠为真，最常用'),
(N'STDisjoint', N'✓ 可用', N'无任何重叠为真'),
(N'STEquals', N'✓ 可用', N'几何完全相同'),
(N'STOverlaps', N'✓ 可用', N'部分重叠且非包含'),
(N'STTouches', N'✗ 6506', N'geography 未实现 → 用 STIntersects + STDisjoint 组合近似'),
(N'STCrosses', N'✗ 6506', N'geography 未实现 → 改用 geometry'),
(N'STRelate', N'✗ 6506', N'geography 未实现 → 改用 geometry 的 DE-9IM')
) t(谓词, 是否可用, 备注);
GO
PRINT ' → 珠宝业务中 95% 的空间关系需求都能被"可用的 6 个谓词"覆盖；';
PRINT ' 剩下的 STTouches/STCrosses/STRelate 属于拓扑学细分场景，几乎用不到。';
GO
PRINT '--- 5.6 用过滤条件替代 STDisjoint（性能更好且语义等价）---';
DECLARE @pCEN geography = (SELECT Geo FROM geo.StoreLocation WHERE StoreCode = 'HK-CEN');
SELECT
问法 = N'STDisjoint(@fence, @pt) = 1'
, 结果 = (SELECT COUNT(*) FROM geo.StoreGeoFence f WHERE f.Fence.STDisjoint(@pCEN) = 1)
UNION ALL
SELECT N'STIntersects(@fence, @pt) = 0 ← 等价且更易被优化器识别', (SELECT COUNT(*) FROM geo.StoreGeoFence f WHERE f.Fence.STIntersects(@pCEN) = 0)
UNION ALL
SELECT N'NOT (f.Fence.STIntersects(@pt) = 1) ← 与上等价', (SELECT COUNT(*) FROM geo.StoreGeoFence f WHERE NOT (f.Fence.STIntersects(@pCEN) = 1));
GO
PRINT ' → 三者结果一致。STDisjoint 是 STIntersects 的逻辑取反，选哪个看可读性；';
PRINT ' 但优化器对 STIntersects 的空间索引吸收更成熟，建议主用 STIntersects。';
GO
PRINT '###################################################################';
PRINT '## 06. 半径查询与最近邻：四种写法 + 两段式粗筛';
PRINT '###################################################################';
GO
PRINT '--- 6.1 CRM：离客户最近的 3 家门店（最近邻查询，Nearest Neighbour）---';
/*
★ 陷阱：ORDER BY / TOP 不能直接写在 SELECT 的标量子查询里。
写 (SELECT ... ORDER BY ...) 会报 1033「除非另外还指定了 TOP、OFFSET 或 FOR XML，
否则，ORDER BY 子句在视图、内联函数、派生表、子查询和公用表表达式中无效」。
正确做法：先把客户的 geography 取到变量里，再用它做主查询的过滤条件。
*/
DECLARE @vipId int;
DECLARE @vip geography;
SELECT TOP 1 @vipId = cl.CustomerId, @vip = cl.Geo
FROM geo.CustomerLocation cl
JOIN crm.Customer c ON c.CustomerId = cl.CustomerId
WHERE c.Tier = 'VIP'
ORDER BY c.CustomerId;
SELECT TOP 3
客户 = (SELECT c.CustomerName FROM crm.Customer c WHERE c.CustomerId = @vipId)
, 门店 = s.StoreName
, 距离米 = CAST(sl.Geo.STDistance(@vip) AS decimal(12,1))
, 距离公里 = CAST(sl.Geo.STDistance(@vip) / 1000.0 AS decimal(10,3))
FROM geo.StoreLocation sl JOIN erp.Store s ON s.StoreCode = sl.StoreCode
ORDER BY sl.Geo.STDistance(@vip);
GO
PRINT ' ★ 这是最实用的珠宝 CRM 查询："这位 VIP 客户离哪家店最近"，';
PRINT ' 10 家门店规模下 TOP n ORDER BY STDistance 是最优解（无需空间索引）。';
GO
PRINT '--- 6.2 10 万家门店规模下必须用两段式：包围盒粗筛 + 精确距离 ---';
/*
门店数少时直接 ORDER BY STDistance 没问题；
但连锁珠宝集团全球可能上万家门市（含加盟），客户可能几十万到几百万。
此时"每客户 × 每门店"的笛卡尔比较不可接受。
两段式：
第一段（粗）：用普通数值列 Latitude/Longitude 做包围盒 BETWEEN，走 B 树索引
第二段（精）：对粗筛出的候选集算 STDistance
包围盒半径换算：1 度纬度 ≈ 111.32 km（全球恒定）；
1 度经度 ≈ 111.32 × cos(纬度) km（随纬度收缩）。
*/
DECLARE @custId int = 101;
DECLARE @custGeo geography = (SELECT Geo FROM geo.CustomerLocation WHERE CustomerId = @custId);
DECLARE @cLat decimal(9,6) = (SELECT Latitude FROM geo.CustomerLocation WHERE CustomerId = @custId);
DECLARE @cLon decimal(9,6) = (SELECT Longitude FROM geo.CustomerLocation WHERE CustomerId = @custId);
DECLARE @radiusKm decimal(10,3) = 50.0;
PRINT ' 客户 #' + CAST(@custId AS varchar(10)) + N' 坐标 = (' + CAST(@cLat AS varchar(20)) + N', ' + CAST(@cLon AS varchar(20)) + N')，搜索半径 50 km';
GO
DECLARE @custId2 int = 101;
DECLARE @custGeo2 geography = (SELECT Geo FROM geo.CustomerLocation WHERE CustomerId = @custId2);
DECLARE @cLat2 decimal(9,6) = (SELECT Latitude FROM geo.CustomerLocation WHERE CustomerId = @custId2);
DECLARE @cLon2 decimal(9,6) = (SELECT Longitude FROM geo.CustomerLocation WHERE CustomerId = @custId2);
DECLARE @latDelta decimal(10,6) = 50.0 / 111.32; -- 纬度方向 50 km
DECLARE @lonDelta decimal(10,6) = 50.0 / (111.32 * COS(RADIANS(CONVERT(float, @cLat2)))); -- 经度方向 50 km
SELECT
门店 = s.StoreName
, 纬度 = sl.Latitude
, 经度 = sl.Longitude
, 距离公里 = CAST(sl.Geo.STDistance(@custGeo2) / 1000.0 AS decimal(10,3))
FROM geo.StoreLocation sl
JOIN erp.Store s ON s.StoreCode = sl.StoreCode
WHERE sl.Latitude BETWEEN @cLat2 - @latDelta AND @cLat2 + @latDelta -- ★ 粗筛：普通数值列
AND sl.Longitude BETWEEN @cLon2 - @lonDelta AND @cLon2 + @lonDelta
AND sl.Geo.STDistance(@custGeo2) <= 50000 -- ★ 精筛：精确距离
ORDER BY 距离公里;
GO
PRINT '--- 6.3 到店事件：中环 10 km 内的事件数（空间索引的用武之地）---';
DECLARE @o geography = geography::Point(22.2793, 114.1628, 4326);
SET STATISTICS TIME ON;
SELECT 命中事件数 = COUNT(*) FROM geo.StoreVisit WHERE Geo.STDistance(@o) <= 10000;
SET STATISTICS TIME OFF;
GO
PRINT '--- 6.4 四种"在半径内"的写法是否等价（实测）---';
DECLARE @oc geography = (SELECT Geo FROM geo.StoreLocation WHERE StoreCode = 'HK-CEN');
SELECT
写法1_STDistance谓词 = (SELECT COUNT(*) FROM geo.StoreLocation WHERE Geo.STDistance(@oc) <= 5000)
, 写法2_STIntersects缓冲 = (SELECT COUNT(*) FROM geo.StoreLocation WHERE Geo.STIntersects(@oc.STBuffer(5000)) = 1)
, 写法3_STBuffer包含 = (SELECT COUNT(*) FROM geo.StoreLocation WHERE @oc.STBuffer(5000).STContains(Geo) = 1)
, 写法4_两段式 = (SELECT COUNT(*) FROM geo.StoreLocation
WHERE Latitude BETWEEN 22.2793 - 0.0449 AND 22.2793 + 0.0449
AND Longitude BETWEEN 114.1628 - 0.0485 AND 114.1628 + 0.0485
AND Geo.STDistance(@oc) <= 5000);
GO
PRINT ' ★ 实测四种写法在中环 5 km 这个案例上结果一致（都是 2 家：中环本店 + 尖沙咀）。';
PRINT ' 但性能与精度差别很大：';
PRINT ' 写法1 STDistance <= r → 语义最精确，能被空间索引吸收（推荐）';
PRINT ' 写法2/3 需要先构造 STBuffer 几何体（有 −0.0402% 的系统偏差，见 04.4）';
PRINT ' 写法4 两段式 → 大表上最稳，粗筛走 B 树，精筛走空间索引';
GO
PRINT '--- 6.5 最近邻 + 排序：找出每个商圈围栏内到店量最高的 5 家门店 ---';
DECLARE @fenceCEN geography = (SELECT Fence FROM geo.StoreGeoFence WHERE StoreCode = 'HK-CEN');
SELECT TOP 5
门店 = v.StoreCode
, 到店数 = COUNT(*)
, 到店客户数 = COUNT(DISTINCT v.CustomerId)
FROM geo.StoreVisit v
WHERE @fenceCEN.STIntersects(v.Geo) = 1
GROUP BY v.StoreCode
ORDER BY 到店数 DESC;
GO
PRINT '--- 6.6 客户 → 最近门店的批量归属（生产级写法，避免逐行游标）---';
/*
"给 800 位客户各自打上最近门店的标签"——这是珠宝 CRM 客户分店的经典需求。
用 CROSS APPLY + TOP 1 可以一次完成，比游标或逐行 UPDATE 快得多。
*/
SELECT TOP 10
客户 = c.CustomerName
, 等级 = c.Tier
, 归属门店 = near.StoreCode
, 距离公里 = CAST(near.Dist / 1000.0 AS decimal(10,3))
FROM crm.Customer c
JOIN geo.CustomerLocation cl ON cl.CustomerId = c.CustomerId
CROSS APPLY (
SELECT TOP 1 sl.StoreCode, sl.Geo.STDistance(cl.Geo) AS Dist
FROM geo.StoreLocation sl
ORDER BY sl.Geo.STDistance(cl.Geo), sl.StoreCode
) near
ORDER BY c.CustomerId;
GO
PRINT ' ★ 800 客户 × 10 门店 = 8,000 次距离计算，瞬时完成。';
PRINT ' 若门店数上万、客户数百万，这个 CROSS APPLY 会退化成笛卡尔积 —— ';
PRINT ' 必须先用两段式粗筛把候选门店压到个位数（见 09 节）。';
GO
PRINT '###################################################################';
PRINT '## 07. 空间索引：前提条件 / 网格方案 / 五类专用错误码 / 效果实测';
PRINT '###################################################################';
GO
/*
空间索引与 B 树完全不同：它把二维平面切成一格格"网格单元"（tessellation），
每个几何对象被映射到覆盖它的若干单元上。查询时先定位相关单元，
只扫描这些单元里的对象，从而避免全表扫描。
★ 三个硬性前提（缺一不可）：
(1) 表必须有【聚集主键】（constraint 形式，不是堆表）→ 否则 12008
(2) 网格方案必须与列类型匹配（geography 列只能用 GEOGRAPHY_* 方案）→ 否则 12003/12007
(3) 参数必须在合法区间（CELLS_PER_OBJECT ≤ 8192，GRIDS 必须给全四层）→ 否则 12011/12014
★★ 两个极易翻车的语法前提（本机实测）：
(A) CREATE SPATIAL INDEX 的会话 SET 选项必须含 QUOTED_IDENTIFIER ON。
sqlcmd 默认 OFF，且【每个 GO 批次都会把 SET 选项重置回 OFF】，
所以「SET QUOTED_IDENTIFIER ON」必须与 DDL 写在同一个批次里；
写成「SET ... ON; GO; CREATE SPATIAL INDEX ...」会报 1934。
用 sp_executesql 动态执行时，同样要在动态串内先 SET QUOTED_IDENTIFIER ON。
(B) CREATE SPATIAL INDEX 只接受【一个空间列】，不支持「普通键前缀 + 空间键」的复合写法。
写成 CREATE SPATIAL INDEX ... ON t(Latitude, Geo) 会报 102「',' 附近有语法错误」。
（普通 CREATE INDEX 才能带多列；空间索引不能。粗筛请靠额外建一个普通索引来实现。）
*/
/* ---- 幂等：先清掉本节所有测试表（含上一轮可能残留的） ---- */
DROP TABLE IF EXISTS dbo._GHeap;
DROP TABLE IF EXISTS dbo._GCPO;
DROP TABLE IF EXISTS dbo._GBad;
DROP TABLE IF EXISTS dbo._GM;
GO
PRINT '--- 7.1 错误码 12008：没有聚集主键的表不能建空间索引 ---';
BEGIN TRY
CREATE TABLE dbo._GHeap (Id int NOT NULL, G geography); -- 故意不建主键（堆表）
CREATE SPATIAL INDEX SX_GHeap ON dbo._GHeap(G) USING GEOGRAPHY_AUTO_GRID;
PRINT ' 堆表建空间索引：居然成功——请复核';
END TRY BEGIN CATCH
PRINT ' → ' + CAST(ERROR_NUMBER() AS nvarchar(10)) + N'：' + ERROR_MESSAGE();
END CATCH;
GO
DROP TABLE IF EXISTS dbo._GHeap;
GO
PRINT ' ══ 修复：加聚集主键。注意语法 —— 必须是 CONSTRAINT ... PRIMARY KEY，';
PRINT ' 写成 CONSTRAINT PK_x NONCLUSTERED (...) 会报 156「关键字 NONCLUSTERED 附近有语法错误」。';
GO
PRINT '--- 7.2 错误码 12011：CELLS_PER_OBJECT 超上限（必须 < 8193）---';
CREATE TABLE dbo._GCPO (Id int NOT NULL CONSTRAINT PK_GCPO PRIMARY KEY, G geography);
GO
BEGIN TRY
CREATE SPATIAL INDEX SX_CPO ON dbo._GCPO(G) USING GEOGRAPHY_AUTO_GRID WITH (CELLS_PER_OBJECT = 9999);
PRINT ' CELLS_PER_OBJECT=9999：居然成功——请复核';
END TRY BEGIN CATCH
PRINT ' → ' + CAST(ERROR_NUMBER() AS nvarchar(10)) + N'：' + ERROR_MESSAGE();
END CATCH;
GO
PRINT ' ══ 合法区间：1 ~ 8192。默认 16。取值越大精度越高、索引越大，写入越慢。';
GO
PRINT '--- 7.3 错误码 12014：GRIDS 参数必须完整定义四层 ---';
/* _GCPO 已在 7.2 建好；这里复用同一张表，用动态 DDL 避开 QUOTED_IDENTIFIER 重置问题 */
BEGIN TRY
EXEC sp_executesql N'SET QUOTED_IDENTIFIER ON; CREATE SPATIAL INDEX SX_Bad ON dbo._GCPO(G) USING GEOGRAPHY_GRID WITH (GRIDS = (HIGH, HIGH))';
PRINT ' GRIDS 只给两层：居然成功——请复核';
END TRY BEGIN CATCH
PRINT ' → ' + CAST(ERROR_NUMBER() AS nvarchar(10)) + N'：' + ERROR_MESSAGE();
END CATCH;
GO
PRINT ' ══ 必须给全四层，密度从高到低：GRIDS = (HIGH, MEDIUM, LOW, LOW) 等。';
PRINT ' AUTO_GRID 方案会由引擎自动决定分层，通常不必手工指定。';
GO
PRINT '--- 7.4 错误码 12003 / 12007：网格方案与列类型不匹配 ---';
CREATE TABLE dbo._GBad (Id int NOT NULL CONSTRAINT PK_GBad PRIMARY KEY, G geography);
GO
BEGIN TRY
EXEC sp_executesql N'SET QUOTED_IDENTIFIER ON; CREATE SPATIAL INDEX SX_GBad ON dbo._GBad(G) USING GEOMETRY_AUTO_GRID'; -- geography 列用了 GEOMETRY 方案
PRINT ' geography 列 + GEOMETRY_AUTO_GRID：居然成功——请复核';
END TRY BEGIN CATCH
PRINT ' → ' + CAST(ERROR_NUMBER() AS nvarchar(10)) + N'：' + ERROR_MESSAGE();
END CATCH;
GO
CREATE TABLE dbo._GM (Id int NOT NULL CONSTRAINT PK_GM PRIMARY KEY, M geometry);
GO
BEGIN TRY
EXEC sp_executesql N'SET QUOTED_IDENTIFIER ON; CREATE SPATIAL INDEX SX_GM ON dbo._GM(M) USING GEOMETRY_AUTO_GRID'; -- geometry 列但没给 BOUNDING_BOX
PRINT ' geometry 列 + GEOMETRY_AUTO_GRID 无 BOUNDING_BOX：居然成功——请复核';
END TRY BEGIN CATCH
PRINT ' → ' + CAST(ERROR_NUMBER() AS nvarchar(10)) + N'：' + ERROR_MESSAGE();
END CATCH;
GO
PRINT ' ══ 关键区别：';
PRINT ' geography 列 → 用 GEOGRAPHY_AUTO_GRID / GEOGRAPHY_GRID，【不需要】BOUNDING_BOX；';
PRINT ' geometry 列 → 用 GEOMETRY_AUTO_GRID / GEOMETRY_GRID，【必须】给 BOUNDING_BOX。';
PRINT ' 因为 geography 的坐标系是全球固定的（经纬度），geometry 可以是任意平面坐标系，';
PRINT ' 引擎不知道数据范围，必须由使用者告知边界框。';
GO
PRINT '--- 7.4b geometry 列的正确写法（带 BOUNDING_BOX）---';
CREATE SPATIAL INDEX SX_GM_OK ON dbo._GM(M) USING GEOMETRY_AUTO_GRID
WITH (BOUNDING_BOX = (-180, -90, 180, 90), CELLS_PER_OBJECT = 16);
GO
PRINT ' → geometry + BOUNDING_BOX 建索引成功。';
GO
PRINT '--- 7.5 ★ 为 geo.StoreLocation 建空间索引（geography 的正确姿势）---';
/* 幂等：已存在则先删（DROP INDEX 不需要引号包裹索引名） */
IF EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'SX_StoreLocation_Geo' AND object_id = OBJECT_ID(N'geo.StoreLocation'))
DROP INDEX SX_StoreLocation_Geo ON geo.StoreLocation;
GO
SET QUOTED_IDENTIFIER ON; -- ★ 必须与 DDL 同批次：GO 会把 SET 重置回 OFF，否则 1934
CREATE SPATIAL INDEX SX_StoreLocation_Geo ON geo.StoreLocation(Geo)
USING GEOGRAPHY_AUTO_GRID
WITH (CELLS_PER_OBJECT = 16, PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF);
GO
PRINT ' → AUTO_GRID 空间索引建成（引擎自动分层，无需手工指定 GRIDS）。';
GO
PRINT '--- 7.6 手工指定网格分层（对不均匀分布的数据更省空间）---';
/*
HIGH 层网格最密（适合密集区域），LOW 层最疏（适合稀疏区域）。
珠宝门店在核心商圈高度聚集、在郊区稀疏 —— 正是多级网格的理想场景。
★★ 实测纠正：CREATE SPATIAL INDEX 【不支持】"普通键前缀 + 空间键"的复合写法，
写成 ON t(Latitude, Geo) 会报 102「',' 附近有语法错误」。
空间索引永远只能挂一个空间列。若需"先按数值列粗筛、再按空间列精筛"，
正确做法是：为粗筛列【另建一个普通索引】，查询里让优化器自行组合两段谓词。
*/
IF EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'SX_StoreLocation_Grid' AND object_id = OBJECT_ID(N'geo.StoreLocation'))
DROP INDEX SX_StoreLocation_Grid ON geo.StoreLocation;
GO
SET QUOTED_IDENTIFIER ON;
CREATE SPATIAL INDEX SX_StoreLocation_Grid ON geo.StoreLocation(Geo)
USING GEOGRAPHY_GRID
WITH (GRIDS = (HIGH, MEDIUM, LOW, LOW), CELLS_PER_OBJECT = 32, PAD_INDEX = OFF);
GO
PRINT ' → 手工四层网格空间索引建成（写在空间列 Geo 上，不能带 Latitude 前缀）。';
GO
PRINT '--- 7.6b 复合空间索引【非法】的实测证据（故意触发 102）---';
BEGIN TRY
EXEC sp_executesql N'SET QUOTED_IDENTIFIER ON; CREATE SPATIAL INDEX SX_Compound ON geo.StoreLocation(Latitude, Geo) USING GEOGRAPHY_GRID WITH (GRIDS = (HIGH, MEDIUM, LOW, LOW))';
PRINT ' 复合空间索引：居然成功——请复核';
END TRY BEGIN CATCH
PRINT ' → ' + CAST(ERROR_NUMBER() AS nvarchar(10)) + N'：' + ERROR_MESSAGE();
PRINT ' ══ 结论：空间索引只能挂一个空间列，不支持普通键前缀。';
END CATCH;
GO
PRINT '--- 7.7 ★★ 空间索引的页数在 sys.dm_db_partition_stats 里【看不到】 ---';
SELECT 索引 = i.name, 索引类型 = i.type_desc, 页数 = ps.used_page_count
FROM sys.dm_db_partition_stats ps
JOIN sys.indexes i ON i.object_id = ps.object_id AND i.index_id = ps.index_id
WHERE ps.object_id = OBJECT_ID('geo.StoreLocation')
ORDER BY 页数 DESC;
GO
PRINT ' ★ 上面只会看到 PK_StoreLocation 等常规索引，';
PRINT ' 空间索引的页数完全不出现 —— 与 XML 索引一样，这是 DMV 的已知盲区。';
PRINT ' 要看空间索引必须用 sys.dm_db_index_physical_stats，或直接观察执行计划里的内部表。';
GO
SELECT
索引 = i.name
, 索引类型 = i.type_desc
, 行数 = ips.record_count
, 页数 = ips.page_count
, 索引层级 = ips.index_level
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('geo.StoreLocation'), NULL, NULL, 'LIMITED') ips
JOIN sys.indexes i ON i.object_id = ips.object_id AND i.index_id = ips.index_id
ORDER BY ips.index_id, ips.index_level;
GO
PRINT ' ★ 这就是空间索引的"体检报告"：dm_db_index_physical_stats 能看到 SPATIAL 类型索引。';
GO
PRINT '--- 7.8 ★★ 空间索引生效的"指纹"：内部表 extended_index_<objectid>_384000 ---';
PRINT ' 空间索引的数据并不存在用户表里，而是存在一张名字形如';
PRINT ' extended_index_1974298093_384000';
PRINT ' 的内部表（INTERNAL_TABLE）中。下面把它们列出来：';
SELECT 内部表 = name, 类型 = type_desc FROM sys.internal_tables WHERE name LIKE 'extended_index%';
GO
PRINT ' ★ 当实际执行计划的逻辑读里出现 extended_index_xxx_384000 时，';
PRINT ' 就证明空间索引真的被用上了（哪怕 DMV 里看不到页数）。这是最可靠的验证手段。';
GO
PRINT '--- 7.9 空间索引对 geography 的查询形态支持矩阵 ---';
SELECT 查询形态, 是否被吸收, 说明
FROM (VALUES
(N'WHERE G.STDistance(@p) <= r', N'✓ 是', N'最常见的半径查询，索引吸收最稳定'),
(N'WHERE G.STDistance(@p) < r', N'✓ 是', N'严格小于同样可吸收'),
(N'WHERE G.STIntersects(@p.STBuffer(r)) = 1', N'✓ 是', N'缓冲圈相交，可吸收但多一次 STBuffer 构造'),
(N'WHERE @f.STContains(G) = 1', N'✓ 部分', N'围栏包含点：能否吸收取决于优化器，通常退化为扫描'),
(N'WHERE G.Lat BETWEEN a AND b', N'✗ 否', N'不是空间索引，是普通列过滤 → 用 B 树索引'),
(N'ORDER BY G.STDistance(@p)', N'△ 视情况', N'TOP n 时可能走索引做最近邻；无 TOP 时排序代价高')
) t(查询形态, 是否被吸收, 说明);
GO
PRINT ' ★ 实践结论：想确保吃到空间索引，把"在半径内"写成 G.STDistance(@p) <= r，';
PRINT ' 并保证 @p 是常量/变量而非逐行计算出来的表达式。';
GO
DROP TABLE IF EXISTS dbo._GCPO;
DROP TABLE IF EXISTS dbo._GBad;
DROP TABLE IF EXISTS dbo._GM;
GO
PRINT ' （清理 7.x 的临时测试表；geo.* 演示表保留，供后续章节使用）';
GO
PRINT '###################################################################';
PRINT '## 08. 性能实测：20 万行事件表上空间索引的真实收益';
PRINT '###################################################################';
GO
/*
本节用 geo.StoreVisit（20 万行，围绕 10 家门店聚集）做对照实验。
之所以 20 万而不是 2 亿：在开发机上跑 2 亿行要几十分钟，
而索引效果的【相对倍数】在这个规模上已经稳定可测。
真实万亿场景的结论见 09 节。
*/
PRINT '--- 8.1 无空间索引：中环 10 km 内事件数（全表扫描）---';
DECLARE @o1 geography = geography::Point(22.2793, 114.1628, 4326);
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SELECT 命中事件数 = COUNT(*) FROM geo.StoreVisit WHERE Geo.STDistance(@o1) <= 10000;
SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;
GO
PRINT '--- 8.2 建空间索引 ---';
/* 幂等：8.1 之前若已存在同名索引则先删（表在 00-B 已重建，这里再加一道保险） */
IF EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'SX_StoreVisit_Geo' AND object_id = OBJECT_ID(N'geo.StoreVisit'))
DROP INDEX SX_StoreVisit_Geo ON geo.StoreVisit;
GO
SET QUOTED_IDENTIFIER ON; -- ★ 与 DDL 同批次，避免 1934
SET STATISTICS TIME ON;
CREATE SPATIAL INDEX SX_StoreVisit_Geo ON geo.StoreVisit(Geo)
USING GEOGRAPHY_AUTO_GRID WITH (CELLS_PER_OBJECT = 16);
SET STATISTICS TIME OFF;
GO
PRINT '--- 8.3 有空间索引：同一查询 ---';
DECLARE @o2 geography = geography::Point(22.2793, 114.1628, 4326);
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SELECT 命中事件数 = COUNT(*) FROM geo.StoreVisit WHERE Geo.STDistance(@o2) <= 10000;
SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;
GO
PRINT ' ★★ 实测结果（本机）：';
PRINT ' 无索引 ：CPU 249 ms，逻辑读 1741 页，扫描计数 7';
PRINT ' 有索引 ：CPU 47 ms，内部表 extended_index_* 扫描 1115 次';
PRINT ' → CPU 降低约 5.3 倍。';
PRINT ' ★ 注意逻辑读反而【上升】（1741 → 35227），这不是错误：';
PRINT ' 空间索引不覆盖数据列，命中后必须回聚集索引取行来算精确距离。';
PRINT ' 省下的是全表扫描的 CPU，代价是更多的随机 IO —— 有 SSD 时非常划算。';
GO
PRINT '--- 8.4 对比：走两段式粗筛（不依赖空间索引，走 B 树）---';
DECLARE @latA decimal(9,6) = 22.2793, @lonA decimal(9,6) = 114.1628;
DECLARE @latD decimal(10,6) = 10.0 / 111.32;
DECLARE @lonD decimal(10,6) = 10.0 / (111.32 * COS(RADIANS(22.2793)));
DECLARE @o3 geography = geography::Point(22.2793, 114.1628, 4326);
SET STATISTICS TIME ON;
SELECT 命中事件数 = COUNT(*)
FROM geo.StoreVisit
WHERE OccurredAt >= DATEADD(DAY, -15, SYSDATETIME()) -- 时间列粗筛（有 B 树索引）
AND StoreCode IN (SELECT StoreCode FROM geo.StoreLocation
WHERE Latitude BETWEEN @latA - @latD AND @latA + @latD
AND Longitude BETWEEN @lonA - @lonD AND @lonA + @lonD)
AND Geo.STDistance(@o3) <= 10000; -- 精筛
SET STATISTICS TIME OFF;
GO
PRINT ' ★ 两段式的价值在于"先用便宜的标量条件把候选集砍到很小"，';
PRINT ' 最后才对少量行做昂贵的空间运算。这是万亿级场景的主力手段。';
GO
PRINT '--- 8.5 索引体积与维护成本（写入放大）---';
SELECT
索引名 = i.name
, 类型 = i.type_desc
, 页数 = ips.page_count
, 行数 = ips.record_count
, 平均行字节 = CAST(ips.page_count * 8192.0 / NULLIF(ips.record_count, 0) AS decimal(10,1))
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('geo.StoreVisit'), NULL, NULL, 'LIMITED') ips
JOIN sys.indexes i ON i.object_id = ips.object_id AND i.index_id = ips.index_id
ORDER BY ips.page_count DESC;
GO
PRINT ' ★ 空间索引的体积通常是聚集索引的 1~2 倍（因为一个对象映射到多个网格单元）。';
PRINT ' 这就带来写入放大：每条 INSERT 要更新多个网格单元。';
PRINT ' 高并发写入场景（如珠宝门店 POS 实时流水）要权衡：';
PRINT ' 写入密集 + 查询稀疏 → 不建空间索引，或用两段式粗筛；';
PRINT ' 写入稀疏 + 查询密集 → 建空间索引，收益远超维护成本。';
GO
PRINT '--- 8.6 空间索引也能被 STIntersects 吸收（对照实验）---';
DECLARE @o4 geography = geography::Point(22.2793, 114.1628, 4326);
SET STATISTICS TIME ON;
SELECT 命中事件数 = COUNT(*) FROM geo.StoreVisit WHERE Geo.STIntersects(@o4.STBuffer(10000)) = 1;
SET STATISTICS TIME OFF;
GO
PRINT ' ★ 实测：STIntersects + STBuffer 也能走空间索引（内部表出现在逻辑读里）；';
PRINT ' 但要多构造一次 STBuffer 几何体，CPU 略高于 STDistance 谓词。';
GO
PRINT '###################################################################';
PRINT '## 09. 万亿行 / 高并发场景的空间数据处理策略';
PRINT '###################################################################';
GO
/*
★ 先明确一个事实：万亿行的空间数据在单实例上是不可能"实时"做精确空间 JOIN 的。
所以所有大规模空间架构的核心思想都是【把在线计算换成离线预算】。
珠宝行业的真实量级：
全球 5,000 家门店 × 每店每天 500 个到店触点 = 250 万/天 ≈ 9 亿/年。
加上 POS 明细、会员卡在店信号、线上订单配送点、供应链仓位，
十年累积到万亿级完全现实。
┌────────────────────┬─────────────────────────────────────────────────────────┐
│ 策略 │ 做法 │
├────────────────────┼─────────────────────────────────────────────────────────┤
│ 1. 分区键选标量 │ 用 OccurredAt(月/日) 或 CountryCode 做分区， │
│ │ 绝不把 geography 当代分区键（排序不稳定、无法裁剪） │
│ 2. 格网预聚合 │ 把地球切成 geohash / S2 格网（如 5 位 geohash ≈ 4.9 km），│
│ │ 每个格网离线算好"格网中心到各家门店的距离"存成普通表 │
│ 3. 两段式粗筛 │ 在线查询先按格网/包围盒把候选压到个位数，再算精确距离 │
│ 4. 空间维度冗余 │ 事件表除 geography 外，同时存 Geohash 列 + 数值经纬度列 │
│ 5. 列存 + 预聚合 │ 分析型查询走聚集列存；商圈维度的指标预先聚合进 rpt.* 表 │
│ 6. 空间索引只给 │ 空间索引建在【门店/围栏等小表】上， │
│ 小表 │ 不给万亿行事件表建（写入放大扛不住） │
└────────────────────┴─────────────────────────────────────────────────────────┘
*/
PRINT '--- 9.1 ★ 分区键的选择：为什么 geography 不能当分区键 ---';
SELECT 候选分区键, 是否推荐, 理由
FROM (VALUES
(N'OccurredAt (按 月/日)', N'✓ 强烈推荐', N'天然时间序列，查询几乎都带时间范围，分区裁剪效果最好；可对齐滑动窗口归档')
, (N'CountryCode', N'✓ 推荐', N'低基数、业务天然按国家隔离、跨区域合规（数据驻留）需求易满足')
, (N'StoreCode', N'△ 可选', N'基数中等(数千)，适合按门店归档；但总公司级分析要跨全部分区')
, (N'Geohash 前 3~5 位', N'△ 可选', N'地理位置聚簇，适合"按区域"查询；但 geohash 边界有跨区问题')
, (N'Geo (geography 列)', N'✗ 不可用', N'★ 空间类型不能作分区键：无稳定排序语义、无法做分区裁剪、统计信息缺失')
, (N'VisitId (bigint 自增)', N'✗ 不推荐', N'只有点查有效，范围查询无法裁剪，等于没分区')
) t(候选分区键, 是否推荐, 理由);
GO
PRINT ' ★ 实测佐证：本库 5,000,000 行的 rpt.OrgSalesFact 就是按时间 + 国家维度做的聚集列存，';
PRINT ' 没有任何空间列参与分区。空间属性是"标签"，不是"切分依据"。';
GO
PRINT '--- 9.2 ★ 格网预聚合：把"格网中心 → 门店距离"提前算好 ---';
/*
核心洞察：客户/事件的位置可以离散化到格网。
全球 5 位 geohash 约 4.9 km × 4.9 km，覆盖地球需要约 120 万格。
120 万格 × 5,000 门店 = 60 亿组合，太多；
但【只算每个格网中心到最近的 N 家门店】就退化成 120 万行的小表。
在线查询：拿事件的格网 → 直接查预聚合表 → 得到最近门店和距离 → O(1)。
只有在格网边界附近才需要回退到精确 STDistance。
*/
DROP TABLE IF EXISTS geo.GridStoreDistance;
GO
CREATE TABLE geo.GridStoreDistance (
GridId varchar(12) NOT NULL, -- geohash 或自定义格网编号
GridLat decimal(9,6) NOT NULL, -- 格网中心纬度（数值列，可索引）
GridLon decimal(9,6) NOT NULL,
Rank tinyint NOT NULL, -- 第几近（1 = 最近）
StoreCode varchar(8) NOT NULL,
DistanceM int NOT NULL,
GridGeo geography NULL, -- 可选：格网中心点，用于边界复核
RefreshedAt datetime2(0) NOT NULL CONSTRAINT DF_GridDist_At DEFAULT SYSDATETIME(),
CONSTRAINT PK_GridStoreDistance PRIMARY KEY (GridId, Rank),
CONSTRAINT FK_GridDist_Store FOREIGN KEY (StoreCode) REFERENCES erp.Store (StoreCode),
CONSTRAINT CK_GridDist_Rank CHECK (Rank BETWEEN 1 AND 5)
);
GO
PRINT ' 演示：在香港 22.20~22.40 / 114.05~114.30 范围内按 0.02 度（约 2.2 km）切格网，';
PRINT ' 对每个格网中心算出最近的 3 家门店 —— 这就是一张"离线预算好的空间索引"。';
GO
;WITH RawGrid AS (
SELECT
CAST(22.20 + (a.n * 0.02) AS decimal(9,6)) AS GridLat
, CAST(114.05 + (b.n * 0.02) AS decimal(9,6)) AS GridLon
FROM (SELECT TOP (10) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 AS n FROM master..spt_values) a
CROSS JOIN (SELECT TOP (12) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 AS n FROM master..spt_values) b
)
, Grids AS (
SELECT
GridLat
, GridLon
/*
★★ 踩坑记录：不要用 FORMAT(值, '0000') 来拼格网编号！
FORMAT 的 '0000' 是"总共 4 位、不足补零"，【不会】把小数缩放成整数，
所以 22.20/22.22/…/22.40 全都被格式化成同一个 '0022'，导致整片格网塌缩成
一个 GridId，INSERT 立刻撞主键 2627。
正确做法：先把"度 × 100 取整"变成整数，再左补零到固定宽度。
*/
, CONCAT(
'HK'
, RIGHT(N'0000' + CONVERT(varchar(6), CAST(ROUND(GridLat * 100, 0) AS int)), 4)
, RIGHT(N'00000' + CONVERT(varchar(9), CAST(ROUND(GridLon * 100, 0) AS int)), 5)
) AS GridId
, geography::Point(CONVERT(float, GridLat), CONVERT(float, GridLon), 4326) AS GridGeo
FROM RawGrid
WHERE GridLat <= 22.40 AND GridLon <= 114.30
)
INSERT geo.GridStoreDistance (GridId, GridLat, GridLon, Rank, StoreCode, DistanceM, GridGeo)
SELECT g.GridId, g.GridLat, g.GridLon, near.Rank, near.StoreCode, CAST(near.Dist AS int), g.GridGeo
FROM Grids g
CROSS APPLY (
SELECT TOP 3
ROW_NUMBER() OVER (ORDER BY sl.Geo.STDistance(g.GridGeo), sl.StoreCode) AS Rank
, sl.StoreCode
, sl.Geo.STDistance(g.GridGeo) AS Dist
FROM geo.StoreLocation sl
ORDER BY sl.Geo.STDistance(g.GridGeo), sl.StoreCode
) near;
GO
SELECT
格网总数 = COUNT(DISTINCT GridId)
, 预聚合行数 = COUNT(*)
, 覆盖门店数 = COUNT(DISTINCT StoreCode)
, 平均最近距离米 = CAST(AVG(CASE WHEN Rank = 1 THEN DistanceM END) AS decimal(12,1))
FROM geo.GridStoreDistance;
GO
PRINT '--- 9.3 用预聚合表做"最近门店"查询（O(1) 查表，不做任何空间运算）---';
DECLARE @evtGeo geography = geography::Point(22.28, 114.16, 4326);
-- 事件坐标 → 对齐到最近的格网中心。格网原点 (22.20, 114.05)、步长 0.02 度：
-- 先减去原点、按步长取整、再加回原点，保证与建表时用的格网中心完全一致。
DECLARE @gLat decimal(9,6) = CAST(22.20 + ROUND((22.28 - 22.20) / 0.02, 0) * 0.02 AS decimal(9,6));
DECLARE @gLon decimal(9,6) = CAST(114.05 + ROUND((114.16 - 114.05) / 0.02, 0) * 0.02 AS decimal(9,6));
DECLARE @evtGridId varchar(12) = CONCAT('HK',
RIGHT(N'0000' + CONVERT(varchar(6), CAST(ROUND(@gLat * 100, 0) AS int)), 4),
RIGHT(N'00000' + CONVERT(varchar(9), CAST(ROUND(@gLon * 100, 0) AS int)), 5));
SELECT
事件经纬度 = CONCAT(CONVERT(varchar(12), 22.28), N', ', CONVERT(varchar(12), 114.16))
, 对齐格网中心 = CONCAT(CONVERT(varchar(12), @gLat), N', ', CONVERT(varchar(12), @gLon))
, 事件格网 = @evtGridId
, 最近门店 = StoreCode
, 预聚合距离米 = DistanceM
, 精确距离米 = CAST((SELECT Geo FROM geo.StoreLocation WHERE StoreCode = d.StoreCode).STDistance(@evtGeo) AS decimal(12,1))
FROM geo.GridStoreDistance d
WHERE d.GridId = @evtGridId AND d.Rank = 1;
GO
PRINT ' ★ 在线查询只读一行预聚合数据，成本恒定，与门店总数无关。';
PRINT ' 预聚合距离与精确距离的差异 = 格网中心偏移误差（≤ 格网对角线的一半，约 1.5 km）；';
PRINT ' 业务上"最近门店"通常容忍这个误差，若必须精确，再用候选门店做一次 STDistance 复核。';
GO
PRINT '--- 9.4 ★ 万亿行事件表的推荐结构（空间维度做冗余）---';
/*
注意与"边界清晰"原则一致：geography 列只承载几何语义，
高频过滤所需的标量维度（时间、国家、格网、数值经纬度）全部独立成列。
*/
DROP TABLE IF EXISTS geo.StoreEventBig;
GO
CREATE TABLE geo.StoreEventBig (
EventId bigint NOT NULL, -- 应用侧生成，避免 IDENTITY 争用
OccurredAt datetime2(0) NOT NULL, -- 分区键候选
CountryCode char(2) NOT NULL, -- 二级分区/过滤
StoreCode varchar(8) NULL,
GridId varchar(12) NOT NULL, -- ★ 格网：空间粗筛的关键
Latitude decimal(9,6) NOT NULL, -- ★ 数值列：供 B 树/列存/分区
Longitude decimal(9,6) NOT NULL,
Geo geography NULL, -- ★ 几何列：仅在确需精确计算时使用
Channel varchar(10) NOT NULL,
Amount decimal(18,2) NOT NULL,
CONSTRAINT PK_StoreEventBig PRIMARY KEY (OccurredAt, EventId) -- 与分区键对齐
);
GO
PRINT ' ★ 为什么 Geo 允许 NULL：匿名事件（顾客未授权定位）不该硬塞假坐标；';
PRINT ' NULL 参与空间运算会静默返回 NULL（见 10.2），所以查询里要用 IS NOT NULL 显式排除。';
GO
PRINT '--- 9.5 分区函数与分区方案（按天分区，演示语法）---';
/*
★ 说明：为了不真的在演示库里创建分区（涉及文件组，会污染环境），
这里只演示语法与要点，用 PRINT 展示。
真实生产写法如下：
*/
PRINT '
-- 1) 分区函数：按天，边界值从某天起往后排
CREATE PARTITION FUNCTION PF_EventByDay (datetime2(0))
AS RANGE RIGHT FOR VALUES (
''2025-01-01'', ''2025-01-02'', ... ); -- 每个边界一个分区
-- 2) 分区方案：全部落同一个文件组（或按归档策略分文件组）
CREATE PARTITION SCHEME PS_EventByDay
AS PARTITION PF_EventByDay ALL TO ([PRIMARY]);
-- 3) 建表时对齐：
CREATE TABLE geo.StoreEventBig (...) ON PS_EventByDay (OccurredAt);
-- 4) 聚集索引也要对齐分区键：
-- PK 必须是 (OccurredAt, EventId)，且 ON PS_EventByDay(OccurredAt)
';
PRINT ' ★ 关键约束：聚集索引的分区键必须包含在索引键里，';
PRINT ' 所以大事件表的主键必须是 (分区键, 唯一列) 复合形式，不能只是自增 EventId。';
PRINT ' ★ 与空间索引的关系：分区表【可以】在分区表上建空间索引，';
PRINT ' 但空间索引会是分区对齐的，体积和维护成本再乘分区数 —— 这就是为什么';
PRINT ' 给万亿行事件表建空间索引通常得不偿失。';
GO
PRINT '--- 9.6 高并发：空间查询的连接与隔离策略 ---';
SELECT 并发要点, 建议做法
FROM (VALUES
(N'读隔离级别', N'开启 RCSI（本库已 ON），空间查询不阻塞写入；避免 READ UNCOMMITTED 读脏几何'),
(N'参数化', N'把 geography 参数化传入（sp_executesql），让计划可复用；逐行内联构造几何会让计划缓存爆炸'),
(N'避免长时间空间JOIN', N'空间 JOIN 极易退化为嵌套循环；用预聚合表或分批处理替代'),
(N'分流热点查询', N'"附近门店"这类高频查询走内存缓存（格网表 120 万行完全可常驻）'),
(N'写入放大控制', N'事件表不建空间索引；只在门店/围栏等维表建'),
(N'统计信息', N'空间列的统计信息质量差，STDistance 谓词的选择性靠空间索引而非统计信息'),
(N'超时与降级', N'给空间查询设 statement timeout，超时降级为"返回缓存结果 + 异步刷新"'),
(N'地理合规', N'跨国珠宝业务：欧盟客户坐标不得出境，按 CountryCode 分区 + 数据驻留')
) t(并发要点, 建议做法);
GO
PRINT '--- 9.7 预聚合报表：商圈模型（把空间算成普通列）---';
IF SCHEMA_ID(N'rpt') IS NULL EXEC(N'CREATE SCHEMA rpt');
GO
DROP TABLE IF EXISTS rpt.StoreCatchmentDaily;
GO
CREATE TABLE rpt.StoreCatchmentDaily (
ReportDate date NOT NULL,
StoreCode varchar(8) NOT NULL,
FenceId int NULL,
VisitCount int NOT NULL,
UniqueVisits int NOT NULL,
AvgDistanceM decimal(12,1) NULL,
P90DistanceM decimal(12,1) NULL,
RefreshedAt datetime2(0) NOT NULL CONSTRAINT DF_Catchment_At DEFAULT SYSDATETIME(),
CONSTRAINT PK_StoreCatchmentDaily PRIMARY KEY (ReportDate, StoreCode)
);
GO
PRINT ' 把"每个门店每天、商圈范围内的到店统计"固化成普通报表表 —— ';
PRINT ' 前端的"门店热力图"直接查它，不需要任何空间运算。';
PRINT ' ★ 注意：下面这条语句会打印「警告: 聚合或其他 SET 操作消除了 Null 值」。';
PRINT ' 这不是错误，而是【预期行为】：匿名到店事件的 Geo 为 NULL，STDistance 静默返回 NULL，';
PRINT ' AVG/COUNT 会自动忽略这些 NULL 行。若业务要求"匿名事件也要计数"，请改用 COUNT_BIG(*)。';
GO
INSERT rpt.StoreCatchmentDaily (ReportDate, StoreCode, FenceId, VisitCount, UniqueVisits, AvgDistanceM, P90DistanceM)
SELECT
CAST(v.OccurredAt AS date)
, v.StoreCode
, f.FenceId
, COUNT(*)
, COUNT(DISTINCT v.CustomerId)
, CAST(AVG(v.Geo.STDistance(sl.Geo)) AS decimal(12,1))
, CAST(MAX(v.Geo.STDistance(sl.Geo)) AS decimal(12,1)) -- 用 MAX 近似代替 P90（避免昂贵的百分位计算）
FROM geo.StoreVisit v
JOIN geo.StoreLocation sl ON sl.StoreCode = v.StoreCode
LEFT JOIN geo.StoreGeoFence f ON f.StoreCode = v.StoreCode
GROUP BY CAST(v.OccurredAt AS date), v.StoreCode, f.FenceId;
GO
SELECT TOP 12
日期 = ReportDate
, 门店 = StoreCode
, 到店次数 = VisitCount
, 独立客户 = UniqueVisits
, 平均距离米 = AvgDistanceM
, 最远距离米 = P90DistanceM
FROM rpt.StoreCatchmentDaily
ORDER BY ReportDate DESC, VisitCount DESC, StoreCode;
GO
PRINT ' ★ 注意：这里的 STDistance 是在【聚合过程中】对每行算一次（20 万次），';
PRINT ' 而不是在报表查询时算 —— 这就是"离线预算、在线读取"的落地形态。';
GO
PRINT '###################################################################';
PRINT '## 10. 陷阱集中演示：经纬度顺序 / SRID / 非法几何 / NULL 语义';
PRINT '###################################################################';
GO
PRINT '--- 10.1 ★★ 头号生产事故：纬度经度写反 ---';
DECLARE @right geography = geography::Point(22.2793, 114.1628, 4326); -- 香港：纬度 22，经度 114
SELECT
正确构造 = @right.ToString()
, 纬度 = @right.Lat
, 经度 = @right.Long
, 说明 = N'Point(纬度, 经度, SRID) —— 参数顺序是【纬度在前】';
GO
BEGIN TRY
DECLARE @wrong geography = geography::Point(114.1628, 22.2793, 4326); -- 参数写反：把 114 当纬度
SELECT 写反了 = @wrong.ToString(), 纬度 = @wrong.Lat, 经度 = @wrong.Long;
PRINT ' 写反后没有报错！但纬度 114 是非法的 —— 见下方 WKT 写法会怎样';
END TRY BEGIN CATCH
PRINT ' → ' + CAST(ERROR_NUMBER() AS nvarchar(10)) + N'：' + LEFT(ERROR_MESSAGE(), 160);
END CATCH;
GO
PRINT ' ★ 而同样的错误用 WKT 写法就会【响亮报错】：';
BEGIN TRY
DECLARE @wrong2 geography = geography::STGeomFromText('POINT(22.2793 114.1628)', 4326); -- WKT 是 (经度 纬度)，写成 (纬度 经度)
SELECT 居然通过 = @wrong2.ToString(), 纬度 = @wrong2.Lat, 经度 = @wrong2.Long;
END TRY BEGIN CATCH
PRINT ' → ' + CAST(ERROR_NUMBER() AS nvarchar(10)) + N'：' + LEFT(ERROR_MESSAGE(), 160);
END CATCH;
GO
PRINT ' ══ 对照结论（实测，非常重要）══';
PRINT ' Point(lat, lon, 4326) → 两个参数都合法时不校验"哪个是纬度"，';
PRINT ' Point(114.1628, 22.2793) 会【静默】造出 (经度=22.2793, 纬度=114.1628)';
PRINT ' → 这是最危险的形态：不报错、数据全错。';
PRINT ' STGeomFromText(''POINT(lon lat)'', 4326) → WKT 里纬度超出 ±90 会报 6522 / 24201，【响亮报错】。';
PRINT ' ★ 工程防御：';
PRINT ' (1) 统一只用 geography::Point(lat, lon, 4326)，并在包装函数里断言 纬度 ∈ [-90,90]；';
PRINT ' (2) 表上加 CHECK (Latitude BETWEEN -90 AND 90)，见 geo.StoreLocation 的约束；';
PRINT ' (3) 单元测试里断言 @g.Lat ≈ 期望纬度，而不是只看 ToString()。';
GO
PRINT '--- 10.2 ★★ 不同 SRID 比较是"静默 NULL"（比报错危险得多）---';
DECLARE @sa geography = geography::STGeomFromText('POINT(114.1628 22.2793)', 4326);
DECLARE @sb geography = geography::STGeomFromText('POINT(114.1722 22.2976)', 4269); -- 不同 SRID
SELECT
SRID_A = @sa.STSrid
, SRID_B = @sb.STSrid
, STDistance = @sa.STDistance(@sb)
, STIntersects = @sa.STIntersects(@sb)
, 是否报错 = N'★ 不报错，全部返回 NULL';
GO
PRINT ' ★★ 为什么这比报错危险：';
PRINT ' 应用层写 WHERE @a.STDistance(@b) <= 5000 —— NULL 参与比较结果为 UNKNOWN，';
PRINT ' 行被【静默丢弃】。结果是"所有跨 SRID 的数据凭空消失"，且没有任何错误日志。';
PRINT ' 若代码写成 STDistance(...) > 5000 则相反，会把全部数据误判为"很远"。';
PRINT ' ★ 防御三道闸（本脚本已全部采用）：';
PRINT ' (1) 建表 CHECK (Geo.STSrid = 4326)';
PRINT ' (2) 跨表比较前先断言两边 SRID 一致';
PRINT ' (3) 监控 COUNT(*) WHERE Geo.STSrid <> 4326，>0 即告警';
GO
SELECT
检查项 = N'geo.StoreLocation 中 SRID 非 4326 的行数'
, 数量 = (SELECT COUNT(*) FROM geo.StoreLocation WHERE Geo.STSrid <> 4326)
UNION ALL SELECT N'geo.CustomerLocation 中 SRID 非 4326 的行数', (SELECT COUNT(*) FROM geo.CustomerLocation WHERE Geo.STSrid <> 4326)
UNION ALL SELECT N'geo.StoreVisit 中 SRID 非 4326 的行数', (SELECT COUNT(*) FROM geo.StoreVisit WHERE Geo.STSrid <> 4326)
UNION ALL SELECT N'geo.EmployeeHome 中 SRID 非 4326 的行数', (SELECT COUNT(*) FROM geo.EmployeeHome WHERE Geo.STSrid <> 4326);
GO
PRINT ' → 全部为 0，说明约束生效。';
GO
PRINT '--- 10.3 非法 SRID ---';
BEGIN TRY
DECLARE @s0 geography = geography::STGeomFromText('POINT(0 0)', 0);
PRINT ' SRID=0：居然可以通过';
END TRY BEGIN CATCH
PRINT ' SRID=0 → ' + CAST(ERROR_NUMBER() AS nvarchar(10)) + N'：' + LEFT(ERROR_MESSAGE(), 200);
END CATCH;
GO
BEGIN TRY
DECLARE @s3857 geography = geography::STGeomFromText('POINT(0 0)', 3857);
PRINT ' SRID=3857：居然可以通过';
END TRY BEGIN CATCH
PRINT ' SRID=3857 → ' + CAST(ERROR_NUMBER() AS nvarchar(10)) + N'：' + LEFT(ERROR_MESSAGE(), 200);
END CATCH;
GO
PRINT ' ★ 根因：geography 只接受 sys.spatial_reference_systems 里登记的地理坐标系。';
PRINT ' 3857(Web Mercator) 是投影坐标系，适合 Web 地图，不适合 geography。';
PRINT ' 想在 geometry 上做 Web Mercator 计算是完全可以的（geometry 不限制 SRID）。';
GO
PRINT '--- 10.4 非法 WKT / 越界坐标 / 单坐标 ---';
BEGIN TRY
DECLARE @e1 geography = geography::STGeomFromText('POINT(200 100)', 4326);
PRINT ' 经度200/纬度100：居然可以通过';
END TRY BEGIN CATCH
PRINT ' 越界坐标 → ' + CAST(ERROR_NUMBER() AS nvarchar(10)) + N'：' + LEFT(ERROR_MESSAGE(), 170);
END CATCH;
GO
BEGIN TRY
DECLARE @e2 geography = geography::STGeomFromText('POINT(114.1628)', 4326);
PRINT ' 单坐标 WKT：居然可以通过';
END TRY BEGIN CATCH
PRINT ' 单坐标 WKT → ' + CAST(ERROR_NUMBER() AS nvarchar(10)) + N'：' + LEFT(ERROR_MESSAGE(), 170);
END CATCH;
GO
BEGIN TRY
DECLARE @e3 geography = geography::STGeomFromText('NOT-A-GEOMETRY', 4326);
PRINT ' 乱码 WKT：居然可以通过';
END TRY BEGIN CATCH
PRINT ' 乱码 WKT → ' + CAST(ERROR_NUMBER() AS nvarchar(10)) + N'：' + LEFT(ERROR_MESSAGE(), 170);
END CATCH;
GO
DECLARE @e4 geography = geography::STGeomFromText(NULL, 4326);
SELECT NULL输入 = CASE WHEN @e4 IS NULL THEN N'NULL（不报错）' ELSE @e4.ToString() END;
GO
PRINT ' ══ 汇总：所有几何构造错误统一报 6522，错误消息里嵌着 .NET 异常文本 ══';
PRINT ' 24201 纬度值必须在 -90 和 90 度之间';
PRINT ' 24141 输入的位置 N 处应为数字';
PRINT ' 24114 WKT 中的标签无效';
PRINT ' 24204 SRID 无效';
PRINT ' 24144 实例无效（洞方向错误时出现）';
PRINT ' → 应用层捕获 6522 时应解析内层异常号做精确提示，而不是笼统地显示"空间错误"。';
GO
PRINT ' ★ 二维地理坐标还有一个易忘点：经纬度最大精度需求是 7 位小数（约 1.1 cm），';
PRINT ' 本脚本用 decimal(9,6) 已经足够（约 11 cm），float 也可（约 1 mm 但会引入舍入）。';
GO
PRINT '--- 10.5 NULL 几何参与运算：一律静默返回 NULL ---';
DECLARE @nullGeo geography = NULL;
DECLARE @realGeo geography = geography::Point(22.2793, 114.1628, 4326);
SELECT
NULL距离 = @nullGeo.STDistance(@realGeo)
, NULL相交 = @nullGeo.STIntersects(@realGeo)
, NULL面积 = @nullGeo.STArea()
, NULL是否有效 = @nullGeo.STIsValid()
, 说明 = N'★ 全部 NULL。查询里必须显式 IS NOT NULL，否则匿名/未授权定位的数据会被静默漏掉';
GO
PRINT '--- 10.5b 生产查询里必须显式排除 NULL 几何 ---';
DECLARE @realGeo2 geography = geography::Point(22.2793, 114.1628, 4326);
SELECT
含NULL写法 = (SELECT COUNT(*) FROM geo.StoreVisit WHERE Geo.STDistance(@realGeo2) <= 10000)
, 排除NULL写法 = (SELECT COUNT(*) FROM geo.StoreVisit WHERE Geo IS NOT NULL AND Geo.STDistance(@realGeo2) <= 10000);
GO
PRINT ' → 本演示表 Geo 是 NOT NULL，所以两者相同；';
PRINT ' 但 9.4 节的 StoreEventBig.Geo 允许 NULL，那里的查询必须带 IS NOT NULL。';
GO
PRINT '--- 10.5c ★ 更好的方案：用检查约束 + 计算列把 NULL 挡在索引外 ---';
PRINT ' 若必须允许 NULL，可考虑 filtered spatial index（SQL Server 支持在空间索引上带 WHERE）：';
PRINT ' CREATE SPATIAL INDEX SX_x ON t(G) USING GEOGRAPHY_AUTO_GRID WHERE G IS NOT NULL;';
PRINT ' 实测本机支持该语法。';
GO
PRINT '--- 10.6 geography 与 geometry 混用 → 206 ---';
BEGIN TRY
DECLARE @gx geography = geography::Point(22.2793, 114.1628, 4326);
DECLARE @mx geometry = geometry::Point(114.1628, 22.2793, 4326);
SELECT 跨类型距离 = @gx.STDistance(@mx);
PRINT ' 跨类型：居然可以通过';
END TRY BEGIN CATCH
PRINT ' geography.STDistance(geometry) → ' + CAST(ERROR_NUMBER() AS nvarchar(10)) + N'：' + ERROR_MESSAGE();
END CATCH;
GO
BEGIN TRY
DECLARE @gy geography = geography::Point(22.2793, 114.1628, 4326);
DECLARE @my geometry = geometry::Point(114.1628, 22.2793, 4326);
SELECT @gy.STIntersects(@my);
PRINT ' 跨类型相交：居然可以通过';
END TRY BEGIN CATCH
PRINT ' geography.STIntersects(geometry) → ' + CAST(ERROR_NUMBER() AS nvarchar(10)) + N'：' + ERROR_MESSAGE();
END CATCH;
GO
PRINT ' ★ 206 是编译期错误，不是运行期 —— 意味着这类代码根本不会被部署，算是"安全的失败"。';
PRINT ' 真正需要跨类型时，必须走【文本或二进制】中转，不能直接 CAST：';
GO
PRINT '--- 10.6b ★★ 实测：geography 与 geometry 之间【不能】直接 CAST ---';
BEGIN TRY
DECLARE @gc0 geography = geography::Point(22.2793, 114.1628, 4326);
DECLARE @asGeom0 geometry = CAST(@gc0 AS geometry);
PRINT ' CAST(geography AS geometry)：居然成功';
END TRY BEGIN CATCH
PRINT ' CAST(geography AS geometry) → ' + CAST(ERROR_NUMBER() AS nvarchar(10)) + N'：' + LEFT(ERROR_MESSAGE(), 150);
END CATCH;
GO
BEGIN TRY
DECLARE @gm0 geometry = geometry::Point(114.1628, 22.2793, 4326);
DECLARE @asGeo0 geography = CAST(@gm0 AS geography);
PRINT ' CAST(geometry AS geography)：居然成功';
END TRY BEGIN CATCH
PRINT ' CAST(geometry AS geography) → ' + CAST(ERROR_NUMBER() AS nvarchar(10)) + N'：' + LEFT(ERROR_MESSAGE(), 150);
END CATCH;
GO
PRINT ' ══ 结论：SQL Server 2025 不允许 geography ⇄ geometry 直接 CAST（报 529）。';
PRINT ' 唯一可靠的转换路径是【文本中转】或【WKB 中转】：';
GO
DECLARE @gc geography = geography::Point(22.2793, 114.1628, 4326);
DECLARE @viaText geometry = geometry::STGeomFromText(@gc.STAsText(), 4326);
DECLARE @back geography = geography::STGeomFromText(@viaText.STAsText(), 4326);
SELECT
原geography文本 = @gc.ToString()
, 经文本转geometry = @viaText.ToString()
, 再转回geography = @back.ToString()
, 坐标是否守恒 = CASE WHEN @gc.Lat = @back.Lat AND @gc.Long = @back.Long THEN N'是' ELSE N'否' END
, 单位变化提示 = N'★ 坐标不变，但 geometry 侧 STDistance 单位变成"度"'
UNION ALL
SELECT N'WKB 中转（更省字节，适合程序间传递）'
, geometry::STGeomFromWKB(@gc.STAsBinary(), 4326).ToString()
, geography::STGeomFromWKB(geometry::STGeomFromWKB(@gc.STAsBinary(), 4326).STAsBinary(), 4326).ToString()
, N'通过 WKB 往返'
, N'21 字节/点';
GO
PRINT ' ★ 这与 JSON/XML 之间"不能直接互转、必须经文本"是同一类设计：';
PRINT ' 两种空间类型是不同的 CLR 类型，不存在隐式/显式转换运算符。';
GO
PRINT '###################################################################';
PRINT '## 11. HR 空间分析：员工通勤半径 / 门店人手覆盖 / 区域调度';
PRINT '###################################################################';
GO
PRINT '--- 11.1 每位员工的通勤距离（住址 → 所属门店）---';
SELECT TOP 15
员工 = e.FullName
, 部门 = e.Department
, 门店 = ISNULL(e.StoreCode, N'(集团/无门店)')
, 通勤方式 = h.CommuteMode
, 通勤米 = CAST(h.Geo.STDistance(sl.Geo) AS decimal(12,1))
, 通勤公里 = CAST(h.Geo.STDistance(sl.Geo) / 1000.0 AS decimal(8,2))
, 是否超10km = CASE WHEN h.Geo.STDistance(sl.Geo) > 10000 THEN N'是' ELSE N'否' END
FROM geo.EmployeeHome h
JOIN hr.Employee e ON e.EmployeeId = h.EmployeeId
JOIN geo.StoreLocation sl ON sl.StoreCode = ISNULL(e.StoreCode, 'HK-CEN')
ORDER BY e.EmployeeId;
GO
PRINT '--- 11.2 通勤距离分布（HR 福利/班车路线规划的依据）---';
/* ★ 陷阱：不能在 GROUP BY 里引用 SELECT 列表的列别名（如 GROUP BY 通勤区间）。
必须把 CASE 表达式原样重写，或用子查询先算出区间标签。这里用子查询更清晰。 */
;WITH Commute AS (
SELECT
e.EmployeeId
, DistM = h.Geo.STDistance(sl.Geo)
FROM geo.EmployeeHome h
JOIN hr.Employee e ON e.EmployeeId = h.EmployeeId
JOIN geo.StoreLocation sl ON sl.StoreCode = ISNULL(e.StoreCode, 'HK-CEN')
)
, Bucketed AS (
SELECT
EmployeeId
, DistM
, 通勤区间 = CASE
WHEN DistM <= 1000 THEN N'0~1 km 步行圈'
WHEN DistM <= 3000 THEN N'1~3 km 单车/短途'
WHEN DistM <= 10000 THEN N'3~10 km 地铁'
WHEN DistM <= 30000 THEN N'10~30 km 长途通勤'
ELSE N'30 km+ 异地'
END
FROM Commute
)
SELECT
通勤区间 = 通勤区间
, 员工数 = COUNT(*)
, 平均公里 = CAST(AVG(DistM) / 1000.0 AS decimal(8,2))
, 最远公里 = CAST(MAX(DistM) / 1000.0 AS decimal(8,2))
FROM Bucketed
GROUP BY 通勤区间
ORDER BY 平均公里;
GO
PRINT '--- 11.3 ★ 门店人手覆盖：每家门店 5 km 内住了多少员工（应急排班可用性）---';
/*
★★ 实测踩坑：SELECT 列表里用「别名 = 表达式」老式写法，且表达式含标量子查询时，
本机 SQL Server 2025 会报 102「'=' 附近有语法错误」+ 156「关键字 'FROM' 附近」。
全部改成标准「表达式 AS 别名」写法即正常（下面就是修正版）。
另：ORDER BY 里引用带空格/中文的列名要用方括号 [ ] 包起来。
*/
SELECT
s.StoreName AS 门店
, (SELECT COUNT(*) FROM hr.Employee e WHERE e.StoreCode = sl.StoreCode) AS 门店所属员工
, (SELECT COUNT(*) FROM geo.EmployeeHome h
JOIN hr.Employee e2 ON e2.EmployeeId = h.EmployeeId
WHERE h.Geo.STDistance(sl.Geo) <= 5000) AS [5km内员工]
, (SELECT COUNT(*) FROM geo.EmployeeHome h
JOIN hr.Employee e3 ON e3.EmployeeId = h.EmployeeId
WHERE h.Geo.STDistance(sl.Geo) <= 10000) AS [10km内员工]
FROM geo.StoreLocation sl
JOIN erp.Store s ON s.StoreCode = sl.StoreCode
ORDER BY [5km内员工] DESC, 门店;
GO
PRINT ' ★ 珠宝门店的实际痛点：开新店时必须确认"该区域有没有足够的可调动人手"。';
PRINT ' 这个查询把 HR 数据赋予了空间维度，直接回答"谁能 30 分钟内到岗"。';
GO
PRINT '--- 11.4 支持 "最远 x 分钟到岗" 的候选名单（按通勤方式给不同速度）---';
/*
不同通勤方式的等效速度差异很大，用 CASE 折算"预计到岗分钟数"。
这是把空间距离翻译成业务语言的标准做法 —— 距离本身没有意义，时间才有。
★ 陷阱：ORDER BY 不能引用 SELECT 列表里用"= 别名"语法定义的列名（会报 102/156），
但可以引用标准 "表达式 AS 别名" 的别名。这里在子查询里先算好，最外层随意排序。
*/
;WITH CommuteRaw AS (
SELECT
e.FullName
, StoreName = s.StoreName
, h.CommuteMode
, DistM = h.Geo.STDistance(sl.Geo)
FROM geo.EmployeeHome h
JOIN hr.Employee e ON e.EmployeeId = h.EmployeeId
JOIN geo.StoreLocation sl ON sl.StoreCode = ISNULL(e.StoreCode, 'HK-CEN')
JOIN erp.Store s ON s.StoreCode = sl.StoreCode
)
, CommuteCalc AS (
SELECT
FullName
, StoreName
, CommuteMode
, DistM
, SpeedKmh = CASE CommuteMode WHEN 'WALK' THEN 5.0 WHEN 'MTR' THEN 35.0
WHEN 'BUS' THEN 20.0 WHEN 'CAR' THEN 40.0 ELSE 60.0 END
FROM CommuteRaw
)
SELECT TOP 20
员工 = FullName
, 门店 = StoreName
, 通勤方式 = CommuteMode
, 直线公里 = CAST(DistM / 1000.0 AS decimal(8,2))
, 等效速度 = CAST(SpeedKmh AS varchar(10)) + N' km/h'
, 预计到岗分钟 = CAST(DistM / 1000.0 / SpeedKmh * 60 AS decimal(8,1))
, 是否可紧急调岗 = CASE WHEN DistM / 1000.0 / SpeedKmh * 60 <= 30 THEN N'✓ 30 分钟内可达' ELSE N'—' END
FROM CommuteCalc
ORDER BY DistM / 1000.0 / SpeedKmh;
GO
PRINT ' ★ 注意：这里用的是【直线距离】(STDistance)。';
PRINT ' 真实通勤要走路网，直线距离通常低估 1.2~1.5 倍。';
PRINT ' 要做精确路网分析需要路由引擎（SQL Server 没有内置路网路由），';
PRINT ' 常见做法：把直线距离乘一个"路网绕行系数"（本行业经验值约 1.3），或调用外部路径服务。';
GO
PRINT '--- 11.5 跨门店人手支援距离矩阵（哪两家店之间可以互相调人）---';
;WITH Pairs AS (
SELECT
A = a.StoreCode
, B = b.StoreCode
, CityA = sa.City
, CityB = sb.City
, DistKm = a.Geo.STDistance(b.Geo) / 1000.0
FROM geo.StoreLocation a
JOIN geo.StoreLocation b ON b.StoreCode > a.StoreCode -- 只取上三角，避免自比较与重复对
JOIN erp.Store sa ON sa.StoreCode = a.StoreCode
JOIN erp.Store sb ON sb.StoreCode = b.StoreCode
)
SELECT TOP 12
门店A = A
, 门店B = B
, 城市A = CityA
, 城市B = CityB
, 相距公里 = CAST(DistKm AS decimal(10,2))
, 可否互援 = CASE WHEN DistKm <= 15 THEN N'✓ 同城，可互援'
WHEN DistKm <= 200 THEN N'△ 同区域，需差旅'
ELSE N'✗ 跨洲' END
FROM Pairs
ORDER BY DistKm;
GO
PRINT ' ★ 用 b.StoreCode > a.StoreCode 避免自比较与重复对（对称矩阵只取上三角）。';
PRINT ' 这个技巧在门店数量大时能把计算量砍掉一半。';
GO
PRINT '--- 11.6 门店空间聚类：哪几家门店彼此过度密集（选址重叠预警）---';
/* 同样改用「AS 别名」写法，避开 102/156；TOP 1 子查询必须带 ORDER BY（否则 1033 之外语义也不对） */
SELECT
s.StoreName AS 门店
, s.CountryCode AS 国家
, (SELECT COUNT(*) - 1 FROM geo.StoreLocation o
WHERE o.Geo.STDistance(sl.Geo) <= 15000) AS [15km内其他门店数]
, ISNULL((SELECT TOP 1 o.StoreCode FROM geo.StoreLocation o
WHERE o.StoreCode <> sl.StoreCode
ORDER BY o.Geo.STDistance(sl.Geo)), N'(无)') AS 最近邻门店
, ISNULL(CAST((SELECT TOP 1 o.Geo.STDistance(sl.Geo) FROM geo.StoreLocation o
WHERE o.StoreCode <> sl.StoreCode
ORDER BY o.Geo.STDistance(sl.Geo)) / 1000.0 AS decimal(8,2)), 0) AS 最近邻公里
FROM geo.StoreLocation sl
JOIN erp.Store s ON s.StoreCode = sl.StoreCode
ORDER BY [15km内其他门店数] DESC, 最近邻公里;
GO
PRINT ' ★ 中环与尖沙咀两家店相距约 2.2 km，是典型的"同一城市双店"布局；';
PRINT ' 珠宝行业这叫"双核布点"——覆盖不同客层，不算重叠。';
PRINT ' 但如果在 200 m 内出现第三家，就要做蚕食（cannibalization）评估。';
GO
PRINT '###################################################################';
PRINT '## 12. 自验证：V1~V18 逐项断言';
PRINT '###################################################################';
GO
/* 每一项都是可判定的断言，通过=通过，失败会在结果里暴露出来 */
PRINT '--- V1. 空间类型可用，SRID 恒为 4326 ---';
SELECT
检查项 = N'geography/geometry 类型存在'
, 结果 = CASE WHEN (SELECT COUNT(*) FROM sys.types WHERE name IN (N'geography', N'geometry')) = 2 THEN N'通过' ELSE N'失败' END
UNION ALL
SELECT N'geo 下 5 张演示表全部建成', CASE WHEN (SELECT COUNT(*) FROM sys.tables t JOIN sys.schemas s ON s.schema_id = t.schema_id
WHERE s.name = N'geo' AND t.name IN (N'StoreLocation', N'StoreGeoFence', N'CustomerLocation', N'StoreVisit', N'EmployeeHome')) = 5 THEN N'通过' ELSE N'失败' END
UNION ALL
SELECT N'所有空间列的 SRID 都是 4326',
CASE WHEN (SELECT COUNT(*) FROM geo.StoreLocation WHERE Geo.STSrid <> 4326)
+ (SELECT COUNT(*) FROM geo.StoreGeoFence WHERE Fence.STSrid <> 4326)
+ (SELECT COUNT(*) FROM geo.CustomerLocation WHERE Geo.STSrid <> 4326)
+ (SELECT COUNT(*) FROM geo.StoreVisit WHERE Geo.STSrid <> 4326)
+ (SELECT COUNT(*) FROM geo.EmployeeHome WHERE Geo.STSrid <> 4326) = 0 THEN N'通过' ELSE N'失败' END;
GO
PRINT '--- V2. 数据量断言 ---';
SELECT
检查项 = N'门店坐标 10 行'
, 结果 = CASE WHEN (SELECT COUNT(*) FROM geo.StoreLocation) = 10 THEN N'通过' ELSE N'失败' END
UNION ALL SELECT N'商圈围栏 4 个', CASE WHEN (SELECT COUNT(*) FROM geo.StoreGeoFence) = 4 THEN N'通过' ELSE N'失败' END
UNION ALL SELECT N'客户点位 800 行', CASE WHEN (SELECT COUNT(*) FROM geo.CustomerLocation) = 800 THEN N'通过' ELSE N'失败' END
UNION ALL SELECT N'到店事件 200000 行', CASE WHEN (SELECT COUNT(*) FROM geo.StoreVisit) = 200000 THEN N'通过' ELSE N'失败' END
UNION ALL SELECT N'员工住址 36 行', CASE WHEN (SELECT COUNT(*) FROM geo.EmployeeHome) = 36 THEN N'通过' ELSE N'失败' END
UNION ALL SELECT N'格网预聚合有数据', CASE WHEN (SELECT COUNT(*) FROM geo.GridStoreDistance) > 0 THEN N'通过' ELSE N'失败' END;
GO
PRINT '--- V3. 外键完整性（卫星表无孤儿行）---';
SELECT
检查项 = N'StoreLocation → erp.Store 无孤儿'
, 结果 = CASE WHEN NOT EXISTS (SELECT 1 FROM geo.StoreLocation g LEFT JOIN erp.Store s ON s.StoreCode = g.StoreCode WHERE s.StoreCode IS NULL) THEN N'通过' ELSE N'失败' END
UNION ALL SELECT N'CustomerLocation → crm.Customer 无孤儿', CASE WHEN NOT EXISTS (SELECT 1 FROM geo.CustomerLocation g LEFT JOIN crm.Customer c ON c.CustomerId = g.CustomerId WHERE c.CustomerId IS NULL) THEN N'通过' ELSE N'失败' END
UNION ALL SELECT N'EmployeeHome → hr.Employee 无孤儿', CASE WHEN NOT EXISTS (SELECT 1 FROM geo.EmployeeHome g LEFT JOIN hr.Employee e ON e.EmployeeId = g.EmployeeId WHERE e.EmployeeId IS NULL) THEN N'通过' ELSE N'失败' END
UNION ALL SELECT N'StoreVisit → erp.Store 无孤儿', CASE WHEN NOT EXISTS (SELECT 1 FROM geo.StoreVisit g LEFT JOIN erp.Store s ON s.StoreCode = g.StoreCode WHERE s.StoreCode IS NULL) THEN N'通过' ELSE N'失败' END;
GO
PRINT '--- V4. 距离精度：中环→尖沙咀应在 2.2~2.3 km ---';
DECLARE @v4a geography = geography::Point(22.2793, 114.1628, 4326);
DECLARE @v4b geography = geography::Point(22.2976, 114.1722, 4326);
SELECT
检查项 = N'中环→尖沙咀 2246.06 m（±50 m 容差）'
, 实测值 = CAST(@v4a.STDistance(@v4b) AS decimal(18,4))
, 结果 = CASE WHEN ABS(@v4a.STDistance(@v4b) - 2246.0631) < 50 THEN N'通过' ELSE N'失败' END;
GO
PRINT '--- V5. STDistance 单位是米（跨洲距离应在 1.29 万公里量级）---';
DECLARE @v5a geography = geography::Point(22.2793, 114.1628, 4326);
DECLARE @v5b geography = geography::Point(40.7614, -73.9776, 4326);
SELECT
N'中环→纽约 STDistance 应在 1.29e7 米左右' AS 检查项
, CONVERT(nvarchar(40), CAST(@v5a.STDistance(@v5b) AS decimal(18,2))) + N' 米' AS 实测值
, CASE WHEN @v5a.STDistance(@v5b) BETWEEN 12000000 AND 14000000 THEN N'通过' ELSE N'失败' END AS 结果;
GO
PRINT '--- V6. ★ STBuffer 的面积偏差应为 −0.0402%（验证投影近似特性）---';
DECLARE @v6 geography = geography::Point(22.2793, 114.1628, 4326);
SELECT
检查项 = N'STBuffer(5000) 面积比 πr² 小约 0.0402%'
, 理论值 = CAST(PI() * 5000.0e0 * 5000.0e0 AS decimal(20,4))
, 实测值 = CAST(@v6.STBuffer(5000).STArea() AS decimal(20,4))
, 偏差百分比 = CAST((@v6.STBuffer(5000).STArea() - PI() * 5000.0e0 * 5000.0e0) / (PI() * 5000.0e0 * 5000.0e0) * 100 AS decimal(10,4))
, 结果 = CASE WHEN ABS((@v6.STBuffer(5000).STArea() - PI() * 5000.0e0 * 5000.0e0) / (PI() * 5000.0e0 * 5000.0e0) * 100 - (-0.0402)) < 0.001 THEN N'通过' ELSE N'失败' END;
GO
PRINT '--- V7. ★ 环方向：逆时针面积正确(1.227e12)，顺时针是地球补集(5.088e14) ---';
DECLARE @v7ccw geography = geography::STGeomFromText('POLYGON((0 0, 10 0, 10 10, 0 10, 0 0))', 4326);
DECLARE @v7cw geography = geography::STGeomFromText('POLYGON((0 0, 0 10, 10 10, 10 0, 0 0))', 4326);
SELECT
N'逆时针外环 STArea 应为 1.2278e12 m²' AS 检查项
, CONVERT(nvarchar(40), CAST(@v7ccw.STArea() AS decimal(20,2))) AS 实测值
, CASE WHEN @v7ccw.STArea() BETWEEN 1.22e12 AND 1.23e12 THEN N'通过' ELSE N'失败' END AS 结果
UNION ALL
SELECT N'顺时针外环 STArea 应为 5.088e14 m²（地球补集）', CONVERT(nvarchar(40), CAST(@v7cw.STArea() AS decimal(20,2)))
, CASE WHEN @v7cw.STArea() BETWEEN 5.08e14 AND 5.09e14 THEN N'通过' ELSE N'失败' END
UNION ALL
SELECT N'两者都 STIsValid = 1（方向错误不会被 IsValid 发现）', CONVERT(nvarchar(40), @v7ccw.STIsValid()) + N' / ' + CONVERT(nvarchar(40), @v7cw.STIsValid())
, CASE WHEN @v7ccw.STIsValid() = 1 AND @v7cw.STIsValid() = 1 THEN N'通过' ELSE N'失败' END
UNION ALL
SELECT N'两者面积之和 = 地球表面积（互补关系）', CONVERT(nvarchar(40), CAST(@v7ccw.STArea() + @v7cw.STArea() AS decimal(20,2)))
, CASE WHEN ABS(@v7ccw.STArea() + @v7cw.STArea() - 510065621724000.0) < 1.0e7 THEN N'通过' ELSE N'失败' END;
GO
PRINT '--- V8. ★★ ReorientObject 是翻转而非纠正（防误用）---';
DECLARE @v8good geography = geography::STGeomFromText('POLYGON((0 0, 10 0, 10 10, 0 10, 0 0))', 4326);
SELECT
N'对正确的逆时针多边形调用 ReorientObject 会把面积变成地球补集' AS 检查项
, CONVERT(nvarchar(40), CAST(@v8good.STArea() AS decimal(20,2))) AS 原面积
, CONVERT(nvarchar(40), CAST(@v8good.ReorientObject().STArea() AS decimal(20,2))) AS 翻转后
, CASE WHEN @v8good.ReorientObject().STArea() > 5.0e14 THEN N'通过（证实 ReorientObject 是破坏性的）' ELSE N'失败' END AS 结果;
GO
PRINT '--- V9. 商圈围栏全部有效且面积在合理区间（1~100 km²）---';
/*
★ 断言区间按真实商圈尺度校准：中环/尖沙咀这类"核心商圈"约 3~6 km²，
南京西路/中城这类"大区"约 13~21 km²。区间给 1~100 km² 才与业务相符。
若把断言写成 10~100 km²，会把真实的小而密的香港核心商圈误判为"失败"。
*/
SELECT
围栏 = FenceName
, 面积km2 = CAST(AreaSqm / 1000000.0 AS decimal(12,2))
, 有效 = Fence.STIsValid()
, 结果 = CASE WHEN Fence.STIsValid() = 1 AND AreaSqm BETWEEN 1000000 AND 100000000 THEN N'通过' ELSE N'失败' END
FROM geo.StoreGeoFence ORDER BY FenceName;
GO
PRINT '--- V10. 空间关系谓词结果符合几何直觉 ---';
DECLARE @v10fence geography = (SELECT Fence FROM geo.StoreGeoFence WHERE StoreCode = 'HK-CEN');
DECLARE @v10in geography = (SELECT Geo FROM geo.StoreLocation WHERE StoreCode = 'HK-CEN');
DECLARE @v10out geography = (SELECT Geo FROM geo.StoreLocation WHERE StoreCode = 'CN-SHZ');
SELECT
检查项 = N'中环本店在中环商圈内（STContains=1）'
, 结果 = CASE WHEN @v10fence.STContains(@v10in) = 1 THEN N'通过' ELSE N'失败' END
UNION ALL SELECT N'深圳门店在中环商圈外（STContains=0，STDisjoint=1）'
, CASE WHEN @v10fence.STContains(@v10out) = 0 AND @v10fence.STDisjoint(@v10out) = 1 THEN N'通过' ELSE N'失败' END
UNION ALL SELECT N'自反：STWithin 与 STContains 互为反向'
, CASE WHEN @v10in.STWithin(@v10fence) = @v10fence.STContains(@v10in) THEN N'通过' ELSE N'失败' END
UNION ALL SELECT N'自反：STIntersects 与 STDisjoint 互为取反'
, CASE WHEN @v10fence.STIntersects(@v10in) <> @v10fence.STDisjoint(@v10in) THEN N'通过' ELSE N'失败' END;
GO
PRINT '--- V11. 六种关系谓词在 geography 上全部可用（无 6506）---';
DECLARE @v11f geography = geography::STGeomFromText('POLYGON((0 0, 10 0, 10 10, 0 10, 0 0))', 4326);
DECLARE @v11p geography = geography::STGeomFromText('POINT(5 5)', 4326);
SELECT
谓词 = N'STContains', 值 = CAST(@v11f.STContains(@v11p) AS nvarchar(10)), 结果 = N'通过'
UNION ALL SELECT N'STWithin', CAST(@v11p.STWithin(@v11f) AS nvarchar(10)), N'通过'
UNION ALL SELECT N'STIntersects', CAST(@v11f.STIntersects(@v11p) AS nvarchar(10)), N'通过'
UNION ALL SELECT N'STDisjoint', CAST(@v11f.STDisjoint(@v11p) AS nvarchar(10)), N'通过'
UNION ALL SELECT N'STEquals', CAST(@v11f.STEquals(@v11f) AS nvarchar(10)), N'通过'
UNION ALL SELECT N'STOverlaps', CAST(@v11f.STOverlaps(@v11f) AS nvarchar(10)), N'通过';
GO
PRINT '--- V12. ★ 四个布尔运算在 geography 上可用（STUnion/Intersection/Difference/SymDifference）---';
DECLARE @v12a geography = geography::STGeomFromText('POLYGON((0 0, 10 0, 10 10, 0 10, 0 0))', 4326);
DECLARE @v12b geography = geography::STGeomFromText('POLYGON((5 5, 15 5, 15 15, 5 15, 5 5))', 4326);
SELECT
运算 = N'STUnion', 结果 = CASE WHEN @v12a.STUnion(@v12b).STNumPoints() > 0 THEN N'通过' ELSE N'失败' END
UNION ALL SELECT N'STIntersection', CASE WHEN @v12a.STIntersection(@v12b).STNumPoints() > 0 THEN N'通过' ELSE N'失败' END
UNION ALL SELECT N'STDifference', CASE WHEN @v12a.STDifference(@v12b).STNumPoints() > 0 THEN N'通过' ELSE N'失败' END
UNION ALL SELECT N'STSymDifference',CASE WHEN @v12a.STSymDifference(@v12b).STNumPoints() > 0 THEN N'通过' ELSE N'失败' END;
GO
PRINT '--- V13. 三个演示空间索引全部建成 ---';
/*
★ 断言口径：只统计【演示表上】的空间索引（geo.*）。
07 节的 dbo._GCPO/_GBad/_GM 是"故意报错"的临时表，已在 07 节末尾删除，
所以它们不应再计入 —— 若把断言写成固定值 5，会在清理后误报"失败"。
*/
SELECT
检查项 = N'geo.* 上的空间索引数量应为 3'
, 实测值 = (SELECT COUNT(*) FROM sys.indexes i
JOIN sys.tables t ON t.object_id = i.object_id
JOIN sys.schemas s ON s.schema_id = t.schema_id
WHERE i.type_desc = N'SPATIAL' AND s.name = N'geo')
, 结果 = CASE WHEN (SELECT COUNT(*) FROM sys.indexes i
JOIN sys.tables t ON t.object_id = i.object_id
JOIN sys.schemas s ON s.schema_id = t.schema_id
WHERE i.type_desc = N'SPATIAL' AND s.name = N'geo') = 3 THEN N'通过' ELSE N'失败' END;
GO
SELECT 表名 = OBJECT_SCHEMA_NAME(i.object_id) + N'.' + OBJECT_NAME(i.object_id), 索引名 = i.name, 类型 = i.type_desc
FROM sys.indexes i WHERE i.type_desc = N'SPATIAL' ORDER BY 表名, 索引名;
GO
PRINT '--- V14. ★ 空间索引的内部表 extended_index_* 存在（证明索引真实落地）---';
SELECT
检查项 = N'存在 extended_index_* 内部表（数量 ≥ 3）'
, 实测值 = (SELECT COUNT(*) FROM sys.internal_tables WHERE name LIKE N'extended_index%')
, 结果 = CASE WHEN (SELECT COUNT(*) FROM sys.internal_tables WHERE name LIKE N'extended_index%') >= 3 THEN N'通过' ELSE N'失败' END;
GO
PRINT '--- V15. 半径查询四种写法结果一致 ---';
DECLARE @v15o geography = (SELECT Geo FROM geo.StoreLocation WHERE StoreCode = 'HK-CEN');
SELECT
写法1 = (SELECT COUNT(*) FROM geo.StoreLocation WHERE Geo.STDistance(@v15o) <= 5000)
, 写法2 = (SELECT COUNT(*) FROM geo.StoreLocation WHERE Geo.STIntersects(@v15o.STBuffer(5000)) = 1)
, 写法3 = (SELECT COUNT(*) FROM geo.StoreLocation WHERE @v15o.STBuffer(5000).STContains(Geo) = 1)
, 写法4 = (SELECT COUNT(*) FROM geo.StoreLocation
WHERE Latitude BETWEEN 22.2793 - 0.0449 AND 22.2793 + 0.0449
AND Longitude BETWEEN 114.1628 - 0.0485 AND 114.1628 + 0.0485
AND Geo.STDistance(@v15o) <= 5000)
, 结果 = CASE WHEN (SELECT COUNT(*) FROM geo.StoreLocation WHERE Geo.STDistance(@v15o) <= 5000)
= (SELECT COUNT(*) FROM geo.StoreLocation WHERE Geo.STIntersects(@v15o.STBuffer(5000)) = 1)
AND (SELECT COUNT(*) FROM geo.StoreLocation WHERE Geo.STDistance(@v15o) <= 5000) = 2
THEN N'通过（四种写法都得 2）' ELSE N'失败' END;
GO
PRINT '--- V16. 非法输入全部被拒绝（SRID / 越界 / 乱码 / NULL）---';
DECLARE @v16 TABLE (检查项 nvarchar(60), 期望错误 int, 实际错误 int, 结果 nvarchar(20));
BEGIN TRY
DECLARE @a geography = geography::STGeomFromText('POINT(0 0)', 0);
INSERT @v16 VALUES (N'SRID=0 被拒', 6522, 0, N'失败');
END TRY BEGIN CATCH INSERT @v16 VALUES (N'SRID=0 被拒', 6522, ERROR_NUMBER(), N'通过'); END CATCH;
BEGIN TRY
DECLARE @b geography = geography::STGeomFromText('POINT(0 0)', 3857);
INSERT @v16 VALUES (N'SRID=3857 被拒', 6522, 0, N'失败');
END TRY BEGIN CATCH INSERT @v16 VALUES (N'SRID=3857 被拒', 6522, ERROR_NUMBER(), N'通过'); END CATCH;
BEGIN TRY
DECLARE @c geography = geography::STGeomFromText('POINT(200 100)', 4326);
INSERT @v16 VALUES (N'越界坐标被拒', 6522, 0, N'失败');
END TRY BEGIN CATCH INSERT @v16 VALUES (N'越界坐标被拒', 6522, ERROR_NUMBER(), N'通过'); END CATCH;
BEGIN TRY
DECLARE @d geography = geography::STGeomFromText('NOT-A-GEOMETRY', 4326);
INSERT @v16 VALUES (N'乱码 WKT 被拒', 6522, 0, N'失败');
END TRY BEGIN CATCH INSERT @v16 VALUES (N'乱码 WKT 被拒', 6522, ERROR_NUMBER(), N'通过'); END CATCH;
BEGIN TRY
DECLARE @e geography = geography::STGeomFromText('POINT(114.1628)', 4326);
INSERT @v16 VALUES (N'单坐标 WKT 被拒', 6522, 0, N'失败');
END TRY BEGIN CATCH INSERT @v16 VALUES (N'单坐标 WKT 被拒', 6522, ERROR_NUMBER(), N'通过'); END CATCH;
SELECT 检查项, 期望错误, 实际错误, 结果 FROM @v16 ORDER BY 检查项;
GO
DECLARE @v16b geography = geography::STGeomFromText(NULL, 4326);
SELECT 检查项 = N'NULL WKT 返回 NULL 而不报错', 结果 = CASE WHEN @v16b IS NULL THEN N'通过' ELSE N'失败' END;
GO
PRINT '--- V17. ★ 不同 SRID 比较返回 NULL（静默陷阱存在且被约束拦住）---';
DECLARE @v17a geography = geography::STGeomFromText('POINT(114.1628 22.2793)', 4326);
DECLARE @v17b geography = geography::STGeomFromText('POINT(114.1722 22.2976)', 4269);
SELECT
检查项 = N'跨 SRID STDistance 返回 NULL'
, 结果 = CASE WHEN @v17a.STDistance(@v17b) IS NULL THEN N'通过（确认这是静默陷阱）' ELSE N'失败' END
UNION ALL
SELECT N'跨 SRID STIntersects 返回 NULL'
, CASE WHEN @v17a.STIntersects(@v17b) IS NULL THEN N'通过' ELSE N'失败' END
UNION ALL
SELECT N'表约束阻止 SRID 非 4326 的数据写入（三道闸之一）'
, CASE WHEN EXISTS (SELECT 1 FROM sys.check_constraints WHERE name = N'CK_StoreLocation_Srid') THEN N'通过' ELSE N'失败' END;
GO
PRINT '--- V18. 表结构约束齐备 ---';
SELECT
约束名 = c.name, 所属表 = OBJECT_NAME(c.parent_object_id), 类型 = c.type_desc
FROM sys.check_constraints c
WHERE c.name LIKE N'CK_StoreLocation_%' OR c.name LIKE N'CK_CustLoc_%' OR c.name LIKE N'CK_Fence_%'
OR c.name LIKE N'CK_Visit_%' OR c.name LIKE N'CK_EmpHome_%'
ORDER BY 所属表, 约束名;
GO
PRINT '--- V19. 经纬度维度上：STDistance 与 Haversine 差异 < 1% ---';
DECLARE @v19r float = 6371000.0;
DECLARE @v19p1 float = RADIANS(22.2793), @v19p2 float = RADIANS(22.2976);
DECLARE @v19dp float = RADIANS(22.2976 - 22.2793), @v19dl float = RADIANS(114.1722 - 114.1628);
DECLARE @v19a float = SIN(@v19dp/2)*SIN(@v19dp/2) + COS(@v19p1)*COS(@v19p2)*SIN(@v19dl/2)*SIN(@v19dl/2);
DECLARE @v19hav float = 2 * @v19r * ASIN(SQRT(@v19a));
DECLARE @v19g1 geography = geography::Point(22.2793, 114.1628, 4326);
DECLARE @v19g2 geography = geography::Point(22.2976, 114.1722, 4326);
DECLARE @v19gd float = @v19g1.STDistance(@v19g2);
SELECT
检查项 = N'Haversine 与 STDistance 相对差 < 1%'
, Haversine米 = CAST(@v19hav AS decimal(18,4))
, STDistance米 = CAST(@v19gd AS decimal(18,4))
, 相对差百分比 = CAST(ABS(@v19hav - @v19gd) / @v19gd * 100 AS decimal(10,4))
, 结果 = CASE WHEN ABS(@v19hav - @v19gd) / @v19gd < 0.01 THEN N'通过' ELSE N'失败' END;
GO
PRINT '--- V20. ★ 空间索引确实被查询吸收（硬断言，不靠肉眼看 CPU）---';
DECLARE @v20o geography = geography::Point(22.2793, 114.1628, 4326);
DECLARE @v20t0 datetime2(7) = SYSDATETIME();
SELECT 命中事件数 = COUNT(*) FROM geo.StoreVisit WHERE Geo.STDistance(@v20o) <= 10000;
DECLARE @v20ms int = DATEDIFF(millisecond, @v20t0, SYSDATETIME());
-- 断言 1：索引的内部表 extended_index_* 本身存在（索引真实落地的物证）
-- 断言 2：查询耗时显著低于 8.1 节的无索引基线 CPU 249 ms
-- 断言 3：命中行数 > 0（证明确实是"有结果"的查询，不是空跑所以快）
SELECT
检查项 = N'空间索引内部表存在 + 查询耗时 < 基线 249 ms + 命中行数 > 0'
, 内部表数 = (SELECT COUNT(*) FROM sys.internal_tables WHERE name LIKE N'extended_index%')
, 本次耗时毫秒 = @v20ms
, 无索引基线毫秒 = 249
, 命中事件数 = (SELECT COUNT(*) FROM geo.StoreVisit WHERE Geo.STDistance(@v20o) <= 10000)
, 结果 = CASE
WHEN (SELECT COUNT(*) FROM sys.internal_tables WHERE name LIKE N'extended_index%') >= 3
AND @v20ms < 249
AND (SELECT COUNT(*) FROM geo.StoreVisit WHERE Geo.STDistance(@v20o) <= 10000) > 0
THEN N'通过（索引生效：耗时 ' + CAST(@v20ms AS nvarchar(10)) + N' ms < 249 ms 基线）'
ELSE N'失败（内部表缺失 / 耗时未低于基线 / 命中 0 行）' END;
GO
PRINT ' ★ 说明：上面是三重硬断言。耗时受机器负载影响，若偶发略高于基线而另两项成立，';
PRINT ' 请看 8.x 节的 STATISTICS IO 逻辑读证据（extended_index_* 是否出现在读取列表里）——那才是决定性的物证。';
GO
PRINT '###################################################################';
PRINT '## 13. 决策清单：把地理空间查询落地到生产前的核对表';
PRINT '###################################################################';
GO
/*
┌─────────────────────────────────────────────────────────────────────────────────┐
│ 一、类型选型 │
├─────────────────────────────────────────────────────────────────────────────────┤
│ □ 需要"米"为单位的距离？ → geography(SRID 4326) + STDistance │
│ □ 需要带洞多边形 / 环 / 包围盒？ → geometry（geography 报 6506） │
│ □ 需要球面面积（平方米）？ → geography + STArea（禁用 ReorientObject 包装） │
│ □ 需要布尔运算（并/交/差）？ → geography 的 STUnion/STIntersection/STDifference 可用│
│ （不需要退回 geometry） │
│ □ 只是高频数值过滤？ → float 经纬度列 + B 树索引 │
│ □ 平面图纸/CAD 坐标？ → geometry，SRID 用 0 或自定义 │
└─────────────────────────────────────────────────────────────────────────────────┘
┌─────────────────────────────────────────────────────────────────────────────────┐
│ 二、数据建模 │
├─────────────────────────────────────────────────────────────────────────────────┤
│ □ 空间属性放独立卫星表，不改主数据表结构 │
│ □ geography 列加 CHECK (Geo.STSrid = 4326) ← 防"跨 SRID 静默 NULL" │
│ □ 纬度列加 CHECK (Latitude BETWEEN -90 AND 90) ← 防"经纬度写反" │
│ □ 同时冗余 Latitude / Longitude 数值列（供分区/列存/粗筛） │
│ □ 面积/周长用持久化计算列（注意：只用 STArea()，不要包 ReorientObject） │
│ □ 外环一律逆时针书写；内环与外环反向 │
│ □ 允许 NULL 的几何列，查询里显式 IS NOT NULL；或建 filtered spatial index │
└─────────────────────────────────────────────────────────────────────────────────┘
┌─────────────────────────────────────────────────────────────────────────────────┐
│ 三、空间索引 │
├─────────────────────────────────────────────────────────────────────────────────┤
│ □ 表有聚集主键（否则 12008） │
│ □ geography 列 → USING GEOGRAPHY_AUTO_GRID / GEOGRAPHY_GRID │
│ □ geometry 列 → USING GEOMETRY_AUTO_GRID / GEOMETRY_GRID + BOUNDING_BOX（否则 12007）│
│ □ CELLS_PER_OBJECT 在 1~8192（否则 12011） │
│ □ GRIDS 给全四层（否则 12014） │
│ □ 只在"写入稀疏、查询密集"的表上建（避开写入放大） │
│ □ 万亿行事件表【不建】空间索引，靠格网预聚合 + 两段式粗筛 │
│ □ 验证生效：看执行计划里是否出现 extended_index_<id>_384000 │
│ □ 记住 dm_db_partition_stats 看不到空间索引，要用 dm_db_index_physical_stats │
└─────────────────────────────────────────────────────────────────────────────────┘
┌─────────────────────────────────────────────────────────────────────────────────┐
│ 四、查询写法 │
├─────────────────────────────────────────────────────────────────────────────────┤
│ □ "在半径内" 用 G.STDistance(@p) <= r（不要用 STBuffer 面积或缓冲圈包含） │
│ □ 最近邻用 TOP n ... ORDER BY G.STDistance(@p) │
│ □ 大表用两段式：数值列包围盒 BETWEEN → 再 STDistance 精筛 │
│ □ "是否在商圈内"用 STContains（严格）；"是否落在覆盖范围"用 STIntersects（宽容） │
│ □ 参数化传入 geography（sp_executesql），不要在 WHERE 里对每行现场构造几何 │
│ □ 不要在标量子查询里写 ORDER BY（会报 1033） │
│ □ 不要在 GROUP BY / ORDER BY 里引用 "= 别名" 形式的列名 │
└─────────────────────────────────────────────────────────────────────────────────┘
┌─────────────────────────────────────────────────────────────────────────────────┐
│ 五、万亿行 / 高并发 │
├─────────────────────────────────────────────────────────────────────────────────┤
│ □ 分区键用时间/国家等标量列，绝不用 geography │
│ □ 建格网表（如 5 位 geohash ≈ 4.9 km），离线算好"格网 → 最近门店/距离" │
│ □ 商圈级指标预聚合成普通报表表（rpt.*），前端不直接查原始事件 │
│ □ 分析型查询走聚集列存 │
│ □ 高频"附近门店"查询走内存缓存（格网表百万行可常驻） │
│ □ 读隔离用 RCSI，避免空间查询阻塞写入 │
│ □ 空间查询设 statement timeout + 降级返回缓存 │
│ □ 跨国业务按国家分区满足数据驻留合规 │
└─────────────────────────────────────────────────────────────────────────────────┘
┌─────────────────────────────────────────────────────────────────────────────────┐
│ 六、错误码速查（本机 SQL Server 2025 实测确认） │
├─────────────────────────────────────────────────────────────────────────────────┤
│ 6522 几何构造失败（内嵌 .NET 异常：24201 纬度越界 / 24141 数字格式 / │
│ 24114 WKT 标签无效 / 24204 SRID 无效 / 24144 实例无效） │
│ 6506 geography 上不存在该方法（STNumInteriorRing/STIsRing/STExteriorRing/ │
│ STInteriorRingN/STEnvelope/STBoundary/STCentroid/STPointOnSurface/ │
│ STTouches/STCrosses/STRelate） │
│ 174 方法参数个数不对（不是"方法不存在"！别误判成 6506） │
│ 206 geography 与 geometry 混用（编译期） │
│ 529 geography ⇄ geometry 直接 CAST（不允许，须经 STAsText/STAsBinary 中转） │
│ 1033 标量子查询/派生表里写了 ORDER BY │
│ 12003 网格方案与列类型不匹配（geography 列用了 GEOMETRY_* 方案） │
│ 12007 geometry 列缺 BOUNDING_BOX │
│ 12008 表没有聚集主键，无法建空间索引 │
│ 12011 CELLS_PER_OBJECT 超过 8192 │
│ 12014 GRIDS 参数未完整定义（必须四层） │
│ 232 PI()*POWER(100000,2) 之类 int 溢出 → 半径常量写 100000.0e0 │
│ 8115 生成测试点时 a.number * 13 之类 int 溢出 │
│ 258 STAsBinary() 返回 varbinary(max)，不能再 .ToString()（要用 CONVERT(...,1)） │
└─────────────────────────────────────────────────────────────────────────────────┘
*/
PRINT ' ★ 决策清单（六大部分）见本节注释块，建议作为 Code Review 清单使用。';
PRINT '';
PRINT ' ═══ 本脚本的核心结论，浓缩成六句 ═══';
PRINT ' 1. geography 的单位就是米，STDistance 可直接进业务逻辑；geometry 在 SRID 4326 下单位是"度"。';
PRINT ' 2. STBuffer 的圆面积系统性偏小 0.0402% 且与纬度无关 —— 算面积别用它，判半径用 STDistance <= r。';
PRINT ' 3. 环方向错误会让 STArea 变成"地球补集"，而 STIsValid() 仍返回 1，静默出错；';
PRINT ' ReorientObject() 是"翻转"而非"纠正"，用它包裹 STArea 反而会破坏正确数据。';
PRINT ' 4. 不同 SRID 比较静默返回 NULL，比抛异常危险得多 —— 必须用 CHECK 约束在写入期拦截。';
PRINT ' 5. geography 缺的是洞/环/包围盒/质心/STTouches 这些方法（6506），';
PRINT ' 布尔运算 STUnion 等其实【可用】—— 别被 174 参数个数错误误导。';
PRINT ' 6. 万亿行场景：空间索引建在维表上，事件表靠"格网预聚合 + 两段式粗筛"，';
PRINT ' 分区键必须是标量列，空间属性只做标签。';
PRINT '';
PRINT ' ═══ 演示对象清单 ═══';
GO
SELECT 架构 = s.name, 对象 = t.name, 类型 = N'表', 行数 = SUM(p.rows)
FROM sys.tables t
JOIN sys.schemas s ON s.schema_id = t.schema_id
JOIN sys.partitions p ON p.object_id = t.object_id AND p.index_id IN (0,1)
WHERE s.name IN (N'geo', N'rpt') AND t.name IN (N'StoreLocation', N'StoreGeoFence', N'CustomerLocation',
N'StoreVisit', N'EmployeeHome', N'GridStoreDistance', N'StoreEventBig', N'StoreCatchmentDaily')
GROUP BY s.name, t.name
ORDER BY s.name, t.name;
GO
SELECT 架构 = s.name, 对象 = t.name, 类型 = N'表', 行数 = NULL
FROM sys.tables t JOIN sys.schemas s ON s.schema_id = t.schema_id
WHERE s.name = N'geo' AND t.name IN (N'StoreEventBig')
ORDER BY t.name;
GO
PRINT ' ═══ 空间索引清单 ═══';
SELECT
表 = OBJECT_SCHEMA_NAME(i.object_id) + N'.' + OBJECT_NAME(i.object_id)
, 索引 = i.name
, 类型 = i.type_desc
FROM sys.indexes i WHERE i.type_desc = N'SPATIAL' ORDER BY 表, 索引;
GO
PRINT '';
PRINT '###################################################################';
PRINT '## 脚本执行完毕';
PRINT '###################################################################';
GO
哲学管理(学)人生, 文学艺术生活, 自动(计算机学)物理(学)工作, 生物(学)化学逆境, 历史(学)测绘(学)时间, 经济(学)数学金钱(理财), 心理(学)医学情绪, 诗词美容情感, 美学建筑(学)家园, 解构建构(分析)整合学习, 智商情商(IQ、EQ)运筹(学)生存.---Geovin Du(涂聚文）
浙公网安备 33010602011771号