Model / geo · All operations

Locate IP addresses

A variant of Work with geography

Look up the country, city and coordinates of IP addresses in a geolocation database.

Status

In arcform

Runs as a SQL step

arcform carries it as a sql: step today, with the inet extension.

The DuckDB step

Look up the country, city and coordinates of IP addresses in a geolocation database

SQL
INSTALL inet; LOAD inet;
-- a geolocation database is a table of address blocks (documentation ranges stand in for real ones), some inside others
CREATE TEMP TABLE blocks AS SELECT cidr::INET AS cidr, split_part(cidr, '/', 2)::INTEGER AS prefix, country, city, lat, lon FROM (VALUES
  ('203.0.113.0/24', 'AU', 'Sydney', -33.87, 151.21), ('198.51.0.0/16', 'DE', 'Frankfurt', 50.11, 8.68), ('198.51.100.0/24', 'FR', 'Paris', 48.86, 2.35),
  ('192.0.2.0/24', 'JP', 'Tokyo', 35.68, 139.69), ('2001:db8::/32', 'BR', 'Sao Paulo', -23.55, -46.63)) t(cidr, country, city, lat, lon);
CREATE TEMP TABLE visitors AS SELECT * FROM (VALUES (1, '203.0.113.7'), (2, '198.51.100.200'), (3, '198.51.7.1'), (4, '192.0.2.1'), (5, '8.8.8.8'), (6, '2001:db8::1'), (7, 'not an address'), (8, NULL)) t(id, ip);
-- the most specific block holding the address wins: 198.51.100.200 is in the /16 and the /24
CREATE TEMP TABLE located AS
SELECT v.id, v.ip, b.country, b.city, b.lat, b.lon
FROM visitors v LEFT JOIN blocks b ON try_cast(v.ip AS INET) <<= b.cidr
QUALIFY row_number() OVER (PARTITION BY v.id ORDER BY b.prefix DESC NULLS LAST) = 1;
SELECT * FROM located ORDER BY id;
SELECT CASE WHEN ((SELECT count(*) = 8 AND list(country ORDER BY id) = ['AU', 'FR', 'DE', 'JP', NULL, 'BR', NULL, NULL] AND list(city ORDER BY id) = ['Sydney', 'Paris', 'Frankfurt', 'Tokyo', NULL, 'Sao Paulo', NULL, NULL]
    AND (SELECT lat = 48.86 AND lon = 2.35 FROM located WHERE id = 2) FROM located)) THEN 'as expected' ELSE error('the eight visitors should resolve to Sydney, Paris, Frankfurt, Tokyo, nothing, Sao Paulo, nothing and nothing, taking the narrower block for the second') END AS verdict;
  • The statement builds its own data.
  • It needs the inet extension.
  • The statement checks its own result and raises an error when it is wrong: “the eight visitors should resolve to Sydney, Paris, Frankfurt, Tokyo, nothing, Sao Paulo, nothing and nothing, taking the narrower block for the second”.
  • It passed on DuckDB v2.0.0 (Cyanoptera) d54cbbe3df.

What other tools call it

Meridian's classification; an independent blind rating agreed on 95.7% of 324 rows (variant, version 3).

Apache NiFi (2)
  • GeoEnrichIPread 2026-09-27

    Look up geolocation for an IP address in a MaxMind database and add it to attributes

  • GeoEnrichIPRecordread 2026-09-27

    Record-oriented IP geolocation lookup against a MaxMind database

Pentaho Data Integration (1)
  • MaxMind GeoIP Lookupread 2026-09-27

    Look up an IPv4 address in a MaxMind database, adding geography, ISP or organisation

Talend (1)
  • tAddLocationFromIPread 2026-09-27

    Look up the country, region, city and coordinates of IP addresses in an IP location database file

Background

Internet geolocation is software that deduces the geographic position of a device connected to the internet, for example from its IP address.[1] IP geolocation databases range from country level to city or postal code level, with varying claims of accuracy, generally higher at the country level.[1] MaxMind's GeoIP and GeoLite City and Country databases give the country, subdivisions, city and postal code associated with IPv4 and IPv6 addresses.[2]

When to use it

In MaxMind's GeoIP databases, locations are often near the centre of the population, and no location should be used to identify a particular address or household.[2] MaxMind's GeoIP City blocks carry an accuracy_radius field: the radius in kilometres around the location where the IP address is likely to be.[2] Carry the accuracy radius alongside the coordinates, and treat an IP location as an area rather than a point. (Meridian's view)

If geolocation software maps the IP addresses of an entire county or territory to one location, such as its geographic centre, the people who live there can face considerable problems.[1] A farmstead northeast of Potwin, Kansas became the default site of 600 million IP addresses when MaxMind changed the geographic centre it used for the contiguous United States.[1] Proxy servers can be used to circumvent restrictions imposed by geolocation software.[1]

Online retailers and payment processors use geolocation to detect possible credit card fraud by comparing the user's location with the billing or shipping address.[1]

The method

The primary sources of IP address data are the regional Internet registries, which allocate IP addresses among organisations in their service regions.[1]

MaxMind's GeoIP CSV blocks files list each IPv4 or IPv6 network in Classless Inter-Domain Routing (CIDR) format, such as 2.21.92.0/29.[2][3] CIDR notation writes an IP address, a slash and a decimal number counting the consecutive leading 1 bits of the network mask.[3] In DuckDB, the inet extension's INET type stores IPv4 and IPv6 addresses and supports CIDR notation for subnet masks.[4]

Sources

  1. [1]Internet geolocation - Wikipediasecondary source
  2. [2]GeoIP and GeoLite City and Country Databases | MaxMind Developer Portaldocumentation
  3. [3]Classless Inter-Domain Routing - Wikipediasecondary source
  4. [4]inet Extension – DuckDBdocumentation