Как найти мой IP адрес в таблице блоков от GeoLite2

В БД PostgreSQL у меня есть таблица blocks, которая импортируется из GeoLite2-City-Blocks.csv со следующей структурой:

network_start_ip cidr NOT NULL,
network_mask_length integer NOT NULL,
geoname_id bigint,
registered_country_geoname_id bigint,
represented_country_geoname_id bigint,
postal_code character(50),
latitude DOUBLE PRECISION,
longitude DOUBLE PRECISION,
is_anonymous_proxy boolean,
is_satellite_provider boolean

для хранения IP-адреса используется тип данных CIDR, НО в GeoLite2-City-Blocks.csv есть только START_IP_ADDRESS и MASK_LENGTH.

пример данных из этой таблицы:

::ffff:1.0.0.0/128   120    2077456     2077456  ....

как я могу выбрать строку, содержащую мой IP-адрес, например 87.197.148.121? необходимо вычислить END_IP_ADDRESS для отдельного столбца?


postgresql cidr geoip maxmind
person termix    schedule 17.06.2014    source источник
comment
я только что нашел этот поток в stackoverflow: stackoverflow.com/questions/15384179/ кажется, решает этот вопрос.   -  person Greg    schedule 17.06.2014

Ответы (1)


arrow_upward
0
arrow_downward

Вы захотите использовать оператор contains для inet/cidr:

select * from blocks
where set_masklen(network_start_ip,network_mask_length) >>= '::ffff:87.197.148.121'::inet;

Есть пара проблем. Во-первых, представление данных разделило сеть и маску на разные поля, поэтому этот запрос будет очень неэффективным. Эти два поля можно тривиально объединить, например:

alter table blocks add column net_ip inet;

Тогда будет однократная миграция данных:

update blocks set net_ip = set_masklen(network_start_ip,network_mask_length);

Тогда запрос выше будет более понятным:

select * from blocks where net_ip >>= '87.197.148.121'::inet;

Однако я все равно не думаю, что этот запрос будет работать, потому что данные net_ip выражены в ipv6, а сравнение в ip4v. Вы можете написать функцию преобразования или:

select * from blocks where net_ip >>= concat('::ffff:', '87.197.148.121')::inet;

Это вообще не оптимально. net_ip не индексируется. Для этого вам понадобится расширение ip4r для postgres.

person Greg    schedule 17.06.2014