1. 概述

IP定位库有多家厂商提供，这里以业内流行的Maxmind为例，介绍使用方法。
https://dev.maxmind.com

MaxMind

2. Download IP Data

MaxMind提供多种level的免费IP库，这里以City级别为例。

Country level

wget "https://download.maxmind.com/app/geoip_download?edition_id=GeoLite2-Country&license_key=gZES6ucsOZscurZL&suffix=tar.gz" -O GeoLite2-Country.mmdb.tar.gz

City level csv

wget "https://download.maxmind.com/app/geoip_download?edition_id=GeoLite2-City-CSV&license_key=gZES6ucsOZscurZL&suffix=zip" -O GeoLite2-City.mmdb.zip
unzip GeoLite2-City.mmdb.zip

$ ll -h
total 357M
-rw-rw-r-- 1 kevin kevin   55 Apr 19 22:34 COPYRIGHT.txt
-rw-rw-r-- 1 kevin kevin 215M Apr 19 22:33 GeoLite2-City-Blocks-IPv4.csv
-rw-rw-r-- 1 kevin kevin  65M Apr 19 22:33 GeoLite2-City-Blocks-IPv6.csv
-rw-rw-r-- 1 kevin kevin 8.7M Apr 19 22:34 GeoLite2-City-Locations-de.csv
-rw-rw-r-- 1 kevin kevin  11M Apr 19 22:33 GeoLite2-City-Locations-en.csv
-rw-rw-r-- 1 kevin kevin 8.6M Apr 19 22:34 GeoLite2-City-Locations-es.csv
-rw-rw-r-- 1 kevin kevin 9.2M Apr 19 22:33 GeoLite2-City-Locations-fr.csv
-rw-rw-r-- 1 kevin kevin  12M Apr 19 22:33 GeoLite2-City-Locations-ja.csv
-rw-rw-r-- 1 kevin kevin 8.7M Apr 19 22:33 GeoLite2-City-Locations-pt-BR.csv
-rw-rw-r-- 1 kevin kevin  12M Apr 19 22:33 GeoLite2-City-Locations-ru.csv
-rw-rw-r-- 1 kevin kevin 9.0M Apr 19 22:33 GeoLite2-City-Locations-zh-CN.csv
-rw-rw-r-- 1 kevin kevin  398 Apr 19 22:34 LICENSE.txt

3. 创建Pg数据表

这里在官方建议create sql的基础上，添加geoip2_network.id字段，主要原因是sqlalchemy需要每张表有一个primary key。

create table geoip2_network (
  id int not null primary key,
  network cidr not null,
  geoname_id int,
  registered_country_geoname_id int,
  represented_country_geoname_id int,
  is_anonymous_proxy bool,
  is_satellite_provider bool,
  postal_code text,
  latitude numeric,
  longitude numeric,
  accuracy_radius int
);
 
create index on geoip2_network using gist (network inet_ops);
 
create table geoip2_location (
  geoname_id int not null,
  locale_code text not null,
  continent_code text not null,
  continent_name text not null,
  country_iso_code text,
  country_name text,
  subdivision_1_iso_code text,
  subdivision_1_name text,
  subdivision_2_iso_code text,
  subdivision_2_name text,
  city_name text,
  metro_code int,
  time_zone text,
  is_in_european_union bool not null,
  primary key (geoname_id, locale_code)
);

4. 导入到数据库

数据库密码：xxxeeeaaa
注意：csv文件的位置需要修改，否则会报错

psql -U kevin -d passport -h pgm-xxx.pg.rds.aliyuncs.com -p 5432 \
-c "\copy geoip2_network(
  network, geoname_id, registered_country_geoname_id, represented_country_geoname_id,
  is_anonymous_proxy, is_satellite_provider, postal_code, latitude, longitude, accuracy_radius
) FROM '/home/kevin/temp/geoip/GeoLite2-City-CSV_20220419/GeoLite2-City-Blocks-IPv4.csv' with (format csv,header);"

psql -U kevin -d passport -h pgm-xxx.pg.rds.aliyuncs.com -p 5432 \
-c "\copy geoip2_network(
  network, geoname_id, registered_country_geoname_id, represented_country_geoname_id,
  is_anonymous_proxy, is_satellite_provider, postal_code, latitude, longitude, accuracy_radius
) from '/home/kevin/temp/geoip/GeoLite2-City-CSV_20220419/GeoLite2-City-Blocks-IPv6.csv' with (format csv, header);"

psql -U kevin -d passport -h pgm-xxx.pg.rds.aliyuncs.com -p 5432 \
-c "\copy geoip2_location(
  geoname_id, locale_code, continent_code, continent_name, country_iso_code, country_name,
  subdivision_1_iso_code, subdivision_1_name, subdivision_2_iso_code, subdivision_2_name,
  city_name, metro_code, time_zone, is_in_european_union
) from '/home/kevin/temp/geoip/GeoLite2-City-CSV_20220419/GeoLite2-City-Locations-en.csv' with (format csv, header);"

psql -U kevin -d passport -h pgm-xxx.pg.rds.aliyuncs.com -p 5432 \
-c "\copy geoip2_location(
  geoname_id, locale_code, continent_code, continent_name, country_iso_code, country_name,
  subdivision_1_iso_code, subdivision_1_name, subdivision_2_iso_code, subdivision_2_name,
  city_name, metro_code, time_zone, is_in_european_union
) from '/home/kevin/temp/geoip/GeoLite2-City-CSV_20220419/GeoLite2-City-Locations-zh-CN.csv' with (format csv, header);"

5. 测试

select latitude, longitude, accuracy_radius,
       location.continent_name as location_continent_name,
       location.country_name as location_country_name,
       location.subdivision_1_name as location_subdivision_1_name,
       location.city_name as location_city_name,
       registered_country.continent_name as registered_country_continent_name,
       registered_country.country_name as registered_country_country_name,
       represented_country.continent_name as represented_country_continent_name,
       represented_country.country_name as represented_country_country_name
from geoip2_network net
left join geoip2_location location on (
  net.geoname_id = location.geoname_id
  and location.locale_code = 'en'
)
left join geoip2_location registered_country on (
  net.registered_country_geoname_id = registered_country.geoname_id
  and registered_country.locale_code = 'en'
)
left join geoip2_location represented_country on (
  net.represented_country_geoname_id = represented_country.geoname_id
  and represented_country.locale_code = 'en'
)
where network >> '221.121.10.101';

6. 参考文档

• Importing GeoIP2 and GeoLite2 databases to PostgreSQL
  https://dev.maxmind.com/geoip/importing-databases/postgresql