Mobile Operator/Carrier detection using MaxMind – Second Part – Creating carriers JSON file

In this post we saw how the first steps to mobile operator/carrier detection. We saw how the data of carriers and carriers mapping is stored in database.
Now we will see how to convert this data to a JSON file. Our mobile operator/carrier detection module will be probably be used by an Ad server. Ad servers are usually not connected to database (you need Ad server to be robust and work under high pressure). Therefore, we would like to be able to load the carriers information from a simple file.
For this purpose, we will create a simple class named CarrierDataHolder that will host all the mobile operator/carrier mapping in a single map. The map is constructed from 2 inner classes: a composite key named Key and value named Value.
These are the members of the map Key:

• countryCode
• isp
• org

These are the members of the map Value:

• carrierId
• name

Note, that for the Key of the map, we implement “hashCode” and “equals” methods, otherwise the map would not function properly.
By using this map, we later be able to get any carrier simple by supplying country code, isp and organization. The country code isp and organization will be extracted from the user ip using MaxMind.
The CarrierDataHolder contains 2 important methods:

• loadCarriersFromDB – This method gets a database connection and loads all the carrier and carrier mapping information to the carrierMapping map.
• toJSONFile – This method stores all the data loaded to carrierMapping map to a JSON file. The data is saved to JSON using XStream open source project.

Let’s have a look at the CarrierDataHolder class:

package com.bashan.blog.geo;
import com.thoughtworks.xstream.XStream;
import com.thoughtworks.xstream.io.json.JettisonMappedXmlDriver;
import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import java.sql.*;
import java.util.HashMap;
import java.util.Map;
/**
* @author Bashan
*/
public class CarrierDataHolder {
 
public final Map<Key, Value> carrierMapping = new HashMap<Key, Value>();
 
public void loadCarriersFromDB(Connection conn) throws SQLException {
carrierMapping.clear();
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("select c.carrier_id, c.country_code, c.name, cm.isp, cm.org from carrier c inner join " +
"carrier_mapping cm on c.carrier_id = cm.carrier_id order by c.carrier_id");
  while (rs.next()) {
    int carrierId = rs.getInt("carrier_id");
String countryCode = rs.getString("country_code");
String name = rs.getString("name");
String isp = rs.getString("isp");
String org = rs.getString("org");
carrierMapping.put(new Key(countryCode, isp, org), new Value(carrierId, name));
}
}
 
private XStream getXStream() {
XStream xstream = new XStream(new JettisonMappedXmlDriver());
xstream.setMode(XStream.NO_REFERENCES);
xstream.alias("carrierMapping", Map.class);
xstream.alias("key", Key.class);
xstream.alias("value", Value.class);
  return xstream;
} 
 
public String toJSON() {
XStream xStream = getXStream();
  return xStream.toXML(carrierMapping);
} 
 
public void toJSONFile(File file) throws IOException {
XStream xStream = getXStream();
FileWriter fileWriter = null;
  try {
fileWriter = new FileWriter(file);
xStream.toXML(carrierMapping, fileWriter);
} finally {
      if (fileWriter != null) {
fileWriter.close();
}
}
}
 
public static class Key {
 
private String countryCode;
private String isp;
private String org;
 
public Key() {
}
 
public Key(String countryCode, String isp, String org) {
  this.countryCode = countryCode;
  this.isp = isp;
  this.org = org;
}
 
public String getCountryCode() {
  return countryCode;
} 
 
public void setCountryCode(String countryCode) {
  this.countryCode = countryCode;
} 
 
public String getIsp() {
  return isp;
} 
 
public void setIsp(String isp) {
  this.isp = isp;
} 
 
public String getOrg() {
  return org;
} 
 
public void setOrg(String org) {
  this.org = org;
}
 
@Override
public boolean equals(Object o) {
  if (this == o) return true;
  if (o == null || getClass() != o.getClass()) return false;
Key key = (Key) o;
  if (countryCode != null ? !countryCode.equals(key.countryCode) : key.countryCode != null) return false;
  if (isp != null ? !isp.equals(key.isp) : key.isp != null) return false;
  if (org != null ? !org.equals(key.org) : key.org != null) return false;
  return true;
} 
 
@Override
public int hashCode() {
  int result = countryCode != null ? countryCode.hashCode() : 0;
result = 31 * result + (isp != null ? isp.hashCode() : 0);
result = 31 * result + (org != null ? org.hashCode() : 0);
  return result;
}
} 
 
public static class Value {
 
private int carrierId;
private String name;
 
public Value() {
} 
 
public Value(int carrierId, String name) {
  this.carrierId = carrierId;
  this.name = name;
} 
 
public int getCarrierId() {
  return carrierId;
} 
 
public void setCarrierId(int carrierId) {
  this.carrierId = carrierId;
} 
 
public String getName() {
  return name;
} 
 
public void setName(String name) {
  this.name = name;
}
}
}

The data loaded by the loadCarriersFromDB method is taken from carrier and carrier_mapping tables. This post explains how they are created.

