I have two tables: one with geom points of species and an other one with height contour lines geoms. My Idea was to find per specie geom the closest point to a contour 'geom' line and then select its elevation. So I can find out between which elevations I can find a specific species

My though was the following:

SELECT od.elevation
FROM shp_opendem od
     JOIN species sp ON ST_ClosestPoint(sp.geom, od.geom)
WHERE sp.name = 'somename'

This isn't running as ST_ClosestPoint is not a boolean which is required in a join. The exact error message:

ERROR:  argument of JOIN/ON must be type boolean, not type geometry

Any tips how to solve this?


You are looking for a (K) Nearest Neighbor search with one result per input:

SELECT sp.*,
FROM   species AS sp
  SELECT elevation
  FROM   shp_opendem AS od
         sp.geom <-> od.geom
  LIMIT  1
) AS cnt
-- WHERE sp.name = 'somename'

This will find the closes contour to each point and returns its od.elevation along with all columns of the species table.

The special <-> distance operator is highly optimized for index driven distance searches when used in the ORDER BY expression; make sure you have a spatial index in place on your tables, and ran table maintenance prior to this query!

For more info on the overall concept:

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.