Building Location-Aware XSA Web Applications using Python

Visualization is the key to effective communication. If you can paint a picture for your audience that is easy for them to visualize, it becomes so much easier to get your point across. When it comes to communicating enterprise data, what better tool to use than a map that can intuitively show complex data in a graphical way that is both useful and interesting for the audience. In this blog, I am going to introduce you to the geospatial processing capabilities of SAP HANA and how you can leverage them in a web application involving maps.

SAP HANA includes both database-level spatial functions and spatial web services which are provided through the SAP Cloud Platform. The former allows you to carry out spatial queries (i.e. distance calculation, point clustering, geometry analysis, etc.) at database level whereas the latter provides an API for getting information about Points of Interests, Weather, Wildfire etc. which can come in handy for web applications. In this blog, I am going to focus more on database level capabilities of SAP HANA and use Esri’s ArcGIS API for JavaScript for the front-end visualization and processing.
As always, I am using SAP HANA express edition for my demo in this blog, which means you can follow along easily as well if you wish! I am also assuming you have basic knowledge of how Python applications work in XSA, if that is not the case.

Spatial Reference Systems

Before we start talking about anything spatial related, it is important you understand how Spatial Reference Systems work. As we all know, Earth is spherical, but when representing on a map, we show the Earth to be flat. There are multiple different interpretations of how to translate a spherical model of Earth to a flat, linear model.

A Spatial Reference System (SRS) is a defined space in which geometries are described. Each SRS has an identifier (SRID) and a unit of measure associated with it. An SRS also describes the representation type of the data, that is whether it is stored in spherical form or planar, and the projection which is used to transform coordinates from one SRS to another.

If you are wondering why SRS’s are so important, that was my first question as well. An SRS is a way of describing a geometry (area or point) on a map. You can have many ways to describe the same geometry, and so when it comes to processing geospatial information, e.g. determining whether two areas overlap or not, it is important to know which reference system is used to describe each area. Without understanding how the data (geometry) is represented, it becomes very difficult to do any sort of processing on that data.

In SAP HANA, the default SRS is a Cartesian reference system with an SRID of 0. This is the normal x, y planar coordinate system we use. Another commonly used SRS is the WGS84 spheroidal system with SRID of 4326. This system uses latitude, between range -90 to 90, and longitude, between range -180 to 180, for measurement. The most common application of this system is in GPS’s. Some other commonly used systems include the WGS84 Planar reference with SRID 1000004326 and WGS84 Web Mercator with SRID 3857.

Getting Started

In a typical spatial web application, all mapping and visualization takes place in the front-end module, for which you can use the ArcGIS API for JavaScript, and all the data can be accessed through the back-end module which communicates with the HANA database. Just to reiterate, for all back-end processing, I am using Python.

Let’s start with loading a simple map on the browser! This part is done in the application router module of your XSA application which usually contains all the front-end files. First you need to create an html file named spatial.html in which you load the CSS and JavaScript files from ArcGIS, which are used to create the map. Link this file to a local CSS and JavaScript as well where you can specify more details for the map. Also, create an empty div element in the body which acts as a container for the map.

>>> spatial.html
<!DOCTYPE html>
<html>
<head>
    <meta charset="utf-8">
    <meta name="viewport" content="initial-scale=1, maximum-scale=1, user-scalable=no">

    <!-- for loadng map from arcgis -->
    <link rel="stylesheet" href="https://js.arcgis.com/4.8/esri/css/main.css">
    <script src="https://js.arcgis.com/4.8/"></script>

    <!-- for modifying map -->
    <link rel="stylesheet" href="spatial.css">
    <script type="text/javascript" src="spatial.js"></script>
</head>
<body>
    <div id="viewDiv"></div>
</body>

Now, let’s create a CSS file named spatial.css in which you specify the size and margin settings for the map container.

>>> spatial.css
html, body, #viewDiv {
    padding: 0;
    margin: 0;
    height: 100%;
    width: 100%;
}

Lastly, create a JavaScript file named spatial.js. This will hold the majority of your code will be for all mapping and visualization. The code snippet below shows how to load and consume modules required to create a map from ArcGIS API. First, create a map object with base layer of OpenStreetMaps and SRID of 4326. This map object goes inside a MapView object which shows the map on the screen. Here you can specify more options to modify your map including the initial zoom level and the center point. The padding property is specified if you want to incorporate a panel to show either details about the map or for any other purpose. In my demo, I use this to show more information about selected points.

