Hide
Easily highlight source code for your blog with our Syntax Highlighter. Join Siafoo Now or Learn More

PostgreSQL script for loading GeoIP data Atom Feed 0

In Brief This is an SQL script for loading the MaxMind GeoIP data in CSV format into a PostgreSQL database.... more
# 's
 1--DROP TABLE locations;
2--DROP TABLE blocks;
3
4-- Table DDL
5CREATE TABLE locations
6(
7 id bigint NOT NULL,
8 country character(2) NOT NULL,
9 region character(2),
10 city character varying(75),
11 postal_code character varying(15),
12 latitude numeric(6,4) NOT NULL,
13 longitude numeric(7,4),
14 metro_code integer,
15 area_code integer,
16 CONSTRAINT locations_pkey PRIMARY KEY (id)
17);
18
19CREATE TABLE blocks
20(
21 start_ip bigint NOT NULL,
22 end_ip bigint NOT NULL,
23 location_id bigint NOT NULL,
24 CONSTRAINT blocks_pkey PRIMARY KEY (start_ip, end_ip)
25);
26
27-- Data is in ISO-8859-15 format
28SET client_encoding to "ISO-8859-15";
29
30-- The locations table
31COPY locations (id, country, region, city, postal_code, latitude, longitude, metro_code, area_code)
32FROM '/tmp/GeoLiteCity-Location.csv' WITH CSV HEADER;
33
34-- Then the blocks table
35COPY blocks FROM '/tmp/GeoLiteCity-Blocks.csv' WITH CSV HEADER;
36
37-- Uncomment all of this if you have IP4r
38-- Add the range column
39--ALTER TABLE blocks ADD COLUMN ip_range ip4r;
40--ALTER TABLE blocks ALTER COLUMN ip_range SET STORAGE PLAIN;
41-- Perform Conversion
42-- UPDATE blocks SET ip_range = ip4r(start_ip::ip4, end_ip::ip4)
43-- Drop Unneeded Columns
44--ALTER TABLE blocks DROP COLUMN start_ip;
45--ALTER TABLE blocks DROP COLUMN end_ip;
46
47-- The Indexes
48-- Comment this out if you have IP4r
49CREATE INDEX ix_start_end_ip ON blocks
50 USING btree (start_ip, end_ip) WITH (FILLFACTOR=100);
51CREATE INDEX ix_end_start_ip ON blocks
52 USING btree (end_ip, start_ip) WITH (FILLFACTOR=100);
53
54-- Uncomment if you have IP4
55-- CREATE INDEX ix_ip_range ON blocks
56-- USING gist (ip_range) WITH (FILLFACTOR=100);
57
58-- Utility Functions
59-- Comment this out if you have IP4r
60CREATE OR REPLACE FUNCTION inet_to_block(inet) RETURNS bigint AS $$
61 SELECT $1 - inet '0.0.0.0'
62$$ LANGUAGE SQL;
63
64-- Uncomment this if you have IP4r
65--CREATE OR REPLACE FUNCTION get_location(inet) RETURNS bigint AS $$
66-- SELECT location_id FROM %s.blocks
67-- WHERE ip_range >>= ip4($1)
68--$$ LANGUAGE SQL;

This is an SQL script for loading the MaxMind GeoIP data in CSV format into a PostgreSQL database.

If you have Python it is recommended that you use the Python Utility for loading GeoIP data into a PostgreSQL database

Also I suggest you take a look at Loading the GeoIP City database into PostgreSQL in order to better understand how to use this script.

To use the script:
  1. Create database and login role (user)
  2. Unzip the GeoIP data
  3. Move/Copy the CSV files to /tmp (or edit the script and provide the correct location
  4. Edit each file and remove the first line (the Copyright note)
  5. (optional but recommended) Install IP4r (http://pgfoundry.org/projects/ip4r/)
  6. If you are using IP4r or if otherwise necessary, customize the script
  7. Run the command: psql -U USER -d DATABASE < pg_geoip_load.sql (where USER and DATABASE are the appropriate settings for your database server)
  8. Delete CSV files

Warning

Make sure that the login role USER is a super-user or you will not be able to use COPY with a file input and as such this script will not work