Question Re: SAP HANA Spatial Reverse Geocoding in Technology Q&A

SAP HANA Spatial Reverse Geocoding

Guillaume2 — Mon, 08 Feb 2021 09:17:16 GMT

Hello,

I try to find a way to reverse geocode a lat/long to an address but using the address/geo directories from SAP HANA.

I managed to do so with SDQ Flowgraphs using the geocode control. But I woud like to do the reverse geocoding directly from SQL or from javascript.

Is someone knows if it is possible ? and if so, could give me some infos ?

Re: SAP HANA Spatial Reverse Geocoding

mkemeter — Mon, 08 Feb 2021 09:27:06 GMT

Hi Guillaume,

I am assuming that you do not want to call remote geocoding APIs as you mention, that you would like to use the directories on SAP HANA? For the on-premise versions of SAP HANA, there is an in-database geocoding available. More details can be found in the respective documentation:
https://help.sap.com/viewer/cbbbfc20871e4559abfd45a78ad58c02/2.0.05/en-US/755a567dcb2c4708ba869a03e4c9a4f7.html

Let me know, if this is what you were looking for.

Regards,
Mathias

Re: SAP HANA Spatial Reverse Geocoding

Guillaume2 — Mon, 08 Feb 2021 10:21:34 GMT

es this what I want to do. But I do not understand how to implement it, How to use the index etc...

The only way I found to do it is by using a flowgraph. But I do not want to use flowgraph but direct SQL.

Regards

Re: SAP HANA Spatial Reverse Geocoding

Guillaume2 — Mon, 08 Feb 2021 11:36:09 GMT

To be more precise, here is what I managed to do, and what I would like to do:

What I managed to do: I have a table of POINTS: TEST_POINTS defined as Key: POINTID (Integer), LATITUDE (DOUBLE), LONGITUDE(DOUBLE).

I want to get the 10th nearest address from each point. I have created a flowgraph that include the Geocode transformation to reverse all points from TEST_POINTS (LATITUDE/LONGITUDE). I get as Results 2 TABLES : REVSERSE_GEOCODE with the POINTID and the GEO_INFO_CODE (telling everything is ok or not), and the SEARCHRESULTS table containing the 10th nearest Addresses (get from the GEOCODE and ADDRESS Directories).

This is working fine but as a flowgraph.

What I would like to have is a way to get the 10th nearest addresses directly from SQL. Taking as input all the rows of TEST_POINTs and get as a results the 10th nearest addresses directly without having to execute the flowgraph. I don't know how, maybe using a function or a procedure but only with sql code. And also using standard ADDRESS and GEOCODE Directories from SAP (Which are already installed on my HANA DB).

I See that in the HANA Documentation (also mentioned by Mathias):

https://help.sap.com/viewer/cbbbfc20871e4559abfd45a78ad58c02/2.0.05/en-US/755a567dcb2c4708ba869a03e4c9a4f7.html

There SAP Says:

"This is a solution that has high performance with no third party data and no need to use a web service, and it is not necessary to create a flowgraph using Web-based Development Workbench"

I have the address/geocode directories subscription. And they are installed in my HANA System.

But I am lost, How to do it ? Maybe I am missing some HANA Knowledge ? Do I need to create the INDEX and HOW ? How do I do the Search ?...

Best regards.

Re: SAP HANA Spatial Reverse Geocoding

stefan_uhrig — Mon, 08 Feb 2021 14:57:27 GMT

Hi Guillaume,

The GEOCODE INDEX mentioned by Mathias can be used to do (forward) geocoding, but cannot be used to do reverse geocoding. If set up properly, it will automatically set the locations of addresses in a table, also on insertion of new records and on updates of existing records.

I'm not aware of a solution that can do reverse geocoding and find the 10 nearest addresses for a given location via SQL. However, I'm not an expert on SAP HANA Smart Data Integration and SAP HANA Smart Data Quality.

Best regards,
Stefan

Re: SAP HANA Spatial Reverse Geocoding

Guillaume2 — Mon, 08 Feb 2021 15:24:19 GMT

