如何在同一查询中多次使用限制1语句的顺序？

发布于 2025-01-23 07:56:20 字数 1097 浏览 0 评论 0原文

我已经下载了 openstreetMap数据来自geofabrik 我们可以在其中找到有关道路几何形状的信息。我知道，如果我有一个随机点，并且需要看到我从中拥有的最近路，我可以在PostgreSQL上使用以下查询：

SELECT way, osm_id 
FROM osm AS osm 
ORDER BY osm.way <-> 'SRID=3857;POINT(-3890646.5744145643 -899377.0801662721)'::GEOMETRY LIMIT 1 ;

在这里，我已经对点进行了硬编码point（-3890646.57444145643 -89999377.0801662721）。此查询功能性，并且返回结果：

它发现了哪条路是我观点最接近的道路，并给了我这条路的OSM_ID。这里的问题在于，我想从另一个表格上获得多个几何形状，而不仅仅是一个。由于此查询需要使用限制1才能工作。我可以使用哪种方法来对我在dields_table上拥有的每个点几何形状运行相同的查询？

我可以通过使用编程语言访问PostgreSQL来了解如何解决此问题他们在使用我的第一个查询并逐一获得结果时。但是我不确定这将是理想的解决方案。我想要一个仅使用postgresql的解决方案...我该怎么做？我如何使用限制1语句多次使用订单，并对存在不同表中存在的每个几何形状执行（每行都是不同的点，例如'srid = 3857; point（-3890646.57444145643 -89999377.0808016662721）'' ::几何）？

原文

I've downloaded OpenStreetMap data from Geofabrik where we can find information about road geometries. I know that if I have a random point and I need to see the closest road that I have from it I can use the following query on PostgreSQL:

SELECT way, osm_id 
FROM osm AS osm 
ORDER BY osm.way <-> 'SRID=3857;POINT(-3890646.5744145643 -899377.0801662721)'::GEOMETRY LIMIT 1 ;

Here I have hardcoded the point POINT(-3890646.5744145643 -899377.0801662721). This query is functional and it returns the result:

It finds out which is the closest road from my point and gives me the osm_id of this road. The problem here is that I'd like to get points from a different table where I'll have multiple geometries of points, not just a single one. Since this query requires the use of LIMIT 1 in order to work. What approach could I use to run this same query for each point geometry that I have on a different_table?

I can see how I'd solve this problem by accessing PostgreSQL with a programming language since I'd be able to make a query to get all text points from different_table and then I'd loop through all of them while using my first query and getting its result one by one. But I'm not sure that it would be the ideal solution. I'd like a solution where I use only PostgreSQL... How can I do it? How can I use the ORDER BY with LIMIT 1 statements multiple times and execute it for each geometry that exists in a different table (each row would be a different point like 'SRID=3857;POINT(-3890646.5744145643 -899377.0801662721)'::GEOMETRY)?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

笑叹一世浮沉 2025-01-30 07:56:20

您需要一个横向连接，该连接可以为另一个表的每一行执行一个子查询：

SELECT diff_id, way, osm_id, osm.way <-> diff_geog as distance
FROM different_table cross join lateral (
    select way, osm_id from osm  
    ORDER BY osm.way <-> diff_geog LIMIT 1
) osm ;

You need a lateral join, which executes one subquery once for each row of the other table:

SELECT diff_id, way, osm_id, osm.way <-> diff_geog as distance
FROM different_table cross join lateral (
    select way, osm_id from osm  
    ORDER BY osm.way <-> diff_geog LIMIT 1
) osm ;

回复收藏 0 原文

~没有更多了~