Skip to Content
Technical Articles
Author's profile photo Ian Henry

Importing shapefiles into SAP HANA Cloud using the Wizard

In this blog post I will try and show you the easiest way to import shapefiles data into HANA Cloud, via the wizard.

There are alternative ways of importing spatial data, that can be used.

  • Import shapefile via wizard
  • Import CSV via wizard and convert to spatial data types
  • Import CSV from Cloud Object Store (S3, GCS, ADL) and convert.

For clarification, spatial data means geometries, shapes such as polygons that will be stored as spatial data types.

At minimum, the wizard expects a .tar.gz file containing both the .shp and .dbf file of the the same name.  These must be packaged within a .tar.gz for upload.

Figure%201.5%3A%20Create%20.tar.gz

Figure 1.5: Create .tar.gz

1. Database Explorer

The easiest way to import a shapefile is using the Database Explorer, via Import Data

Right Click and select Import Data, open the wizard.  We can use that with an appropriate .tar.gz and follow the steps.

Figure%202.1%3A%20Import%20Data

Figure 1.1: Import Data

 

Figure%202.2%3A%20Select%20File

Figure 1.2: Import Type, ESRI Shapefiles

 

Figure%202.3%3A%20Select%20File

Figure 1.3: Select file, must be .tar.gz containing .shp and .dbf

Figure%202.4%3A

Figure 1.4: Import Target Schema, table name will be the original filename without extensions

 

Figure 1.5: Specify Spatial Reference (SRID)

 

Figure%202.6%3A%20Import%20Successful

Figure 1.6: Import Complete

 

Figure%202.7%3A%20Check%20Table

Figure 1.7: Check Table

 

Figure%202%3A8

Figure 1.8: Database Explorer Data Preview, SHAPE column not so readable here

Figure%202.9%20DBeaver

Figure 1.9: DBeaver, much better to validate spatial data

 

2. Prepare the Data (if necessary)

Shapefiles often come with additional metadata that can be recognised incorrectly.  You may need to remove or correct this with some data prep, I have used QGIS for this.

The file I’m using is the English geographic regions from UK Office National Statistics (ONS).

Figure%201.1%20Download%20Shapefile

Figure 2.1 Download Shapefile

Figure%201.2%3A%20Feature%20layer%20metadata

Figure 2.2: Feature layer metadata

Viewing the associated metadata of this file shows that the spatial reference is 27700, you can install this spatial reference in the HANA Cloud system.

CREATE PREDEFINED SPATIAL REFERENCE SYSTEM IDENTIFIED BY 27700;

We can open the shapefile with QGIS to check and modify the metadata if required.

Figure%201.2%3A%20QGIS%20-%20Save%20Feature%20Layer%20as

Figure 2.3: QGIS – Save Feature As

Figure 2.4: Save Vector Layer – Select fields for export

 

3. Troubleshooting

The import process does not perform much validation on the spatial datatypes.
For example if one of the points of the shape intersects itself. Thanks goes to a colleague of mine Remi Astier, who educated me on these type of issues. Luckily HANA has some SQL functions to help us ST_ISVALID() and ST_INVALIDREASON().

I was then able to copy and paste the details of the error and identify the problematic points within the  polygon.

SELECT *, SHAPE.ST_INVALIDREASON() FROM COUNTIES WHERE SHAPE.ST_ISVALID()=0; 

Figure%203.1%3A%20ST_INVALIDREASON

Figure 3.1: ST_INVALIDREASON

 

In summary, in this blogpost I described how to use the Import Data for ESRI Shapefiles. With this wizard we can quickly and easily import spatial data into SAP HANA Cloud.

Assigned Tags

      3 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Liliana Cantero
      Liliana Cantero

      Hi Ian, thank you very much for the tutorial. I was able to import a SHP file successfully into a Hana table. Now my question is how to show the polygons in SAC. Thanks a lot! Liliana

      Author's profile photo Ian Henry
      Ian Henry
      Blog Post Author

      Hi Liliana,

      Great stuff.

      I have checked with some colleagues, yes this is feature is available.  However the documentation has not yet been completed.  I plan to create a blog that covers this.  I will let you know once it's available.

      Author's profile photo Ian Henry
      Ian Henry
      Blog Post Author

      Hi Liliana,

      For completeness here the link describing Using Choropleth Layers with HANA Cloud and SAP Analytics Cloud.

      Cheers, Ian.