>>> spatial.js
require([
    "esri/Map",    //for creating the map
    "esri/views/MapView",      //for viewing the map
    "dojo/domReady!"      //ensure DOM ready before execution
], function (
    Map,
    MapView,
    domready
) {
    //create OpenStreetMap with SRID 4326
    var map = new Map({
basemap: "osm",
spatialReference: 4326
    });

    //create MapView centerd at [0, 0]
    var view = new MapView({
container: "viewDiv",
map: map,
zoom: 5,
center: [0, 0],
padding: {
            left: 400
} //for side panel
    });
});

If you save these files and run them (locally is fine for now), you should see a blank map similar to the image below:

There you go! You have successfully created an application that shows a whole world map with few simple lines of code!!

Plotting Points

To plot a point on the map, you need to specify the location of the point and the marker to use for the point. You can also include an attributes object which stores information about the point. For plotting a single point on the map, the code looks similar to the snippet below. After declaring the location, marker, and attributes, you need to instantiate a Graphic() object using the point information and add that object to the view.graphics object so that it can be shown on the map. Just to be clear, this code goes in the spatial.js file within the callback function for the require command.

function plotPt(lng, lat, name) {

    //auto type cast to Point()

    var pt = {

        type: 'point',

        longitude: lng,

        latitude: lat,

        spatialReference: {

            wkid: 4326

        }

    }

    //auto type cast to PictureMarkerSymbol()

    var sym = {

        type: 'picture-marker',

        url: 'https://png.icons8.com/metro/1600/marker.png',  //can be any link

        width: 20,

        height: 20

    }

    var attr = {

        name: name

    }

    //graphic containing point

    var ptGraphic = new Graphic({

        geometry: pt,

        symbol: sym,

        attribute: attr

    });

    view.graphics.add(ptGraphic);    //show in view

}

plotPt(0,0, 'sample point');

You would also need to require the “esri/Graphic” module with the rest of the modules at the beginning. Make sure you add the Graphic variable to the parameters of the callback function as well. The order of the parameters in the callback function needs to match the order of the modules in the require statement. Thus, your require statement should look as follows:

