Calculating a path from IoT GPS data using SAP HANA Spatial

[Vitaliy-R]

If you remember one of my earlier posts From Wroclaw to Warsaw and back: IoT analysis with SAP HANA SQL I used SensorPhone app to collect GPS positions of my car while driving from one city to another. SensorPhone then uses IoT service for Neo to collect data in SAP Cloud Platform. Later I used SAP HANA's SQL with geospatial data support and window functions to calculate the speed.

One of the tricks there was how to construct geospatial point data type from two decimal values representing latitude and longitude using Well-known text (WKT) as a text markup language :

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

But what if we want to construct a geospatial LineString representing a path connecting the points I was going through? So last week I turned on SensorPhone app on my phone while walking from a hotel to the location of QCon conference in London.

select "G_CREATED", "C_TIMESTAMP", "C_LONGITUDE", "C_LATITUDE"

from "T_IOT_A7B01790F3E80BC544A3"

where TO_DATE("G_CREATED") = '2018-03-05'

order by "G_CREATED" asc;

Ok, data is there with longitude and latitude values ordered by the timestamp. Now to build the WKT string that can be used to construct a LineString we will use STRING_AGG aggregation to properly format string.

SELECT 

  ST_GeomFromText('LINESTRING ('||STRING_AGG("C_LONGITUDE"||' '||"C_LATITUDE",', ' ORDER BY G_CREATED)||')',4326).ST_asWKT() as "path"

FROM "T_IOT_A7B01790F3E80BC544A3"

WHERE TO_DATE("G_CREATED") = '2018-03-05';

The result is properly formatted String representing sequential points of my walk, like

LINESTRING (-0.12767942670274082 51.493476107500115,-0.1276470738968785 51.4934737428734,-0.12788387803363319 51.4941326330944,...,-0.12918044569300738 51.49937907039622,-0.12916212266345334 51.499476483603914)

As usually I can visualize it using http://geojson.io service. Because the result I have is in WKT and not GeoJSON format, I need to click on Meta and then Load WKT String. Now I can paste the WKT output of the SQL and http://geojson.io will convert it to GeoJSON for me and display the path of my walk visualized (I modified styling to make it better visible). It was a pleasant walk I must say: via St. John's Garden and Westminster Dean's Yard.



And to calculate the average speed of my walk:

SELECT 

  ST_GeomFromText('LINESTRING ('||STRING_AGG("C_LONGITUDE"||' '||"C_LATITUDE",', ' ORDER BY G_CREATED)||')',4326).ST_length()

  /SECONDS_BETWEEN (MIN("G_CREATED"),MAX("G_CREATED"))*3600/1000 as "km/h"

FROM "T_IOT_A7B01790F3E80BC544A3"

WHERE TO_DATE("G_CREATED") = '2018-03-05';



--The result was 5.2397373242698135 in km/h

Have fun with your calculations and visualizations!

---

‘Till next #GeospatialTuesday,
-Vitaliy, aka @Sygyzmundovych