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.