Skip to Content
Technical Articles
Author's profile photo Witalij Rudnicki

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

Assigned tags

      2 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Gregor Wolf
      Gregor Wolf

      Hi Vitaliy,

      another year is gone and I have to check again how many times I've visited a specific location. Now I have a HXE SP04. Unfortunately creating the Procedure:

      CREATE PROCEDURE "GOOGLE"."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 "POINT" FROM "GOOGLE"."LOC_HISTORY"
             	    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."POINT", -1);
          END FOR;
      
      	path := path_temp.ST_asEWKT();
       END

      Fails with the following error:

      Could not execute 'CREATE PROCEDURE "GOOGLE"."PATH_FROM_POINTS" (OUT path CLOB, IN ts_start NVARCHAR(27) DEFAULT ...'
      Error: (dberror) [1281]: wrong number or types of parameters in call: This function is blocked in SQLScript. Usage of this function with other parameter count or types may be possible: line 20 col 26 (at pos 626)

      Do you have any tip for me?

      Best regards
      Gregor

      Author's profile photo Witalij Rudnicki
      Witalij Rudnicki
      Blog Post Author

      Hi Gregor.

      The issue must be in the mismatch between ST_POINT(4326) type of “SANDBOX”.”IOT_TRACK”.”LOC_4326″ column and ST_POINT(0) type of “GOOGLE”.”LOC_HISTORY”.”POINT”.

      I modified the proc as following and it worked for me now:

      CREATE PROCEDURE "GOOGLE"."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 "POINT".ST_SRID(4326) as "POINT" FROM "GOOGLE"."LOC_HISTORY"
             	    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."POINT", -1);
          END FOR;
      
      	path := path_temp.ST_asEWKT();
       END

      Greetings,
      -Vitaliy