Kostenlose Daten zur Geo-Lokalisation von IP-Adressen

Im Internet kann man eine ganze Reihe von Datenbanken und Online-Services finden die Daten zur Verfügung stellen mit denen man eine IP-Adresse einer geographischen Position zuordnen kann. Neben einigen kommerziellen Datenbanken gibt es auch einige kostenlose Datenbanken wie z.B. diese von Blogama.org (dort stehen Versionen als MySQL-Dump und als CSV-Datei zur Verfügung).

Dort finden sich auch einige Beispiele wie man die Datenbank abfragt und auch eine einfache API um die Daten von Blogama per HTTP abzufragen.

Daten auf Deutschland beschränken

Für viele Anwendungen kann es genug sein wenn nur IP-Adressen aus Deutschland lokalisiert werden können. Dazu kann man alle anderen Daten aus der Datenbank entfernen. Die folgenden SQL-Queries leisten genau das und lassen lediglich die IP-Adressen übrig die Deutschland, anonymen Proxies (A1) und Satellitenanbietern (A2) zugeordnet sind.

DELETE FROM cities WHERE country_code NOT IN ('DE','A1','A2');
DELETE FROM fips_countries WHERE code NOT IN ('DE','A1','A2');
DELETE FROM fips_regions WHERE country_code NOT IN ('DE','A1','A2');
DELETE FROM ip_group_city WHERE country_code NOT IN ('DE','A1','A2');
DELETE FROM ip_group_country WHERE country_code NOT IN ('DE','A1','A2');

nach dem Löschen kann man die Datenbank ggf. mit den folgenden SQL-Queries aufräumen:

OPTIMIZE TABLE cities;
OPTIMIZE TABLE fips_countries;
OPTIMIZE TABLE fips_regions;
OPTIMIZE TABLE ip_group_city;
OPTIMIZE TABLE ip_group_country;

Den Zugriff beschleunigen

Normalerweise enthält die Datenbank nur Indizes für die IP-Adressen. Das beschleunigt zwar den Zugriff wenn man die Beispiel-Queries von der Website nutzt, hilft aber leider nicht wenn man z.B. per JOIN-Anweisung noch die Daten aus fips_regions und fips_countries laden möchte. Folgende SQL-Queries fügen noch zusätzliche Indizes hinzu, die in solchen Fällen helfen:

CREATE INDEX ix_country_code ON cities (country_code);
CREATE INDEX ix_latitude ON cities (latitude);
CREATE INDEX ix_longitude ON cities (longitude);

CREATE INDEX ix_country_code ON fips_regions (country_code);
CREATE INDEX ix_code ON fips_regions (code);

CREATE INDEX ix_country_code ON ip_group_city (country_code);
CREATE INDEX ix_region_code ON ip_group_city (region_code);
CREATE INDEX ix_zipcode ON ip_group_city (zipcode);
CREATE INDEX ix_latitude ON ip_group_city (latitude);
CREATE INDEX ix_longitude ON ip_group_city (longitude);

ACHTUNG! Durch die zusätzlichen Indizes erhöht sich natürlich auch der Speicherverbrauch stark.

Um z.B. alle interessanten Daten für eine IP-Adresse zu lesen kann man dann z.B. eine Query wie die folgende nutzen:

SELECT
*
FROM
ip_group_city AS igc
LEFT JOIN
fips_regions AS fr ON (igc.region_code = fr.code) AND (igc.country_code = fr.country_code)
LEFT JOIN
fips_countries AS fc ON (igc.country_code = fc.code)
WHERE
igc.ip_start < = 123456789
ORDER BY
igc.ip_start DESC LIMIT 1

(123456789 ist dabei natürlich die umgewandelte IP-Adresse )

Eine kleine Demo-Implementierung findet ihr hier.

Leider schwankt die Genauigkeit der Daten sehr. Auch zwischen den einzelnen Releases der Datenbank. Mal trifft die Lokalisierung fast auf den Meter genau und manchmal liegt sie Dutzende Kilometer daneben.

Verwandte Themen:

1. d3.js – Daten mit JavaScript visualisieren (und mehr)
2. phpMiniAdmin – Mini-Alternative zu phpMyAdmin