Geek asks and answers

GeoLite2 CSV query in SQL

Does anyone know how to look up IP4 address from MaxMind GeoLite2 CSV using SQL?

I have been using MaxMind free GeoIP data for many years and would like to update their GeoLite2 data. I have block and location data loaded into MySQL tables, but I'm not sure how to determine the address range that the IP4 address falls into. The old format had a start / end number for each block; It seems that the new format has an initial number.

I have already been hunting for documents from MaxMind and Googled developers, but I can not find any information on how to request a new format. I am sure this is obvious, and edit this post if I find out in the interim.

I think, thanks, that I will need to find a record of the first blocks that is greater than or equal to the IP4 address, and LIMIT 1, perhaps.

I use this data both for viewing web applications, and for direct query in SQL to generate reports; therefore, I usually need to make sure that I can implement a search in both Perl code and pure SQL.

I am updating because I see some funny results for Japanese visitors coming from France according to old data.

many thanks

+3
source share
2 answers

The address format used in CSV Geolite2 includes the beginning of the IP address of the block, followed by the length of the prefix #, which can be converted to the end of the IP address of the block.

( , Maxmind "Network_Mask_Length" "Prefix_Length", IPv6, .)

Geolite2 CSV :

network_start_ip,network_mask_length,geoname_id,registered_country_geoname_id,re
presented_country_geoname_id,postal_code,latitude,longitude,is_anonymous_proxy,i
s_satellite_provider

: ( Geolite2-Country-Blocks.csv)

::ffff:81.248.136.0,120,3578476,3017382,,,,,0,0

, IPv4-, ?

First IP address: 81.248.136.0
Prefix_Length/Network_mask_Length: 120
Last IP address: 81.248.136.255

URL- IP-, _Length:

http://www.gestioip.net/cgi-bin/subnet_calculator.cgi

__

+2

Prefix_Length:

( , , ...; -)

http://www.gestioip.net/cgi-bin/subnet_calculator.cgi

IPv6, PL. " " IP-.

IP- Geolite2, , Prefix_Length/address:

Prefix  #addresses
Length

117 2048
118 1024
119 512
120 256
121 128
122 64
123 32
124 16
125 8
126 4
127 2
128 1

, Geolite2 IPv4/IPv6, "IPv4- IPv6-".

"" 96 IPv6, 32 IPv4. ,:: ffff: 192.0.2.128 IPv4 192.0.2.128

() , :

http://en.wikipedia.org/wiki/IPv6

__

+1

More articles:


All Articles