# Find closest location to given point within distance

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;
``````

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.

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
``````
• I know about "nearest neighbor" but I need limit distance, because I have to find out nearest road to given point but if nearest road is too far away I should ignore it. – abonec Aug 5 '16 at 19:39