PostgreSQL中UNION ALL用法及点在多面体内查询问题求助

我来帮你梳理下这个空间查询的问题，以及对应的解决办法：

问题根源拆解 #

首先，你遇到的两个核心问题背后的原因是一致的：

• 全量查询无结果：你的原始查询用了FROM p, final_results_all_operators这种交叉连接，会生成regional表行数 × 点表行数的笛卡尔积数据。当点数量接近1万时，这个计算量会直接撑爆数据库的临时资源，导致查询超时或无响应，自然没有结果返回。
• UNION ALL报错：PostgreSQL中，WITH子句属于单个SELECT语句的局部定义，不能在UNION ALL的两端分别定义独立的WITH。而且就算语法修正了，这种分批方式还是基于全量笛卡尔积做偏移，效率极低，还可能因为结果集顺序不稳定导致重复或遗漏数据。

优化后的解决方案 #

方案1：重构查询逻辑，彻底避免笛卡尔积 #

这是最推荐的做法，直接用EXISTS或JOIN来实现点与多边形的匹配，大幅减少计算量：

用EXISTS判断点是否在任意多边形内

SELECT fro.*
FROM public.final_results_all_operators fro
WHERE EXISTS (
    SELECT 1
    FROM public.regional r
    WHERE ST_Contains(ST_GeomFromText(r.multipolygon), fro.point)
);

用JOIN获取匹配的点与多边形（支持去重）

如果一个点可能落在多个多边形内，用DISTINCT去重：

SELECT DISTINCT fro.*, r.multipolygon
FROM public.final_results_all_operators fro
JOIN public.regional r 
  ON ST_Contains(ST_GeomFromText(r.multipolygon), fro.point);

方案2：如果必须分批查询，修正UNION ALL写法 #

如果因为特殊场景需要分批，要把WITH子句提升到整个查询的外层，而不是每个SELECT单独定义：

WITH regional_polygons AS (
    SELECT multipolygon FROM public.regional
)
SELECT * 
FROM regional_polygons p, public.final_results_all_operators fro
WHERE ST_Contains(ST_GeomFromText(p.multipolygon), fro.point)
LIMIT 5000

UNION ALL

SELECT * 
FROM regional_polygons p, public.final_results_all_operators fro
WHERE ST_Contains(ST_GeomFromText(p.multipolygon), fro.point)
OFFSET 5000 LIMIT 4952;

不过更高效的分批方式是按点的主键（比如id）范围拆分，避免全量计算：

-- 假设点表有主键id，按id分段查询
SELECT fro.*, r.multipolygon
FROM public.final_results_all_operators fro
JOIN public.regional r 
  ON ST_Contains(ST_GeomFromText(r.multipolygon), fro.point)
WHERE fro.id BETWEEN 1 AND 5000

UNION ALL

SELECT fro.*, r.multipolygon
FROM public.final_results_all_operators fro
JOIN public.regional r 
  ON ST_Contains(ST_GeomFromText(r.multipolygon), fro.point)
WHERE fro.id BETWEEN 5001 AND 9952;

额外性能优化建议 #

1. 创建空间索引：这是空间查询的关键优化，能把ST_Contains的查询速度提升几个数量级：

-- 给点字段创建GIST索引
CREATE INDEX idx_final_results_point ON public.final_results_all_operators USING GIST (point);

-- 给多边形字段创建索引（如果multipolygon是文本类型，先转成geometry再建索引）
ALTER TABLE public.regional ADD COLUMN geom geometry(MultiPolygon, 4326); -- 替换为你的坐标系SRID
UPDATE public.regional SET geom = ST_GeomFromText(multipolygon, 4326);
CREATE INDEX idx_regional_geom ON public.regional USING GIST (geom);

之后查询就可以直接用geom字段，不用每次调用ST_GeomFromText转换。

2. 避免文本存储空间数据：把multipolygon字段从文本类型改成geometry类型存储，减少查询时的转换开销，同时让空间索引更有效。

内容的提问来源于stack exchange，提问作者Zhafari Irsyad