KNMI derived temperature with Inverse Distance Weighting in Oracle SQL
In this article we will build upon an earlier post where we outlined how you can calculate the distance between geographical coordinates using the Haversine formula using pure Oracle SQL. In this example we used a dataset which contains the synoptic meteorological observations provided by the Royal Netherlands Meteorological Institute (KNMI) on a 10 minute basis via a combination of MQTT events and REST APIs.
In this dataset which we perstisted in an Oracle Autonomous database in Oracle Cloud we have a value named ‘ta’ which holds Ambient Temperature 1.5m 10 Min Average. This means that we have a Ambient Temperature datapoint on a per 10 minute basis for each individual weather station which was able to report this value back the Royal Netherlands Meteorological Institute (KNMI)
What we attempt to do in this post is to derive a ambient temperature for any given location (within the Netherlands) on any given moment (which falls within the persisted dataset). For this we will use a combination of the haversine formula and inverse distance weighting, a multivariate interpolation method for value estimation.
Select timeboxed KNMI data with SQL
We need to make sure we get a base dataset which is based upon a given time in an EPOCH…
