PostgreSQL巧妙地使用ST_Within

Question

也许我的问题看起来很愚蠢，但我想问一下，是否有人能告诉我如何改进这个查询：

UPDATE twitter
   SET poi=poi.type_name FROM poi
 WHERE ST_Within (ST_SetSRID(ST_MakePoint(x_coords,y_coords),4326),
 ST_SetSRID(ST_Buffer(poi.wkb_geometry, 0.0005),4326));

我有两个有点数的表："twitter“和"poi"，我需要在twitter周围创建一个50m的缓冲区，并在这个缓冲区中找到"twitter”中的所有点，然后将"poi“的"type_name”属性复制到"twitter“点。问题是查询时间太长，似乎永远不会完成，我不时地尝试检查是否复制了一些属性，但是"twitter.poi“列始终是空的。

我试图通过在"**username**"中添加来限制查询，以检查查询是否正确并工作。

UPDATE twitter
       SET poi=poi.type_name FROM poi
     WHERE uname='some_username' AND ST_Within (ST_SetSRID(ST_MakePoint(x_coords,y_coords),4326),
     ST_SetSRID(ST_Buffer(poi.wkb_geometry, 0.0005),4326));

我唯一不能理解的是如何改进整个"twitter“表的查询。

Answer 1

您没有提供表定义，但是表twitter中的坐标似乎有两个列：x_coords和y_coords。要么用geometry列替换这些索引，然后创建一个简单的GiST索引，要么创建一个实用的GiST索引，如：

CREATE INDEX idx_twitter_point_4326
ON twitter  USING gist (ST_SetSRID(ST_MakePoint(x_coords,y_coords), 4326));

另一个出现在poi上

CREATE INDEX idx_poi_wkb_geometry_4326
ON poi USING gist (ST_SetSRID(p.wkb_geometry, 4326));

这应该用来加速ST_Within()

此函数调用将自动包含一个边框比较，该比较将利用几何图形上可用的任何索引。

距离()函数可能更适合您：

UPDATE twitter t
SET    poi = p.type_name
FROM   poi p
WHERE  ST_Distance(ST_SetSRID(ST_MakePoint(t.x_coords, t.y_coords), 4326)
                  ,ST_SetSRID(p.wkb_geometry, 4326)) < 0.0005
AND    t.poi IS DISTINCT FROM p.type_name;

这是假设在一个poi条目附近最多可以有一个twitter。否则，您应该使用一个子查询，为每个poi选择最接近的twitter。

附加的WHERE子句t.poi IS DISTINCT FROM p.type_name避免空更新。

我自己不使用PostGis，所以没有经过测试。