Skip to Content
Technical Articles
Author's profile photo Tom Turchioe

Did you know you can add Spatial Reference Systems to HANA?

**UPDATE**

I’m happy to report that as of HANA 2 SPS5 and in HANA Cloud, adding spatial reference systems is a lot easier.  In either HANA version, you can issue

CREATE PREDEFINED SPATIAL REFERENCE SYSTEM
 IDENTIFIED BY <SRID>;

Where you replace <SRID> by the spatial reference system you want to create.  Note the common ones like 4236 and 3857 should already be defined.  If you want all spatial reference systems loaded, simply issue:

CREATE PREDEFINED SPATIAL REFERENCE SYSTEMS;

Here’s a great blog by Vitaliy Rudnytskiy which is a great HANA spatial primer and also describes the statements in this update: https://developers.sap.com/tutorials/hana-spatial-intro6-srs.html

**END of UPDATE**

By default, HANA has 4 preconfigured Spatial Reference Systems (SRS).  Two of the preconfigured ones most used are 4326 (WGS 84 Spheroidal) and 1000004326 (WGS 84 Planar). However, there are over 3000 other spatial reference systems and many of our customers utilize some of them.  So how do you add additional ones?

There are two ways to add additional ones to HANA.  The first way is to add one at a time using the CREATE SPATIAL REFERENCE SYSTEM command in a SQL Console as a user with the required privileges (i.e. SYSTEM).  The second way is to use the HANA Geospatial Metadata Installer.  This web based admin tool will install additional SRS – a total of 3988 [as of HANA SPS 12] spatial reference systems.  They will be available in the target HANA instance after an update is applied using the tool.  Here is a screenshot of the admin tool after the update was applied.

In order to install the DU containing the Geospatial Metadata Installer, please follow the full instructions which are available in the appendix of the SAP HANA Spatial Reference Guide here.  The basic steps are:

  1. Download the appropriate file from the SAP Portal
  2. Unzip the file (on the client where you’re running HANA Studio)
  3. Import the unzipped file (HCOSPATIALMI.tgz) using HANA Studio
  4. Create two HANA users: one to use the Geospatial Metadata Installer and the second establish the required SQLCC connection and add new spatial reference systems in that HANA instance
  5. Assign the user for the Geospatial Metadata Installer.  This is accomplished using the XS Admin console. Here’s where there may be a slight twist (see below).  I’ve verified that for HANA 2.0 SP2, you will still need to grant the Application Privileges outlined in the discussion thread below.
  6. Finally, use the Geospatial Metadata Installer.  There are two functions available as shown in the image below:

You can view the current state which should show 4 spatial reference systems present.  To add additional ones, use the “Start Update Immediately” function.  You can’t select which ones are added, it will add the remaining 3984 spatial reference systems (as of SPS 12) to the target HANA instance.  The update occurs within a matter of 10 or 15 seconds.  Once the update is completed, you can check the status using the “View Current State…” function which is shown in the first screenshot at the top.

The slight twist I mentioned above is logging into the XS Admin console.  I used the SYSTEM user and the result was an Access Forbidden error.  It turns out the SYSTEM user did not have the requisite Application Privileges. Make sure the user accessing the XS Admin console has the Application Privilege “sap.hana.xs.admin.roles:SQLCCAdministrator”. There is also a discussion thread which describes this issue in some detail. It is available here: https://archive.sap.com/discussions/thread/3656934

These are the Application Privileges required for the SYSTEM user to be able to access the XS Admin tool:

After updating the available spatial reference systems, I wanted to make sure a particular SRS (WKID of 26781) existed after the update.  To find out, I issued the following query to verify:

select * from ST_SPATIAL_REFERENCE_SYSTEMS WHERE SRS_ID = 26781;

The query returned the following result:

Once you’ve verified the desired SRID or SRIDs that you need exist, you can use spatial methods in SQL to transform between them.  The proper spatial method is ST_Transform(<SRID>).  At first glance, it looks like ST_SRID(<SRID>) might work as well, but this method only changes the SRID and does not actually transform the geometries.

It’s common to transform from one SRS to another in GIS packages. Typically, at the client level, you’re working with small datasets (100s or 1000s of features).  When large datasets with spatial data need to be transformed, having that capability in SAP HANA means the transformation can be done at high speed.

In summary, it’s very straightforward to add additional spatial reference systems to SAP HANA and to transform between them at high speed, Just make sure you carefully follow the instructions in SAP HANA Spatial Reference Guide and in the discussion thread with respect to adding the appropriate Application Privileges for XS Admin console access.

Assigned Tags

      7 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Former Member
      Former Member

      Thanks for the great article and the valuable information it contains! Is there a list somewhere to check whether a particular reference system is available? I would not like to go through the trouble of installing the Metadata Installer only to see that the reference system I would like to use (Gauss-Krüger) is not available -- even if the number of 3988 supported reference systems is enormous...

      Author's profile photo Clemens Kopfer
      Clemens Kopfer

      Completely agree.

      We would like to know if UTM32N is available. I think this should be 32632.

      -->some kind of documentation would be appreciated.

       

      https://epsg-registry.org/

      Kind of common for public German data:

      http://sg.geodatenzentrum.de/web_download/dlm/dlm250-aaa/dlm250-aaa.pdf

      Author's profile photo Tom Turchioe
      Tom Turchioe
      Blog Post Author

      Clemens,

      Yes, 32632 – UTM32N, is part of the included SRS after you add the additional SRSs.  Here’s the query to determine if an SRS is known to HANA:

       

      This time with the screenshot 🙂

      Author's profile photo Tom Turchioe
      Tom Turchioe
      Blog Post Author

      Michael,

      What's the SRID of the Gauss-Krüger reference system?

      Tom

      Author's profile photo Clemens Kopfer
      Clemens Kopfer

      Seems not so easy to answer. eg:
      31466

      https://de.wikipedia.org/wiki/Gau%C3%9F-Kr%C3%BCger-Koordinatensystem

      In general:
      Yes, I know that I can query them. Only upon having them installed!
      I cannot simply have our Basis install everything "just for fun". Hence my (or Michael) question on a documentation.

      Author's profile photo Tom Turchioe
      Tom Turchioe
      Blog Post Author

      Clemens,

      Yes, in fact, the additional SRSs added include 31466.

      After you apply the update using the metadata installer, you will have this SRS available and can transform between this SRID and others as well as perform spatial operators.  Just keep in mind that some operations like calculating area and distance when performed using planar spatial reference systems can have some inaccuracy.  See the 3rd paragraph of this blog for a more detailed explanation.

      Hope this helps.

      Author's profile photo Former Member
      Former Member

      The Metadata Installer is derived from the EPSG dataset (http://www.epsg.org). The current version of the Installer is based on version 8.8. of the dataset. There are very few systems that do not have enough data in that set to serve as an SRS in HANA, but generally you can assume that all the EPSG systems you want to use are in there.