[postgis-users] Getting data from table with interval

[Luca Bertoncello]

Am 07.01.2022 um 19:28 schrieb Martin Davis:

Hi Martin,

> How about this for an approach?
> 
> - Create a line from the start and end point using ST_MakeLine
> - Densify it using ST_Segmentize (e.g. to a1 km interval)
> - Extract the individual points using ST_DumpPoints
> - For each point, query to find the nearest elevation point

Very good idea!
So, I try to calculate the line between airport Dresden
(51.134333333333, 13.768) and airfield Kamenz (51.297, 14.1275).
This is my query:

SELECT ST_DumpPoints(
  ST_Segmentize(
    ST_MakeLine(
      ST_Point(14.1275, 51.297),
      ST_Point(13.768, 51.134333333333)
    )::geography,
    1000
  )::geometry
);

I get 33 points as result, and since the distance between the both
aerodromes is about 31 km, it could be correct...

Now I'm not very sure how to get the nearest point in the table for all
these points...
Maybe it's just friday, but I don't know how to do that...
I'm sure, I have to use ST_Distance, but I cannot think the query to get
the table entry closest to a point...

Thank your very much!
Luca Bertoncello
(lucabert at lucabert.de)