Analytics Toolbox for BigQuery

Analytics Toolbox for BigQuery

Go back

Interpolating elevation along a road using kriging

In this example, we will perform kriging interpolation of the elevation along the so-called roller coaster road on the island of Hokkaido, Japan, using as reference points a nearby elevation measurement.

Reference elevation data

The elevation data that will be used as reference to perform the interpolation is composed of 50 elevation points within 500m of the route that were randomly sampled from the NASADEM dataset, available from CARTO’s Data Observatory. This data can be accessed publicly from the table cartobq.docs.nasadem_jp_extract and is previewed in the map below:

Points to be interpolated

Since the route is straight, we can easily calculate evenly distributed points along the route and use them as the points for which to perform the interpolation. The result can be publicly accessed from cartobq.docs.kriging_jp_interp_points.

1
2
3
4
5
SELECT
  ST_GEOGPOINT(142.41*p+142.44*(1-p),43.496*p+43.53*(1-p)) point
FROM
  # generate 101 points evenly spaced along the route
  UNNEST(GENERATE_ARRAY(0,1,0.01)) p;

Computing the interpolation

Now let’s use the kriging interpolation stored procedure, ORDINARY_KRIGING_TABLE to create a table with the result.

1
2
3
4
5
6
7
8
9
CALL `carto-un`.carto.ORDINARY_KRIGING_TABLE(
         'cartobq.docs.nasadem_jp_extract',
         'cartobq.docs.kriging_jp_interp_points',
         'cartobq.docs.kriging_jp_result',
         50,
         1000,
         20,
         'exponential'
     );

In this visualization we can see the computed elevation along the route accentuated by a factor of 10.