elevation_by_contours.sql

with params as (
    select 'SRID=4326;POINT(0 0)' :: gemetry as geom
)
select
    a.altitude,
    a.distance,
    b.altitude, -- may be null, then do not interpolate
    b.distance -- may be null, then do not interpolate
from (-- closest linestring
         select
             c.*,
             +ST_Distance(c.geom :: geography, p.geom :: geography) as distance
         from el_contour_1to5m c, params p
         order by c.geom <-> p.geom
         limit 1
     ) a
    -- left join, as we can be in peak or in lowest area
    left join lateral (
              select
                  c.*,
                  ST_Distance(c.geom :: geography, p.geom :: geography) as distance
              from el_contour_1to5m c, params p
              where not exists(
                  select
                  from el_contour_1to5m j
                  where j.altitude = a.altitude
                        and ST_Intersects(
                            j.geom,
                            ST_ShortestLine(p.geom, c.geom) -- may not work on non-convex isolines :(
                        )
              )
              order by c.geom <-> p.geom
              limit 1
              ) b on true;