Working with HANA spatial data in Javascript

Over the years, I’ve done a fair amount of technical proof-of-concept applications, most of them on HANA or the SAP cloud platform. All of these have been written up into step-by-step “how-to’s”, but precious few of them have found their way here, to the public realm.

Time to change.

This particular blog is not really about a complete end-to-end example, but more a list of lessons learned while working on an app that used HANA spatial, and some IoT sensors. Well, kind of IoT. Half-way IoT. We never used the SAP-CP IoT capabilities, just a native platform somewhere – which came equipped with some nice API’s. But that’s not important.

The use case

We have a bunch of IoT sensors, equipped with GPS and Wifi and stuff. These are set to “ping” their location at specific intervals. We want to track them, or, more to the point, we’d like to know whether they are in the specific location they’re supposed to be in. We want to show this in a UI5 app.

Sensor, in a drawer in my colleague’s desk (the drawer is inside the building. Btw: why are there no cars in the parking lot? Photo must have been taken on a Sunday…).

The sensors (from a company called iTraq, by the way) send their location data to a native platform (as in “not SAP-CP”). From our SAP-CP application, we will do regular REST calls (a scheduled cron job) to the iTraq API to retrieve data. This is what I meant by “kind of IoT”. The IoT is there, just not in our app.

Storing spatial data in the HANA DB

I’m not going to dwell much on the frontend app – it’s outside our scope here. We want to focus on spatial data. To retrieve data for our devices, we call the API as mentioned above, and get a “status report” for each device, with coordinates showing the location. We stuff this into a HANA DB:

Here, we have two tables; one for areas (where we will store the locations our devices should reside within), and another one for the devices themselves. Areas are polygons (shapes), whereas the devices have one point as their location.

The ST_GEOMETRY and ST_POINT columns are defined as SRID 4326, which is one of the most common Spatial Reference System Identifiers used by (among others) Google.

Keep “4326” in mind. It will become important in a few minutes.

Key learning #1: HANA tables with spatial data cannot be exposed as XSOdata services

Instead, they have to be embedded into a UDF (user-defined function) and wrapped in a calculation view. In fact, if you try to use the table containing spatial columns directly in a calculation view, the view will activate just fine – but don’t you dare exposing it as an XSOdata service – all hell will break loose!

Sort of. As in “the .xsodata service will not activate”.

Instead, we have to resort to a workaround: create a table function (UDF) that converts the spatial column into a JSON object – using the ST_AsGeoJSON function, consume the table function in a calculation view, and expose this view as a service.

Our table function (UDF):

Note the conversion of the spatial column (the “area”) to a JSON object.

And since we cannot directly expose a table function as an odata service (another shortcoming of HANA 1), here’s the calculation view embedding the UDF table function:

Key learning #2: in the Web IDE (up to HANA 2 sp1), you cannot see metadata for UDF’s in calculation view nodes

As the observant reader will have noticed, this calculation view was created using the HANA Studio. This is due to yet another shortcoming of HANA 1 (and HANA 2 up to SPS1), in the browser IDE this time: Using a UDF in a calc view node fails to bring up the metadata… in other words: you won’t see the columns!)

The solution, as mentioned, is using the good old HANA Studio. Create your calc view, add the UDF to your node, map the columns you need, save, then open the calculation view in the browser IDE (or continue to use the HANA Studio, for that matter).

Now, we can expose the UDF as an XS OData service:

Key learning #3: some spatial functions do not work with SRID 4326

Remember 4326? The SRID we selected (because, hey, Google uses it, too, so it’s practical for us to store our values in SRID 4326 (except Google mixes latitude and longitude and reverses their order – must be some clever trade barrier scheme, what do I know)). Well, let’s do something simple: use a HANA spatial function or two to play around with our spatial data, to see if we can break something.

Let’s start by a little “warm-up” exercise: simply selecting the columns from our table, converting the area to a “well-known text” using the ST_asWKT() function:

select Areas.”id” as ID, Areas.”name” as NAME, Areas.”area“.ST_asWKT() as AREA from “ourspatialpackage.BoxTracker.data::BoxTracker.Areas” Areas;

Result:

Nice. We can see that the “AREA” column is indeed a polygon. However, if we try to use the ST_WITHIN function on the polygon, it fails:

select Areas.”id” as ID, Areas.”name” as NAME, NEW ST_POINT(‘Point(0 0)’).ST_WITHIN(Areas.”area“) as WITHIN from “ourspatialpackage.BoxTracker.data::BoxTracker.Areas” Areas;

This should resolve as “false” – or “0” – since the coordinates (0,0) are surely not inside our polygon area. Right?

Result:

The error up close (see how ugly it looks?):

The full error reads:

“Error in reading value (type 3): dberror(getInteger): 669 – spatial error: exception 1610041: A geometry with SRID=4326 is not supported for method ST_Within(ST_Geometry). at function st_within() (at pos 47)”

