[GIS] Find closest location to given point within distance

postgispostgresqlsql

I have table location with column geo_data with DDL:

CREATE TABLE location
(
    id INTEGER PRIMARY KEY NOT NULL,
    geo_data GEOGRAPHY(GEOMETRY,4326),
);
CREATE INDEX index_location_on_geo_data ON location USING GIST (geo_data);

And I use this query to find nearest location to given point:

SELECT ST_AsText(location.geo_data), st_distance(ST_GeomFromText('POINT(37.611100 55.756926)', 4326), location.geo_data) as distance
FROM location
WHERE ST_Dwithin(ST_GeomFromText('POINT(37.611100 55.756926)', 4326), location.geo_data, 1200)
ORDER BY distance
LIMIT 1;

And it works great but I confused by two issues about this query:

  1. There is no functions about to just find one closest location to given point and I should use ST_Dwithin, sort by distance and limit by 1;
  2. In order to work around above limitation I should define my point twice (first in select statement in order to sort result set and second in where clause within function ST_Dwithin). And this fact of duplication is also confusing me.

Is there any solutions to eliminate this drawbacks? Or maybe make this solution more faster.

Best Answer

You have the solution there. If you want a "nearest neighbor without a tolerance" you can use the <-> operator instead like this:

SELECT * FROM location
ORDER BY geo_data <-> ST_GeogFromText('POINT(37.611100 55.756926)')
LIMIT 1

That will give you the one nearest point, regardless of how far away it might be. This can be useful for data sets that are quite variable in their feature density, where a cut-off radius might be hard to set in advance.

If you want to avoid the duplicate input points, you can just stick the input into a CTE on top of the query like this:

WITH pt AS (
  SELECT ST_GeogFromText('POINT(37.611100 55.756926)') AS geo_data
)
SELECT 
  ST_AsText(location.geo_data),   
  ST_Distance(pt.geo_data, location.geo_data) AS distance
FROM location
JOIN pt
ON ST_Dwithin(pt.geo_data, location.geo_data, 1200)
ORDER BY distance
LIMIT 1
Related Question