Add Planar Geometries to SAP HANA Database in SAP HANA Cloud

In our current dataset, we have a column of type ST_Geometry (*) holding latitude and longitude values.
We can view latitude and longitude as double values by selecting ST_X() (*) and ST_Y() (*) from our geometry column.

SELECT
	"geometry_GEO".ST_X() AS LONGITUDE,
	"geometry_GEO".ST_Y() AS LATITUDE
FROM LONDON_VERTICES;

Geometries can be represented in different Spatial Reference Systems (SRS). The given latitude and longitude values are based on a round-earth model and the corresponding spatial reference system is WGS84 (id 4326).

For performance reasons, it is recommended to use a projected spatial reference system instead of a round-earth model. This way Euclidean geometry can be used for spatial calculations, which is less expensive than calculations on the sphere. The second general recommendation when dealing with spatial data is to persist the base geometries. This way, in-database optimizations such as spatial indices can be leveraged.

Check out this brief YouTube Video to get an overview of the concept of Spatial Reference Systems.

Now that you know the basics, your first step in this exercise will be to create a planar spatial reference system in SAP HANA Cloud, SAP HANA database.

SAP HANA is already aware of more than 9000 spatial reference systems - including the spatial reference system defined by EPSG. A suitable projected spatial reference system for UK is the SRS with id 32630.

Before, we can create our first column using this SRS, we need to install it on SAP HANA Cloud, SAP HANA database. Installation has to be done only the first time we are using this SRS. Since 32630 is part of EPSG and already known to SAP HANA Cloud, we can issue our creation statement referencing only the id of the SRS.

1. In SAP HANA Database Explorer, open a SQL Console.

2. Set the schema DAT260 by executing this statement:

   SQL

   Copy

   SET SCHEMA DAT260;
   

3. Paste this statement to the console and execute it:

   SQL

   Copy

   CREATE PREDEFINED SPATIAL REFERENCE SYSTEM IDENTIFIED BY 32630;
   

4. A list of all installed Spatial Reference Systems can be found in the table ST_SPATIAL_REFERENCE_SYSTEMS(*). To confirm that the above statement has installed SRS 32630, you can check the result by executing this statement:

   SQL

   Copy

   SELECT * FROM ST_SPATIAL_REFERENCE_SYSTEMS WHERE SRS_ID = 32630;
   

   

5. You should now see one record in the Results panel. Next, we will add a column to store geometric data in our tables.

Before we can persist the geometry data with the now installed SRS, we need to create a column for storing this data. We will add a column to all our tables, name it SHAPE and specify the type to be ST_Geometry with the SRS 32630. We will enhance the existing tables by using the ALTER TABLE statement. Paste these statements to your SQL console and execute them:

ALTER TABLE LONDON_POI ADD (SHAPE ST_Geometry(32630));

ALTER TABLE LONDON_EDGES ADD (SHAPE ST_Geometry(32630));
ALTER TABLE LONDON_VERTICES ADD (SHAPE ST_Geometry(32630));

ALTER TABLE LONDON_TUBE_CONNECTIONS ADD (SHAPE ST_Geometry(32630));
ALTER TABLE LONDON_TUBE_STATIONS ADD (SHAPE ST_Geometry(32630));

Once that is done, you can check if the column has been added successfully by opening the tables in your catalog.

Next, you can fill the column SHAPE with geometries in SRS 32630 by transforming the existing WGS84 geometries.

You can transform geometries from one spatial reference system to another with the function ST_Transform(*).

To transform the existing geometries to SRS 32630 and persist the result in column SHAPE, execute these statements:

UPDATE LONDON_POI SET SHAPE = "geometry_GEO".ST_Transform(32630);

UPDATE LONDON_EDGES SET SHAPE = "geometry_GEO".ST_Transform(32630);
UPDATE LONDON_VERTICES SET SHAPE = "geometry_GEO".ST_Transform(32630);

UPDATE LONDON_TUBE_CONNECTIONS SET SHAPE = SHAPE_4326.ST_Transform(32630);
UPDATE LONDON_TUBE_STATIONS SET SHAPE = SHAPE_4326.ST_Transform(32630);

You can check if the data has been added successfully to the column by right-clicking on the tables in your catalog and selecting Open Data. There, you should now see the transformed data.
You have now enhanced the existing data model by a planar projection of the WGS84 geometries.

In the next tutorial, learn how to determine distances to target Points of Interest (POI).