Skip to Content
Technical Articles
Author's profile photo Uwe Fetzer

Using multi model capabilities of SAP HANA in the new ABAP 1909 Developer Edition – Part II – Spatial functions

This is part two of a three part series:

In contrast to part I (Document Store), every function in this part you can legaly use also in your S/4HANA system with ABAP 1909 and above (please correct me immediatelly, if I’m wrong).

In the ABAP Platform in SAP S/4HANA 1909 – Overview and Product Highlights slidedeck  on page 23 I’ve found a remarkable short notice:

VARIOUS NEW BUILT-IN DATA TYPES IN THE ABAPDICTIONARY
For geodata
GEOM_EWKB – to describe geometric position in a given coordinate reference systems

Unfortunatelly I haven’t found any example in the new ABAP Developer Edition, where this new data type is used, so I started on my own and I hoped that it is exactly the missing part I’ve searched for for a while.

The doccumentation on help.sap.com doesn’t provide much information (none), especially on how I can enter the spatial reference system.

The idea

As you may know I’m an EV driver for many years already. So my idea is obvious: search for the nearest charging points for a given geo coordinate. Because I’m working for a local utilities company it was easy to get the data of our own chargingpoints.

Preparation

Because at the end we want to display the result in a map, I’m using my open source ABAP framework “GeoJSON” which needs the JSON Document Class.

You can install both packages with AbapGit (transaction ZABAPGIT), which is pre-installed already on this ABAP Developer Edition, yay 🙂

(not needed for the spatial functions, just for the visualization)

The database table

For our project we only need the address and the coordinates. SAP HANA should translate the coordinates into the spatial representation later. As key I’m using a Guid.

I’ve started with this table definition:

define table zchargingpoints {
  key mandt : mandt not null;
  key guid  : guid_16 not null;
  street    : ad_street;
  house_num : ad_hsnm1;
  post_code : ad_pstcd1;
  city      : ad_city1;
  longitude : geolon;
  latitude  : geolat;
  geo       : geom_ewkb;
}

As I’ve said, I had no clue how to enter the reference system. Fortunatelly we still have the SAPGUI. I swapped to the GUI DDIC view, et voilà, there’s a new column called “Coordinate”.

DDIC%20Gui%20view

DDIC Gui view

So I’ve entered a well known spatial reference system (Standard spatial reference system for spherical surfaces of the Earth) saved and gone back to the text representation of the table definition. Ah, seems we have found the correct way to enter the reference system:

  @AbapCatalog.geo.spatialRefSystem : '4326'
  geo       : geom_ewkb;

After googling this annotation I also found a documentation by the way 😉

AMDP to create spatial data

To translate geo coordinates into a spatial object and insert them into the database table I’ve written a short class with an AMDP method.

CLASS zcl_abap_spatial_amdp DEFINITION
  PUBLIC
  FINAL
  CREATE PUBLIC .

  PUBLIC SECTION.
    INTERFACES if_amdp_marker_hdb .

    CLASS-METHODS insert_geo_location
      IMPORTING VALUE(i_mandt)     TYPE mandt
                VALUE(i_guid)      TYPE guid_16
                VALUE(i_street)    TYPE ad_street
                VALUE(i_house_num) TYPE ad_hsnm1
                VALUE(i_post_code) TYPE ad_pstcd1
                VALUE(i_city)      TYPE ad_city1
                VALUE(i_latitude)  TYPE geolat
                VALUE(i_longitude) TYPE geolon
      RAISING   cx_amdp_execution_failed .

ENDCLASS.

CLASS zcl_abap_spatial_amdp IMPLEMENTATION.

  METHOD insert_geo_location
    BY DATABASE PROCEDURE FOR HDB
    LANGUAGE SQLSCRIPT
    USING zchargingpoints.

    INSERT INTO zchargingpoints VALUES (
                                  i_mandt,
                                  i_guid,
                                  i_street,
                                  i_house_num,
                                  i_post_code,
                                  i_city,
                                  i_latitude,
                                  i_longitude,
                                  NEW ST_POINT(i_longitude, i_latitude).ST_SRID(4326)
                                );

  ENDMETHOD.

ENDCLASS.

You see, our field with the new datatype geom_ewkb will be filled by creating a new point object.

Upload charging point data

Now we are ready to upload the CSV. Except of the AMDP call there’s nothing fancy here.

DATA lines TYPE string_table.

cl_gui_frontend_services=>gui_upload(
  EXPORTING
	filename                = 'D:\data\c\chargingpoints.csv'
  CHANGING
	data_tab                = lines
  EXCEPTIONS
	OTHERS                  = 8
).

IF sy-subrc <> 0.
  cl_demo_output=>display( 'Upload error' ).
  RETURN.
ENDIF.

DELETE lines INDEX 1.

DATA point TYPE zchargingpoints.

TRY.
	LOOP AT lines REFERENCE INTO DATA(line).

	  point-guid = cl_system_uuid=>create_uuid_x16_static( ).

	  DATA lat TYPE c LENGTH 20.
	  DATA lon TYPE c LENGTH 20.

	  SPLIT line->* AT ';' INTO point-street point-house_num point-post_code point-city lat lon.
	  REPLACE ',' IN lat WITH '.'.
	  REPLACE ',' IN lon WITH '.'.
	  point-latitude = lat.
	  point-longitude = lon.

	  zcl_abap_spatial_amdp=>insert_geo_location(
		i_mandt     = sy-mandt
		i_guid      = point-guid
		i_street    = point-street
		i_house_num = point-house_num
		i_post_code = point-post_code
		i_city      = point-city
		i_latitude  = point-latitude
		i_longitude = point-longitude
	  ).

	ENDLOOP.

  CATCH  cx_uuid_error
		 cx_amdp_execution_failed INTO DATA(lcx).
	cl_demo_output=>display( lcx->get_text( ) ).
