GeoDB

База данных в формате SQLite3 для определения геолокации по IPv4

Скачать GeoDB.sqlite

Примеры запросов к БД

Пример 1

with ip2long(ip, long) as (
	select '62.212.64.19', 0
	union all
	select
		ip,
		cast(replace(ip, ltrim(ip, '1234567890'), '') * 16777216
			+ rtrim(rtrim(rtrim(ltrim(ltrim(ip, '1234567890'), '.'), '1234567890'), '.'), '1234567890') * 65536
			+ ltrim(ltrim(rtrim(ltrim(ltrim(ip, '1234567890'), '.'), '1234567890'), '1234567890'), '.') * 256
			+ replace(ip, rtrim(ip, '1234567890'), '') as integer
		) long
	from ip2long where long = 0
)
select
	n.ip,
	k.iso      "continent_code",
	k.name_en  "continent_name_en",
	k.name_ru  "continent_name_ru",
	s.iso      "country_iso",
	s.name_en  "country_name_en",
	s.name_ru  "country_name_ru",
	r.iso      "region_code",
	r.name_en  "region_name_en",
	r.name_ru  "region_name_ru",
	g.name_en  "city_name_en",
	g.name_ru  "city_name_ru",
	g.lat      "city_lat",
	g.lon      "city_lon",
	r.timezone "region_timezone"
from networks n
	left join countries  s on s.id = n.country_id
	left join continents k on k.id = s.continent_id 
	left join regions    r on r.id = n.region_id
	left join cities     g on g.id = n.city_id
where
	n.ip <= (select long from ip2long i where i.long != 0)
order by ip desc
limit 1;

Пример 2

select
	n.ip,
	k.iso      "continent_code",
	k.name_en  "continent_name_en",
	k.name_ru  "continent_name_ru",
	s.iso      "country_iso",
	s.name_en  "country_name_en",
	s.name_ru  "country_name_ru",
	r.iso      "region_code",
	r.name_en  "region_name_en",
	r.name_ru  "region_name_ru",
	g.name_en  "city_name_en",
	g.name_ru  "city_name_ru",
	g.lat      "city_lat",
	g.lon      "city_lon",
	r.timezone "region_timezone"
from networks n
	left join countries  s on s.id = n.country_id
	left join continents k on k.id = s.continent_id 
	left join regions    r on r.id = n.region_id
	left join cities     g on g.id = n.city_id
where n.ip <= (
	(substr(:ip, 1, instr(:ip, '.') - 1) * 16777216) +
	(substr(:ip, instr(:ip, '.') + 1, instr(substr(:ip, instr(:ip, '.') + 1), '.') - 1) * 65536) +
	(substr(:ip, instr(:ip, '.') + instr(substr(:ip, instr(:ip, '.') + 1), '.') + 1, instr(substr(:ip, instr(:ip, '.') + instr(substr(:ip, instr(:ip, '.') + 1), '.') + 1), '.') - 1) * 256)
)
order by n.ip desc
limit 1;