From Wroclaw to Warsaw and back: IoT analysis with SAP HANA SQL

In my previous blog posts, I did visualization of non-transformed data from SensorPhone app persisted in the SAP HANA database of HCP Trial via IoT Services. This time I want to calculate the speed I was driving. Although this time I’ll use another subset of data – from another trip.

Yesterday I returned from SAP Innovation Day in Warsaw, where I presented an Overview of SAP HANA Cloud Platform (thanks Sven Kohlhaas and Matthias Steiner for inputs). This SAP Innovation Day was as well a great chance to catch up with some SCN members: Waldemar Falinski, Michal Korzen, Mariusz Wziatek, Miroslaw Orzelski, Robert Rembelski, Karol Dworak and to get to know new: Katarzyna Tenerowicz, Bartlomiej Sakwerda and Adam Gorka.

I used SensorPhone to collect iPhone sensors and GPS data again. Here is a route on the way back from Warsaw to Wrocław. Unfortunately, there were some gaps in postings, but not significant to screw calculations.

So, I’ve got GPS points and timestamps and now need to calculate the speed in km/h for each point (every table’s row) as a distance driven from the previous GPS location divided by the time span between measurements.

To find the previous record’s value in the same column C_TIMESTAMP we need to use SAP HANA SQL lag window function:

LAG("C_TIMESTAMP", 1) OVER (ORDER BY "C_TIMESTAMP")

This lag function returns the value of C_TIMESTAMP from the -1 (i.e. previous) record ordered by C_TIMESTAMP, and if the offset crosses boundaries, then by default the null value is returned. So, we will need IFNULL() function to handle such a situation and assign 0 km/h to the speed calculation.

To calculate the distance between two points for which we have latitude and longitude values, we need to use SAP HANA spatial data processing – in particular, ST_Point data type to create geospatial points:

NEW ST_Point('POINT ('||"C_LONGITUDE"||' '||"C_LATITUDE"||')', 4326)

This ST_Point constructor creates a geospatial point concatenating longitude and latitude from the table into WKT stirng (Well-Known Text format) in Spatial Reference System 4326 (aka WGS84). Then function ST_Distance is used to calculate a distance to another point.

Because the lag function cannot be used with the spatial data type point, we need to use this function with C_LONGITUDE and C_LATITUDE. Here is a complete SELECT in SAP HANA SQL for my table T_IOT_A7B01790F3E80BC544A3 containing the data from the SensorPhone app:

SELECT
  UTCTOLOCAL ("C_TIMESTAMP", 'CET') as TIMECET,
  TO_DECIMAL("C_ALTITUDE") as ALTITUDE,
  TO_DECIMAL("C_LONGITUDE") as LONGTITUDE,
  TO_DECIMAL("C_LATITUDE") as LATITUDE,
    IFNULL(NEW ST_Point('POINT ('||"C_LONGITUDE"||' '||"C_LATITUDE"||')', 4326).ST_Distance
  (NEW ST_Point('POINT ('||LAG("C_LONGITUDE", 1, "C_LONGITUDE") OVER (ORDER BY "C_TIMESTAMP")||' '||LAG("C_LATITUDE", 1, "C_LATITUDE") OVER (ORDER BY "C_TIMESTAMP")||')', 4326), 'meter')/
  SECONDS_BETWEEN (LAG("C_TIMESTAMP", 1) OVER (ORDER BY "C_TIMESTAMP"),"C_TIMESTAMP")*3600/1000, 0) AS "KMpH"
FROM
(select distinct "C_DEVICE", "C_TIMESTAMP", "C_ALTITUDE", "C_LONGITUDE", "C_LATITUDE"
from "T_IOT_A7B01790F3E80BC544A3"
where TO_DATE("G_CREATED") = '2016-06-23')
order by "C_TIMESTAMP" asc;

which returns something like:

If I want to visualize the speed in SAP Lumira similarly to the way I visualized the altitude values, then I just need to reuse the same SQL query during the data acquisition (pasted in ‘Query’ field):

And then do a time-series line chart again to analyze the speed profile:

PS. After these few posts on PhoneSensor data in HCP IoT, I got some requests from colleagues to share access to this data in my account. The next blog is discussing Three ways of sharing SAP HCP IoT message consumption with others via OData.

-Vitaliy aka @Sygyzmundovych