[postgis-users] Getting data from table with interval

[Luca Bertoncello]

Am 07.01.2022 um 20:01 schrieb Luca Bertoncello:

Hi again

> 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...

So, I found a query to get the nearest point:

SELECT *, ST_Distance(latlng::geometry,
'0101000020E6100000E17A14AE47412C40BC74931804A64940') AS dist FROM
elevation ORDER BY dist LIMIT 5;

this get the 5 nearest points to a given coordinate.
It works, but it works 17 seconds to return the result...
If I have to get all nearest points for all coordinates in many lines,
the database works for hours...

I'm sure, there is a better ways, but I don't know it...

Any suggestion?

Thanks a lot
Luca Bertoncello
(lucabert at lucabert.de)