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 at “HANA 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:-
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.
Below you can see how we can store and retrieve data from a Point or Geometry Type
SET SCHEMA “DEMO_SPA”;
create column table spatial_point
insert into spatial_point values (new ST_POINT(0.0, 0.0));
select point.ST_AsGeoJSON() from spatial_point;
SET SCHEMA “DEMO_SPA”;
create column table spatial_geom
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;
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:-
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:-
- Download Putty(http://www.putty.org/) and PSCP(http://www.nber.org/pscp.html)
- 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>
- You can login to you server using putty and unzip the files.
- 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 as you can see I had the list of all the constituencies along with the following information:-
- The state to which it belong, the ruling party, area and the color code(some of the fields were added later)
- The shape information.
I can also see the shape as GeoJson and the data looked like as shown below.
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