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.