Use Geospatial Methods and Predicates feature in SAP HANA

[shaunakhs]

Business Example:

There is a requirement from a client where the client wants to calculate the distances between their customers and their stores. Typically, locations or addresses in a database are stored in text fields, making it difficult to pinpoint the exact geographical locations.

 

To be able to calculate distances accurately, you have decided to store the geographical locations of the customers and stores, using the SAP HANA geospatial model and its related functions.

 

Spatial Data:

 

To be able to achieve the above requirement we need to rely on a special type of data called Spatial Data. This Spatial data is data that describes the position, shape, and orientation of objects in a defined space. Spatial data is represented as 2D geometries in the form of points, line strings, and polygons. For example, the following image shows the state of Massachusetts, representing the union of polygons representing zip code regions.

 

Spatial Feature in SAP HANA:

HANA Spatial feature delivers the ability to store and process geospatial data types like ST_POINT, ST_GEOMETRY etc. These data types allow application developers to associate spatial information with their data. For example, a table representing companies could store the location of the company as a point, or store the delivery area for the company as a polygon. SAP HANA also delivers Nokia mapping services as part of the HANA Spatial features and this can be used to create apps using HANA XS engine through which Nokia mapping services can be called using the Nokia API. Once Spatial data is loaded into HANA you can create or extend HANA models (Analytic, Calculation Views) using HANA Studio and make these models available for analysis and visualization thru SAP Lumira or custom HTML5 applications. SAP HANA SQL script has been extended to support the Spatial SQL MM Standards for storing and accessing geospatial data.

 

Spatial Type Hierarchy:

 

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. In Geometry data type we can store any of the child types like Line, Polygon or Point.

 

Example of ST_POINT

 

The shape of the geographical data you want to store determines which data type you will use. Geospatial data is typically entered using the WKT (Well Known Text) format. This format is maintained by the Open Geospatial Consortium (OGC) as part of the Simple Features defined for the Open GIS Implementation Specification for Geographic Information.

Step By Step Implementation:

 

1. Let’s assume we already have schema called in our SAP HANA system. In case we do not, we can create a new one by running the below statement:

 

CREATE SCHEMA TRAINING

 

2. We will create a table with column type ST_POINT and insert and view the record

 

Let’s create a table called ST_POINT_EXAMPLE

 

CREATE COLUMN TABLE TRAINING.ST_POINT_EXAMPLE

(	

COORDINATE ST_POINT

);

Insert values into ST_POINT table using the below codes:

INSERT INTO TRAINING.ST_POINT_EXAMPLE VALUES (NEW ST_POINT (40.71, 74.1));

INSERT INTO TRAINING.ST_POINT_EXAMPLE VALUES (NEW ST_POINT (40.72, 74.2));

INSERT INTO TRAINING.ST_POINT_EXAMPLE VALUES (NEW ST_POINT (40.73, 74.3));

INSERT INTO TRAINING.ST_POINT_EXAMPLE VALUES (NEW ST_POINT (40.74, 74.4));

 

3. The output of SELECT query will be as below

SELECT COORDINATE.ST_ASGEOJSON () FROM TRAINING.ST_POINT_EXAMPLE;

 

 

Example of ST_GEOMETRY

 

The ST_Geometry type is the maximal super type of the geometry type hierarchy. We will create a table with column type ST_POINT and insert and view the record.

 

Step By Step Implementation:

 

1. Let’s create a table called ST_GEOMETRY_EXAMPLE under the same TRAINING schema

CREATE COLUMN TABLE TRAINING.ST_GEOMETRY_EXAMPLE

(

SHAPE ST_GEOMETRY

);

2. Insert values into ST_GEOMETRY_EXAMPLE table

INSERT INTO TRAINING.ST_GEOMETRY_EXAMPLE VALUES (NEW ST_POINT(0.0, 0.0) );

INSERT INTO TRAINING.ST_GEOMETRY_EXAMPLE VALUES (NEW ST_POLYGON('POLYGON((0.0 0.0, 4.0 0.0, 2.0 2.0, 0.0 0.0))') );

3. The output of SELECT query will be as below.

SELECT SHAPE.ST_ASGEOJSON () FROM TRAINING.ST_GEOMETRY_EXAMPLE;

 

Calculate distance between two cities:

 

Now, we will see an example on calculating distance between two cities. To do so, let’s create a new table called GD which is storing the City details i.e. ID, Name, Co-ordinates

 

1. Create the table using the below code

CREATE COLUMN TABLE TRAINING.GD ( 

ID BIGINT NOT NULL PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,  

CITY_NAME NVARCHAR (40) NULL, 

CITY_COORDINATE ST_GEOMETRY);

 

2. Then insert data for two cities. Let’s say Bangalore and Hyderabad

INSERT INTO TRAINING.GD (CITY_NAME, CITY_COORDINATE) VALUES ('HYDERABAD', new ST_POINT ('POINT (78.4867 17.3850 )')); 

INSERT INTO TRAINING.GD (CITY_NAME, CITY_COORDINATE) VALUES ('BANGALORE', new ST_POINT ('POINT (77.5946 12.9716)'));

 

3.  Now we have data loaded in the table. So, let us create stored procedure to calculate the distance between the 2 cities Bangalore and Hyderabad. Here we need to use ST_Distance method to calculate the distance between 2 points. The store procedure will take three inputs. Origin City, Destination City and Unit of Measure. Here is the below code

 

PROCEDURE "<Schema>::CALCULATE_DISTANCE" 

(IN ORIGIN_CITY nvarchar(50), 

 IN DESTINATION_CITY nvarchar(50), 

 In UNIT_OF_MEASURE nvarchar(50) ) 

	

	LANGUAGE SQLSCRIPT

	SQL SECURITY INVOKER 

	READS SQL DATA AS

BEGIN

/***************************** 

	Write your procedure logic 

 *****************************/

 

DECLARE STRING_STR varchar(2000); 



STRING_STR :=  'SELECT A.CITY_NAME AS "Origin City",B.CITY_NAME AS "Destination City", A.CITY_COORDINATE.ST_Distance(B.CITY_COORDINATE,' || '''' || :UNIT_OF_MEASURE || '''' || ') AS "Distance" 

             from TRAINING.GD A,TRAINING.GD B where A.CITY_NAME = ' || '''' || :ORIGIN_CITY || '''' || ' and B.CITY_NAME = ' || '''' ||  :DESTINATION_CITY || '''';



EXECUTE IMMEDIATE (:STRING_STR); 

 

END;

 

4. Run the store procedure by using Kilometers and Yard as the unit of measure

 

 

 

Conclusion

By this exercise, we have implemented and explored different Geospatial Methods and Predicates feature in SAP HANA, which will help  business to be able to see the distance between two different location (i.e. The distance between the store and factory, The distance between two different stores.)