How find my IP address in table of blocks from GeoLite2

Question

In PostgreSQL DB I have table blocks, which is import from GeoLite2-City-Blocks.csv with this structure:

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

to store IP address is used CIDR data type, BUT in GeoLite2-City-Blocks.csv there is only START_IP_ADDRESS and MASK_LENGTH.

data example from this table:

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

how can I select row, which contains my IP address for example 87.197.148.121 ? it is neccessary calculate END_IP_ADDRESS to separate column ?

Answer 1

You will want to use the contains operator for inet/cidr:

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

There are a couple issues. First, the data representation has split the network and the mask into different fields, so this query will be very inefficient. These two fields could be trivially combined like:

alter table blocks add column net_ip inet;

Then there would be a one time data migration:

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

Then the query above would be more easily understood :

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

However, I still don't think this query will work, because the net_ip data is expressed in ipv6, and the comparison is in ip4v. You might write a conversion function, or:

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

This is not optimal at all. net_ip is not indexed. To do that, you will need ip4r extension for postgres.