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?

geozelot
  • 30,050
  • 4
  • 32
  • 56
Bastiaan Wakkie
  • 123
  • 3

1 Answers1

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:

geozelot
  • 30,050
  • 4
  • 32
  • 56