On Spatial analysis with HANA

[former_member102219]

Scenario

I chose to model fire propagation across the suburban landscape. Obviously, since I’m not a subject matter expert in that, I didn’t try to make the model actually useful for real world predictions. Rather my aim was to pick a realistically looking aspect of fire propagation and use it to showcase spatial analysis capabilities of HANA.

So, leaving aside all other factors of fire propagation, let’s assume that in an event of fire the wind may carry away burning particles, which, when falling to the ground, may start new fires.

Spatial data used

I used three sources of data, all available for free from Australian Bureau of Statistics:

• - boundaries of postcodes in New South Wales,
• - boundaries of Australian Statistical Geography Standard (ASGS) mesh blocks,
• - census data associated with ASGS mesh blocks: population, number dwellings, type of land use.

I imported all that data into HANA using IMPORT statement and using SRS 1000004326, which would allow to measure distances – more about that in the next section.

Flat Earth, Round Earth

The Earth has an ellipsoid shape, hence one geographical degree means different distance on the equator and in Sydney. That makes using degrees difficult for measuring distances. HANA provides a few pre-set Spatial Reference Systems (SRS), falling into either “flat Earth” or “round Earth” category. Measuring distances only works in “flat Earth” one.

In a fully-fledged GIS system, there would be some sort of transformation available to convert a shape from linear to polar units and back. HANA lacks that functionality: the ST_TRANSFORM() function will serve some other purpose (and only in SPS10), and the ST_TRANSLATE function has just been added to the feature list. One is left with either the option to implement Vincenty’s formulae, or assume that 1 polar degree has the same value within a local map. For example,

SELECT new st_point(151,-31,1000004326).st_distance(new st_point(151.001,-31,1000004326), 'meter')

  as v_dx

from dummy

should give a metric value of the longitude polar unit in the near proximity of the point -31 lat 151 long.

My understanding of the example above is that behind the scene, HANA converts geographic degrees into some internal “flat Earth” coordinates (of SRS 1000004326) and calculates the distance. The values of those internal coordinates are not made available to the user.

Using one of these options, one can use HANA math functions and build a shape in linear system, translate it to geographic degrees and save to the database, or just pass back to SAPUI5 application as GeoJSON().

Interface

I picked Google Maps API, as it seemed to have the easiest learning curve for me. Other choices would be probably Nokia Here maps and SAP Visual Business. All these APIs can be integrated into SAPUI5, and SAP Visual Business, in fact, has its own controls in SAPUI5 framework.

To make the application portable between map providers, I moved all calculation and measuring logic to database procedures and used the Google Maps API only for geospatial data visualisation and for user input capture.

Google Maps visualises javascript objects of class google.maps.Data.Feature, where a Feature has the ID, some properties and, lastly, its geometry represented as GeoJSON. Conversion of HANA internally stored spatial objects into GeoJSON has to happen in database procedures, and Features can be built either on the server (XSJS) or on the client (SAPUI5) side.

For my app, I implemented visualization of postcode containing a point and attached it to a Click event on the map:

Simple spatial functions

I built a simple tool to measure distances on the map. The google.maps API captures two consecutive Click events, then HANA uses ST_DISTANCE() spatial function to measure the distance between those two points.

Similarly, using distance measurement functions within an arctangent expression, I calculated the simulated wind direction from two consecutive Click events.

Modelling smoke dispersal

For a particle to be carried X units away from the fire along the wind direction, there is some probability associated with that event. That probability has normal distribution along X. Then, there is some probability for a particle to diffuse Y units across the wind direction -- that probability also has normal distribution, but obviously with different parameters; in fact, there are equations describing that along/across dispersion.

For the purpose of further work, I changed those equations to outline a patch on the surface, that encompasses all probabilities below some threshold (say, 1-2 sigmas) and created a HANA stored procedure to calculate an ellipsoid-like shape of that patch.

In this scenario, an area inside the red patch is under significant risk of fire (blue arrow indicates the wind direction).

Spatial join

I wanted to find out a) what ASGS meshblocks would fall into the risk area and get some statistics about them, b) to what postcodes those mesh blocks belong. Both tasks would require joining tables not by usual alpha/numeric  fields, but using a spatial relationship between shapes in records. Examples would be “A within X meters from B””, “A overlaps/touches B”, “A is covered by B” etc.

A simplified way to use spatial joins would be in a Calculation View, and there is a SAP tutorial for that. An SQLScript example for “intersection join” would look like this:

SELECT t1.mb_code11 as mesh_code,

t2.POA_2006 as postcode

from "SPATIAL"."T_SHP_MESH" as t1

inner join "SPATIAL"."T_SHP_POSTAL" as t2

    on t1.shape.st_intersects(t2.shape) = 1

Here, I added a SAPUI5 Table that would interactively select the row with the data about a mesh block the user clicks on:

Performance considerations

HANA is undeniably fast, but my experience with spatial analysis in HANA so far indicates that there is some amount of optimisation to be done. I may be pushing HANA too far with the amount of data I loaded, but since it’s just one Australian state I doubt that.

So, performance degrades dramatically with increased complexity of spatial features being analysed, increased both in terms of the number features and number of vertices in features. One should be careful, for example, with using ST_BUFFER(), as it produces a rather finely shaped circle polygon with the number of vertices that can totally choke the database. It would be good of SAP to provide functionality to reduce the number of vertices in a shape, I remember having that in ESRI’s Arc/INFO.

Another idea that proved useful was to build a “spatial index” of loaded shapes, for example by creating a rectangle containing each shape:

insert into "SPATIAL"."T_SHP_MESH_INDEX"

( select mb_code11, mb_cat11,

         new st_polygon('Polygon ((' ||

               shape.st_xmin() || ' ' || shape.st_ymin() || ', ' ||

               shape.st_xmin() || ' ' || shape.st_ymax() || ', ' ||

               shape.st_xmax() || ' ' || shape.st_ymax() || ', ' ||

               shape.st_xmax() || ' ' || shape.st_ymin() || ', ' ||

               shape.st_xmin() || ' ' || shape.st_ymin() || '))')

               as shape

               from "SPATIAL"."T_SHP_MESH")

Rough and slightly redundant selection of features might be made using that “spatial index” and then fine selection with real shapes would be performed on a subset. In my case, this trick reduced selection time from eternity to a few seconds.

Possible extension

This model has some potential for further extension for predictive analysis.

The ASGS mesh blocks already provide land use type and population density, which may give a clue on how fire-prone a mesh block is: say, a high-rise block is less prone to catch a fire than a bushland piece. Further, some historical data on detected fires, with their coordinates, time of detection, wind parameters etc. could be used to derive spatial relationships (clustering? distances?) between historical fires and build a more thorough predictive model.