一、目的
本测试在探究在有限的计算机配置下，如何高效地对千万级的空间数据进行空间查询和关键字查询。通过实际操作和测试，评估不同查询策略的性能，为处理大规模空间数据提供可行的解决方案。
计算机配置如下：
- 内存（16G）：
- 内存 1 名称为 3200 MHz，大小 8GB，频率 3200 MHz，数据宽度 64。
- 内存 2 名称为 3200 MHz，大小 8GB，频率 3200 MHz，数据宽度 64。
- CPU（AMD 6 核 4600Hz）：
- CPU 名称为超微半导体 AMD Ryzen 5 4600H with Radeon Graphics
- 六核，核心数 6，
- 默认频率 3000 MHz，外频 100 MHz，当前频率 3000 MHz，
- 二级缓存为 512-KB,12-way set associative,64-byte line size，
- 三级缓存为 64-KB,18-way set associative,64-byte line size，
- CPU 电压 1.200 V，
- 数据宽度 64。
- 硬盘（SSD）：
- 厂商为 Micron MTFDHBA512TDV，大小 512GB，为主硬盘，序列号为 00A0_7501_2A3B_3DE6。
二、主要思路
- 首先，在给定的计算机配置下，创建一个包含两千万条空间数据的表
random_points。数据范围假定在经度 100 - 170，纬度在 20 - 70 之间，采用 4326 坐标系。 - 为了更符合一般兴趣点的规则，表中的属性字段
pname的生成规则为前半部分每 2000 个相同，由md5((id%5000)::text))的前半部分和md5(random()::text))的后半部分组成，并进行了两次字符串处理，分别在第五个字符和第九个字符处插入空格。 - 分别为属性字段
pname和空间字段geom创建 GIST 类型索引，以提高查询效率。 - 构建一个多边形，然后进行空间查询和关键字查询的组合操作，即查询包含在多边形中，并满足关键字要求的前 100 条记录。
三、主要过程
- 删除和创建表
首先使用以下 SQL 语句删除可能存在的同名表：
drop t**ab**le if exists random_points;
然后使用以下 SQL 语句创建表
random_points，包含字段id、空间字段geom和属性字段pname。生成的数据范围在经度 100 - 170，纬度在 20 - 70 之间，4326 坐标系。pname字段存储属性信息，前半部分为md5((id%5000)::text))，后半部分为md5(random()::text))。创建表耗时 90s。
create t**ab**le random_points aswith bounds as (
select100 as origin_x,20 as origin_y,70 as width,50 as height)selectid,ST_Point(width * (random() - 0.5) + origin_x,height * (random() - 0.5) + origin_y,4326)::Geometry(Point,4326) as geom,concat(substring(md5((id%5000)::text),0,5),substring(md5(random()::text),0,5)) as pname
frombounds,generate_series(0,10000000) as id;
- 字符串再处理
为了便于观察，使用以下 SQL 语句对
pname字段进行两次处理，分别在第五个字符和第九个字符处插入空格。
UPDATE random_points
SET pname = substring(pname, 1, 4) || ' ' || substring(pname, 5);
UPDATE random_points
SET pname = substring(pname, 1, 8) || ' ' || substring(pname, 9);
- 创建索引
为
pname字段创建 GIST 类型索引，耗时 960s，索引类型为gist_trgm_ops，以支持关键字查询的相似性匹配。
CREATE INDEX random_points_pname_idx ON random_points USING GIST (pname gist_trgm_ops);
为
geom字段创建 GIST 类型索引，耗时 390s，以提高空间查询效率。
CREATE INDEX random_points_geom_idx ON random_points USING GIST ( geom );
- 空间查询和关键字查询组合
构建一个多边形，使用以下 SQL 语句将其命名为
poly。
with poly as (SELECT ST_MakePolygon( ST_GeomFromText('LINESTRING(120 30,120 31,121 31,121 29,120 30)',4326)) as geom)
进行嵌套查询，先从
random_points表和poly中筛选出位于多边形内的记录，然后对结果按照与关键字的相似性进行降序排序，并取前 10 条记录。
SELECT t.*,similarity(pname, 'abc') as siml FROM (select r.* from random_points r,poly where st_contains(poly.geom,r.geom)) t
ORDER BY siml desc limit 10;
查询结果 总耗时 0.001s
| id | geom | pname | siml |
|---|---|---|---|
| 8408214 | POINT (120.83824589907863 30.062078059390522) | 89d4 614 abc32c | 0.1764706 |
| 7316546 | POINT (120.0774601303543 30.20134481329915) | aff0 ab5 ab6127 | 0.13333334 |
| 9040632 | POINT (120.98812953740612 29.460764651496056) | abd8 a0c aa3a0b | 0.125 |
| 6579227 | POINT (120.09593195010038 30.250940353799365) | aafd abe c8de08 | 0.11764706 |
| 5863486 | POINT (120.3497148509021 29.8889356761509) | ab4f 4c4 422f81 | 0.11764706 |
| 734406 | POINT (120.87540667125438 29.98574429934731) | ae87 ab0 2d671f | 0.11764706 |
| 5194425 | POINT (120.24255897006032 30.198223695596553) | a4d4 318 abaadd | 0.11764706 |
| 3552394 | POINT (120.44942020766044 30.368667344833504) | d092 d8b ab48a1 | 0.11764706 |
| 4769559 | POINT (120.85389792822224 30.749234346861414) | a5b9 455 abbfd4 | 0.11764706 |
| 941308 | POINT (120.07061196111543 30.654686270683023) | a087 11e ab5702 | 0.11764706 |
仔细一点：
四、测试结论
在给定的计算机配置下，通过合理的表结构设计、字符串处理、索引创建以及查询策略，可以较为高效地对千万级空间数据进行空间查询和关键字查询。创建表、更新字符串和创建索引的过程相对耗时较长，但一旦索引创建完成，实际查询操作非常迅速。这表明在处理大规模空间数据时，提前进行索引创建和优化是非常关键的。同时，也验证了 PostGIS 在处理复杂空间查询和关键字查询方面的强大能力。
五、完整SQL
--计算机配置：
----■内存（16G）：----------------------------------
----内存1名称 3200 MHz
----大小 8 GB
----频率 3200 MHz
----数据宽度 64
----内存2名称 3200 MHz
----大小 8 GB
----频率 3200 MHz
----数据宽度 64
----■CPU（AMD 6核4600Hz）：
----CPU名称 超微半导体 AMD Ryzen 5 4600H with Radeon Graphics 六核
----CPU温度 69°C
----厂商 超微半导体
----核心数 6
----默认频率 3000 MHz
----外频 100 MHz
----当前频率 3000 MHz
----二级缓存 512-KB,12-way set associative,64-byte line size
----三级缓存 64-KB,18-way set associative,64-byte line size
----CPU电压 1.200 V
----数据宽度 64
----■硬盘（SSD）：
----厂商 Micron MTFDHBA512TDV
----大小 512 GB
----主硬盘 是
----序列号 00A0_7501_2A3B_3DE6.--删除表
drop table if exists random_points;
--创建表，2千万条数据
--生成的数据范围假定在经度100-170，纬度在20-70之间，4326坐标系
--pname字段存储属性信息，为了更符合一般兴趣点的规则，也就是前半段可能相同，所以该字段的前半部分每2000个相同
----前半部分：md5((id%5000)::text))
----后半部分：md5(random()::text))
--耗时90s
create table random_points aswith bounds as (
select100 as origin_x,20 as origin_y,70 as width,50 as height)selectid,ST_Point(width * (random() - 0.5) + origin_x,height * (random() - 0.5) + origin_y,4326)::Geometry(Point,4326) as geom,concat(substring(md5((id%5000)::text),0,5),' ',substring(md5(random()::text),0,5)) as pname
frombounds,generate_series(0,10000000) as id;UPDATE random_points
SET pname = substring(pname, 1, 4) || ' ' || substring(pname, 5);
UPDATE random_points
SET pname = substring(pname, 1, 8) || ' ' || substring(pname, 9);--创建属性字段pname的GIST类型索引，耗时960s
CREATE INDEX random_points_pname_idx ON random_points USING GIST (pname gist_trgm_ops);
--创建空间字段geom的GIST类型索引，耗时390s
CREATE INDEX random_points_geom_idx ON random_points USING GIST ( geom );--构建一个多边形
--查询包含在多边形中，并满足关键字[abc]要求的前10条记录
--总耗时 0.001s
with poly as (SELECT ST_MakePolygon( ST_GeomFromText('LINESTRING(120 30,120 31,121 31,121 29,120 30)',4326)) as geom)
SELECT t.*,similarity(pname , 'abc') as siml FROM (select r.* from random_points r,poly where st_contains(poly.geom,r.geom)) t
ORDER BY siml desc limit 10;
