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. 

1. Download the Geolocation data from here.

2. Unzip the file you downloaded.

3. 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.

4. 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.

5. 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;

6. Configure the datasource for the GEO_LOCATION_DATA . A default datasource for GEO_LOCATION_DATA  is packed by default in the  <WSO2_API-M_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 .

   deployment.yaml

   - 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:<DB_TYPE>://<DB_HOST>:<DB_PORT>/openbank_geolocationdb?autoReconnect=true&useSSL=false' 
       username: 'DB_USER'
       password: 'DB_PASS'
       driverClassName: com.mysql.jdbc.Driver
       maxPoolSize: 50
       idleTimeout: 60000
       validationTimeout: 30000
       isAutoCommit: false

   What's Next

   You can remove historical data in the API Management Analytics profile by data purging. For more information, see Purging Analytics Data.