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

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.  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)
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.  This discussion thread shows which privileges must be added for the user accessing the XS Admin console. 

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.