GeoIP via PLPython

Library Snippets

Accessing the GeoIP database via a PL/Python procedure

Works with PostgreSQL

PostgreSQL 8.4+ (?)

Written in

PL/PythonU

Depends on

PL/Python,
GeoIP Python module

Simple Python functions for accessing a local MaxMind GeoIP database. Usually the GeoIP databases are installed in /usr/share/GeoIP/, files named `GeoIP.dat`, `GeoIPCity.dat` and `GeoIPOrg.dat`. A sample `GeoIP.dat` is usually included with the geoip package, for free.

GeoIP Country database

CREATE TYPE geoip_country as (
    country_code  text,
    country_name  text
);

CREATE OR REPLACE FUNCTION
geoip_country_by_addr(addr inet) RETURNS geoip_country
LANGUAGE plpythonu AS $$
  import GeoIP
  gi = GeoIP.open('/usr/share/GeoIP/GeoIP.dat', GeoIP.GEOIP_STANDARD)
  code = gi.country_code_by_addr(addr)
  name = gi.country_name_by_addr(addr).decode('iso8859-1')
  return (code, name)
$$;

Example:

db=> select * from geoip_country_by_addr('200.46.204.71');
 country_code | country_name 
--------------+--------------
 PA           | Panama

db=> select (geoip_country_by_addr('200.46.204.71')).country_name;
 country_name 
--------------
 Panama

GeoIP City database

CREATE TYPE geoip_record as (
    country_code  text,
    country_code3 text,
    country_name  text,
    region        text,
    region_name   text,
    city          text,
    postal_code   text,
    latitude      real,
    longitude     real,
    metro_code    int,
    area_code     int,
    dma_code      int,
    time_zone     text
);

CREATE OR REPLACE FUNCTION
geoip_record_by_addr(addr inet) RETURNS geoip_record
LANGUAGE plpythonu AS $$
  import GeoIP
  gi = GeoIP.open('/usr/share/GeoIP/GeoIPCity.dat', GeoIP.GEOIP_STANDARD)

  rec = gi.record_by_addr(addr)
  if not rec:
    return None

  return (
    rec['country_code'],
    rec['country_code3'],
    rec['country_name'].decode('iso8859-1'),
    rec['region'],
    rec['region_name'].decode('iso8859-1') if rec['region_name'] else None,
    rec['city'].decode('iso8859-1') if rec['city'] else None,
    rec['postal_code'],
    rec['latitude'],
    rec['longitude'],
    rec['metro_code'],
    rec['area_code'],
    rec['dma_code'],
    rec['time_zone'])
$$;

Example:

db=> \x
db=> select * from geoip_record_by_addr('207.173.203.187');
-[ RECORD 1 ]-+--------------------
country_code  | US
country_code3 | USA
country_name  | United States
region        | OR
region_name   | Oregon
city          | Portland
postal_code   | 
latitude      | 45.5183982849
longtitude    | -122.655403137
metro_code    | 820
area_code     | 503
dma_code      | 820
time_zone     | America/Los_Angeles

GeoIP Organization database

CREATE OR REPLACE FUNCTION
geoip_org_by_addr(addr inet) RETURNS text
LANGUAGE plpythonu AS $$
  import GeoIP
  gi = GeoIP.open('/usr/share/GeoIP/GeoIPOrg.dat', GeoIP.GEOIP_STANDARD)
  org = gi.org_by_addr(addr)
  if org:
    return org.decode('iso8859-1')
$$;

Example

db=> SELECT geoip_org_by_addr('189.61.111.0');
        geoip_org_by_addr         
----------------------------------
 NET Serviços de Comunicação S.A.

See also

• GeoIP index