postgresql-geoip.sql

--
-- GEOIP IN POSTGRESQL
--

-- We use two approaches. First using PostgreSQL inet and cidr types and indexing (PostgreSQL 9.4 and later),
-- and then using ip4r (https://github.com/RhodiumToad/ip4r).
-- The performance of ip4r indexes is significantly better than PostgreSQL's own index.
-- An operation that took 42s using ip4r took 47 minutes using PostgreSQL's cidr index.

--
-- PostgreSQL inet and cidr approach
--

create table geoip_blocks (
	network cidr,
	geoname_id bigint,
	registered_country_geoname_id bigint,
	represented_country_geoname_id bigint,
	is_anonymous_proxy bool,
	is_satellite_provider bool
);

copy geoip_blocks from '/tmp/GeoLite2-Country-Blocks-IPv4.csv' delimiter ',' csv header;
copy geoip_blocks from '/tmp/GeoLite2-Country-Blocks-IPv6.csv' delimiter ',' csv header;

create index geoip_blocks_network_idx on geoip_blocks using gist (network inet_ops);

create table geoip_locations (
	geoname_id bigint,
	locale_code varchar(2),
	continent_code varchar(2),
	continent_name varchar(255),
	country_iso_code varchar(2),
	country_name varchar(255)
);

copy geoip_locations from '/tmp/GeoLite2-Country-Locations-en.csv' delimiter ',' csv header;

create or replace function geoip_country_code(remoteaddress inet)
returns varchar(2)
language sql
as 'select country_iso_code from geoip_locations where geoname_id in (select geoname_id from geoip_blocks where network >>= remoteaddress)';

-- Now you can use the geoip_country_code function
select geoip_country_code('219.88.188.238'::inet);


--
-- IP4R approach
--
-- Install on Ubuntu:
-- apt-get install postgresql-9.5-ip4r

create extension if not exists ip4r;

create table geoip_blocks4 (
	network ip4r,
	geoname_id bigint,
	registered_country_geoname_id bigint,
	represented_country_geoname_id bigint,
	is_anonymous_proxy bool,
	is_satellite_provider bool
);

CREATE INDEX geoip_blocks4_network_idx ON geoip_blocks4 USING gist (network);

create table geoip_locations (
	geoname_id bigint,
	locale_code varchar(2),
	continent_code varchar(2),
	continent_name varchar(255),
	country_iso_code varchar(2),
	country_name varchar(255)
);

create or replace function geoip_country_code(remoteaddress ip4)
returns varchar(2)
language sql
stable
as 'select country_iso_code from geoip_locations where geoname_id in (select geoname_id from geoip_blocks4 where network >>= remoteaddress)';

create table geoip_blocks6 (
	network ip6r,
	geoname_id bigint,
	registered_country_geoname_id bigint,
	represented_country_geoname_id bigint,
	is_anonymous_proxy bool,
	is_satellite_provider bool
);

CREATE INDEX geoip_blocks6_network_idx ON geoip_blocks6 USING gist (network);

create or replace function geoip_country_code(remoteaddress ip6)
returns varchar(2)
language sql
stable
as 'select country_iso_code from geoip_locations where geoname_id in (select geoname_id from geoip_blocks6 where network >>= remoteaddress)';

create or replace function geoip_country_code(remoteaddress text)
returns varchar(2)
as
$BODY$
DECLARE
	country_code varchar(2);
BEGIN
	IF strpos(remoteaddress, ':') = 0 THEN 
		select country_iso_code into country_code from geoip_locations where geoname_id in (select geoname_id from geoip_blocks4 where network >>= remoteaddress::ip4);
	ELSE
		select country_iso_code into country_code from geoip_locations where geoname_id in (select geoname_id from geoip_blocks6 where network >>= remoteaddress::ip6);
	END IF;
	return country_code;
END
$BODY$
language plpgsql
stable;

-- Must do these copies as superuser
copy geoip_blocks4 from '/tmp/GeoLite2-Country-Blocks-IPv4.csv' delimiter ',' csv header;
copy geoip_blocks6 from '/tmp/GeoLite2-Country-Blocks-IPv6.csv' delimiter ',' csv header;
copy geoip_locations from '/tmp/GeoLite2-Country-Locations-en.csv' delimiter ',' csv header;

-- Now you can use the geoip_country_code function
select geoip_country_code('219.88.188.238');