Hi Stefan,

Thanks for your answer. So, ok INDEX GEOCODE will not work for reverse, I will try to search for other solution then.

But for the direct Geocoding (Address => Geo Point), I have implemented it as the documentation says: using the GEOCODE INDEX. But nothing appear in the table:

I have created a Table :

Create COLUMN TABLE "TEST_POINTS_GEO" (
"POINTID" Integer COMMENT 'Geo Point ID',
"POINT_COUNTRY" NVARCHAR(30) COMMENT 'Country',
"POINT_STATE" NVARCHAR(30) COMMENT 'State',
"POINT_COUNTY" NVARCHAR(30) COMMENT 'County',
"POINT_CITY" NVARCHAR(30) Comment 'City',
"POINT_POSTALCODE" NVARCHAR(30) COMMENT 'Postal Code',
"POINT_DISTRICT" NVARCHAR(30) COMMENT 'District',
"POINT_STREET" NVARCHAR(30) COMMENT 'Street',
"POINT_HOUSENUM" NVARCHAR(30) COMMENT 'House Number',
"POINT_ADDR" NVARCHAR(30) COMMENT 'Address Line',
"POINT_GEOCODE" ST_GEOMETRY COMMENT 'Geo Point as WKT',
"LATITUDE" FLOAT COMMENT 'Latitude',
"LONGITUDE" FLOAT COMMENT 'Longitude',
PRIMARY KEY ("POINTID"))
COMMENT 'Test Points GeoCode'
UNLOAD PRIORITY 5 AUTO MERGE;

I have created the Index like that :

CREATE GEOCODE INDEX TEST_GEO ON "TEST_POINTS_GEO"("POINT_COUNTRY","POINT_STATE","POINT_CITY","POINT_POSTALCODE","POINT_STREET","POINT_HOUSENUM","POINT_ADDR","POINT_GEOCODE");

=> At this point everything is OK.

and I insert data into the table like That:

insert into "TEST_POINTS_GEO" (POINTID, POINT_COUNTRY, POINT_CITY, POINT_POSTALCODE, POINT_STREET, POINT_HOUSENUM) VALUES(
3,'FR','PARIS','75001','RUE DE RIVOLI','49');

The Row Appears in the TEST_POINTS_GEO, but no coordinates in the POINT_GEOCODE, nothing changed the value is still empty. Maybe I misunderstood something ??

regards.

Re: SAP HANA Spatial Reverse Geocoding

stefan_uhrig — Tue, 09 Feb 2021 09:57:01 GMT

Hi Guillaume,

Have you set the geocode provider as described (https://help.sap.com/viewer/cbbbfc20871e4559abfd45a78ad58c02/2.0.05/en-US/93a156496e624230b797f40a3c907933.html): ALTER SYSTEM ALTER CONFIGURATION ('xsengine.ini','SYSTEM') set ('geocoding','provider') = 'local' WITH RECONFIGURE

Further setup instructions are available from https://help.sap.com/viewer/cbbbfc20871e4559abfd45a78ad58c02/2.0.05/en-US/799246f744b44e38936d633e83784b15.html. It describes which columns must be mapped and mentions that you have to enable the script server.

If geocoding still does not work, have a look at view M_INDEXING_QUEUES to check if your address rows are scheduled for indexing (or have been indexed). As a last resort, check the index server and XS engine trace files for entries tagged with "geocoding" as described in https://help.sap.com/viewer/cbbbfc20871e4559abfd45a78ad58c02/2.0.05/en-US/55bf350bad5a4d5c8265fbc5ec205851.html.

Best regards,
Stefan

Re: SAP HANA Spatial Reverse Geocoding

Guillaume2 — Wed, 17 Feb 2021 14:16:50 GMT

Hello Mathias,

I finally found how to solve my point. I understand (Thanks to Stefan) that I cannot do Reverse-geocoding, with the INDEX. At least, I find the way to use it in the Forward geocoding.

Concerning the reverse Geocoding I will Ue my Flowgraph or an external API (like opencage).

Thanks.