SA-MP Forums

Go Back   SA-MP Forums > SA-MP Scripting and Plugins > Filterscripts > Includes

Reply
 
Thread Tools Display Modes
Old 11/11/2018, 05:19 PM   #1
Calisthenics
Gangsta
 
Join Date: May 2018
Posts: 559
Reputation: 92
Default GeoLite

GeoLite (SQLite)

It is based on the free product GeoLite2 by MaxMind.

I was updating the country database every month for MySQL and decided to update the GeoIp databases by Whitetiger's include as many people requested. It turned out I was unable to, the way the databases were structured. I converted my version to SQLite and started comparing the two includes with geolite.inc being victorious. But this was to be expected with not only the good database structure and the use of indexes but also the appropriate queries to avoid range scans. Even though latest database provide more data than last year, it did not affect the performance in any way.

The past days, I managed to import Autonomous System (AS) and City databases with the original databases having big flows.
  • Certain organizations in Autonomous System list did have many unique identifiers (ASN - Autonomous System Number) registered to IANA. All duplicates were removed and kept only their initial ASN.
  • Certain ip ranges in City database:
    • did not provide a city name. Country name was used in replacement.
    • did not provide a city name, nor a country name. Continent name was used in replacement.

The above issue arose another problem related to time zones.
  • Antarctica, Asia and Europe have time zone set as +00:00
  • Cities with country name as replacement have central standard time set mostly, with few exceptions. Some examples are:
    • United States is set to have UTC -05:00 (Washington, DC) whereas Central Standard Time is in Chicago (-06:00)
    • Russia is set to Moscow Standard Time (+03:00)

I initially posted these changes and improvements in Whitetiger's thread but due to their absence, I decided to create a new thread. I was also unaware if Whitetiger would accept the changes, nor how the updates would be done.

Installation

Repository: https://github.com/George480/geolite
Releases: https://github.com/George480/geolite/releases
Include: https://raw.githubusercontent.com/Ge...er/geolite.inc

Save as geolite.inc into pawno\include folder. Include in your code and begin using the library:

Code:
#include <geolite>
Place the database you want to use into scriptfiles folder.

Functions
IP-Based Functions:
  • GetIpAutonomousSystem(const geolite_ip[], geolite_dest[], geolite_len = sizeof (geolite_dest))
    • Stores the Autonomous System organization (ISP is an Autonomous System) according to given IP, passed by reference.
    • Returns 1 on success (database file exists in scriptfiles folder) or 0 on failure.
  • GetIpCountry(const geolite_ip[], geolite_dest[], geolite_len = sizeof (geolite_dest))
    • Stores the Country name according to given IP, passed by reference.
    • Returns 1 on success (database file exists in scriptfiles folder) or 0 on failure.
  • GetIpCity(const geolite_ip[], geolite_dest[], geolite_len = sizeof (geolite_dest))
    • Stores the City name according to given IP, passed by reference.
    • Returns 1 on success (database file exists in scriptfiles folder) or 0 on failure.
  • GetIpUTC(const geolite_ip[], geolite_dest[], geolite_len = sizeof (geolite_dest))
    • Stores the UTC offset according to given IP, passed by reference.
    • Returns 1 on success (database file exists in scriptfiles folder) or 0 on failure.
  • IsIpProxy(const geolite_ip[])
    • Requires country database.
    • Returns 1 if the given ip is public proxy otherwise 0. It will also return 0 if database file does not exist in scriptfiles folder.
Player-Based Functions:
  • GetPlayerAutonomousSystem(playerid, geolite_dest[], geolite_len = sizeof (geolite_dest))
    • Stores the Autonomous System organization (ISP is an Autonomous System) according to given player's IP, passed by reference.
    • Returns 1 on success (database file exists in scriptfiles folder and player is connected) or 0 on failure.
  • GetPlayerCountry(playerid, geolite_dest[], geolite_len = sizeof (geolite_dest))
    • Stores the Country name according to given player's IP, passed by reference.
    • Returns 1 on success (database file exists in scriptfiles folder and player is connected) or 0 on failure.
  • GetPlayerCity(playerid, geolite_dest[], geolite_len = sizeof (geolite_dest))
    • Stores the City name according to given player's IP, passed by reference.
    • Returns 1 on success (database file exists in scriptfiles folder and player is connected) or 0 on failure.
  • GetPlayerUTC(playerid, geolite_dest[], geolite_len = sizeof (geolite_dest))
    • Stores the UTC offset according to given player's IP, passed by reference.
    • Returns 1 on success (database file exists in scriptfiles folder and player is connected) or 0 on failure.
  • IsPlayerUsingProxy(playerid)
    • Requires country database.
    • Returns 1 if the ip of the given player is public proxy otherwise 0. It will also return 0 if database file does not exist in scriptfiles folder.

Usage
pawn Code:
#include <a_samp>
#include <sscanf2>
#include <geolite>

main() {}

public OnPlayerConnect(playerid)
{
    new player_name[MAX_PLAYER_NAME], player_country[MAX_COUNTRY_LENGTH], connection_text[80];
       
    GetPlayerName(playerid, player_name, MAX_PLAYER_NAME);
    GetPlayerCountry(playerid, player_country, MAX_COUNTRY_LENGTH);

    format(connection_text, sizeof (connection_text), "%s joined from %s", player_name, player_country);
    SendClientMessageToAll(0xFFFF00FF, connection_text);
    return 1;
}

Extra Notes
127.0.0.1 is given as "Unknown" as it is a private IP.

