Skip to content

Instantly share code, notes, and snippets.

@karlvr
Last active April 1, 2022 01:15
Show Gist options
  • Save karlvr/8ff1900bfc9cba36468640c26ae3b2bb to your computer and use it in GitHub Desktop.
Save karlvr/8ff1900bfc9cba36468640c26ae3b2bb to your computer and use it in GitHub Desktop.
--
-- 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');
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment