I'm using the indexed nearest neighbor search to return the closest points to a given starting location. My points also have attributes like color and shape, stored as strings. I want each query to only return the closest point for each type of point.

So if my points were, in order of distance to my starting location:

  • red square
  • red square
  • red circle
  • orange square
  • blue circle

The query for the four closest points would return:

  • the closest red square
  • red circle
  • orange square
  • blue circle

I tried using DISTINCT but it's asking to give the DISTINCT column in the ORDER BY and I haven't had success in doing that.

1 Answer 1


Here is a nice quick solution,

    SELECT color_column, shape_column FROM table_name GROUP BY color_column, shape_column
   SELECT other_column 
   FROM table_name 
      color_column = c.color_column AND
      shape_column = c.shape_column 
   ORDER BY the_geom <-> starting_geom 
   LIMIT 1
  ) other  
  • Thanks. That'll get me results for distinct colors but I'm interested in getting distinct color and shape combinations. I've edited the question to flesh out that distinction with the red circle example. I tried adding a second WITH clause and an AND to the conditional but I get syntax error near c. I'm confused as to how c is getting defined.
    – mhkeller
    Dec 29, 2013 at 0:27
  • I've edited my result. You can group by whatever number of columns you want distinct answers for. The 'c' alias was from something like "SELECT * FROM table_name c", where I aliased the table name, I've removed it just to avoid confusion. Dec 29, 2013 at 21:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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