geoip - Geolocation using GeoIP

背景

geoip是使用IP地址查询地理位置的一个插件，提供了以下几个查询函数。

geoip_country_code(inet) - returns country code (2 chars)  
geoip_country(inet) - returns all country info (code, name, ...)  
geoip_city_location(inet) - returns just location ID (INT)  
geoip_city(inet) - returns all the city info (GPS, ZIP code, ...)  
geoip_asn(inet) - retusn ASN name and IP range  

这个插件需要用到IP地址库，地址库可以到 www.maxmind.com下载。

安装

wget http://api.pgxn.org/dist/geoip/0.2.3/geoip-0.2.3.zip  
unzip geoip-0.2.3.zip  
export PATH=/opt/pgsql/bin:$PATH  
cd geoip-0.2.3  
gmake clean; gmake; gmake install  
psql  
=# create extension geoip;  

导入地址库：

wget http://download.maxmind.com/download/geoip/database/asnum/GeoIPASNum2.zip  
wget http://geolite.maxmind.com/download/geoip/database/GeoIPCountryCSV.zip  
wget http://geolite.maxmind.com/download/geoip/database/GeoLiteCity_CSV/GeoLiteCity-latest.zip  
unzip ...  
$ sed 's/^\("[^"]*","[^"]*",\)"[^"]*","[^"]*",\("[^"]*","[^"]*"\)/\1\2/' GeoIPCountryWhois.csv > countries.csv  
$ tail -$((`wc -l GeoLiteCity-Location.csv | awk '{print $1}'`-2)) GeoLiteCity-Location.csv > locations.csv  
$ cd GeoLiteCity_20150407  
$ tail -$((`wc -l GeoLiteCity-Blocks.csv | awk '{print $1}'`-2)) GeoLiteCity-Blocks.csv > blocks.csv  
$ tail -$((`wc -l GeoLiteCity-Location.csv | awk '{print $1}'`-2)) GeoLiteCity-Location.csv > locations.csv  
  
postgres@db-172-16-3-150-> psql  
psql (9.4.1)  
Type "help" for help.  
postgres=# COPY geoip_country FROM '/home/postgres/countries.csv' WITH csv DELIMITER ',' NULL '' QUOTE '"' ENCODING 'ISO-8859-2';  
COPY 104679  
postgres=# CREATE TEMPORARY TABLE geoip_city_block_tmp (  
postgres(#     begin_ip    BIGINT      NOT NULL,  
postgres(#     end_ip      BIGINT      NOT NULL,  
postgres(#     loc_id      INTEGER     NOT NULL  
postgres(# );  
CREATE TABLE  
postgres=# CREATE TEMPORARY TABLE geoip_asn_tmp (  
postgres(#     begin_ip    BIGINT      NOT NULL,  
postgres(#     end_ip      BIGINT      NOT NULL,  
postgres(#     name        TEXT        NOT NULL  
postgres(# );  
CREATE TABLE  
postgres=# COPY geoip_city_block_tmp FROM '/home/postgres/GeoLiteCity_20150407/blocks.csv' WITH csv DELIMITER ',' NULL '' QUOTE '"' ENCODING 'ISO-8859-2';  
COPY 2018008  
postgres=# COPY geoip_city_location FROM '/home/postgres/GeoLiteCity_20150407/locations.csv' WITH csv DELIMITER ',' NULL '' QUOTE '"' ENCODING 'ISO-8859-2';  
COPY 658951  
postgres=# COPY geoip_asn_tmp FROM '/home/postgres/GeoIPASNum2.csv' WITH csv DELIMITER ',' NULL '' QUOTE '"' ENCODING 'ISO-8859-2';  
COPY 224846  
postgres=# INSERT INTO geoip_city_block  
postgres-#      SELECT geoip_bigint_to_inet(begin_ip),  
postgres-#             geoip_bigint_to_inet(end_ip), loc_id  
postgres-#        FROM geoip_city_block_tmp;  
INSERT 0 2018008  
postgres=# INSERT INTO geoip_asn  
postgres-#      SELECT geoip_bigint_to_inet(begin_ip),  
postgres-#             geoip_bigint_to_inet(end_ip), name  
postgres-#        FROM geoip_asn_tmp;  
INSERT 0 224846  

测试：

postgres=# SELECT * FROM geoip_city('78.45.133.255'::inet);  
 loc_id | country | region | city  | postal_code | latitude | longitude | metro_code | area_code   
--------+---------+--------+-------+-------------+----------+-----------+------------+-----------  
  54219 | CZ      | 78     | Ceska |             |  49.2814 |   16.5648 |            |            
(1 row)  
postgres=# SELECT * FROM geoip_city('202.101.172.35'::inet);  
 loc_id | country | region |   city   | postal_code | latitude | longitude | metro_code | area_code   
--------+---------+--------+----------+-------------+----------+-----------+------------+-----------  
  68433 | CN      | 02     | Hangzhou |             |  30.2936 |  120.1614 |            |            
(1 row)  
postgres=# SELECT * FROM geoip_asn('202.101.172.37'::inet);  
   begin_ip   |     end_ip     |      name         
--------------+----------------+-----------------  
 202.101.64.0 | 202.102.51.255 | AS4134 Chinanet  
(1 row)  

性能：

postgres=# select count(*) from (SELECT geoip_asn('202.101.172.37'::inet) from generate_series(1,1000000)) t;  
  count    
---------  
 1000000  
(1 row)  
Time: 57561.637 ms  

单次查询约0.057毫秒。

postgres=# select count(*) from (SELECT geoip_city('202.101.172.37'::inet) from generate_series(1,100000)) t;  
 count    
--------  
 100000  
(1 row)  
Time: 10020.797 ms  

单次查询约0.1毫秒。

postgres=# select count(*) from (SELECT geoip_country('202.101.172.37'::inet) from generate_series(1,100000)) t;  
 count    
--------  
 100000  
(1 row)  
Time: 2977.582 ms  

单次查询约0.03毫秒。

其他：

postgres=# select count(*) from (SELECT geoip_city_location('202.101.172.37'::inet) from generate_series(1,100000)) t;  
 count    
--------  
 100000  
(1 row)  
Time: 8339.120 ms  
postgres=# select count(*) from (SELECT geoip_country_code('202.101.172.37'::inet) from generate_series(1,100000)) t;  
 count    
--------  
 100000  
(1 row)  
Time: 2931.577 ms  

参考

1. http://pgxn.org/dist/geoip/0.2.3/

2. http://dev.maxmind.com/geoip/legacy/geolite/

digoal’s 大量PostgreSQL文章入口