Friday, February 17, 2017

Geolocate IP Address on OracleDV using Custom R-Script


Prerequisite - Internet Connection
In this blog we will discuss about how to Geolocate IP Address on OracleDV using Custom R-Script. Geolocation is the process of identification or estimation of the real-world geographic location of an object like Mobile phone or a Computer/Machine. Real-world geographic location details include City,Region,Country,Postal code and most importantly Latitude and Longitude etc. This script uses freegeoip.net web service to geolocate the IP address. This script takes IP Address and an ID column which uniquely identifies this IP address, invokes the web service and returns geographic details of the location where this machine is used. These details include Latitude,Longitude, City, Country, Region Postal Code among other information. 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:
1) You have to deploy Heat Map custom visualization plugin from Oracle BI Public store to get above rendering.
2) freegeoip.net restricts number of requests from a particular ip to 15000/hour. If the number of requests exceed 15000 in an hour it throws http 403 forbidden error, before your quota is refurbished.
3) As of now the script invokes this service for each and every IP, as a result this can be little slow. We will try to improve this script further as soon as freegeoip.net provides interface/API to make bulk requests.

How does this script work: This script performs GeoTagging for IP Address. It takes IP Address(both IPv4 and IPv6 formats) as input and returns the latitude and longitude where the machine/computer with this IP address is operating. This script uses a webservice called freegeoip.net. In this script we invoke this service by calling the HTTP API by passing in the IP Address. In addition to the latitude and longitude details, it returns other geographic information like country name, region/state name, zip code, time zone etc. For invalid IP addresses, the script populates all the result columns values with "Invalid IP Address". Since this invokes freegeoip.net webservice, internet connection is mandatory for this script. This script uses rjson r-package.
NOTE: this script does not guarantee to return the latitude and longitude details for each and every valid IP Address neither does it guarantee to return these geographic details completely/accurately.

Inputs to the Script:
1) ID: which uniquely determines the IP Address. This column is needed to join back the result set returned by R to the dataset in DV
2) ipaddr : IP Address. Both IPV4 and IPV6 formats are accepted.

Optional Inputs:
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.

Output:    
1) Geographic details: This script returns the geographic details of the location where this IP Address is located. Geographic details include City, State/County/Region, Country, zip code, time zone and lat long.
2) Located: If the IP Address couldn't be located, the script returns the column "located" with value "N". If the IP Address is successfully located it returns "Y".

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 rjson R package already, please install it using following instructions
    Open R console(double click Rgui.exe present in <Advanced_Analytics_Install_Dir>\bin\x64) and
    install rjson 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("rjson")
3) Download GeoLocate_ipaddress_V1.zip from OracleBI Public Store and unzip it.
4) Copy R.GeoLocateIPAddress.xml to <DV_INSTALL_DIRECTORY>\OracleBI1\bifoundation\advanced_analytics\script_repository
5) Download and deploy Heat 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
    NOTE: 
Use Proxy_Url optional parameter in EVALUATE_SCRIPT only if your network requires you to use proxy.

No comments:

Post a Comment