# Find nearest distinct features to a point

I am trying to select a single distinct features closest to an XY point in PostGIS.

So I can find a single record for each distinct code by using:

``````Select Distinct ON (code) * FROM table;
``````

Which returns 100 records, out of 1mill, one for each distinct code.

I can then use the PostGIS `<->` operator to find the closest feature:

``````SELECT name, gid
FROM geonames
ORDER BY geom <-> st_setsrid(st_makepoint(-90,40),4326)
LIMIT 1;
``````

So I need to use both of these queries together to find a feature of each distinct code but the closest one to the point.

How can I use both queries together?

I have tried:

``````SELECT 1 FROM table WHERE EXISTS
(SELECT DISTINCT ON (code) * FROM table) ORDER BY geom <-> st_setsrid(st_makepoint(-90,40),4326)
LIMIT 1;
``````

Also this fails:

``````SELECT * FROM table WHERE code IN
(SELECT DISTINCT ON (code) * FROM table) ORDER BY geom <-> st_setsrid(st_makepoint(-90,40),4326)
LIMIT 1;
``````

None of which works.

I haven't got a installation of postgis to test this on, but something like the following should work.

``````SELECT *
FROM (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY code ORDER BY ST_Distance(geom, ST_SetSRID(ST_MakePoint(-90,40),4326)) RN
FROM Table
) Closest
WHERE RN = 1;
``````
• Unfortunately this does not work. It wants ST_Distance to have st_distance(geometry, geometry, integer), that final integer I do not know. – tjmgis Sep 3 '14 at 7:28
• I'll edit the answer. I looks like it requires a boolean to indicate if a spherical calc should be used. postgis.refractions.net/docs/ST_Distance.html – MickyT Sep 3 '14 at 9:28
• looks like I got your SQL working. ST_Distance did not seem to like have a st_makepoint, so I saved the point into its own table and then was able to do ST_Distance(table.geom, point.geom) Whizzed through 40mill records in 37mins!! Thanks – tjmgis Sep 3 '14 at 14:54
• Seen what the problem was likely to be with the make point. Have fixed – MickyT Sep 3 '14 at 18:40