[postgis-users] Getting data from table with interval

[Luca Bertoncello]

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
(lucabert at lucabert.de)