Wednesday, February 15, 2017

Reverse Geocoding on OracleDV using Custom R-Scripts

Pre-requisites: Internet connection

In this blog we will discuss about an interesting and new addition to Oracle BI Public Store : custom R-Script to perform Reverse Geocdong on OracleDV. Reverse Geocoding is the process of back(reverse) coding of a point location (latitude,longitude) to a readable address or place name. This helps in identifying a nearby street address,places, areas, county, state ,country etc. This custom R-Script takes latitude and longitude details as input and returns Address in a general format with details like, House number, Street name, Settlement/Area/County/Muncipality, city, state, country and Postal code details. This script can be downloaded from Oracle BI Public Store and here is how your DV Desktop will look like after deploying this plugin:

Please note that you have to deploy Custom Points Map custom visualization plugin from BI Public store to get this rendering.

How does this script work: This script performs Reverse Geocoding by taking longitude and latitude as inputs and returning the address of that particular location. This script uses Oracle Geocoder service to identify the locations. So internet connection is required for this script to work. Please note that some countries like Japan are not supported by reverse geocoding as of now. For Latitude Longitude falling in those geographies, currently the script will not be able to identify the address. Geocoder service accepts requests in the following xml format:

<geocode_request vendor="elocation">
    <input_location id="27010" country="us" longitude="-122.26193971893862" latitude="37.53195483966782" />

In the above format, element and attributes between <input_location id = "XXXX" ...... /> represents a row. In the script we construct xml request with this format, batch the requests and send for geocoding. This script uses httr and XML R packages.

Please note
1) This script does not guarantee to geocode each and every longitude and latitude given, neither does it guarantee to provide accurate details, these are only approximations.To differentiate between the latlong that could be reverse geocoded and that could be not, we have added a flag called "address_located" which will be set to "Y" if the latlong could be reverse Geocoded and "N" if not.
2) Please clean the Data for any NULLS/NAs in latitude or longitude columns

Inputs to the Script:
1) ID: this is a mandatory column and identifies the combination of lat/long
2) longitude: Longitude of the location to be identified
3) latitude : Latitude of the location to be identified

Optional Inputs:
1) service_url : Specify the URL for Oracle geocoder service. If you do not specify anything by default the current ongoing version of Oracle Geocoder service.
2) proxy_url : If your network requires you to use some proxy(because of some firewall or VPN), please specify the proxy url with the port number.

1) This script gives the address in a general format that is broken into individual components like house_number, street, settlement, city, state, country, postal_code. Please note that all the components of address are not guaranteed to be present for each identified address. Some components for some addresses may not be returned by the R-script.
2) address_located: This column tells whether the latlong could be reverse geocoded or not. If address_located is "Y" then the latlong was successfully identified. If address_located is "N" then the latlong could not be located successfully.

Steps to deploy this R-Script plugin in your local OracleDV:

1) Install Advanced Analytics feature in Oracle DV by clicking on the below icon. This will install Oracle R deployment. Alternatively you can install Advanced Analytics by running install_advanced_analytics.cmd present in <DV_INSTALL_DIRECTORY>

2) If not installed httr and XML R packages already, please install it using following instructions
    Open R console(double click Rgui.exe present in <Advanced_Analytics_Install_Dir>\bin\x64) and
    install httr & XML Package.
    Following are the R commands to install:
     Set Proxy:
        $ Sys.setenv(http_proxy="http://<your_proxy_host>:<port>")
           set proxy appropriate to your network settings.
     Install Package:
        $ install.packages("httr")
        $ install.packages("XML")
3) Download from OracleBI Public Store and unzip it.
4) Copy R.Geocoding.xml to <DV_INSTALL_DIRECTORY>\OracleBI1\bifoundation\advanced_analytics\script_repository
5) Download and deploy Custom Points Map custom visualization plugin from Oracle BI Public Store. Instructions to deploy this Custom Viz plugin are described in the Public store.
6) Import the .dva project to Oracle DV. Password for the .dva file is Admin123
Use Proxy_Url optional parameter in EVALUATE_SCRIPT only if your network requires you to use proxy.

No comments:

Post a Comment