Technical Articles
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:
- 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
Greetings! Nicely explained, helpful to understand the spatial function.
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 in the Alps
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.
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.
+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.