2

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?

Improve this question

1 Answer 1

Reset to default
2

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

SELECT sp.*,
       cnt.elevation
FROM   species AS sp
CROSS JOIN LATERAL (
  SELECT elevation
  FROM   shp_opendem AS od
  ORDER BY
         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:

Improve this answer

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.