[postgis-users] Getting data from table with interval

[Luca Bertoncello]

Am 07.01.2022 um 21:55 schrieb Luca Bertoncello:

> Does someone have an idea?

OK, I got it with a function:

CREATE OR REPLACE FUNCTION getElevationsOnLine(startPoint geometry,
endPoint geometry, pointsInterval int)
RETURNS TABLE (
  lat DOUBLE PRECISION,
  lng DOUBLE PRECISION,
  elevation REAL,
  dist DOUBLE PRECISION
)
LANGUAGE plpgsql
AS $$
DECLARE
  point geometry;
BEGIN
  FOR point IN
    SELECT (ST_DumpPoints(ST_Segmentize(ST_MakeLine(startPoint,
endPoint)::geography, pointsInterval)::geometry)).geom
  LOOP
    RETURN QUERY
      SELECT elevation2x2.lat, elevation2x2.lng, elevation2x2.elevation,
elevation2x2.latlng <-> point AS dist
      FROM elevation2x2
      ORDER BY dist LIMIT 1;
  END LOOP;
END;
$$;

SELECT * FROM getElevationsOnLine(ST_Point(14.1275, 51.297),
ST_Point(13.768, 51.134333333333), 1000);

it seems to work...

Have a nice weekend!
Luca Bertoncello
(lucabert at lucabert.de)