注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

PostgreSQL research

公益是一辈子的事, I'm 德哥@Digoal, Just Do it!

 
 
 

日志

 
 

geoip - Geolocation using GeoIP  

2015-04-27 10:56:22|  分类: PgSQL Develop |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |
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


[参考]

Flag Counter
  评论这张
 
阅读(1341)| 评论(0)
推荐 转载

历史上的今天

在LOFTER的更多文章

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2017