在 PostGIS 中进行千万级空间数据的空间查询和关键字查询

一、目的

本测试在探究在有限的计算机配置下，如何高效地对千万级的空间数据进行空间查询和关键字查询。通过实际操作和测试，评估不同查询策略的性能，为处理大规模空间数据提供可行的解决方案。

计算机配置如下：

• 内存（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。

二、主要思路

1. 首先，在给定的计算机配置下，创建一个包含1千万条空间数据的表random_points。数据范围假定在经度 100 - 170，纬度在 20 - 70 之间，采用 4326 坐标系。
2. 为了更符合一般兴趣点的规则，表中的属性字段pname的生成规则为前半部分每 2000 个相同，由md5((id%5000)::text))的前半部分和md5(random()::text))的后半部分组成，并进行了两次字符串处理，分别在第五个字符和第九个字符处插入空格。
3. 分别为属性字段pname和空间字段geom创建 GIST 类型索引，以提高查询效率。
4. 构建一个多边形，然后进行空间查询和关键字查询的组合操作，即查询包含在多边形中，并满足关键字要求的前 100 条记录。

三、主要过程

1. 删除和创建表

首先使用以下 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 as
  with bounds as (
select
	100 as origin_x,
	20 as origin_y,
	70 as width,
	50 as height
  )
  select
	id,
	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
from
	bounds,
	generate_series(0,10000000) as id;

2. 字符串再处理

为了便于观察，使用以下 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);

3. 创建索引

为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 );

4. 空间查询和关键字查询组合
   查询在某个多边形范围内的，关键字为abc的数据

构建一个多边形，使用以下 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

标记一下：

四、测试结论

在给定的计算机配置下，通过合理的表结构设计、字符串处理、索引创建以及查询策略，可以较为高效地对千万级空间数据进行空间查询和关键字查询。创建表、更新字符串和创建索引的过程相对耗时较长，但一旦索引创建完成，实际查询操作非常迅速。这表明在处理大规模空间数据时，提前进行索引创建和优化是非常关键的。同时，也验证了 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 as
  with bounds as (
select
	100 as origin_x,
	20 as origin_y,
	70 as width,
	50 as height
  )
  select
	id,
	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
from
	bounds,
	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;