Experiment with Elevation API for slope

[EPajares]

https://open-elevation.com/

[EPajares]

This would be especially important for the Munich version as we don't have a good DEM here.

[EPajares]

This project might be more active: https://github.com/ajnisbet/opentopodata

[EPajares]

There is promising way to get points on linestring to derive more point for getting a more accurate band of slope:

SELECT st_linefrommultipoint( ST_Lineinterpolatepoints(geom,0.01)) FROM ways WHERE id = 247933

[EPajares]

After doing further research on it: It could be more convenient to have a built in database function that derives the elevation based on interpolation of nearby measurements. Some common methods are Inverse Distance Weigthed Method and the more computational expensive Kriging. I checked for built in PostGIS-Functions and found this one: https://postgis.net/docs/RT_ST_InvDistWeight4ma.html Though I am not really sure what it does. In Stackoverflow (read comments https://gis.stackexchange.com/questions/218207/querying-elevation-data-in-postgis) it was indicated that this is not really suitable for the interpolation needed.

As discussed with @majkshkurti we could do an own interpolation method for this task. We could try to have an algorithm doing the following:

1. Get points on linestring in a certain interval (e.g. 10 m): x----x----x--x

Table could look something like this way_id	id	length	geom
1	1	10	point
1	2	10	point
1	3	10	point
1	4	6	point

2. Define for each of the points (x) the 3 closest measurements from the dem-raster:

   o(x,y,z)                       
                        x               o(x,y,z)
   
               o(x,y,z)
   

3. Save the sets of three and get all points (x) that have the them as closest:

way_id	id	length	x_y_point	x_y_z_dem
1	1	10	[x,y]	[[x,y,z],[x,y,z],[x,y,z]]
1	2	10	[x,y]	[[x,y,z],[x,y,z],[x,y,z]]
1	3	10	[x,y]	[[x,y,z],[x,y,z],[x,y,z]]
1	4	6	[x,y]	[[x,y,z],[x,y,z],[x,y,z]]

4. Group by x_y_z_dem and then way_id

way_id	ids	lengths	x_y_points	x_y_z_dem
1	[[1,2],[3,4]]	[[10,10],[10,6]]	[[[x,y],[x,y]],[[x,y],[x,y]]]	[[[x,y,z],[x,y,z],[x,y,z]],[[x,y,z],[x,y,z],[x,y,z]]]

5. Pass values to highly performant C-database-function that return the slope profile for the link:

compute_slope_profile(ids integer[][], lengths float[][], x_y_points float[][], x_y_z_dem float[][][])

6. Save slope_profile in the ways table

7. Compute slope_impedance

Some links: https://gis.stackexchange.com/questions/83470/choosing-idw-vs-kriging-interpolation-for-dem-creation https://www.youtube.com/watch?v=J-IB4_QL7Oc https://www.youtube.com/watch?v=3eoEWcxXBNs

[EPajares]

With the last commit in scenario_labelling I pushed an implementation that is computing slopes using IDW-Method. Due to time constraints I have done the implementation is SQL + PL/pgSQL instead of C. Therefore performance is not very good but can be improved further. The results still need to be tested and performance needs to be improved but for now it should be fine. Therefore this issue will be closed.