The issue is – as described in the error message – related to the simple (but annoying) fact that the SRID 4326 geocoding system is not supported by certain functions, such as ST_WITHIN.

In brief:

Slightly revised SQL based on the above:

select Areas.”id” as ID, Areas.”name” as NAME, Areas.”area“.ST_COVERS(NEW ST_POINT(46.46750877093199, 6.834692537881728)) as WITHIN from “nestle.dev.glb.atec.BoxTracker.data::BoxTracker.Areas” Areas;

Bingo. We nailed it. Use ST_COVER, not ST_WITHIN for SRID 4326.

The full technical reference of all methods supported for “round earth” scenarios.

The Full Monty: a service to tell whether something is inside something else

Let’s build on the above and create some kind of XS service that shows whether a location (point) is inside any of our areas (we’ll pretend they are critical off-limit zones that no point should be able to access. As in “pointless”).

As already mentioned, spatial columns cannot be exposed directly in OData services – they need to be converted to JSON first, something which is not possible in a calculation view (even when using calculated columns). Hence, we will have to create our UDF (table function), and consume it in a calculation view. The calc view will take a Point as an input variable (actually in String format, since ST_POINT is not a valid data type for input parameters) and traverse all the polygons in the Areas table. It will return a “WITHIN” variable of value 0 or 1 depending on whether the device is inside any of the polygons or not.

We can play around with this, and create several functions/views showing either one single value (inside or outside any of the areas), or several rows (all areas with specific “inside/outside” values for each. In any case the implementation is more or less as follows:

Table function (UDF) to handle the decision logic (is our point inside or outside one of our areas?):

FUNCTION "BOXTRACKER"."ourmightypackage.BoxTracker.functions::Inside" ( IN im_location String ) 

 RETURNS TABLE(

    "id" INTEGER,

    "name" VARCHAR(100),

    "within" INTEGER

    )

    LANGUAGE SQLSCRIPT   

    SQL SECURITY INVOKER 

    DEFAULT SCHEMA BOXTRACKER

    READS SQL DATA AS

BEGIN  

RETURN

    select "id", "name", "area".ST_COVERS(NEW ST_POINT(:im_location)) as "within"

    from "ourmightypackage.BoxTracker.data::BoxTracker.Areas";

END;

The function takes a parameter which is actually a spatial variable (it will be a point in our case – that’s the whole point); then executes a select statement on the Areas table – returning the ID, NAME and WITHIN columns. The WITHIN column is dynamically created, and is the result of determining whether the point is within the polygon. Note the object notation – we execute the ST_COVERS function on the “areas” column, and “feeds” it a point based on the input parameter.

We expose the UDF in a (very simple) calculation view (HANA Studio to the rescue!) – the idea is to have the WITHIN column exposing TRUE or FALSE depending on whether the ST_POINT input parameter is inside the polygon:

The calculation view has a parameter of type ST_POINT, mapped to the input parameter of the UDF which is the data source:

Now for some mapping:

Calling the calculation view (here, the input parameter is actually one of the points inside one of our polygons, so the condition holds true for that specific row):

Note the syntax for the input parameter, IP_LOCATION: this is a string of the format ‘Point(x y)’. The single quotes are necessary. Also, no comma between the x and y coordinates.

Result:

Seems to work!

Key learning #4: how to work with spatial data types in XSJS (or Javascript in general)

A few words on using spatial logic in server-side Javascript (XSJS) on SAP-CP. If working with the $.hdb API (generating SQL requests from Javascript), care has to be taken when it comes to assigning geospatial data to Javascript variables.

Javascript has no support for HANA spatial data types, something that should not come as much of a surprise. The below code sample shows an attempt to deduce whether a specific location (the “displayLocation” variable) is within a certain distance of another location (the one selected from the “Location” table, which I haven’t told you about before, but which contains ST_POINTs). In other words, we’re going to see whether two ST_POINT’s are within a certain distance of each other (forget polygons for now).

Here is the sample code:

The important point here is that the “display” object, referred to in the first line, was selected earlier – as a “well known text”. Hence the attribute name display.locationWKT. Here is an excerpt from that earlier select:

The “location” column is of type ST_POINT. Had we selected it “as-is”, we would have received a garbled result that would not have been recognizable when fed into the main select shown above. Instead, we have to manually build a new ST_POINT object using the well-known text-formatted variable. This is exactly what happens here:

Note the escaped single quotes. HANA is picky about the formatting of SQL statements.

The net result is that the previously selected spatial information can – after being reformatted into a string that effectively declares a “new” ST_POINT entity – be used in our second select statement, as shown:

Here, we apply our nicely formatted new spatial point to be used as a parameter for the ST_WithinDistance function, which operates on the “L”.”location” column.

And – before you ask why we don’t have to do the same tweaking of the “L”.”location” column: it’s because we never select it into a Javascript variable – we just use it to evaluate whether the two points are within a certain distance of each other. The result of that particular operation is what will be stored in our resulting Javascript object – but that’s a different story.