Am 07.01.2022 um 20:35 schrieb Paul Ramsey: Hi Paul
> Use the nearest neighbor operator > > https://www.postgis.net/workshops/postgis-intro/knn.html Even too long... About 16 seconds... I think I should create an index? gis=# explain analyze SELECT *, latlng::geometry <-> '0101000020E6100000E17A14AE47412C40BC74931804A64940' AS dist FROM elevation2x2 ORDER BY dist LIMIT 5; QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=36934.26..36934.84 rows=5 width=60) (actual time=16980.924..16993.938 rows=5 loops=1) -> Gather Merge (cost=36934.26..185732.21 rows=1275322 width=60) (actual time=16980.922..16993.931 rows=5 loops=1) Workers Planned: 2 Workers Launched: 2 -> Sort (cost=35934.23..37528.39 rows=637661 width=60) (actual time=16415.051..16415.054 rows=4 loops=3) Sort Key: (((latlng)::geometry <-> '0101000020E6100000E17A14AE47412C40BC74931804A64940'::geometry)) Sort Method: top-N heapsort Memory: 26kB Worker 0: Sort Method: top-N heapsort Memory: 25kB Worker 1: Sort Method: top-N heapsort Memory: 25kB -> Parallel Seq Scan on elevation2x2 (cost=0.00..25342.91 rows=637661 width=60) (actual time=5.866..12833.383 rows=510129 loops=3) Planning Time: 0.174 ms Execution Time: 16994.001 ms (12 Zeilen) gis=# \d elevation2x2 Tabelle »public.elevation2x2« Spalte | Typ | Sortierfolge | NULL erlaubt? | Vorgabewert -----------+------------------+--------------+---------------+------------- latlng | geography | | | lat | double precision | | | lng | double precision | | | elevation | real | | | Indexe: "idx_elevation2x2" gist (latlng) > Do you have a link to your source data? The table of elevations? Well, this was a very hard job... I got the EU-DEM data from https://land.copernicus.eu/imagery-in-situ/eu-dem/eu-dem-v1.1?tab=download Then I converted the 38GB TIFFs in an SQL database. First of all, I reduced the precision, since 25 meter is for flying too much precision. One or two kilometer is enough. With gdalwarp I reduced the precision to 2 km, then I wrote a Python script to read the data from the reduces TIFF and create an SQL file to import in the database. Last but not least, since I need to import the data in my App, splitted by country, I create a table joining the data from elevation with the ways of all countries in Europe I calculated from OpenStreetMaps. It works, and I have in my App the vertical profile of the fly... Now I wanted to have an API to the data to use them in another program to plan the flights... Regards Luca Bertoncello ([email protected]) _______________________________________________ postgis-users mailing list [email protected] https://lists.osgeo.org/mailman/listinfo/postgis-users
