C_TIMESTAMP
we need to use SAP HANA SQL lag
window function:LAG("C_TIMESTAMP", 1) OVER (ORDER BY "C_TIMESTAMP")
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.ST_Point
data type to create geospatial points:NEW ST_Point('POINT ('||"C_LONGITUDE"||' '||"C_LATITUDE"||')', 4326)
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.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;
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
13 | |
10 | |
10 | |
7 | |
7 | |
6 | |
5 | |
5 | |
5 | |
4 |