PostGIS：计算一个点和 2 个 fks 之间的最小距离：起点和终点

发布于 2024-11-15 15:24:08 字数 551 浏览 3 评论 0原文

我有一个表 Transport，其中有 2 个 fks 指向表 Spot。这些 fks 存储传输的起点和目的地。

我需要执行一个查询来获取点与原点之间以及同一点与目的地之间的最小距离。

这就是我所做的，虽然这给了我 34 个结果，但它应该是 17 个。我知道我做错了，但我无法得到正确的查询：

SELECT LEAST(
   ST_Distance(ST_GeographyFromText('SRID=4326; POINT(-3 40)'), s.point), 
   ST_Distance(ST_GeographyFromText('SRID=4326; POINT(-3 40)'), s.point)
) FROM spot s RIGHT OUTER JOIN transport t 
ON t.origin = s.id OR t.destination = s.id;

显然我不应该在这里使用 OR。我尝试过双重连接，但没有成功。

感谢您的帮助

原文

I have a table Transport that has 2 fks pointing a table Spot. those fks store origin and destination of the transport.

I need to do a query that gets the minimum distance between a point and origin and between the same point and destination.

This is what I have done, though this gets me 34 results and it should be 17. I know I'm doing it wrong, but I can't get the query right:

SELECT LEAST(
   ST_Distance(ST_GeographyFromText('SRID=4326; POINT(-3 40)'), s.point), 
   ST_Distance(ST_GeographyFromText('SRID=4326; POINT(-3 40)'), s.point)
) FROM spot s RIGHT OUTER JOIN transport t 
ON t.origin = s.id OR t.destination = s.id;

Obviously I shouldn't be using an OR here. I have tried to do double JOINs, but didn't get it right.

Thanks for your help

分享到QQ

分享到微博

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

发布评论

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

白鸥掠海 2024-11-22 15:24:08

您必须使用两个联接，因为您的查询需要一个表示起点的表 Spot 和一个表示目的地的表 Spot。

尝试这样：

SELECT LEAST(
   ST_Distance(ST_GeographyFromText('SRID=4326; POINT(-3 40)'), sOrigin.point), 
   ST_Distance(ST_GeographyFromText('SRID=4326; POINT(-3 40)'), sDestination.point)
) FROM transport t
LEFT OUTER JOIN spot sOrigin ON t.origin = sOrigin.id 
LEFT OUTER JOIN spot sDestination ON t.destination = sDestination.id;

You have to use two join, because your query need a table Spot meaning Origin, and a table Spot meaning Destination.

Try like this :

SELECT LEAST(
   ST_Distance(ST_GeographyFromText('SRID=4326; POINT(-3 40)'), sOrigin.point), 
   ST_Distance(ST_GeographyFromText('SRID=4326; POINT(-3 40)'), sDestination.point)
) FROM transport t
LEFT OUTER JOIN spot sOrigin ON t.origin = sOrigin.id 
LEFT OUTER JOIN spot sDestination ON t.destination = sDestination.id;

回复收藏 0 原文

~没有更多了~