[postgis-users] Getting data from table with interval

[Paul Ramsey]

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 <lucabert at 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
> (lucabert at lucabert.de)
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users