Classless Inter-Domain Routing (CIDR) is a method for IP address allocation and routing. It was developed to slow down the depletion of IPv4 addresses. CIDR notation compactly represents an IP address and its network mask, typically consisting of an IP address followed by a slash and a decimal number indicating the count of network prefix bits.

PostgreSQL offers native support for CIDR data, unlike some other SQL databases, thus avoiding potential compatibility issues. It accommodates both IPv4 and IPv6 networks, occupying 7 or 19 bytes. This native support eliminates the need for users to convert CIDR notation to other data types, such as varchar in MySQL, allowing for direct storage.

IP2Location recently launched a new download option for its geolocation databases, now based on CIDR. This enhancement offers significant advantages for users and site administrators across diverse applications. For instance, site administrators can now effortlessly pinpoint specific CIDRs to block access from. This tutorial will guide you through the process of utilizing this new CIDR data file with PostgreSQL.

Prerequisites

To begin, please ensure that you have acquired the CIDR data file. This file is available for download from your dashboard following the completion of your data purchase. This tutorial will utilize PostgreSQL as an example. PostgreSQL comes pre-installed on many Linux distributions. For other operating systems, users can refer to the official documentation at https://www.postgresql.org/download/ to get started.

Database Setup

To begin, a table must be created to load the DB26 CIDR data file. The key distinction between this and a standard data file is that the CIDR column replaces both the ip_from and ip_to columns. The complete table schema for its creation is provided below:

CREATE TABLE ip2location_db26_cidr (
    cidr CIDR NOT NULL,
    country_code CHAR(2) NOT NULL,
    country_name VARCHAR(64) NOT NULL,
    region_name VARCHAR(128) NOT NULL,
    city_name VARCHAR(128) NOT NULL,
    latitude DOUBLE PRECISION DEFAULT NULL,
    longitude DOUBLE PRECISION DEFAULT NULL,
    zip_code VARCHAR(30) DEFAULT NULL,
    time_zone VARCHAR(8) DEFAULT NULL,
    isp VARCHAR(255) NOT NULL,
    domain VARCHAR(128) NOT NULL,
    net_speed VARCHAR(8) NOT NULL,
    idd_code VARCHAR(5) NOT NULL,
    area_code VARCHAR(30) NOT NULL,
    weather_station_code VARCHAR(10) NOT NULL,
    weather_station_name VARCHAR(128) NOT NULL,
    mcc VARCHAR(128) DEFAULT NULL,
    mnc VARCHAR(128) DEFAULT NULL,
    mobile_brand VARCHAR(128) DEFAULT NULL,
    elevation INTEGER NOT NULL,
    usage_type VARCHAR(11) NOT NULL,
    address_type CHAR(1) NOT NULL,
    category VARCHAR(10) NOT NULL,
    district VARCHAR(128) NOT NULL,
    asn VARCHAR(10) NOT NULL,
    as_name VARCHAR(256) NOT NULL
);

Data Import

Make sure you’ve got the CIDR data file ready. This tutorial used DB26 as an example, but feel free to use any other CIDR data file—just adjust the table schema from the last section.

PostgreSQL’s \\copy command is used to load data from a CSV file into a target table. To perform this, use the following command:

\copy ip2location_db26_cidr FROM '/path/to/db26.CIDR.CSV' WITH (FORMAT csv, HEADER false);

Please replace ‘/path/to/db26.CIDR.CSV’ with the actual file path of your CIDR data. Upon the execution, you shall see the output like this: COPY 3124692

To optimize the query process, you must create indexes for the table. Execute the following queries to generate the necessary indexes:

CREATE INDEX idx_cidr ON ip2location_db26_cidr (cidr);
CREATE INDEX idx_isp ON ip2location_db26_cidr (isp);

IP Lookups

You can easily look up records from the table using a SELECT statement. For instance, to find the record for the IP address 8.8.8.8, execute the following query: SELECT * FROM ip2location_db26_cidr WHERE cidr << '8.8.8.8'::inet;. Remember to use single quotes instead of double quotes around the IP address.

Applications

The IP2Location CIDR data file offers various applications. Server administrators can leverage it to manage web page access based on geographical regions and optimize user experience by directing requests to the closest edge server, identified through CIDR.

Furthermore, the CIDR data file enhances security by allowing administrators to quickly determine the physical location of incoming attacks using IP address geolocation information, enabling timely server protection.

Finally, the CIDR data file is valuable for enriching geolocation information. Site administrators can integrate CIDR geolocation details, such as country and ISP, into log files. This facilitates post-analysis, helping them identify areas where their site is most popular.

Conclusion

In summary, the IP2Location CIDR data file offers significant benefits for server administrators and online content providers. It enhances security by allowing administrators to identify the geographical origin of connections, enabling them to block malicious attacks like DDoS. Additionally, it optimizes content delivery and user experience by facilitating geo-targeted content, leading to localized advertisements, region-specific product offerings, compliance with regulations, and language-appropriate websites, ultimately increasing relevance, conversion rates, and overall user satisfaction for businesses.