Poorman's geoIP lookups for kdb+/q using Maxmind's (CSV) GeoIP database

maxmind.q

/ Poorman's geoIP lookups for kdb+/q using Maxmind's (CSV) GeoIP database
/ Copyright (C) 2023, coreMem Limited <info@coremem.com>
/ SPDX-License-Identifier: Unlicense
/ Website: https://gist.github.com/jimdigriz/2dd4b249d2e3f24d8838f6466674f945
/ Usage:
/ q)/ unzip GeoLite2-Country-CSV_20230117.zip somewhere
/ q)\l maxmind.q
/ q)/ load the GeoIP data in
/ q)maxmind"GeoLite2-Country-CSV_20230117"
/ q)/ load in your dataset
/ q)\l /db
/ q)/ assume that your have a table t with an integer representation
/ q)/ of an IPv4 address (ie. "I"$"192.0.2.1") in src column
/ q)meta t
/ c    | t f a
/ -----| -----
/ date | d
/ src  | i
/ user | s
/ ..
/ q)select ip:{"."sv string"i"$0x0 vs x}each src, country_iso_code:geoip[`country_iso_code;src] from t where date = .z.d
/ q)select distinct user, country_iso_code:geoip[`country_iso_code;src] from t where date in .z.d-1+til 10, geoip[`country_iso_code;src]in`RU`SY`IR`KP`CU

maxmind:{
 maxmind_geoname::`geoname_id xkey`geoname_id xasc("ISSSSSB";enlist",")0:hsym`$x,"/GeoLite2-Country-Locations-en.csv";
 maxmind_ipv4::("*IIIBB";enlist",")0:hsym`$x,"/GeoLite2-Country-Blocks-IPv4.csv";
 `network_end xasc update network_end:{x:"I"$"/"vs x;"i"$x[0]+("i"$2 xexp 32-x[1])-1}each network from `maxmind_ipv4;
 update geoname:`maxmind_geoname$maxmind_ipv4`geoname_id from`maxmind_ipv4;}

geoip:{(maxmind_geoname each(maxmind_ipv4 binr[maxmind_ipv4`network_end;y])`geoname_id)x}