This is part two of a three part series:

• Part I: Document Store aka NoSQL database 
• Part II: Spatial functions
• Part III: Graph functions

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 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 nearest charging stations

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

Cheers, Uwe