Multi Model - Spatial

Objective

After completing this lesson, you will be able to use SAP HANA Cloud’s Spatial engine to analyze and to visualize customer data

With SAP HANA Cloud’s spatial engine, application builders can add spatial intelligence to business data to gain deeper insights, identify new opportunities and discover root causes of complex issues.

Some key features include:

  • Native storage for 2D, 3D and 4D vector data types
  • 100+ native, in-memory, geospatial functions that integrate into SQL
  • Vector Tile integration
  • Compliant with open standards for spatial data formats
  • Esri-supported enterprise geodatabase
  • Open-source plugins (GeoServer, QGIS)
  • Python interface for data science

Try it out!

Viewing the State map

This exercise uses the dataset from the GX_WORLD_COUNTRIES table to highlight borders of a country.

  1. Switch to the SAP HANA Database Explorer.

To display spatial data on a map, the default byte limit in the Database Explorer must first be increased.

  1. Proceed as follows:

    • Switch to Settings.
    • Open the SQL Console tab.
    • Change the Byte limit for Large Objects (LOBs) to 1000024.
    • Choose Save.

  1. Exit the settings and switch back to the Database Explorer. Select Tables and search for GX_WORLD_COUNTRIES.

  2. Right-click on GX_WORLD_COUNTRIES and select Open Data.

  1. Scroll down to Germany and double-click on the SHAPE entry or right-click and select View Spatial Data.

  1. The map will appear:

Viewing customers on the map

Now we want to display all customers from Germany. To do this, execute a database query and consider the points individually.

  1. Select the SQL Console icon to open a new SQL console, then copy the below SQL statement and choose Run.
Code Snippet
1
SELECT * FROM GX_CUSTOMERS;

  1. Notice the addresses already exist in the table as latitude and longitude. In order to use the map viewer, these numbers must be combined into a singular data type called ST_POINT using the ST_POINT function in SAP HANA Cloud. Paste the following SQL command into the console and execute it:
Code Snippet
12345678
SELECT 
	CUSTOMER_ID,
	NEW ST_POINT(
		TO_DOUBLE(CUSTOMER_LONGITUDE), 
		TO_DOUBLE(CUSTOMER_LATITUDE), 
		1000004326
	) AS LOCATION
FROM "GX_CUSTOMERS";

  1. Double-click on a LOCATION entry which will render a map displaying the customer location:

The next step is to include this logic within a database view for ease of reuse.

  1. Copy and paste the SQL statement below into the SQL Console, then execute the statement.
Code Snippet
12345678
CREATE VIEW "VW_CUSTOMER_LOCATION" AS
SELECT CUSTOMER_ID,
  NEW ST_POINT(
    TO_DOUBLE(CUSTOMER_LONGITUDE),
    TO_DOUBLE(CUSTOMER_LATITUDE),
    1000004326
  ) AS CUSTOMER_LOCATION
FROM "GX_CUSTOMERS";

Note

Click on “Views” in the Catalog and verify that the newly created view is listed.

Viewing all customers on the map

Now it is possible to query all customer locations as a collection of points.

  1. To display all customer locations at the same time, the points must be combined into a single object called a geometry collection. The following command gathers all the points from the database view for display by the map:
Code Snippet
12
SELECT ST_CollectAggr(CUSTOMER_LOCATION) 
FROM "VW_CUSTOMER_LOCATION";
  1. Double-click on the resulting output row, or right-click and select View spatial data the option on the result:

  1. A map displaying all the customer locations appears:

Separating customer addresses to a specific area

For this use case, reduce the set of customers to those only in Germany. Using the polygon from GX_WORLD_COUNTRIES, we can check which points are within a certain border. To further simplify processing, update the previously created view.

  1. Switch back to SQL Console, copy, paste and execute the following SQL statement:
Code Snippet
12345678910111213141516171819
drop view VW_CUSTOMER_LOCATION;

CREATE VIEW "VW_CUSTOMER_LOCATION" AS
SELECT CUSTOMER_ID,
  NEW ST_POINT(
    TO_DOUBLE(CUSTOMER_LONGITUDE),
    TO_DOUBLE(CUSTOMER_LATITUDE),
    1000004326
  ) AS CUSTOMER_LOCATION
