如何从存储表中从存储位置(纬度，经度)找到附近的商店?

Question

我有一个名为stores的Postgresql表，其中包含商店的位置(lat, long)，我可以使用查询从商店找到附近的商店。但是，我找不到一个查询来创建一个"就绪"生成的表，该表为每个商店创建附近商店的列表。这是我用来获取附近商店列表的查询:

select mds.id, mds.store_name
from public.store mds,
(select latitude, longitude from public.store where id = '3f6077c0-c56b-4570-883f-4c16dc19855e') as st,
sqrt(111.12 * (mds.latitude - st.latitude) * 111.12 * (mds.latitude - st.latitude) + (111.12 * (mds.longitude - st.longitude) * cos(st.latitude / 92.215)) * (111.12 * (mds.longitude - st.longitude) * cos(st.latitude / 92.215))) as distance
where distance <= 20
order by distance
limit 100

我无法用public.store.id替换'3f6077c0-c56b-4570-883f-4c16dc19855e'。存储表的表列为:

| id  | store_name  | latitude   |  longitude  |

请帮我这个请求。非常感谢。

Answer 1

使用扩展PostGIS更好地处理空间查询。它有很多非常方便的函数，使得空间查询非常容易编写和维护。我的建议:

安装Postgis(参见另一个答案)

在表中添加一个几何列，例如

SELECT AddGeometryColumn ('public','store','geom',4326,'POINT',2);

根据经度和纬度值创建点几何图形:

UPDATE store SET geom = ST_MakePoint(longitude,latitude);

索引它(使查询更快)

CREATE INDEX idx_store_geom ON store USING gist (geom);

之后，列出给定点的最近邻居的查询是这样的:

SELECT * FROM store
ORDER BY geom <-> ST_SetSRID(ST_MakePoint(92.215,111.12),4326)

或者如果你想要离每个商店最近的商店..

SELECT * FROM store mds,
LATERAL (SELECT store_name,ST_Distance(geom,mds.geom) FROM store
WHERE id <> mds.id
ORDER BY geom <-> mds.geom
LIMIT 1) c (closest_store,distance);

• 操作符<->代表距离，因此在ORDER BY子句中使用LIMIT 1只选择最接近参考几何的记录。
• 4326表示空间参考系统WGS84。它可能会根据你的坐标而变化。

Demo:db<>fiddle