postgis - 如何按最近点查询postgis数据并只返回该点的结果？

Question

我有一个包含 4.6 亿条记录的 postgis 表。它有一个时间戳和点列。

我正在基于这些数据构建图表，属于最近点的每个时间戳的值列表，传单将纬度/经度从地图（用户单击的位置）发送到生成图表就绪数据的脚本。

SELECT thevalue 
FROM thetable 
WHERE ST_DWithin (thepoint, ST_MakePoint($get_lon, $get_lat), 0.04) 
ORDER BY thedate 
LIMIT 1000

这很好用（对于一些点击），但必须有更好/更快的方法，我希望查询知道要听什么点并且只返回那个点的值。这个要求有更好的功能吗？

标签： postgis

Answer 1

你有什么几何之王？你用的是什么投影？我将假设您的观点在 wgs84 (epsg:4326)

如果您希望距离准确，最好在计算中使用地理：

alter points_table add column geog geography
update points_table set geog = geom::geography

创建索引，运行cluster和analyze加速查询

create index my_index_geog on points_table using gist(geog) /* change geog for geom if using geometry */
cluster points_table using my_index_geog
analyze points_table

获得最近的点：

SELECT point_id 
FROM points_table
ORDER BY geog <-> ST_SetSrid(ST_MakePoint($get_lon, $get_lat),4326)::geography limit 1;

一起获得值：

select value
from table
where point_id = (SELECT point_id 
FROM points_table
ORDER BY geog <-> ST_SetSrid(ST_MakePoint($get_lon, $get_lat),4326)::geography limit 1)
order by thedate
limit 1000;

另外，我建议保留一个仅包含点 id 和几何/地理的表，以便最近点查询运行得更快。如果您创建这样的表，称为only_points，则查询变为：

select value
from table
where point_id = (SELECT point_id 
FROM only_points
ORDER BY geog <-> ST_SetSrid(ST_MakePoint($get_lon, $get_lat),4326)::geography limit 1)
order by thedate
limit 1000;

如果您需要继续使用geometry，那么您需要在几何上创建索引，基于集群geom并运行查询：

select value
from table
where point_id = (SELECT point_id 
FROM points_table
ORDER BY geom::geography <-> ST_SetSrid(ST_MakePoint($get_lon, $get_lat),4326)::geography limit 1)
order by thedate
limit 1000;

但是，它会更慢，因为您将在每一步都转换为地理

请参阅Postgis 和PostGIS 地理类型和索引中的 KNN