You have an index on the geography, but you cast to geometry in your distance calc, so the index helps you not at all.
Do this SELECT *, ST_Distance(latlng, '0101000020E6100000E17A14AE47412C40BC74931804A64940') AS dist FROM elevation ORDER BY dist LIMIT 5; > On Jan 7, 2022, at 11:18 AM, Luca Bertoncello <lucab...@lucabert.de> wrote: > > 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 > (lucab...@lucabert.de) > _______________________________________________ > postgis-users mailing list > postgis-users@lists.osgeo.org > https://lists.osgeo.org/mailman/listinfo/postgis-users _______________________________________________ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users