通过 PLPython 的 GeoIP

库代码片段

通过 PL/Python 过程访问 GeoIP 数据库

适用于 PostgreSQL

PostgreSQL 8.4+ (?)

编写于

PL/PythonU

依赖于

PL/Python,
GeoIP Python 模块

用于访问本地 MaxMind GeoIP 数据库的简单 Python 函数。通常，GeoIP 数据库安装在/usr/share/GeoIP/，文件名为`GeoIP.dat`、`GeoIPCity.dat` 和 `GeoIPOrg.dat`。通常，geoip 包中会包含一个示例 `GeoIP.dat`，它是免费的。

GeoIP 国家数据库

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)
$$;

示例

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 城市数据库

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'])
$$;

示例

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 机构数据库

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')
$$;

示例

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

另请参阅

• GeoIP 索引