Skip to Content

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:

/wp-content/uploads/2014/10/20141010_132954_560830.png

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* 😉

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 🙂

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…

/wp-content/uploads/2014/10/20141010_140005_560954.png

…and after pasting some geoJSON and the transform request similar to this:

/wp-content/uploads/2014/10/20141010_140556_560955.png

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:
    • http://<yourHostName>:80<yourInstanceNumber>/sap/hana/xs/ide/editor/
    • https://<yourHostName>:43<yourInstanceNumber>/sap/hana/xs/ide/editor/


  • Create an application with a sub-package and give it a name (here: ‘converterTest’)
    • right click on content and select ‘Create application’

               /wp-content/uploads/2014/10/20141016_082826_565066.png

                     /wp-content/uploads/2014/10/20141016_081543_565067.png

  • delete the index.html file

                    /wp-content/uploads/2014/10/20141016_081611_565065.png

  • now select the package in the tree

                    /wp-content/uploads/2014/10/20141010_144005_560959.png

  • 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:

                    /wp-content/uploads/2014/10/20141010_144821_560960.png

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:

  1. select the index.html beneath your package
  2. 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’:

/wp-content/uploads/2014/10/20141010_151334_560968.png

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

                    /wp-content/uploads/2014/10/20141010_145932_560962.png

In order to convert this geoJSON to SQL inserts, you now have to copy the JSON into the XS application:

/wp-content/uploads/2014/10/20141010_150222_560966.png

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:

/wp-content/uploads/2014/10/20141010_150601_560967.png

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 my colleague 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

To report this post you need to login first.

8 Comments

You must be Logged on to comment or reply to a post.

  1. Kai-Michael Roesner

    A couple of remarks:

    The procedure above will not work if you already have an .xsapp file in the package tree where you created the package for the geoJSON converter. In this case just skip this step.

    You probably noticed yourself but just to be sure: You can’t just remove the .txt.zip extensions. Instead you have to extract the files to your PC directory and then remove the .txt extensions.

    Also, at least for the .xsaccess file, you’ll have to remove the .txt extension after uploading it to the Web IDE. Windows won’t let you have a file without file name (it interprets .xsaccess as extension).

    (0) 
    1. Kai-Christoph Mueller Post author

      Hi Kai-Michael

      thanks a lot for the hint!

      I wasn’t aware Windows is not capable of creating .anything files from scratch w/o a console or editor.

      I changed the procedure to make it more convenient for XS newbies as well.

      Best regards

      kc

      (0) 
      1. Kai-Michael Roesner

        Hi Kai-Christoph,

        the .xsaccess file that you provided as a .txt.zip works fine – you can just drag and drop the extracted .xsaccess.txt file from Win explorer to the IDE drop zone and then rename the file in the IDE to .xsaccess!

        Best regards

        Kai.

        (0) 
        1. Kai-Christoph Mueller Post author

          Hi Kai-Michael,

          thank you.

          This was actually my first approach,but: I could not upload more than three files to SCN and it is not convenient to create ‘only suffix’ files on windows machines. Renaming after upload also seems to be cumbersome to me…

          As one has to create the .xsapp file anyway, I think the whole Post is more self-contained when focusing on the implementation, namely: index.html and logic.xsjs.

          In the end it just does not matter at all how the app is created…that’s a basic XS dev task and not topic of this Post.

          All the best

          kc

          (0) 
    2. Quoc Hung

      Hi Kai-Michael

      thanks a lot for the hint!

      I wasn’t aware Windows is not capable of creating .anything files from scratch w/o a console or editor.

      I changed the procedure to make it more convenient for XS newbies as well.

      Best regards

      QuocHung

      (0) 
    1. Kai-Christoph Mueller Post author

      Hi Philipp,

       

      sorry for the late answer. It looks like with the change of the SCN theme it got lost and I also could not find them locally. Furthermore I cannot guarantee it will still run with a current release.

      Please ask the SCN Blogs moderators for this.

      Meanwhile I will try to find them on some archived disk drive.

      Sorry I cannot help you more and all the best,

      Kai-Christoph

       

      (0) 

Leave a Reply