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
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
4326to a variable
path_tempusing WKT standard
POINT EMPTY. It would work as well with
LINESTRING EMPTY, but I wanted to show that the method
ST_AddPointworks as well if starting with a point.
- Next I use index
-1in 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