1

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.

2

Here is a nice quick solution,

WITH c AS (
    SELECT color_column, shape_column FROM table_name GROUP BY color_column, shape_column
)
SELECT 
  c.color_column, 
  c.shape_column,
  (
   SELECT other_column 
   FROM table_name 
   WHERE 
      color_column = c.color_column AND
      shape_column = c.shape_column 
   ORDER BY the_geom <-> starting_geom 
   LIMIT 1
  ) other  
FROM c 
  • 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 '13 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. – andrewxhill Dec 29 '13 at 21:10

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.