FROM "GX_CUSTOMERS",
  "GX_WORLD_COUNTRIES"
WHERE "COUNTRY" = 'Germany'
  AND "SHAPE".ST_Contains(
    NEW ST_POINT(
      TO_DOUBLE(CUSTOMER_LONGITUDE),
      TO_DOUBLE(CUSTOMER_LATITUDE),
      1000004326
    )
  ) = 1;
  1. Now execute the following statement to organize all customers in Germany as a collection bordered by the German border (the filter condition of the SQL View checks whether the locations are in Germany).
Code Snippet
1234
SELECT "SHAPE".ST_Collect(ST_CollectAggr(CUSTOMER_LOCATION))
FROM "VW_CUSTOMER_LOCATION",
"GX_WORLD_COUNTRIES"
WHERE "COUNTRY" = 'Germany';

  1. Double-click on the result, or right-click and select View spatial data to view the resulting map:

Clustering

Using spatial processing, view the clustering of all customer locations in Germany.

  1. Switch back to SQL Console, copy, paste and execute the following SQL statement to create a new database view.
Code Snippet
1234567
CREATE VIEW "VW_SPATIAL_CLUSTERING" AS
SELECT ST_ClusterID() AS ID,
  ST_ClusterCell() AS SHAPE,
  ST_ClusterCell().ST_PointOnSurface().ST_AsText() AS POINT,
  COUNT(*) AS COUNT
FROM "VW_CUSTOMER_LOCATION" GROUP CLUSTER BY "CUSTOMER_LOCATION" USING HEXAGON X CELLS 20
ORDER BY COUNT DESC;
  1. Check under the Views area of the Catalog to verify that the View is visible.

Now look at the cluster result from different views.

  1. In Database Explorer, right-click on the VW_SPATIAL_CLUSTERING view and select Open Data.

  1. Look at the largest hexagon with 200 entries by double-clicking the SHAPE entry. The result for the highest customer density is in an area around Frankfurt:

Check out some other entries to get the customer densities for different locations!

Further information

With SAP HANA Cloud’s spatial engine, application builders can add spatial intelligence to business data to gain deeper insights, identify new opportunities and discover root causes of complex issues.

Some key features include:

  • Native storage for 2D, 3D and 4D vector data types
  • 100+ native, in-memory, geospatial functions that integrate into SQL
  • Vector Tile integration
  • Compliant with open standards for spatial data formats
  • Esri-supported enterprise geodatabase
  • Open-source plugins (GeoServer, QGIS)
  • Python interface for data science

Try it out!

Viewing the State map

This exercise uses the dataset from the GX_WORLD_COUNTRIES table to highlight borders of a country.

  1. Switch to the SAP HANA Database Explorer.

To display spatial data on a map, the default byte limit in the Database Explorer must first be increased.

  1. Proceed as follows:

    • Switch to Settings.
    • Open the SQL Console tab.
    • Change the Byte limit for Large Objects (LOBs) to 1000024.
    • Choose Save.

  1. Exit the settings and switch back to the Database Explorer. Select Tables and search for GX_WORLD_COUNTRIES.

  2. Right-click on GX_WORLD_COUNTRIES and select Open Data.

  1. Scroll down to Germany and double-click on the SHAPE entry or right-click and select View Spatial Data.

  1. The map will appear:

Viewing customers on the map

Now we want to display all customers from Germany. To do this, execute a database query and consider the points individually.

  1. Select the SQL Console icon to open a new SQL console, then copy the below SQL statement and choose Run.
Code Snippet
1
SELECT * FROM GX_CUSTOMERS;

  1. Notice the addresses already exist in the table as latitude and longitude. In order to use the map viewer, these numbers must be combined into a singular data type called ST_POINT using the ST_POINT function in SAP HANA Cloud. Paste the following SQL command into the console and execute it:
Code Snippet
12345678
SELECT 
	CUSTOMER_ID,
	NEW ST_POINT(
		TO_DOUBLE(CUSTOMER_LONGITUDE), 
		TO_DOUBLE(CUSTOMER_LATITUDE), 
		1000004326
	) AS LOCATION
FROM "GX_CUSTOMERS";

  1. Double-click on a LOCATION entry which will render a map displaying the customer location:

The next step is to include this logic within a database view for ease of reuse.

  1. Copy and paste the SQL statement below into the SQL Console, then execute the statement.
Code Snippet
12345678
CREATE VIEW "VW_CUSTOMER_LOCATION" AS
SELECT CUSTOMER_ID,
  NEW ST_POINT(
    TO_DOUBLE(CUSTOMER_LONGITUDE),
    TO_DOUBLE(CUSTOMER_LATITUDE),
    1000004326
  ) AS CUSTOMER_LOCATION
FROM "GX_CUSTOMERS";

Note

Click on “Views” in the Catalog and verify that the newly created view is listed.

Viewing all customers on the map

Now it is possible to query all customer locations as a collection of points.

  1. To display all customer locations at the same time, the points must be combined into a single object called a geometry collection. The following command gathers all the points from the database view for display by the map:
Code Snippet
12
SELECT ST_CollectAggr(CUSTOMER_LOCATION) 
FROM "VW_CUSTOMER_LOCATION";
  1. Double-click on the resulting output row, or right-click and select View spatial data the option on the result:

  1. A map displaying all the customer locations appears:

Separating customer addresses to a specific area

For this use case, reduce the set of customers to those only in Germany. Using the polygon from GX_WORLD_COUNTRIES, we can check which points are within a certain border. To further simplify processing, update the previously created view.

  1. Switch back to SQL Console, copy, paste and execute the following SQL statement:
Code Snippet
12345678910111213141516171819
drop view VW_CUSTOMER_LOCATION;

CREATE VIEW "VW_CUSTOMER_LOCATION" AS
SELECT CUSTOMER_ID,
  NEW ST_POINT(
    TO_DOUBLE(CUSTOMER_LONGITUDE),
    TO_DOUBLE(CUSTOMER_LATITUDE),
    1000004326
  ) AS CUSTOMER_LOCATION
FROM "GX_CUSTOMERS",
  "GX_WORLD_COUNTRIES"
WHERE "COUNTRY" = 'Germany'
  AND "SHAPE".ST_Contains(
    NEW ST_POINT(
      TO_DOUBLE(CUSTOMER_LONGITUDE),
      TO_DOUBLE(CUSTOMER_LATITUDE),
      1000004326
    )
  ) = 1;
  1. Now execute the following statement to organize all customers in Germany as a collection bordered by the German border (the filter condition of the SQL View checks whether the locations are in Germany).
Code Snippet
1234
SELECT "SHAPE".ST_Collect(ST_CollectAggr(CUSTOMER_LOCATION))
FROM "VW_CUSTOMER_LOCATION",
"GX_WORLD_COUNTRIES"
WHERE "COUNTRY" = 'Germany';

  1. Double-click on the result, or right-click and select View spatial data to view the resulting map:

Clustering

Using spatial processing, view the clustering of all customer locations in Germany.

  1. Switch back to SQL Console, copy, paste and execute the following SQL statement to create a new database view.
Code Snippet
1234567
CREATE VIEW "VW_SPATIAL_CLUSTERING" AS
SELECT ST_ClusterID() AS ID,
  ST_ClusterCell() AS SHAPE,
  ST_ClusterCell().ST_PointOnSurface().ST_AsText() AS POINT,
  COUNT(*) AS COUNT
FROM "VW_CUSTOMER_LOCATION" GROUP CLUSTER BY "CUSTOMER_LOCATION" USING HEXAGON X CELLS 20
ORDER BY COUNT DESC;
  1. Check under the Views area of the Catalog to verify that the View is visible.

Now look at the cluster result from different views.

  1. In Database Explorer, right-click on the VW_SPATIAL_CLUSTERING view and select Open Data.

  1. Look at the largest hexagon with 200 entries by double-clicking the SHAPE entry. The result for the highest customer density is in an area around Frankfurt:

Check out some other entries to get the customer densities for different locations!

Further information

Learning
SAP FacebookSAP YoutubeSAP LinkedIn