Country, City and ASN databases will be updated every first Wednesday of every month.

It opens the databases on startup according to which database exists in scriptfiles folder, therefore if you prefer to use the Country database only, place maxmind_country.db into scriptfiles folder and not the other two databases.

It only detects public proxies and not VPNs.

A MySQL version would require non-threaded queries to keep the same usage of functions. If you have any suggestion, please inform me.

Constants:
pawn Code:
#define MAX_AUTONOMOUS_SYSTEM_LENGTH    95
#define MAX_COUNTRY_LENGTH              45
#define MAX_CITY_LENGTH                 64
#define MAX_UTC_LENGTH                  7

Requirements
sscanf: https://github.com/maddinat0r/sscanf/releases

Credits
  • MaxMind - GeoLite2
  • Alex "******" Cole - sscanf
  • Andy Skelton - ordering by `ip_to` (avoidance of range scan)
  • Nikolay Bachiyski - ordering by `ip_to` (avoidance of range scan)
  • Mark Robson - highest `ip_from` which is less than or equal to the given IP (avoidance of next country returned due to gaps)

Last edited by Calisthenics; 05/12/2018 at 09:36 AM.
Calisthenics is offline   Reply With Quote
Old 12/11/2018, 12:58 AM   #2
Pottus
High-roller
 
Pottus's Avatar
 
Join Date: Jun 2012
Posts: 4,857
Reputation: 1292
Default Re: GeoLite

It only feels like half the picture, you should look at taking the plugin route to auto-update the databases.
Pottus is offline   Reply With Quote
Old 05/12/2018, 10:15 AM   #3
Calisthenics
Gangsta
 
Join Date: May 2018
Posts: 559
Reputation: 92
Default Re: GeoLite

New version released!

Added IsIpProxy and IsPlayerUsingProxy functions.
Restructured tables for better compression.
Include: https://raw.githubusercontent.com/Ge...er/geolite.inc

Note: The free product of MaxMind can only detect public proxies, not VPNs.

Databases were updated for December 2018: https://github.com/George480/geolite/releases/tag/v2.0

Quote:
Originally Posted by Pottus View Post
It only feels like half the picture, you should look at taking the plugin route to auto-update the databases.
The way databases are distributed to fix duplicates and other issues mentioned, it requires mysql, a table for time zones, a procedure to re-assign ids for ASN and a set of queries to execute. I simply download, extract and move .CSV files to be able to use LOAD FILE from mysql. I paste a handful amount of queries and they are updated on their own but it takes 45 minutes. Exporting data and importing to the respective databases is the last step.

I cannot think of a way to do it but manually.
Calisthenics is offline   Reply With Quote
Old 05/12/2018, 11:28 AM   #4
BigETI
Banned
 
Join Date: Mar 2010
Location: Germany
Posts: 1,055
Reputation: 359
Default Re: GeoLite

You could start versioning your database and implement a web interface, where this library could do a web request to download the changes for its database.
BigETI is offline   Reply With Quote
Old 03/01/2019, 05:03 PM   #5
Calisthenics
Gangsta
 
Join Date: May 2018
Posts: 559
Reputation: 92
Default Re: GeoLite

Databases were updated for January 2019: https://github.com/George480/geolite/releases/tag/v3.0
Calisthenics is offline   Reply With Quote
Old 06/02/2019, 01:02 PM   #6
Calisthenics
Gangsta
 
Join Date: May 2018
Posts: 559
Reputation: 92
Default Re: GeoLite

Databases were updated for February 2019: https://github.com/George480/geolite/releases/tag/v4.0
Calisthenics is offline   Reply With Quote
Old 07/02/2019, 02:57 PM   #7
ToiletDuck
Huge Clucker
 
ToiletDuck's Avatar
 
Join Date: Apr 2012
Location: Philippines, Lucena City
Posts: 393
Reputation: 43
Default Re: GeoLite

Quote:
Originally Posted by Calisthenics View Post
Databases were updated for February 2019: https://github.com/George480/geolite/releases/tag/v4.0
Thanks for updating it.
ToiletDuck is offline   Reply With Quote
Old 05/03/2019, 08:26 PM   #8
ReshiramZekrom
Big Clucker
 
ReshiramZekrom's Avatar
 
Join Date: May 2012
Location: Bari, Italy
Posts: 133
Reputation: 14
Default Re: GeoLite

Thank you really much!
__________________
ReshiramZekrom is offline   Reply With Quote
Old 06/03/2019, 06:09 PM   #9
Calisthenics
Gangsta
 
Join Date: May 2018
Posts: 559
Reputation: 92
Default Re: GeoLite

Databases were updated for March 2019: https://github.com/George480/geolite/releases/tag/v5.0

Quote:
Originally Posted by ToiletDuck View Post
Thanks for updating it.
Quote:
Originally Posted by ReshiramZekrom View Post
Thank you really much!
Thank you for using it.
Calisthenics is offline   Reply With Quote
Old 04/04/2019, 01:30 PM   #10
Calisthenics
Gangsta
 
Join Date: May 2018
Posts: 559
Reputation: 92
Default Re: GeoLite

IsIpProxy and IsPlayerUsingProxy functions were only working with country database. It is now possible to use them with city database in case the country database is not used. Re-download at: https://raw.githubusercontent.com/Ge...er/geolite.inc

Databases were updated for April 2019: https://github.com/George480/geolite/releases/tag/v6.0
Calisthenics is offline   Reply With Quote
Reply

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off



All times are GMT. The time now is 09:52 AM.


Powered by vBulletin® Version 3.8.6
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.