Let’s have a look at a small test program that loads all carriers information and stores it to a JSON file. Note that we assume the name of the database is adserver:

public static void main(String[] args) {
CarrierDataHolder carrierDataHolder = new CarrierDataHolder();
Connection conn = null;
  try {
String userName = "root";
String password = "root";
String url = "jdbc:mysql://localhost:3306/adserver?useUnicode=true&characterEncoding=UTF-8";
Class.forName("com.mysql.jdbc.Driver").newInstance();
conn = DriverManager.getConnection(url, userName, password);
carrierDataHolder.loadCarriersFromDB(conn);
carrierDataHolder.toJSONFile(new File("c:\\data\\carriers.json"));
} catch (Exception e) {
e.printStackTrace();
} finally {
  if (conn != null) {
    try {
conn.close();
} catch (SQLException sql) {
sql.printStackTrace();
}
}
}
}

You can download the class CarrierDataHolder by pressing this link. Note that this class is using XStream open source project. Therefore you will have to download XStream jar files as well (xstream and jettison).
Here is the mobile operators/carriers detection JSON file generated by using the CarrierDataHolder class.

Mobile Operator/Carrier detection using MaxMind – First Part – Database information

One important key targeting of mobile advertising networks, is the ability to target mobile campaigns for specific mobile operators/carrier. In order to it, we have to be able to detect the mobile operator of a given IP. I have been looking for quite some time (the passed 2 years) for a decent solution for mobile operator detection. The only decent solution (there are not much services like that) came from Quova. The problem with Quova, is that they don’t give you an API and some binary file (like most other geo/device targeting services). They insist you install dedicated server solution, that you have to query by HTTP, in order to get the information you want. If you have several machines, you have to start install Quova solution on each and every one of them.
Since we didn’t like Quova solution on the company that I work for (Mobile Ad Network), and we couldn’t find any other good solution, we decided on developing our own in-house solution. The solution is based on user IP and ISP and Organization data as it extracted from MaxMind geo service.
This is in general the detection processes:

• Get client IP.
• Use MaxMind to extract ISP and Organization from client IP.
• Check what is the corresponding mobile operator/carrier for the given combination of ISP and Organization.

The solution is quite simple and neat. The only problem with it, is that it is needed to be maintained on a regular basis, since Geo information is being changed all the time.
In order to be able to identify mobile operators, we first need to have two tables containing the following information:

• Mobile operators/carriers table, containing all mobile operators in the world. You can have a look at this post in order to see how I built the information in this table.
• Mobile operators and their ISP and organization. This table maps between mobile operators and their corresponding ISPs and organizations. Building this table is a hard work involving data mining, research and continuing refinement and update process. During the time of this post, the data in this table is quite good, but not full (not all countries/mobile operators are mapped).

Note for:

• The data in these tables is loaded to memory, since we need good performance.
• MaxMind updates it’s ISP and Organization data on a monthly basis.
• ISP and Organization is being changed all the time. The changes are not drastic, but they happen all the time, so data is needed to be maintained, and there is a need to keep refining the mapping between mobile operators/carriers and combinations of ISP and Organizations.

Let’s have a look at the structure of the mobile operators/carriers table. The table is called “carrier” and contains 3 columns:

• carrier_id – The id of the mobile operator/carrier. This is not some universal id. It is an arbitrary id used only by the system.
• country_code – The country code of the mobile operator/carrier.
• name – The name of the mobile operator/carrier.

The SQL script that creates this table:

CREATE TABLE `carrier` (

`carrier_id` int(10) unsigned NOT NULL AUTO_INCREMENT,

`country_code` char(2) NOT NULL,

`name` varchar(100) NOT NULL,

PRIMARY KEY (`carrier_id`)

) ENGINE=InnoDB;

This script contains both the scrip that creates “carrier” table as well as its data. It contains all the mobile operators in the world. This data has to be maintained (carriers are being added or merged from time to time), but not very often.

This is the structure of the table that maps ISPs and Organizations to mobile operators/carriers. The table is called: “carrier_mapping” and also contains 3 columns:

• carrier_id – The id of the mobile operator/carrier, for which we would to like to map a combination of ISP and Organization.
• isp – ISP corresponds to carried_id.
• org – Organization corresponds to carrier_id.

The SQL script that creates this table:

CREATE TABLE `carrier_mapping` (

`carrier_id` int(10) unsigned NOT NULL AUTO_INCREMENT,

`isp` varchar(50) NOT NULL,

`org` varchar(50) NOT NULL,

PRIMARY KEY (`carrier_id`,`isp`,`org`),

CONSTRAINT `FK_carrier_mapping_carrier_id` FOREIGN KEY (`carrier_id`) REFERENCES `carrier` (`carrier_id`)

) ENGINE=InnoDB;

This script contains both the scrip that creates “carrier_mapping” table as well as its data. It contains a decent amount of mapping of most large mobile operators/carriers in the world. Note that the data here may not be so accurate in a few months or so.