# 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
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
END FOR;

path := path_temp.ST_asEWKT();
END;``````

• 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

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
AS
BEGIN
DECLARE path_temp ST_GEOMETRY;
DECLARE CURSOR c_points FOR
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
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

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
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