text: ## geoip - Geolocation using GeoIP  
                           
  ### 作者        
  digoal        
          
  ### 日期         
  2015-04-27        
            
  ### 标签        
  PostgreSQL , geoIP , IP地址转经纬度    
                      
  ----                    
                       
  ## 背景        
  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/  
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
  #### [PostgreSQL 许愿链接](https://github.com/digoal/blog/issues/76 "269ac3d1c492e938c0191101c7238216")
  您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。[开不开森](https://github.com/digoal/blog/issues/76 "269ac3d1c492e938c0191101c7238216").  
    
    
  #### [9.9元购买3个月阿里云RDS PostgreSQL实例](https://www.aliyun.com/database/postgresqlactivity "57258f76c37864c6e6d23383d05714ea")
    
    
  #### [PostgreSQL 解决方案集合](https://yq.aliyun.com/topic/118 "40cff096e9ed7122c512b35d8561d9c8")
    
    
  #### [德哥 / digoal's github - 公益是一辈子的事.](https://github.com/digoal/blog/blob/master/README.md "22709685feb7cab07d30f30387f0a9ae")
    
    
  ![digoal's wechat](../pic/digoal_weixin.jpg "f7ad92eeba24523fd47a6e1a0e691b59")
    
  