In case you have missed the news: recently version 3.5 of GDAL has been released, which now includes an OGR database driver for SAP HANA in autoconf & cmake builds: https://gdal.org/drivers/vector/hana.html. This new functionality was contributed to the GDAL project by SAP HANA multi-model engineering team.
The Geospatial Data Abstraction Library (GDAL) is an open-source computer software library for reading and writing raster and vector geospatial data formats. It also comes with a variety of useful command line utilities for data translation and processing.
As stated above SAP HANA driver is included in cmake builds, so I have built GDAL binaries on my MacBook laptop with Apple M1 (ARM-based) chip.
I would like to show you some examples of using the software with SAP HANA first (thanks to Maksim Rylov from the engineering team of Mathias Kemeter for inspiration), and then explain the steps of how I built the binaries in a separate blog post.
Examples of using GDAL with SAP HANA
As mentioned GDAL comes with a variety of useful command line utilities for data translation and processing. Let’s have a look at examples of using two of them: ogrinfo and ogr2ogr. Please share more examples in the comments, if you get some!
ogrinfo
Firstly, let’s check if HANA driver is built into our GDAL binaries:
ogrinfo --formats
Secondly, let’s check spatial features loaded into the schema "TESTGEO". In my database, there is a table "world-continents" loaded during the previous exercise Import zipped shapefiles into SAP HANA Cloud using Database Explorer.
ogrinfo -ro HANA:"DSN=DBADMIN-VITAL-TRIAL-HC-HDB;SCHEMA=TESTGEO"
Please note that while on macOS both single quotes ‘ and double quotes ” work, I found only double quotes ” working without issues on Windows.
The connection string "DSN=DBADMIN-VITAL-TRIAL-HC-HDB;SCHEMA=TESTGEO" is the ODBC connection string. I will share its setting a bit later in the post. For now please note that SCHEMA is the necessary part of it.
Thirdly, let’s check summary-level (hence -so flag) details about "world-continents" table (or a “layer” accordingly to the GDAL terminology).
ogrinfo -ro HANA:"DSN=DBADMIN-VITAL-TRIAL-HC-HDB;SCHEMA=TESTGEO"
-so world-continents
Without the -so flag the command will additionally output values of all features too, like
You can limit values outputted by using -sql parameter, eg. only continent name and size in square miles and kilometers for continents smaller than 5,000,000 sq miles.
ogrinfo -ro HANA:"DSN=DBADMIN-VITAL-TRIAL-HC-HDB;SCHEMA=TESTGEO"
-sql "SELECT CONTINENT, SQMI, SQKM FROM "world-continents" WHERE SQMI<5000000"
ogr2ogr
At the time of writing this post, there is a number of spatial data formats that the SAP HANA database can import, incl. ESRI Shapefiles or Geohash. ogr2ogr makes it possible to import/export in all spatial vector formats supported by the binary, in my case 66 formats, incl. KML:
ogr2ogr --formats | grep vector | wc -l
In an example, I used before I have used ESRI Shapefile from samples shared by Esri. In the same sample, you can find data in a different format: Keyhole Markup Language, or KML.
Equivalent command on Windows would be
ogr2ogr --formats | findstr vector | find /c /v ""
I have downloaded those Esri samples to a local ~/ProjectsLocal/arcgis-runtime-samples-data folder, and it includes a file with volcanoes in the KML format:
ogrinfo -ro ~/ProjectsLocal/arcgis-runtime-samples-data/kml/Volcanoes.kml
…and I can see there are 3 volcano areas in Western Europe:
ogrinfo -ro ~/ProjectsLocal/arcgis-runtime-samples-data/kml/Volcanoes.kml
-so "Western Europe"
Let’s load them into my SAP HANA database:
ogr2ogr -f "HANA" HANA:"DSN=DBADMIN-VITAL-TRIAL-HC-HDB;SCHEMA=TESTGEO"
-t_srs EPSG:4326
~/ProjectsLocal/arcgis-runtime-samples-data/kml/Volcanoes.kml
"Western Europe"Please note the Spatial Reference System transformation to EPSG:4326 done using -t_srs flag.
Here is the preview of the data loaded into the table TESTGEO."WESTERN EUROPE":
I have not heard about the West Eifel Volcanic Field in Germany before.
ODBC setup
As promised, here is an explanation about ODBC setup as GDAL is using ODBC to connect to the SAP HANA database. It means I have both installed on my laptop with macOS:
- SAP HANA Client, and
- unixODBC.
SAP HANA Client
SAP HANA Client has been installed in the default location for macOS, ie. /Applications/sap/hdbclient/.
file /Applications/sap/hdbclient/libodbcHDB.dylib
/Applications/sap/hdbclient/hdbuserstore LIST
As you can see:
- It is Intel (
x86_64) SAP HANA Client’s ODBC library. - I have created a HANA User Store entry with a key
DBADMIN-VITAL-TRIAL-HC-HDB, that allows me to connect to my HANA database in SAP HANA Cloud as aDBAdminuser.
unixODBC
At the time of writing this blog post SAP HANA Client is available for macOS on Intel only, so I needed both binaries in Intel versions. Otherwise, mixing Intel binary of SAP HANA Client with ARM binary of unixODBC resulted in the error like:
ERROR 1: HANA connection failed: ERROR: 0: 01000 : [unixODBC][Driver Manager]Can't open lib '/Applications/sap/hdbclient/libodbcHDB.dylib' : file not found
So, I have brew installed in an Intel-compatible architecture session, the same as I have described in Running hdbcli on an Apple M1 chip: an alternative way with using arch command.
Then I used this Intel-based brew to install Intel-compatible unixodbc on i386 emulated architecture.
arch
which brew
brew info unixodbc
file $(which odbcinst)
odbcinst -j
ODBC’s user data sources are configured in ~/.odbc.ini file. Here is mine:
cat ~/.odbc.ini
Please, do not be confused as I used the same DBADMIN-VITAL-TRIAL-HC-HDB value for both:
- a key in the HANA user store, and
- a DSN key in the ODBC configuration.
You can find more information about ODBC driver for SAP HANA in Daniel Van Leeuwen‘s tutorial “Connect Using the SAP HANA ODBC Driver“.
That’s the end of the review using GDAL 3.5+ software with SAP HANA database to process spatial data. In a separate post, I describe how I have built GDAL binaries on macOS with Apple M1 (ARM-based) chip.
Regards,
-Vitaliy, aka @Sygyzmundovych
