In this example, it was straightforward with only one entry available anyhow.
Over years I posted quite many Geospatial Tuesday posts -- short examples of what you can do with SAP HANA Spatial SQL. Some of those examples were based on the public free geospatial data available in Esri Shapefile format that you would import into your SAP HANA database.
But over the years technologies have changed (including a movement to the cloud) plus some of the datasets became unavailable. Let me try to address both in this article.
Most of my Geospatial Tuesday examples were done using the free SAP HANA, express edition, as the database and SAP HANA tools plug-in in Eclipse IDE. Now I mostly work with SAP HANA Cloud and the primary tool is Database Explorer in SAP Business Technology Platform.
You might have read the post by ian.henry about Importing shapefiles into SAP HANA Cloud using the Wizard. But at that time SAP Database Explorer allowed you to import only shapefiles compressed as
The good news:
One of the datasets that are not available anymore is the Continents, which I used in "SAP HANA, tell me where the center of Europe is" downloading it from Jefferson Community & Technical College. Now I was able to find the same dataset among samples shared by Esri.
Let use it to load into the SAP HANA Cloud trial's HANA database using Database Explorer.
The three files required by SAP HANA are
So, let's download (or select, if you cloned the complete repository of sample files from Esri's repository on GitHub)...
...and then compress them as a ZIP file.
Please note it is important that you do not include folder names along with files into the ZIP file!
At MacOS it happens automatically if you are using standard Finder's compression. So, I am using preinstalled
Now with the ZIP file containing all required shapefile components is available on our local computer it is time to move to SAP HANA Cloud and to open Database Explorer (aka DBX).
Right-click on the database entry, where you want to import geospatial data into...
and pick Import Data to open a wizard to load ESRI Shapefiles type.
Selecting the file from your local machine should be straightforward, but then make sure you select only the entry(-ies) containing required shapefile data and not any other files you might have in the ZIP file.
To be consistent with my previous examples, in the next step I put
The next step is where you need to know what is the SRID describing the projection of the data loaded.
The list you have shows the SRIDs activated in your database and available in the
In my case, I simply want to use the same SRID
Once the import is completed successfully, we should have the table with data available for querying.
Let's check!
Let's use a query similar to the one before.
Or -- as you might be aware -- to check results visually you can use the DBevar client. Please note removed
Technologies change, but at least spatial data stay the same! Ok, almost 😉
I hope this new functionality helps you to run my Geospatial Tuesday examples in SAP HANA Cloud now!
Let's not stay still (at least with the knowledge),
-Vitaliy, aka @Sygyzmundovych
But over the years technologies have changed (including a movement to the cloud) plus some of the datasets became unavailable. Let me try to address both in this article.
Most of my Geospatial Tuesday examples were done using the free SAP HANA, express edition, as the database and SAP HANA tools plug-in in Eclipse IDE. Now I mostly work with SAP HANA Cloud and the primary tool is Database Explorer in SAP Business Technology Platform.
You might have read the post by ian.henry about Importing shapefiles into SAP HANA Cloud using the Wizard. But at that time SAP Database Explorer allowed you to import only shapefiles compressed as
.tgz, which is not a format usual among users of the MS Windows OS.The good news:
Since April you can import shapefiles compressed as .zip too 🚀Load world continents shapefile from Esri
One of the datasets that are not available anymore is the Continents, which I used in "SAP HANA, tell me where the center of Europe is" downloading it from Jefferson Community & Technical College. Now I was able to find the same dataset among samples shared by Esri.
Let use it to load into the SAP HANA Cloud trial's HANA database using Database Explorer.
The three files required by SAP HANA are
.shp, .shx and .dbf. Please note that SAP HANA does not need .prj projection file as the database is using Spatial Reference IDs instead. As you will see it means that you need to know what SRID corresponds to the spatial data you are going to load.Prepare files
So, let's download (or select, if you cloned the complete repository of sample files from Esri's repository on GitHub)...
...and then compress them as a ZIP file.
Please note it is important that you do not include folder names along with files into the ZIP file!
At MacOS it happens automatically if you are using standard Finder's compression. So, I am using preinstalled
zip tools.ls -l world-continent/*
zip -jX world-continent.zip world-continent/world-continents*
zipinfo -l world-continent.zipUse the import wizard in DBX
Now with the ZIP file containing all required shapefile components is available on our local computer it is time to move to SAP HANA Cloud and to open Database Explorer (aka DBX).
Right-click on the database entry, where you want to import geospatial data into...
and pick Import Data to open a wizard to load ESRI Shapefiles type.
Selecting the file from your local machine should be straightforward, but then make sure you select only the entry(-ies) containing required shapefile data and not any other files you might have in the ZIP file.
To be consistent with my previous examples, in the next step I put
TESTGEO as a schema. The wizard will pick your database user's schema if you leave the field empty.The next step is where you need to know what is the SRID describing the projection of the data loaded.
The list you have shows the SRIDs activated in your database and available in the
ST_SPATIAL_REFERENCE_SYSTEMS table.In SAP HANA Cloud you can add a predefined SRID using the SQL statementCREATE PREDEFINED SPATIAL REFERENCE SYSTEM IDENTIFIED BY, if missing in the list. Alternatively, you can add all predefined SRIDs with a single SQL statementCREATE PREDEFINED SPATIAL REFERENCE SYSTEMS.
If unsure, you can leave the filed empty, which equals to the SRID0and convert spatial data to the required Spatial Reference ID later.
In my case, I simply want to use the same SRID
1000004326 as in earlier examples.Once the import is completed successfully, we should have the table with data available for querying.
Let's check!
Find central points of continents
Let's use a query similar to the one before.
SELECT "CONTINENT", "SHAPE".ST_Centroid().ST_asWKT()
FROM "TESTGEO"."world-continents";Or -- as you might be aware -- to check results visually you can use the DBevar client. Please note removed
ST_asWKT() method.SELECT "CONTINENT", "SHAPE".ST_Centroid() AS "CENTRAL_POINT"
FROM "TESTGEO"."world-continents";Technologies change, but at least spatial data stay the same! Ok, almost 😉
I hope this new functionality helps you to run my Geospatial Tuesday examples in SAP HANA Cloud now!
Let's not stay still (at least with the knowledge),
-Vitaliy, aka @Sygyzmundovych
- SAP Managed Tags:
