Calculating a path from IoT GPS data stored as ST_Point

In my previous #GeospatialTuesday blog Calculating a path from IoT GPS data I shared how to create a LineString representing a path connecting the points represented by two decimal columns with latitude and longitude in the table.

But what if each point data is already stored in a table as spatial ST_Point?

In my case I have a table defined as following.

CREATE COLUMN TABLE "SANDBOX"."IOT_TRACK" (
	"TIMESTAMP" TIMESTAMP,
	"LOC_4326" ST_GEOMETRY(4326) 
);

And in my case the table stores GPS tracking points as I walked in the morning a week ago from a hotel to a venue of Code Europe in Poznań, to give a talk on Geospatial SQL with SAP HANA.

Luckily, with SAP HANA 2.0 SP 3 there is a new method ST_AddPoint available. So, this is how I approached it using this method in SQLScript. I’d love to hear, if you come up with any other way to do the same!

CREATE OR REPLACE PROCEDURE "SANDBOX"."PATH_FROM_POINTS" 
(OUT path CLOB, 
 IN ts_start NVARCHAR(27) DEFAULT '0001-01-01 00:00:00.0000000', 
 IN ts_end NVARCHAR(27) DEFAULT '9999-12-31 23:59:59.9999999'
)
 LANGUAGE SQLSCRIPT 
 READS SQL DATA 
 AS
 BEGIN
 	DECLARE path_temp ST_GEOMETRY;
    DECLARE CURSOR c_points FOR
       	    SELECT "LOC_4326" FROM "SANDBOX"."IOT_TRACK"
       	    WHERE "TIMESTAMP" BETWEEN To_TimeStamp(ts_start) AND To_TimeStamp(ts_end)
       		ORDER BY "TIMESTAMP";
       		
 	SELECT ST_GeomFromEWKT('SRID=4326; POINT EMPTY') INTO path_temp FROM dummy;

    FOR cur_row AS c_points
	DO
		path_temp := :path_temp.ST_AddPoint(cur_row."LOC_4326", -1);
    END FOR;

	path := path_temp.ST_asEWKT();
 END;

A few comments:

• I first assign an empty point with Spatial Reference id 4326 to a variable path_temp using WKT standard POINT EMPTY. It would work as well with LINESTRING EMPTY, but I wanted to show that the method ST_AddPoint works as well if starting with a point.
• Next I use index -1 in the method ST_AddPoint. That means to add the new point at the end of the string.

Let me call the procedure now for a day of June 12th, 2018.

CALL "SANDBOX"."PATH_FROM_POINTS"(path => ?, ts_start => To_TimeStamp('2018-06-12'), ts_end => To_TimeStamp('2018-06-13'));

And the result is in EWKT format…

…which I can copy and visualize once again using http://geojson.io/ website.

If you want to try this procedure, but have no data, then you can just simply execute the following INSERT to add a few random points at Sahara desert. And then use the CALL PROCEDURE for today’s date.

INSERT INTO "SANDBOX"."IOT_TRACK" VALUES(
 TO_TIMESTAMP(NOW()), 
 new ST_Point(-12+RAND(),24.5+RAND()).ST_SRID(4326)
);

CALL "SANDBOX"."PATH_FROM_POINTS"
(path => ?, ts_start => To_TimeStamp(CURRENT_DATE));

Please let me know what you would modify or improve in this approach!

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