Skip to Content

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

2 Comments
You must be Logged on to comment or reply to a post.
  • 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

    • 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