Skip to Content
Author's profile photo Trinoy Hazarika

Experiences with SAP HANA Geo-Spatial Features โ€“ Part 1

SAP HANA Geospatial processing feature was launched with SAP HANA SP6. As a developer I had no idea even what spatial processing meant but with the help of the content available over SCN and doing some hand-on as a part of SAP Blue Project(You can see the full blog atHANA Cookbook for MySQL Developers) I have come up with a small demo of an application which highlights some of the geospatial features.

The demo will look as shown below:-

/wp-content/uploads/2014/02/main1_395467.png

The blog will be divided into 2 parts where-in I will try to highlight how the application was created from an end to end perspective.

In Part 1(current document) I will explain some of the geospatial features available in SAP HANA and how we can write logic to store and access such data. I will also show what data was used in my application.

In PART 2 of this blog Experiences with SAP HANA Geo-Spatial Features – Part 2 I will show you details on how geospatial information can be access via XSJS as GeoJson and how can we integrate Leaflet MAP client to display the data.

So to start SAP HANA has brought in some new spatial data types like POINT (ST_POINT) and Geometry (ST_GEOMETRY) to store spatial information. A point is like a fixed single location in space and will be represented by X and Y co-ordinates (*can also have a Z co-ordinate in case of 3D space).

A Geometry is like a super class container and can store the below type within it.

/wp-content/uploads/2014/02/pic1_395361.jpg

Below you can see how we can store and retrieve data from a Point or Geometry Type

Point

Geometry

SET SCHEMA “DEMO_SPA”;

create column table spatial_point

(

point ST_POINT

);

insert into spatial_point values (new ST_POINT(0.0, 0.0));

select point.ST_AsGeoJSON() from spatial_point;

Output:-

SET SCHEMA “DEMO_SPA”;

create column table spatial_geom

(

shape ST_GEOMETRY

);

insert into spatial_geom values (new ST_POINT(0.0, 0.0) );

insert into spatial_geom values (new ST_POLYGON(‘POLYGON((0.0 0.0, 4.0 0.0, 2.0 2.0, 0.0 0.0))’) );

select shape.ST_AsGeoJSON() from spatial_geom;

Output:-

So basically in a Geometry data type we can store any of the child types like Line, Polygon or Point. You can also see that we are querying the data as GeoJson which is a special JSON format for encoding a variety of geometrical data structures and it is easily understood by most of the MAP client APIs. More information about GeoJson can be found below here: – http://geojson.org/.

SAP HANA also provides a list of other means to extract data from geometrical data structures apart from GeoJson like Well Known Text (WKT), Well Known Binary (EKB) etc. More information is available in the HANA SPATIAL reference found in the link below:-
http://help.sap.com/hana_platform#section7

Now coming to the application where-in I am showing all the parliamentary constituencies of India (*Note: The data regarding the parties ruling the constituencies might not be accurate as the data was collected was from 2009 and it might have changed over time)

So first I had to collect the shape files for all the constituencies of INDIA. Finally after some amount of research here and there I finally got the data of the Indian parliamentary constituency. You can find the same in this link https://drive.google.com/folderview?id=0B3zSndF4HyuLVG43NUJrUHlCLTQ&usp=sharing.

