Find locations within distance of multiple locations-CodePudding

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?

CodePudding user response：

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