3

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.

2

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.