The data was in ESRI shape file format (http://en.wikipedia.org/wiki/Shapefile) and luckily SAP HANA supported the shape file import into it.

So in order to load shape file into HANA you need to perform the 4 simple steps stated below:-

  1. Download Putty(http://www.putty.org/) and PSCP(http://www.nber.org/pscp.html)
  2. Copy the shape file(zip it first) from your local machine into HANA using PSCP
    C:\<path to PSCP directory>>pscp.exe  <source file> <OS_Username>@<HANA _server name>:<destination folder>
  3. You can login to you server using putty and unzip the files.
  4. You can import the shape files in HANA by running the below command

        IMPORT “Schema_Name”.”Table_Name” AS SHAPEFILE FROM ‘path to shape file’
        Note: Don’t give the extension of the shape file in the path. Just mention its name.

So once I imported my shape files into HANA, the data looked like below:-
/wp-content/uploads/2014/02/pic11_395362.png

So as you can see I had the list of all the constituencies along with the following information:-

  1. The state to which it belong, the ruling party, area and the color code(some of the fields were added later)
  2. The shape information.

I can also see the shape as GeoJson and the data looked like as shown below.

/wp-content/uploads/2014/02/pic2_395441.png

So you can see that most of the shape are really complex and are created using polygons and multipolygons with a large number of points.

In the next part of this blog, I will explain how we can expose the data out of HANA as a XSJS service and how we can visualize the data using LEAFLET.

Please find the 2nd Part of the Blog in the following link Experiences with SAP HANA Geo-Spatial Features – Part 2

Assigned Tags

      28 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Chintan Mota
      Chintan Mota

      Nice blog! Looking forward to the next part!

      Author's profile photo Trinoy Hazarika
      Trinoy Hazarika
      Blog Post Author

      Please find the second part in the below link

      Experiences with SAP HANA Geo-Spatial Features – Part 2

      Author's profile photo Hari Sagar Pacca
      Hari Sagar Pacca

      Really Nice one crystal clear .

      Author's profile photo Trinoy Hazarika
      Trinoy Hazarika
      Blog Post Author

      Thanks!!!!! ๐Ÿ™‚

      Author's profile photo Kumar Prashant
      Kumar Prashant

      Hi Trinoy,

      Thanks for the excellent blog. I was trying to implement this. and I am facing an issue. It will be great if you can kindly help. While importing the shape files I am able to import only one file. If i run the same import command for other file it's not letting me do that and complains that the table already exists.

      Could not execute 'IMPORT "ID280253"."IN_PC" AS SHAPEFILE FROM '/usr/sap/HET/HDB00/work/DATA/S01_PC'' in 49 ms 466 µs .


      SAP DBTech JDBC: [2] (at 18): general error: Can't import due to table already exists: line 1 col 19 (at pos 18)

      Also some I am getting sql exception for some of the files:

      Could not execute 'IMPORT "ID280253"."IN_PC" AS SHAPEFILE FROM '/usr/sap/HET/HDB00/work/DATA/S01_PC'' in 428 ms 519 µs .


      SAP DBTech JDBC: [2]: general error: Shapefile Import failed. SQLException.

      ~Prashant

      Author's profile photo Kumar Prashant
      Kumar Prashant

      I got the first one resolved by using following sql statment:

      IMPORT "<SCHEMA>"."<TableName>" AS SHAPEFILE  FROM '<file Location>' WITH REPLACE;

      I was able to import files for :

      S04-Bihar

      S05-Goa

      S07-Haryana

      S11-Kerala

      S19-Punjab

      S21-Sikkim

      S22- Tamilnadu

      S23-tripurA

      S24-UP

      S25-West Bengal

      S27-Jharkhand

      For rest I am facing issue. Any clue..?

      Author's profile photo Trinoy Hazarika
      Trinoy Hazarika
      Blog Post Author

      Hi Prashant

      I had faced some issue while importing shapes in rev70 but when i upgrade to rev71, i was able to resolve it.

      Which version are you using ?

      Trinoy

      Author's profile photo Kumar Prashant
      Kumar Prashant

      Thanks Trinoy. We are at Rev 70 only, I have requested for upgrade. Will keep you posted.

      ~Prashant

      Author's profile photo Kumar Prashant
      Kumar Prashant

      Hi Trinoy,

      This is kind of weird but after updating to revision 72 I am able to import shape file 01 which was earlier giving error, but now I am unable to import multiple files. I have started to face my first problem and the REPLACE addition also is not working. Will it be possible for you to share the SQL command which you used to import multiple files.

      ~Prashant

      Author's profile photo Trinoy Hazarika
      Trinoy Hazarika
      Blog Post Author

      can you try this

      create a TMP table

      DROP TABLE INDIA_PC_TMP

      import content into the temp table

      IMPORT INDIA_PC_TMP AS SHAPEFILE FROM <loc>

      then fill the main table from the temp table.

      INSERT INTO INDIA_PC SELECT * FROM INDIA_PC_TMP;

      I agree it is not a good way but let me know if this works.....

      Author's profile photo Arjun K T
      Arjun K T

      Thank You for sharing this , even though we need to create 28 tables, finally it worked .

      Author's profile photo Trinoy Hazarika
      Trinoy Hazarika
      Blog Post Author

      Thanks....Glad you like the blog....

      Author's profile photo Swapan Saha
      Swapan Saha

      This is part of SAP HANA Cookbook for MySQL Developers | SAP HANA. Please feel free to check out entire Cookbook.

      Regards,

      Swapan

      Author's profile photo Former Member
      Former Member

      Hi Trinoy,

      You rock!

      Regards,

      Balaji

      Author's profile photo Katan Patel
      Katan Patel

      That's great work.  I've been trying to do this for a while, but ran into issues changing spatial references to the other default formats. Everything just default to 0, which was annoying as this would impact the results of spatial queries.  Just noticed HANA 7.0 Dev Edition is available, so hopefully that issue goes away, but would be good to confirm the exact edition spatial is available from.

      Also Is there any documentation that explains how to add new spatial references to the table?  If you go to

      http://spatialreference.org/

      and check out an example like

      http://spatialreference.org/ref/epsg/2000/

      You can certain formats you can import, including SQL for PostGIS.  Would be nice if there was a HANA entry here or Hana supported import of these existing formats.

      Interesting to see how ESRI will be using this and the impact on products (or services??) like Geo.E. 

      P.s. I love Leaflet too. It's so flexible

      Cheers,

      Katan

      Author's profile photo Trinoy Hazarika
      Trinoy Hazarika
      Blog Post Author

      Hi Katan,

      Would you please elaborate on the different formats and the way in which you tried that. I can try to replicate the same in my system and figure out if the issue still exists.

      And i agree with you Leaflet is really amazing!!!!!

      I have not done much research on ESRI so can`t comment on that but will try to get some facts around that topic and will let you know.

      Cheers

      Trinoy Hazarika

      Author's profile photo Katan Patel
      Katan Patel

      Hi Trinoy,

      I was following the examples from the HANA Spatial Reference Guide to test stuff out. 

      So there is a view for spatial reference systems information ST_SPATIAL_REFERENCE_SYSTEMS system view.

      I was trying to setup data to use WGS 84 (planar) - SRID 1000004326, which is there by default.

      I used this statement to add a column and it failed here if I included an SRID. 

      ALTER TABLE SpatialShapes ADD (location ST_POINT(1000004326));

      If I did not include it, I could see the column was created successfully using SRID 0.  There was another view which I could use to see all spatial columns in all tables and metadata about them including SRID, but I've forgotten it's name... 


      Unfortunately I have trashed the instance.  So need to create a new one and test again.  It was all pretty new back then. 


      Cheers,


      Katan

      Author's profile photo Former Member
      Former Member

      Hi,

      I'm running the SAP HANA Developer version 70 on Amazon's AWS and I get the same SQL error when I try to import the ESRI shape files. (SAP DBTech JDBC: [2]: general error: Shapefile Import failed. SQLException.)

      Do you know how and if there is a possibility to update HANA to version 72 on AWS?

      Thank you very much for your help in advance,

      Juergen

      Author's profile photo Rushi Ns
      Rushi Ns

      HI

      what are the 28 tables you mentioned that we need to create before import. can you send me the format of the tables because my import is failing due to the tables are not available. in your example you mentioned only 2 tables but the import required INDIA_PC table columns ? where is this info.

      BR,

      Rushi.

      Author's profile photo Rushi Ns
      Rushi Ns

      hi

      Further more, i have created a table based on the screenshot you have added with the following columns. Please confirm is this is right to use.

      "ST_CODE",

      "ST_NAME",

      "PC_NAME",

      "PC_CODE",

      "AREA",

      "PC_NO",

      "FLAG",

      "PARTY",

      "SHAPE",

      "COLOR"


      let me know.

      BR,

      Rushi.

      Author's profile photo Chris Bezuidenhout
      Chris Bezuidenhout

      I get http://b.tile.cloudmade.com/77b3738c9c724dd88e52815e3a5317da/122598/256/4/10/6.png Failed to load resource: the server responded with a status of 403 (Forbidden)

      then going there:

      http://b.tile.cloudmade.com/77b3738c9c724dd88e52815e3a5317da/122598/256/4/10/6.png

      get: Wrong apikey

      Author's profile photo ' Pavan ' Golesar
      ' Pavan ' Golesar

      Thanks for this post,

      looking forward to try it. ๐Ÿ™‚ ๐Ÿ™‚

      also, It was a nice demo at SAP TechEd 2015 _Bangalore. ๐Ÿ™‚

      Thanks,

      --Pavan G

      Author's profile photo Former Member
      Former Member

      Nice introduction to the topic. Thanks ๐Ÿ™‚

      In addition to the topic: Has somebody experience with a transport scenario for HANA content that is based on spatial data and computations?

      So, usually - from my experience - you try to not create the physical tables, views and procedures directly from SQL. Because this will lead to serious trouble if you want to move your content/implementation between systems (typically DEV env -> PROD env). Instead you use design time objects. For example Core Data Services - CDS (.hdbdd) for your schema. But I didn't find any documentation that CDS is supporting spacial data types.. 


      So what are the options to work with spatial data on a design time level?

      Thanks,

      Mathias

      Author's profile photo Thomas Jung
      Thomas Jung

      Geospatial types were supported in CDS (hdbdd) as of SPS 09. Support for the Geospatial functions was added in SPS 10.

      Author's profile photo Former Member
      Former Member

      Ok I didn't check the latest docs.. sry.

      But thanks for the fast reply and very nice to here that there is a full integration of spatial data and calculations on design time level.

      Best, Mathias

      Author's profile photo VIJAYA SIMHA CHINTARLAPALLI REDDY
      VIJAYA SIMHA CHINTARLAPALLI REDDY

      Really great to see this ๐Ÿ™‚

      Author's profile photo Marcus Roth
      Marcus Roth

      How can I setup a "spatial system" on SAP HANA Express Edition 2.0 SPS1? On the HXE website this option is announced as a part of HXE but I cant find any how-to or tutorial.

      Author's profile photo Tom Turchioe
      Tom Turchioe

       

      Marcus,

       

      HANA spatial is included as a part of HXE.ย  Once you've installed and configured HXE (see here), HANA spatial is accessible just as it is on HANA base, platform and enterprise editions.ย There are tutorialsย (see here) and videos (see here) and a lot more.ย  If you're interested in integrating ArcGIS and HANA together, see here, here and here.ย  The last link is an FAQ that Sharon Om and I put together.ย  Take a look and let me know if you need any additional help.