Find nearest distinct features to a point

Question

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.

Answer 1

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;