Ako na GeoIP s využitím MySQL

Toto je stručný návod na využitie dát z MaxMind GeoLite City pomocou vyhľadávanie cez MySQL databázu. Nie každý webhosting má tuto podporu implementovanú priamo vo svojom systém s použitím PHP funkcii určených pre GeoIP lokalizáciu. Nemôžete si tam sami inštalovať programy či knižnice a nemáte možnosť si GeoIP nainštalovať binárne. MaxMind obsahuje databázu IP adries pre identifikáciu kontinentu, krajiny, mesta, zemepisnej šírky a dĺžky, ale aj informácie o organizácii, ktorej je IP adresa pridelená.

Hudobný nástroj s použitím 2k guľôčok  ·  Ako na RSYNC cez Windows?

Takáto identifikácia je vhodná ak chcete povoliť, či zamedziť prístup k službe pre konkrétnu krajinu, či kontinent. Tiež je vhodná na lepšie cielený marketing podľa regiónu čo je v prípade GeoIP City na Slovensku úplne nepoužiteľné, ale niekto to možno využijete.Na stránke http://www.maxmind.com/app/geolitecity môžete stiahnuť databázu GeoLite Country (IPv6), GeoLite City (IPv6) a GeoLite ASN (IPv6) v binárnej podobe alebo CSV. Nás bude zaujímať CSV formát, ktorý použijeme na importovanie do našej MySQL databázy. Návod bude obsahovať len importovanie GeoLite City, ostatné si môžete podľa návodu importovať podobným spôsobom. Čiže sa prihlásime do našej MySQL a predpokladáme, že už máme vytvorenú databázu napríklad geoip. Ak nie, tak sa musíme prihlásiť s oprávnením vytvárať databázy (root).

CREATE DATABASE geoip;
USE geoip;

CREATE TABLE blocks (startIpNum int(10) unsigned NOT NULL, endIpNum int(10) unsigned NOT NULL, locId int(10) unsigned NOT NULL, PRIMARY KEY (endIpNum));

CREATE TABLE location (locId int(10) unsigned NOT NULL, country char(2) NOT NULL, region char(2) NOT NULL, city varchar(50), postalCode char(5) NOT NULL, latitude float, longitude float, dmaCode integer, areaCode integer, PRIMARY KEY (locId));

Týmto sme si vytvorili dve tabuľky do ktorých môžeme importovať pomocou príkazu LOAD DATA INFILE súbory GeoLiteCity-Blocks.csv a GeoLiteCity-Location.csv.

LOAD DATA LOCAL INFILE 'GeoLiteCity-Blocks.csv'
INTO TABLE geoip.blocks
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' LINES TERMINATED BY '\n';

LOAD DATA LOCAL INFILE 'GeoLiteCity-Location.csv'
INTO TABLE geoip.location
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' LINES TERMINATED BY '\n';

Ak chcete tabuľky aktualizovať, tak by ste ich mali LOCKnúť.

LOCK TABLES blocks READ, location READ;
DELETE from blocks;
DELETE from location;

Takto sme si naimportovali CSV do našej databázy a môžeme bez problémov identifikovať podľa IP adresy kód krajiny, mesto, či ostatné informácie pokiaľ sa nachádzajú v databáze.

Teraz sa ale každý pýtate, ako efektívne vyhľadávať v tej zmesi dát. Keďže databáza IP adries môže obsahovať aj rozsah IP adries, tak by bolo asi dosť zložité vyhľadávať podľa klasickej IP adresy (192.168.0.1) a je nutné hľadanú IP adresu previesť pomocou PHP funkcie ip2long() na jej dlhý tvar a takto získame číslo 3232235521, ktoré si uložíme do premennej $ip. Teraz môžeme vyhľadávať:

SELECT locId FROM blocks WHERE $ip BETWEEN startIpNum AND endIpNum LIMIT 1

Takto získame locId, ktoré uložíme do premennej $locId a podľa ktorého v druhej tabuľke vyhľadáme konkrétne informácie.

SELECT * FROM location WHERE locId = $locId LIMIT 1

Ďalej si už narábate s informáciami z databázy ako potrebujete vy. Podobne môžete importovať databázu GeoLite Country (IPv6), GeoLite City (IPv6) a GeoLite ASN (IPv6). Na adrese http://www.maxmind.com/en/geoip_demo môžete využiť demo službu pokiaľ chcete len sem tam zistiť polohu. Na tejto svojej stránke si monitorujem návštevnosť a na identifikáciu využívam tri rôzne zdroje. Prvým zdrojom je Lite verzia MaxMindáckej databázy o ktorej tu píšem a je zadarmo. Druhým zdrojom je DB-IP.com, kde je možné si taktiež stiahnuť Free verziu, alebo môžete použiť ich Free API kde je maximálne 2000 požiadaviek / deň. Posledným zdrojom je API z Telize.com, ktoré je bez limitov (aspoň som nič také nenašiel), ale nie je zjavne moc často aktualizované a úplne presné. Najpresnejšia mi príde platená verzia z MaxMind.com a DB-IP.com.

Databázu z DB-IP.com naimportujete presne tak isto ako databázu z MaxMind.com, tam je ale problém v tom, že ip adresy nie sú v číselnom formáte. Takže po importe dát do tabuľky je dobré túto tabuľku následne prekonvertovať pomocou SELECTU:

INSERT INTO nova_tabulka (ipstart, ipend, ...) SELECT INET_ATON(ipstart), INET_ATON(ipend), ... FROM DB-IP_tabulka

Takto dostaneme novú tabuľku, kde bude ipstart a ipend už v číselnom formáte. Môžeme použiť na vyhľadávanie podobný SELECT ako som použil vyššie pri Maxmindáckej databáze, ale keďže rozsahy IP adries idú za sebou a nemali by sa zjavne prekrývať, tak tento SELECT je výkonnejší:

SELECT * FROM nova_tabulka WHERE ipend >= $ip LIMIT 1

Tento SELECT sa môže použiť aj vyššie, ale v Maxmindáckej databáze to vyzerá, že rozsahy IP adries nejdú tiež za sebou, ale je niekoľko krát rýchlejší. Tiež treba vytvoriť index pre ipend. Prajem veľa spokojných lokalizovaní.