[postgis-users] Getting data from table with interval
pramsey at cleverelephant.ca
Fri Jan 7 11:53:17 PST 2022
You have an index on the geography, but you cast to geometry in your distance calc, so the index helps you not at all.
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
More information about the postgis-users