在多个位置的距离内找到位置

发布于 2025-02-13 05:52:16 字数 929 浏览 0 评论 0原文

我正在尝试运行一个查询，该查询将找到任何给定位置的给定距离的位置。这是用于搜索结果，用户可以选择多个位置进行查看。

我当前的方法是使用st_closestpoint并传递st_point在PHP中生成的数组。然后，我将该字符串传递到st_collect。

$points = $locations->map(function ($location) {
    return sprintf('ST_Point(%s, %s)', $location->longitude, $location->latitude);
})->implode(', ');

SELECT *
FROM listing_locations
WHERE ST_DWithin(
    coordinate,
    ST_ClosestPoint(coordinate, ST_Collect(Array[%s]),
    1000, 
FALSE)

但是，这是行不通的，因为它看起来像st_closestpoint不喜欢这些混合参数：

SQLSTATE[42883]: Undefined function: 7 ERROR:  function st_closestpoint(geometry, geography) does not exist

我有gist（coortion :: geagraphy）似乎很有用。

我缺少什么 - 有更好的方法可以做到这一点，还是这是一种不好的方法？我应该每次都在其他位置执行查询吗？

原文

I'm trying to run a query that will find locations with a given distance of any of the given locations. This is for a search result where users can select multiple locations to look around.

My current approach is to use ST_ClosestPoint and pass in an array of ST_Point generated in PHP. Then I pass that string into ST_Collect.

$points = $locations->map(function ($location) {
    return sprintf('ST_Point(%s, %s)', $location->longitude, $location->latitude);
})->implode(', ');

SELECT *
FROM listing_locations
WHERE ST_DWithin(
    coordinate,
    ST_ClosestPoint(coordinate, ST_Collect(Array[%s]),
    1000, 
FALSE)

However, this doesn't work because it looks like ST_ClosestPoint doesn't like these mixed arguments:

SQLSTATE[42883]: Undefined function: 7 ERROR:  function st_closestpoint(geometry, geography) does not exist

I have a gist(coordinate::geography) index on listing_locations which seems like it would be useful to use.

What am I missing - is there a better way to do this, or is this a bad approach? Should I be performing the query each time with a different location?

分享到QQ

分享到微博

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

发布评论

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

ま柒月 2025-02-20 05:52:16

要使事情变得更简单，将st_collect放在子查询或CTE中，在外部查询中使用st_dwithin与新创建的多点，列coortion以及给定的距离，例如

WITH j (points) AS (
  SELECT 
   ST_Collect( 
    (ST_SetSRID(ST_MakePoint(-81.70361, 28.62167), 4326)),
    (ST_SetSRID(ST_MakePoint(-81.70365, 28.62169), 4326))
   )::geography
)
SELECT *, ST_Distance(j.points,coordinate) AS distance
FROM listing_locations
JOIN j ON ST_DWithin(j.points,coordinate,100,false);

或将其放入空间连接（以恕我基本不阅读）

SELECT *, ST_Distance(j.points,coordinate) AS distance
FROM listing_locations
JOIN (SELECT
       ST_Collect( 
        (ST_SetSRID(ST_MakePoint(-81.70361, 28.62167), 4326)),
        (ST_SetSRID(ST_MakePoint(-81.70365, 28.62169), 4326))
       )::geography) j (points)
       ON ST_DWithin(j.points,coordinate,100,false);

演示中：

To make things simpler put the ST_Collect in a subquery or CTE, and in the outer query use ST_DWithin with the newly created MultiPoint, the column coordinate and a given distance, e.g.

WITH j (points) AS (
  SELECT 
   ST_Collect( 
    (ST_SetSRID(ST_MakePoint(-81.70361, 28.62167), 4326)),
    (ST_SetSRID(ST_MakePoint(-81.70365, 28.62169), 4326))
   )::geography
)
SELECT *, ST_Distance(j.points,coordinate) AS distance
FROM listing_locations
JOIN j ON ST_DWithin(j.points,coordinate,100,false);

Or put it in a spatial join (imho less readable)

SELECT *, ST_Distance(j.points,coordinate) AS distance
FROM listing_locations
JOIN (SELECT
       ST_Collect( 
        (ST_SetSRID(ST_MakePoint(-81.70361, 28.62167), 4326)),
        (ST_SetSRID(ST_MakePoint(-81.70365, 28.62169), 4326))
       )::geography) j (points)
       ON ST_DWithin(j.points,coordinate,100,false);

Demo: db<>fiddle

回复收藏 0 原文

~没有更多了~