GeoIP via PLPython
From PostgreSQL wiki
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.
Contents |
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.
