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.
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.
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)
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:
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".
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:
After googling this annotation I also found a documentation by the way 😉
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.
You see, our field with the new datatype geom_ewkb will be filled by creating a new point object.
Now we are ready to upload the CSV. Except of the AMDP call there's nothing fancy here.
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.
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):
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.
To display the charging points (and the given point), we'll use the GeoJson framework (see "Preparation")
That's it 🙂
The code and charging point data you can find in my Github repository.
Cheers, Uwe
- 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
- SAP Managed Tags:
5 Comments
