A.Thakur A.Thakur - 25 days ago 8
SQL Question

Creating spatial interpolation function

I have got a huge table with data as

(ID, LATITUDE, LONGITUDE, SPEED, TIME)


where time contains information about
ID's
position
(LAT, LON)
after every few seconds.

For example:

at 08:42:05 PM, 08:42:15 PM, 08:42:18 PM etc.

I want to apply linear interpolation algorithm to find ID's position at time that doesn't exist in database (for eg: at 08:42:07 PM).

Is there an elegant method in SQL or PL/SQL to create these interpolation functions?

Is there any other efficient spatial interpolation method that can be applied for more efficient results?

Edit: Click here for the table (Expected results are marked in bold and can vary based on the interpolation function used).

Answer

Here is a solution that works for any time that is strictly greater than the least time in the table and less than or equal to the greatest time in the table (for that given id).

:id and :time are bind variables. In SQL Developer or Toad you'll get a pop-up window asking you for values for the bind variables. For :id you can enter 1 and for :time you enter something like 2016-04-30 23:15:33 (no single quotes - the input is automatically interpreted as a string); the code applies to_number() around :id and to_date() around :time, with the proper date format model.

For my sample data (in the positions table at the top), the only valid :id is 1, and note that I entered the times in reverse order (to model the fact that rows are unordered); when you test, the time must be greater than the time in the SECOND row and less than or equal to the time in the first row.

The first trick is to insert a row for the input time (:time) into the table, with NULL for lat and lon. The rest is pretty standard SQL, using the analytic functions lag() and lead() and interpolation formulas.

with positions (id, lat, lon, time) as (
       select 1, 30.200, 39.294, to_date('2016-04-30 23:20:56', 'yyyy-mm-dd hh24:mi:ss') 
           from dual union all
       select 1, 29.390, 39.407, to_date('2016-04-30 23:04:42', 'yyyy-mm-dd hh24:mi:ss') 
           from dual
     ),
     augmented (id, lat, lon, time, idx) as (
       select  id, lat , lon , time, 1 from positions union all
       select to_number(:id), null, null, to_date(:time, 'yyyy-mm-dd hh24:mi:ss'), 0 
           from dual
     ),
     before_and_after as (
       select lat, lon, time, idx,
              lag(lat)   over (partition by id order by time, idx) as prev_lat,
              lag(lon)   over (partition by id order by time, idx) as prev_lon,
              lag(time)  over (partition by id order by time, idx) as prev_time,
              lead(lat)  over (partition by id order by time, idx) as next_lat,
              lead(lon)  over (partition by id order by time, idx) as next_lon,
              lead(time) over (partition by id order by time, idx) as next_time
       from   augmented
       where  id = to_number(:id)
     ) select * from before_and_after;
select prev_lat + (next_lat - prev_lat) * 
            (to_date(:time, 'yyyy-mm-dd hh24:mi:ss') - prev_time) / 
                (next_time - prev_time) as interp_lat,
       prev_lon + (next_lon - prev_lon) * 
            (to_date(:time, 'yyyy-mm-dd hh24:mi:ss') - prev_time) / 
                (next_time - prev_time) as interp_lon
from   before_and_after
where  idx = 0;