SAP HANA Spatial Reverse Geocoding

[Guillaume2]

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 ?

[mkemeter]

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/755a567dcb2c4708ba869a03e4...

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

Regards,
Mathias

[Guillaume2]

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

G.

[Guillaume2]

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/755a567dcb2c4708ba869a03e4...

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.

[stefan_uhrig]

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

[Guillaume2]

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.

G.

[stefan_uhrig]

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/799246f744b44e38936d633e83.... 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/55bf350bad5a4d5c8265fbc5ec....

Best regards,
Stefan

[Guillaume2]

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.