-- | |
-- 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