ENDTRY.

Please remenber to start the report with <F8> instead of <F9> because to upload a file from your PC we still need the GUI here.

Find the 10 nearest charging stations

To determine the nearest charging stations we need another AMDP procedure (you can add these type definitions and the method to the already existing class from above):

    TYPES ty_long_char TYPE c LENGTH 5000.

    TYPES: BEGIN OF ty_nearest,
             geojson  TYPE ty_long_char,
             distance TYPE p LENGTH 13 DECIMALS 5,
           END OF ty_nearest.

    TYPES: tt_nearest TYPE STANDARD TABLE OF ty_nearest WITH EMPTY KEY.

    CLASS-METHODS get_nearest
      IMPORTING VALUE(i_latitude)  TYPE geolat
                VALUE(i_longitude) TYPE geolon
      EXPORTING VALUE(e_nearest)   TYPE tt_nearest.


  METHOD get_nearest
    BY DATABASE PROCEDURE FOR HDB
    LANGUAGE SQLSCRIPT
    OPTIONS READ-ONLY
    USING zchargingpoints.

    e_nearest = SELECT TOP 10
                  geo.ST_AsGeoJSON() as geojson,
                  NEW ST_POINT(i_longitude, i_latitude).ST_SRID(4326).ST_Distance(geo, 'kilometer') AS distance
                  FROM zchargingpoints
                  ORDER BY distance;

  ENDMETHOD.

How does it work?

For a given point (i_longitude, i_latitude) we calculate the distance in kilometer to every charging point (field “geo” in the database table) and we get back the lowest 10 entries.

Beside the distance we also get the found point in a format called GeoJson, which we’ll use for the visualization now.

Visualization

To display the charging points (and the given point), we’ll use the GeoJson framework (see “Preparation”)

"enter geocoordinates in or around Cologne, Germany
DATA lat TYPE geolat VALUE '50.961558'.
DATA lon TYPE geolon VALUE '6.931160'.

DATA(geojson) = NEW zcl_geojson( ).

DATA(point) = geojson->get_new_point(
				i_latitude  = CONV #( lat )
				i_longitude = CONV #( lon )
			  ).

point->set_properties(
	i_popup_content = 'You are here'
	i_fill_color    = '#0000ff'
).

geojson->add_feature( point ).

zcl_abap_spatial_amdp=>get_nearest(
  EXPORTING
	i_latitude  = lat
	i_longitude = lon
  IMPORTING
	e_nearest = DATA(nearest)
).

LOOP AT nearest REFERENCE INTO DATA(near).

  point = geojson->get_new_point( ).
  point->set_geometry_from_json( CONV #( near->geojson ) ).
  point->set_properties( i_popup_content = |Distance { near->distance } km| ).

  geojson->add_feature( point ).

ENDLOOP.

DATA(json_string) = geojson->get_json( ).

cl_demo_output=>display_html(
  NEW zcl_geojson_leafletjs( )->get_html(
	   i_json = json_string
	   i_width_x_in_px = 900
	   i_use_circle_markers = abap_true         "use circle markers
   )
).

That’s it 🙂

10%20nearest%20charging%20stations

10 nearest charging stations

The code and charging point data you can find in my Github repository.

Cheers, Uwe

Assigned Tags

      5 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Himanshu Sah
      Himanshu Sah

      Greetings! Nicely explained, helpful to understand the spatial function.

      Author's profile photo Matthias Kabel
      Matthias Kabel

      Very nice technical explanation.

      Sorry to say, but in real world the distance in great circles is not the distance you need.

      We have an application (in an old fashioned R/3 EHP8 with MSSQL) for a real world scenario. There you need a geo service wich is able to calculate distances on streets. Especially in a mountain environment there are huge differences to a theoretical approach. Let's have a look on this screenshot. Areas you can reach in 30 or 60 minutes by car.

      So be careful with the spatial features of HANA, your mileage my vary.

      Isochrones%20in%20the%20Alps

      Isochrones in the Alps

       

       

      Author's profile photo Mathias Kemeter
      Mathias Kemeter

      Of course, to calculate isochrones, you need to add a street network and combine the spatial with the graph engine. The approach is described here:
      https://blogs.sap.com/2021/01/05/calculating-isochrones-using-sap-hana-graph-and-spatial/

      It's a matter of the use case, which approach to take. Both is consumable within the ABAP layer (...looking forward to the third part of the series on HANA Graph 😊). The typical "shop finder" application does not bother with street or travel distances.

      Author's profile photo Uwe Fetzer
      Uwe Fetzer
      Blog Post Author

      Hi Matthias,

      it really depends on the use case.

      My scenario is not so far away from the reality. Imagine you are an app developer who wants to display charging stations on your phone. You really don't want to load all worlwide charging stations, but only those, which are visible right now.

      Author's profile photo Katan Patel
      Katan Patel

      +1 for leaflet usage.  I love that library.  So easy to integrate.  I'm also loving this series as it covers a a variety of techniques that are new to me in ABAP/HANA.