Did you know HANA comes with a geospatial engine since SPS07 (What s New? SAP HANA SPS 07 Geospatial Pr... | SAP HANA & What's new in SAP HANA SP07)? Did you think of the power you have, when master, real time and geospatial data all come together in one engine? Scenarios like real time tracking the spread of infections (healthcare), decision support systems for urban planning (crime, commute, recreation areas, ...), targeted customer marketing, effective sales delivery and last but not least connected cars are just some of them coming to my mind immediately.
So you really should start thinking about all the possibilities you have got right now!
When we started to implement a scenario for the Solheim cup the first question to be solved was: How to model the world and get the model into HANA?
This Post is going to answer this question and give you some handy code you can easily deploy into your cloud or on premise HANA instance.
UPDATE:
If you are interested in the recording of my speech at SAP TechEd Session in Las Vegas, check out this one:
Prerequisites
Entering the geospatial world, you will be facing concepts, which are not too familiar for the most of us. Of course we all have used services like google maps and different navigation systems, but do you already know about all the conventions and standards like clock wise orientation or geoJSON?
As we are looking at the geospatial engine in HANA, I assume you do have some basic experience with HANA itself.
OK, so let's get started with the easy prerequisite:
Technology
You need to have access to a HANA box. This may be an on-premise instance, your HANA factory cloud account, a HANA cloud trial instance or anything else running HANA with a revision >= 70. If you do not have anything at all, I strongly recommend using the free to use HANA cloud trial instance.
Other than that you need the usual developer rights and rights for the schema, you want to persist the geospatial data in.
I am using the HANA factory cloud and my account has got the following roles:
out of which the most important ones are:
- sap.hana.xs.ide.roles::Developer
- sap.hana.xs.debugger::Debugger
- sap.hana.xs.ide.roles::CatalogDeveloper
- sap.hana.xs.ide.roles::EditorDeveloper
I usually tend to give my user everything containing *xs* :wink:
If you already have installed HANA Studio or HANA Cloud computing platform tools, that's good and you can use it as well. But actually you won't need it here :smile:
Knowledge
If you are new to the area of geospatial you should spend some time to get familiar with the topic. Though it is not necessary to get the things of this Post running. Nevertheless I recommend spending some time here, as there are some really special things about it:
- Longitude & latitude versus Cartesian coordinates
- radian versus degrees
- Spatial reference systems { 0 || WGS84-4326 || WGS84-1000004326 }
- Clock-wise/counter clock-wise orientation
- data formats like geoJSON, KML, shape files, ...
As a good start I recommend reading the SAP HANA Spatial Reference guide.
geoJSON
The most important thing right now is: you have to understand what geoJSON is. If you already are familiar with JSON, that's easy. If not you should read:
On the official page (GeoJSON) there is a nice and crisp definition:
"
GeoJSON is a format for encoding a variety of geographic data structures.
{ "type": "Feature",
"geometry": {
"type": "Point",
"coordinates": [125.6, 10.1] },
"properties": { "name": "Dinagat Islands" }
}
}
GeoJSON supports the following geometry types: Point, LineString, Polygon, MultiPoint, MultiLineString, and MultiPolygon. Lists of geometries are represented by a GeometryCollection. Geometries with additional properties are Feature objects. And lists of features are represented by a FeatureCollection.
".. source: http://geojson.org
Nothing to add here. Please see the GeoJSON Specification for more details.
Installation
After you deployed the code you will have a converter from geoJSON to SQL inserts. These can be copied and executed in a SQL console. The tool is capable of creating a destination table for you. It allows to specify schema and table name. The created table has a very simple structure:
(ID type int, GEO type ST_GEOMETRY)
Is is meant as a starting point for you and therefore kept simple on purpose. The converter looks like this in it's initial state...
...and after pasting some geoJSON and the transform request similar to this:
Deployment
Assuming you have got the necessary rights, the easiest way to deploy the tool is via the Web IDE.
Get the code
You will find two out of the required files in the attachment for which you need to remove the .txt extension after extracting the zip file.
The application (.xsapp & .xsaccess) needs to be created by yourself, if you do not plan to use an existing one (you need to know what you do, if you do not follow my script).
After downloading the files, please extract them and remove the ending '.txt' Your folder now should contain:
- index.html
- converts the JSON input to SQL inserts
- contains the front end logic
- logic.xsjs
- checks for DB consistency
- handles create DB object request
As the coding is pretty straight forward, so I do not think it makes a lot of sense to go into details here. If you have questions or remarks regarding the coding, please do not hesitate to ask.
Deploy the code
We are going to use the Web IDE/ development workbench to get the job done.
All you have to do is:
- Open one of the following URLs in you browser:
- Create an application with a sub-package and give it a name (here: 'converterTest')
- right click on content and select 'Create application'
- delete the index.html file
- now select the package in the tree
- select the local files using your file browser (Windows Explorer, Nautlius, Dolphin,...)
- drag and drop them to the 'multi-file drop zone'
- in the console you should see some log messages:
08:15:07 >> Application in Package converterTest created successfully.
08:16:20 >> File converterTest/index.html deleted successfully.
08:24:23 >> File logic.xsjs uploaded successfully.
08:24:24 >> File index.html uploaded successfully.
- unfolding the created package reveals:
And that's it. The converter has been installed and you go ahead and use it.
Using the converter
Run it
To run the converter, you just go to one of the following URLs:
- http://<yourHostName>:80<yourInstanceNumber>/<yourPackageName>/
- https://<yourHostName>:43<yourInstanceNumber>/<yourPackageName>/
As you already might be in the editor, the easiest way to achieve is:
- select the index.html beneath your package
- push F8 OR click the green run arrow in the icons menu on the left site
If you want to bookmark the URL, I would recommend removing everything behind index.html
Prepare data structures
You have to ensure the sequence and a table for the data import do exist. This can either be done via the tool itself, or manually. Using the tool, you just provide the desired names and hit 'Create these DB objects':
If you prefer to do it manually, open a SQL console and send (assuming your schema name shall be GEO and the table name will be geoTable😞
CREATE SCHEMA GEO;
CREATE COLUMN TABLE "GEO"."geoTable" ("ID" INTEGER CS_INT, "GEO" ST_GEOMETRY(4326) CS_GEOMETRY) UNLOAD PRIORITY 5 AUTO MERGE;
CREATE SEQUENCE "GEO"."geoSequence";
Of course you do need the necessary authorizations on the according schema...
Use it
In order to use the tool, we have to get some geoJSON. There are plenty of possibilities out there, personally I most of all like http://geojson.io, but this is up to you...
Using geojson.io you need to:
- go to http://geojson.io
- search for your spot (e.g. 'Yosemite national park')
- select one of the tools (point, linestring, polygon)
- model whatever you want to use later on
- watch out: polygons have to be closed in the end
- finally you will come up with your modeled geoJSON on the right side of your window
In order to convert this geoJSON to SQL inserts, you now have to copy the JSON into the XS application:
Now hit 'Transform to WKT' in the converter tool (WKT stands for WellKnownText, which is a standard notation).
If table and sequence exist (see subchapter above) and have the right format (ID type INT, GEO type ST_GEOMETRY) you will get the inserts within a new div inside this window:
So ... that was easy, wasn't it?
What's next?
Now that you have some geo data available in your database, you can use it using standard SQL statements. This way you can filter for certain records e.g. cars within a certain distance (ST_DISTANCE function) or whether a golf ball hit the green (ST_WITHIN). There are a lot of functions available right now and there will be a lot of new ones with SPS09.
Please check the SAP HANA Spatial Reference guide for a list of all functions and a deep dive into the topic.
Conclusion
I hope you got the central idea on how to get started in the geospatial area and understand the big potential it offers.
I am pretty sure we are going to see very interesting innovations coming around that corner.
The tool itself can be enhanced in many ways (e.g. allow tables which are structured in another way, execute the sql code via front end, handle multiGEOs [not available at geojson.io]). As the converter was supposed to be a starting point, I rather kept it straight forward and simple. I hope you like it this wat?!
If you are interested in further geospatial topics, you are more than welcome to join me (in Las Vegas) on (Wednesday or on Thursday) or frank.albrecht Frank Albrecht in Berlin at our SAP TechEd && d-code session 'Mapping the World with SAP HANA Geospatial Engine (DEV103)'.
UPDATE:
The session was recorded and is now available online at: DEV103 - Mapping the World with SAP HANA Geospatial Engine | teched
Stay tuned for more
Kai-Christoph
- SAP Managed Tags:
9 Comments
