Over years I posted quite many Geospatial Tuesday posts — short examples of what you can do with SAP HANA Spatial SQL. Some of those examples were based on the public free geospatial data available in Esri Shapefile format that you would import into your SAP HANA database.

But over the years technologies have changed (including a movement to the cloud) plus some of the datasets became unavailable. Let me try to address both in this article.

Most of my Geospatial Tuesday examples were done using the free SAP HANA, express edition, as the database and SAP HANA tools plug-in in Eclipse IDE. Now I mostly work with SAP HANA Cloud and the primary tool is Database Explorer in SAP Business Technology Platform.

You might have read the post by Ian Henry about Importing shapefiles into SAP HANA Cloud using the Wizard. But at that time SAP Database Explorer allowed you to import only shapefiles compressed as .tgz, which is not a format usual among users of the MS Windows OS.

The good news:

Since April you can import shapefiles compressed as .zip too 

Load world continents shapefile from Esri

One of the datasets that are not available anymore is the Continents, which I used in “SAP HANA, tell me where the center of Europe is” downloading it from Jefferson Community & Technical College. Now I was able to find the same dataset among samples shared by Esri.

Let use it to load into the SAP HANA Cloud trial’s HANA database using Database Explorer.

The three files required by SAP HANA are .shp, .shx and .dbf. Please note that SAP HANA does not need .prj projection file as the database is using Spatial Reference IDs instead. As you will see it means that you need to know what SRID corresponds to the spatial data you are going to load.

Prepare files

So, let’s download (or select, if you cloned the complete repository of sample files from Esri’s repository on GitHub)…

…and then compress them as a ZIP file.

Please note it is important that you do not include folder names along with files into the ZIP file!

At MacOS it happens automatically if you are using standard Finder’s compression. So, I am using preinstalled zip tools.

ls -l world-continent/*
zip -jX world-continent.zip world-continent/world-continents*
zipinfo -l world-continent.zip

Use the import wizard in DBX

Now with the ZIP file containing all required shapefile components is available on our local computer it is time to move to SAP HANA Cloud and to open Database Explorer (aka DBX).

Right-click on the database entry, where you want to import geospatial data into…

and pick Import Data to open a wizard to load ESRI Shapefiles type.

Selecting the file from your local machine should be straightforward, but then make sure you select only the entry(-ies) containing required shapefile data and not any other files you might have in the ZIP file.

In this example, it was straightforward with only one entry available anyhow.

To be consistent with my previous examples, in the next step I put TESTGEO as a schema. The wizard will pick your database user’s schema if you leave the field empty.

The next step is where you need to know what is the SRID describing the projection of the data loaded.

The list you have shows the SRIDs activated in your database and available in the ST_SPATIAL_REFERENCE_SYSTEMS table.

In SAP HANA Cloud you can add a predefined SRID using the SQL statement CREATE PREDEFINED SPATIAL REFERENCE SYSTEM IDENTIFIED BY, if missing in the list. Alternatively, you can add all predefined SRIDs with a single SQL statement CREATE PREDEFINED SPATIAL REFERENCE SYSTEMS.

If unsure, you can leave the filed empty, which equals to the SRID 0 and convert spatial data to the required Spatial Reference ID later.

In my case, I simply want to use the same SRID 1000004326 as in earlier examples.

Once the import is completed successfully, we should have the table with data available for querying.

Let’s check!

Find central points of continents

Let’s use a query similar to the one before.

SELECT "CONTINENT", "SHAPE".ST_Centroid().ST_asWKT()
FROM "TESTGEO"."world-continents";

Or — as you might be aware — to check results visually you can use the DBevar client. Please note removed ST_asWKT() method.

SELECT "CONTINENT", "SHAPE".ST_Centroid() AS "CENTRAL_POINT"
FROM "TESTGEO"."world-continents";

Technologies change, but at least spatial data stay the same! Ok, almost 

I hope this new functionality helps you to run my Geospatial Tuesday examples in SAP HANA Cloud now!

Let’s not stay still (at least with the knowledge),
-Vitaliy, aka @Sygyzmundovych