require([

    "esri/Map",   

    "esri/views/MapView",      

    "dojo/domReady!",

    "esri/Graphic"

], function (

    Map,

    MapView,

    domready,

    Graphic

)

If you refresh the spatial.html file now, the output should be like the following:

Now, let’s say you want to pull points from your HANA database and plot them on the map. The code on the front-end would look as follows. Here, I am using web sockets to communicate between front-end and back-end. If you are not familiar with how to implement web sockets. In the snippet below, I initiate a ‘getPts’ event which receives a list of points (pts) from the back-end. The callback function plots these points onto the map.

//globalSocket is connected to wss://py.hanapm.local.com:30033/spatial

globalSocket.emit('getPts', pts => {

    for (var i = 0; i < pts.length; i++) {

        //call function from before on each point received

        plotPt(pts[i].lng, pts[i].lat, pts[i].name);

    }

});

On the back-end, you need to define an event handler for ‘getPts’ which queries points from the database and sends them to the front-end. The code should look like the following. Again, for details on web sockets implementation or other details about Python applications in XSA, refer to my previous blogs.

def on_getPts(self):

    conn = connectDB('spatial-db')    # establish connection with DB

    c = conn.cursor()

    

    #get name, longitude, and latitude

    query = '''

        SELECT 

        NAME, LOC_4326.ST_X(), LOC_4326.ST_Y()

        FROM TABLE_NAME

    '''

    conn.execute(query)

    pts = []

    for result in cursor.fetchall():

        pts.append({

            'name': result[0],

            'lng': result[1],

            'lat': result[2]

        })

    return pts

Let’s have a closer look at the query in the snippet above. ‘LOC_4326’ is a column in our table which contains the location of the record in the form of ST_Point (a spatial data type used to store point coordinates in HANA). The ST_X() and ST_Y() functions return the longitude and latitude, respectively, of the point in the LOC_4326 column.

That’s it! You should now be able to get points from a table in your database and plot them onto the world map.

Layers

So far, we have covered how to plot points on the map. What if you wanted to draw two different kinds of points? Maybe two different kinds of graphics (i.e. points and lines)? This is where the layers play a role. You can have multiple different layers within your map which you hide and show as need be. Some of the common types of layers include GraphicsLayer, FeatureLayer, and CSVLayer. I’ll cover some more details about GraphicsLayer here. For the other two, I’ll talk about them as they come up later in the blog.

GraphicsLayer, as the name suggests, contains one or more Graphic objects. The types of Graphic objects in each layer do not have to be the same (i.e. you can have a point and a line in the same layer). You can create multiple layers with different graphics in each layer and show each based on the user input. Creating a GraphicsLayer is also very simple. You just need to pass a list of Graphic objects to the GraphicsLayer constructor and that’s it!

Looking at our previous example, if you were to modify it to incorporate GraphicsLayer, you could modify the plotPt function so that it returns the graphics after creation rather than showing them straight away. In the callback function for the ‘getPts’ event, you can store the graphics from plotPt function into a list which you can pass to the layer constructor to create a layer. You can then add this layer to the map to show it. The snippet below explains this in code.

function plotPt(lng, lat, name) {

    //auto type cast to Point()

    var pt = {...}

    //auto type cast to PictureMarkerSymbol()

    var sym = {...}

    var attr = {...}

    //graphic containing point

    var ptGraphic = new Graphic({...});

    //view.graphics.add(ptGraphic);    //show in view

    return ptGraphic;      //send back graphic

}

globalSocket.emit('getPts', pts => {

    var graphicsList = []

    for (var i = 0; i < pts.length; i++) {

        graphicsList.push(plotPt(pts[i].lng, pts[i].lat, pts[i].name));

    }

    

    var gLayer = new GraphicsLayer({

        graphics: graphicsList

    });

    map.add(gLayer);

});

For more details on functions you can call on map object for different layers, follow this link. Don’t forget to add the “esri/GraphicsLayer” module to the require statement and the callback function header!

Selecting Points Using Polygons

One of the extremely useful features of ArcGIS is the ability to let users draw on maps. You can allow your users to select an area or a specific region on the map by just drawing a polygon around it. In my demo application, which is included at the bottom of this blog, I have incorporated the ability to select multiple points from the map by drawing a polygon around them and seeing different statistics related to those points.

First, you need to modify the spatial.html file to include the “draw polygon” widget provided by Esri. Add the following lines inside the “viewDiv” element (map container) in the body of the html file.

<div id="draw-polygon" class="esri-widget--button esri-widget esri-interactive" 

    title="Draw and measure polygon">

    <span class="esri-icon-polygon"></span>

</div>

Now, modify your spatial.js file to include the code snippet below. First, add the draw polygon widget provided by Esri to the map. Then, attach an event listener on the view and initialize a Draw object which is needed to visualize user’s input. Once the user clicks the drawPolygonButton, activate the specific event listeners for the drawing functions which allow the user to add vertices and create the polygon. Once the user is done drawing (on double click), the doneDrawingPolygon function. In this function, you can access the vertices of the polygon the user has drawn and use them for further analysis. Keep in mind that these vertices are not in the SRS with SRID 4326, so before you do any further analysis, you need to make sure you convert the vertices to your SRS. The xyToLngLat function of “esri/geometry/support/webMercatorUtils” allows you to convert to WGS84 spherical reference which has an SRID 4326.

view.ui.add("draw-polygon", "top-left");    //add widget

view.when(function (event) {

    var graphic;

    var draw = new Draw({

        view: view

    });

    //create polygon when map area clicked

    var drawPolygonButton = document.getElementById("draw-polygon");

    drawPolygonButton.addEventListener("click", function () {

        enableCreatePolygon(draw, view);

    });

    //event handlers for creating polygon

    function enableCreatePolygon(draw, view) {

        var action = draw.create("polygon");

        view.focus();

        action.on("vertex-add", drawPolygon);

        action.on("cursor-update", drawPolygon);

        action.on("draw-complete", doneDrawingPolygon);

    }

    //draw polygon on map - main function

    function drawPolygon(event) {

        var polygon = createPolygon(event.vertices);

        graphic = createGraphic(polygon);

        view.graphics.add(graphic);

    }

    //create polygon with given vertices

    function createPolygon(vertices) {

        return new Polygon({

    rings: vertices,

            spatialReference: view.spatialReference

        });

    }

    //show polygon on map

    function createGraphic(polygon) {

        graphic = new Graphic({

            geometry: polygon,

            symbol: {

                type: "simple-fill",

                color: [178, 102, 234, 0.8],

                style: "solid",

                outline: {

                    color: [0, 0, 0],

                    width: 2

                }

            }

        });

        return graphic;

    }

    //use polygon

    function doneDrawingPolygon(event){

        vertices = event.vertices;    //not in SRID 4326

        polygonCoord = [];

for (var i = 0; i < vertices.length; i++) {

polygonCoord.push(webMercatorUtils.xyToLngLat(

vertices[i][0], vertices[i][1]

));    //now in SRID 4326

}

    }

});

At this point, you have the vertices of the polygon your user has drawn in proper latitude and longitude units for SRID 4326. Now, let’s use HANA to figure out which points from your database are covered within this polygon using the ST_Within() function. We can use the following lines of code within our JavaScript file to send the polygon coordinates to our back-end and log the response we get. For the ST_Within() function in HANA, the polygon you provide has to be enclosed (i.e. the last coordinate provided has to be the same as first). However, the vertices we have now only have the coordinates of the points from the first drawn to the last drawn. Thus, before sending the coordinates to the back-end, you should add the coordinate of the first point to the end of the “polygonCoord” list as shown below.

polygonCoord.push(polygonCoord[0]);

globalSocket.emit('polygonDrawn', polygonCoord, response => {

    console.log(response)

});

In the Python back-end, you need to take the coordinate of the polygon as input, send a query to HANA and obtain the points from our database that are covered within the polygon. Finally, you can reply to the JavaScript front-end with the names and locations of the points that are within the polygon. The code here looks like the following:

def on_polygonDrawn(self, polygonCoord):

    #write coordinates into a WKT form

    polygonString = 'POLYGON(('

    for coord in polygonCoord:

        polygonString += str(coord[0]) + ' ' + str(coord[1]) + ', '

    polygonString = polygonString[:-2] + '))'

    withinQuery = '''

        SELECT LOC_4326.ST_Transform(1000004326).ST_Within(

            ST_GeomFromText('%s', 4326).ST_Transform(1000004326))

        AS WITHIN, 

        NAME, LOC_4326.ST_X(), LOC_4326.ST_Y()

        FROM STRAVELAG

        WHERE LOC_4326 IS NOT NULL

    ''' % polygonString     #SQL INJECTION

    agencyResponse = []

    for result in executeQuery(connectDB('spatial-db'), withinQuery):

        if (result[0] == 1):

            agencyResponse.append({

                "Name": result[1],

                "Latitude": result[2],

                "Longitude": result[3]

            })

    return agencyResponse

Let’s look at the query here as well. The ST_Within() function can only be called on geometries in a planar SRS, so before we can call the function itself, we need to transform our location points to the planar SRS with SRID 1000004326 from spherical SRS with SRID 4326. The ST_Within() function returns another column with either 1 or 0 for each record it is called upon where 1 means the record is covered in the polygon and 0 means that it is not.

So, to sum it all up, you can allow users to draw a polygon on the map. Once they’re done drawing, you can get the coordinates of the polygon and send them within a query to HANA requesting individual points (in the form ST_POINT) that fall within that polygon (in terms of location).

Spatial Clustering

When you are showing a large set of data points at once, it is useful to use some technique to show trends or aggregates rather than showing each data point individually. One such technique is spatial clustering which creates clusters of nearby points based on each one’s location.

HANA supports three algorithms for clustering, namely Grid, K-Means, and DBSCAN. The Grid method works by dividing the view into a grid of cells (i.e. 5×5, 6×8, etc.) and clustering all the points in each cell together. The diagram below shows an example of this.

K-Means algorithms is based on centroids, so it clusters together points based on their distance from a central point. You can specify the number of clusters you want when using K-Means and the algorithm chooses centroids accordingly to create clusters.

DBSCAN stands for density-based spatial clustering of applications with noise. This algorithm is a bit more complex as it clusters points based on a “neighborhood radius” and the minimum number of points required to make a cluster. This is mostly used for non-spherical clusters.

To implement clustering within your XSA applications, you can have the front-end request cluster information (ID, number of points, location, etc.) from the back-end and show that as points or polygons on the map. In this blog, I am going to focus on the K-Means algorithm. The front-end code can be as shown in the snippet below. The only thing that is different in plotting clusters is that if you want to show the number of points in each cluster on the marker, you need to create two graphics, one with picture marker and the other with text marker showing the count.

function clusterPts(numClusters){

    globalSocket.emit('getClusters', numClusters, pts => {

        var clusterPoints = []

                

        for (var i = 0; i < pts.length; i++) {

        var count = pts[i].Count //num of points in each cluster

            

            //red marker for 5+ points, yellow for others

        var source = count > 5 ? "images/cluster_marker_red.png" : "images/cluster_marker_yellow.png";

            

            var ptSymbol = {

type: "picture-marker",

url: source,

width: "30px",

height: "30px"

}

var textSymbol = { //to display num of pts in cluster

type: "text", 

color: "black", 

text: count,

verticalAlignment: "middle"

}

            var pt = {

type: 'point',

longitude: points[i].Longitude,

latitude: points[i].Latitude,

spatialReference: {wkid: 4326}

}

            var attr = {

            ClusterID: pts[i].ClusterID

            }

            var textGraphic = new Graphic(pt, textSymbol, attr); //shows count

var picGraphic = new Graphic(pt, ptSymbol, attr); //show marker

clusterPoints.push(picGraphic);

clusterPoints.push(textGraphic);

        }

        

        var clusterLayer = new GraphicsLayer({

        graphics: clusterPoints

        });

        

        map.add(clusterLayer);

    });

}

On the back-end, you just need to run a query on HANA to get the cluster information given number of clusters required and send this information back to the front-end.

def on_getCluster(self, num):

query = '''

    SELECT ST_ClusterID() AS CID, 

        COUNT(*) AS COUNT,

        ST_ClusterCentroid().ST_X() AS CENTER_LNG,

        ST_ClusterCentroid().ST_Y() AS CENTER_LAT

        FROM (

            SELECT LOC_4326.ST_Transform(1000004326) AS OBJ_LOCATION

            FROM TABLE_NAME

            WHERE LOC_4326 IS NOT NULL

        )

        GROUP CLUSTER BY OBJ_LOCATION 

        USING KMEANS CLUSTERS %d;

    ''' % (num) #SQL INJECTION

    

    response = []

    conn = connectDB('spatial-db')

    cursor = conn.cursor()

    conn.execute(query)

    

    for result in cursor.fetchall():

    response.append({

        'ClusterID': result[0],

            'Count': result[1],

            'Longitude': result[2],

            'Latitude': result[3]

        })

     return response

Once again, let’s look at the query here. For the K-Means algorithm, you have access to ST_ClusterID and ST_ClusterCentroid which return the ID and centroid of each cluster, respectively. Since this is an aggregate (GROUP BY) function, you can obtain the count of points in each cluster as well. You can also perform other aggregate functions here as well according to your need. The clustering algorithm acts on a column of ST_POINTs which is provided by the nested SELECT statement. The points have to be in a planar SRS so we have to transform to SRID 1000004326 here as well.

So far, you have seen how to perform “static” clustering of points, which basically means, if you were to zoom in or out on your view currently, you would not see the clusters change. They are all static. To make this dynamic, you can attach event listeners on the view for zoom in and zoom out. These events can carry out new clustering queries from HANA whenever there is a significant change in zoom level.

Heat Maps

Another extremely useful technique for aggregating data points is using heat maps. These are easy to create using ArcGIS. Basically, you need to create either a FeatureLayer or a CSVLayer that includes a HeatmapRenderer and a data source (including longitude and latitude).

I am not completely sure how to make this work using a FeatureLayer. The documentation says that a FeatureLayer can be used, however, it does also mention that “Layers created from feature collections or client-side graphics are not supported”. If you can get FeatureLayer to work with heat maps, please comment below! Anyway, you can still incorporate heat maps in XSA applications using CSVLayer. It does require you to have a CSV file containing location information hosted somewhere on the web. The layer uses this CSV file to fetch location data for the heat maps. Assuming that you have a CSV file hosted somewhere, you can build heat maps as follows.

function showHeatmap(){

var baseURL = "https://raw.githubusercontent.com/subhanaltaf/xsa-python-geospatial/master/db/src/data/loads/"

var url = baseURL + "travel_agencies_latlng.csv";

esriConfig.request.corsEnabledServers.push(url);

    //create renderer specifying resolution and color scale

const renderer = {

        type: "heatmap",

        colorStops: [

{ color: "rgba(0, 64, 255, 0)", ratio: 0 },

{ color: "#0044ff", ratio: 0.25 },

{ color: "#00ff00", ratio: 1 }],

maxPixelIntensity: 25,

minPixelIntensity: 0

    };

    //create csv layer with renderer and csv file data

heatMapLayer = new CSVLayer({

        url: url,

        title: "Travel Agencies",

latitudeField: "LAT",

longitudeField: "LNG",

        renderer: renderer

}); 

    //show on map

    map.removeAll();

map.add(heatMapLayer);

}

Demo

I hope all the information I have described above is useful in integrating maps into your XSA applications. I have a short demo of an application I made using XSA with Python as back-end. This demo includes all the functionalities I have described above and the ability to search database using Fuzzy Search (accounts for typos, other errors in search string) and calculate distance between two points.