Configuring Geolocation Based Statistics

API-Manager Analytics consists of data analysis based on geographical locations that generate graphs. Configuring geolocation based statistics for WSO2 Open Banking is guided through a step by step process below.

Download the Geolocation data from here.
Unzip the file you downloaded.
Create the openbank_geolocationdb database in your database server and execute the relevant database script from the extracted GeolocationData/dbscripts directory.

The examples in this document use MySQL as the database. However, database configurations can be done using MySQL, Microsoft SQL, and Oracle databases.

This can be done using the MySQL Workbench.
For detailed instructions to run the database script, see MySQL Documentation - The Workbench Scripting Shell.

Restore data to the BLOCKS and LOCATION tables by importing data from the BLOCKS.csv and LOCATION.csv from the GeolocationData/data directory of the extracted zip using the commands given below.

Importing GeolocationData/data/LOCATION.csv

mysqlimport -u <DB_USERNAME> -p  --ignore-lines=2 --fields-terminated-by=, --fields-optionally-enclosed-by='"' --local openbank_geolocationdb <EXTRACTED_LOCATION>/GeolocationData/data/LOCATION.csv;

Importing GeolocationData/data/BLOCKS.csv

mysqlimport -u <DB_USERNAME> -p --ignore-lines=2 --fields-terminated-by=, --fields-optionally-enclosed-by='"' --local openbank_geolocationdb <EXTRACTED_LOCATION>/GeolocationData/data/BLOCKS.csv;

For more information, see MySQL Documentation - Data Export and Import.

Check whether your imported dataset is working properly by executing the following query in your MySQL editor.

SELECT loc.country_name,loc.subdivision_1_name 
FROM BLOCKS block , LOCATION loc 
WHERE block.network_blocks = '<Network_part_of_ipv4>' 
AND <Long_value_of_publilc_IP> BETWEEN block.network AND block.broadcast AND block.geoname_id=loc.geoname_id;

Sample query

SELECT loc.country_name,loc.subdivision_1_name 
FROM BLOCKS block , LOCATION loc 
WHERE block.network_blocks = '221.192' 
AND 3720398641 BETWEEN block.network AND block.broadcast AND block.geoname_id=loc.geoname_id;

Configure the datasource for the GEO_LOCATION_DATA. A default datasource for GEO_LOCATION_DATA is packed by default in the <WSO2_AM_ANALYTICS_HOME>/conf/worker/deployment.yaml file under data sources. You can edit the following data source to point to the GEO_LOCATION_DATA database and import the geolocation based data .
```
- name: GEO_LOCATION_DATA
description: "The data source used for geo location database"
jndiConfig:
  name: jdbc/GEO_LOCATION_DATA
definition:
  type: RDBMS
  configuration:
    jdbcUrl: 'jdbc:mysql://localhost:3306/openbank_geolocationdb?autoReconnect=true&useSSL=false' 
    username: 'root'
    password: 'root'
    driverClassName: com.mysql.jdbc.Driver
    maxPoolSize: 50
    idleTimeout: 60000
    validationTimeout: 30000
    isAutoCommit: false
```
If you're using a load balancer in your WSO2 Open Banking API Manager deployment:
1. See Configuring the Proxy Server and the Load Balancer and follow the instructions or
2. Add the GeoLocationStatsHandler handler as explained below:
  1. Open the <WSO2_OB_APIM_HOME>/repository/resources/api_templates/velocity_template.xml file.
  2. Add the following configurations below the APIInvocationLatencyStats handler.
    
    ## add GeoLocationStats handler ## candidate values: PSU-IP-Address (berlin) / x-fapi-customer-ip-address (uk) <handler class="com.wso2.finance.open.banking.gateway.analytics.GeoLocationStatsHandler"> <property name="ipAddressHeaderName" value="PSU-IP-Address"/> </handler>
  3. Republish the APIs.
What's Next
You can remove historical data in the API Management Analytics profile by data purging. For more information, see Purging Analytics Data.

What's Next