Read MaxMind databases (GeoLite, GeoIP)
Maintainer(s):
marselester
Installing and Loading
INSTALL maxmind FROM community;
LOAD maxmind;
Example
SELECT geolite_city('./GeoLite2-City.mmdb', '1.0.64.0', '');
About maxmind
Read MaxMind databases (GeoLite, GeoIP) using table and scalar functions.
Scan all IP network blocks with read_mmdb():
SELECT network, city.names.en
FROM read_mmdb('./GeoLite2-City.mmdb')
WHERE city.names.en IS NOT NULL
LIMIT 1;
┌─────────────┬───────────┐
│ network │ en │
├─────────────┼───────────┤
│ 1.0.64.0/20 │ Hiroshima │
└─────────────┴───────────┘
Limit the scan to a subnet with the network parameter:
SELECT network, city.names.en
FROM read_mmdb('./GeoLite2-City.mmdb', network='1.0.0.0/8')
WHERE city.names.en IS NOT NULL
LIMIT 1;
┌─────────────┬───────────┐
│ network │ en │
├─────────────┼───────────┤
│ 1.0.64.0/20 │ Hiroshima │
└─────────────┴───────────┘
Look up a record by IP address with scalar functions:
SELECT geolite_city('./GeoLite2-City.mmdb', '1.0.64.0', '').city.names.en AS en;
┌───────────┐
│ en │
├───────────┤
│ Hiroshima │
└───────────┘
The third parameter filters which fields to decode for faster lookups:
SELECT geolite_city('./GeoLite2-City.mmdb', '1.0.64.0', 'city').city.names.en AS en;
┌───────────┐
│ en │
├───────────┤
│ Hiroshima │
└───────────┘
Added Functions
| function_name | function_type | description | comment | examples |
|---|---|---|---|---|
| extension_version | scalar | NULL | NULL | |
| geoip_anonymous_ip | scalar | NULL | NULL | |
| geoip_anonymous_plus | scalar | NULL | NULL | |
| geoip_city | scalar | NULL | NULL | |
| geoip_connection_type | scalar | NULL | NULL | |
| geoip_country | scalar | NULL | NULL | |
| geoip_densityincome | scalar | NULL | NULL | |
| geoip_domain | scalar | NULL | NULL | |
| geoip_enterprise | scalar | NULL | NULL | |
| geoip_ip_risk | scalar | NULL | NULL | |
| geoip_isp | scalar | NULL | NULL | |
| geoip_static_ip_score | scalar | NULL | NULL | |
| geoip_user_count | scalar | NULL | NULL | |
| geolite_asn | scalar | NULL | NULL | |
| geolite_city | scalar | NULL | NULL | |
| geolite_country | scalar | NULL | NULL | |
| read_mmdb | table | NULL | NULL |