Skip to Content

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

To report this post you need to login first.

27 Comments

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

      1. 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

        (0) 
        1. 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..?

          (0) 
          1. Trinoy Hazarika 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

            (0) 
            1. 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

              (0) 
              1. Trinoy Hazarika 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…..

                (0) 
    1. 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

      (0) 
      1. Trinoy Hazarika 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

        (0) 
        1. 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

          (0) 
  1. Juergen Ommen

    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

    (0) 
  2. 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.

    (0) 
    1. 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.

      (0) 
  3. Mathias Bergmann

    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

    (0) 
      1. Mathias Bergmann

        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

        (0) 
  4. 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.

    (0) 

Leave a Reply