Explore Visualization Options for Spatial Data in SAP HANA Cloud

For the exercises in this tutorial, a basic understanding of SQL is helpful. To get the most out of the content, we advise to take some time to read and understand the SQL statements and procedures - not just copy/paste and execute. Some of the concepts explained can be re-used in many different scenarios.

You need to set up your own environment to run the exercises. By following the instructions in this section, you will have everything that you need for the exercises presented in the next articles.

These are the requirements to follow along:

1. Prepare your SAP HANA database instance in SAP HANA Cloud.

2. Import the sample data to your instance.

3. Choose one of the options to visualize spatial data depending on your system.

1. Sign-up for the SAP HANA Cloud trial. If you already have a trial account with SAP Cloud Platform, check your trial entitlements to add SAP HANA Cloud. For more information check out Jump Start Your SAP HANA Cloud, SAP HANA Database (free tier or trial).

2. Provision an SAP HANA Cloud, SAP HANA database in trial.

3. Make sure your SAP HANA Cloud, SAP HANA database is running in your trial account.

4. Create a user and assign this user the privileges CREATE SCHEMA and IMPORT. If you are unsure how to create users and assign privileges, you can read more here.

   Take the following code for example.

   SQL Copy

   CREATE USER USER1 PASSWORD "Password1";
   -- Grant CREATE SCHEMA privilege
   GRANT CREATE SCHEMA TO USER1;
   -- Grant IMPORT system privilege
   GRANT IMPORT TO USER1;

5. From BTP cockpit or SAP HANA Cloud Central, open your trial instance in the SAP HANA Database Explorer and log in with this user’s credentials.

We have prepared sample data from OpenStreetMap that we will use throughout the following tutorials. This includes street network and point of interest ( POI ) data from the London area. The sample data was prepared using the osmnx python package(*) and loaded into SAP HANA Cloud, SAP HANA database using the Python Machine Learning Client for SAP HANA.

You can find the sample data in a public GitHub repository: SAP-samples/teched2020-DAT260

The data for the exercises is packaged as an SAP HANA database export. The export file is in the data folder of the GitHub repository. Follow these steps:

1. Download the file to your local computer.

2. Once you have downloaded the export file, you can use the SAP HANA Database Explorer to connect to your SAP HANA Cloud system.

3. Right-click on the Catalog and select Import Catalog Objects to start the wizard.

   

4. You can keep all the default settings in the Import Catalog Objects wizard and click Import.

   

After import, you will find five tables in the schema DAT260. There is LONDON_POI which contains 90k points of interest in the London area. This includes pubs and bike repair shops. The street network data is in LONDON_EDGES, which represents the actual 1.5 million street segments, and LONDON_VERTICES, which describes the 800,000 road junctions. Finally, there are two tables containing data of the London Tube system - LONDON_TUBE_STATIONS and LONDON_TUBE_CONNECTIONS.

The most important tables will be LONDON_POI, LONDON_EDGES and LONDON_VERTICES. Here you can have a look at some of the important columns in these tables:

1. POI Data (Table LONDON_POI)

   The Table LONDON_POI contains points of interest. Other than the naming suggests these POI’s can also be represented by polygon (e.g. the ground view of a house). There are different categories of POI’s. A POI may be a park bench as well as a hospital or school. The field amenity can be used to filter the type of POI.

   

2. Edge Data (Table LONDON_EDGES)

   The Table LONDON_EDGES contains street (or path) segments. If you understand the streets and pathways as a network or graph, you may consider a street the edge of a graph. Intersections between streets would then be vertices (nodes) in this graph.

   

3. Vertex Data (Table LONDON_VERTICES)

   The Table LONDON_VERTICES contains all the vertices, that can be inferred from the street network in table LONDON_EDGES. So, each vertex is essentially a point where at least two street segments come together.

   

For this step, select your preferred method by clicking on the options under the step title.

Especially when working with Spatial data it can be extremely valuable to visualize the results - ideally on a map. Without proper visualization, you will just receive geometries described by latitude/longitude pairs, which is hard to interpret.

Depending on your local restrictions to install software, you can consider one of the following three alternatives to visualize the results of a spatial query.

We will use a reference query to show how each option works. To visualize a simple point, the query generates a point by using a select on table DUMMY that calls the spatial function ST_GeomFromText(*).

SELECT ST_GeomFromText('POINT(8.642057 49.293432)', 4326) AS SHAPE FROM DUMMY;

In this step, you can get to know three visualization options by clicking on the three different tabs.

DBeaver.io GeoJson.io SAP HANA Cloud Function

DBeaver.io - Open Source DB Client with built-in Spatial Visualizations

DBeaver.io is an open source database client with support for SAP HANA Cloud. The free community edition can be downloaded on dbeaver.io.

1. To set up a connection to DBeaver, select the New Database Connection icon in the upper left corner. Search HANA and select the SAP HANA database option. Then select Next.

   

2. Select HANA Cloud under the Edition dropdown. Locate your SQL Endpoint for your SAP HANA Cloud database and paste it into DBeaver’s Host field, removing the :443 at the end.

   

   The Port should auto-populate with 443. Finally, enter your user and password.

   

3. Click Test Connection. If this is your first time connecting to SAP HANA using DBeaver, DBeaver will prompt you to download the necessary driver. Click the download option in the pop-up window. Once the driver is installed and the connection test is successful, click Finish.

   

4. The connection should be visible on the side panel in DBeaver.

   

The example query above will simply return the results while showing a map preview of the geometries.

You should now have an overview of the technical pre-requisites as well as the necessary background information to master the exercises in this tutorial group. When starting with the exercises keep in mind that the data is residing in the schema ´DAT260´. Make sure to set the context to this schema by executing:

SET SCHEMA DAT260;

You are ready to start with the first exercise in the next tutorial. There, you will learn how to add Planar Geometries Based on the tables you have imported.