IP to country: ip2countryd using hostip.info data

[lang_de]Dieser Eintrag ist nur auf Englisch verfügbar.[/lang_de]

hostip.info is a free GeoIP service. Unfortunately their webservice is slow and they are in the middle of a backend change (from 256 ip_X tables to one table listing all /24 nets…). However, their information is more exact than that of countries.nerd.dk.

To convert the data from hostip.info’s format into something remotely sane usable, you have to merge the ranges into CIDR-blocks per country. If you don’t do that, you’ll get a 160MiB result file which will take ages for ip2countryd to process. With proper optimization and merging, though, the resulting file is barely 4MiB in size and ip2countryd loads like a breeze.

• ip2countryd-r3.tgz 12KiB
  Revision 3 of ip2countryd, minor changes, includes a script to convert hostip.info CSV dumps to rbldnsd format.
• hostip-data.tgz 1.1MiB
  The current dump from hostip.info in rbldnsd format, including the cached version.

The test service at udp://ubermutant.net:8000/ has been updated to r3 with the hostip.info db.

Now, for the rant. hostip.info used to use 256 tables in their DB – one for each class A network. Inside each of these tables were two columns for the second and third octet of the IP. Luckily, they got better and away from this inane structure, switching instead to one table that holds the IP in a single INT column, as a long. But they still store one record for each /24 subnet in existence, plus some invalid and reserved ones (0.0.0.0/24? righto!). I don’t know how they query this monster, since the new site source is not yet available in their SVN, but I’m not sure if I even want to know. The sanest possibility I can think of right now is to take the lower 24 bits of the query and look that up. That still leaves one question, though: Why, oh why, must this be done in gigantic MySQL tables and for every single class C subnet?

Sometimes, a database just isn’t the best solution.