# Locate IP addresses

Model / geo

A variant of [Work with geography](https://meridian.online/operations/work-with-geography).

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

Page: <https://meridian.online/operations/work-with-geography/locate-ip-addresses>

## Status

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.

## What other tools call it

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

- Apache NiFi: [`GeoEnrichIP`](https://nifi.apache.org/components/org.apache.nifi.processors.GeoEnrichIP/) (read 2026-09-27)
- Apache NiFi: [`GeoEnrichIPRecord`](https://nifi.apache.org/components/org.apache.nifi.processors.GeoEnrichIPRecord/) (read 2026-09-27)
- Pentaho Data Integration: [`MaxMind GeoIP Lookup`](https://docs.pentaho.com/pdia-data-integration/9.3-data-integration/pdi-transformation-steps-reference-overview) (read 2026-09-27)
- Talend: [`tAddLocationFromIP`](https://help.qlik.com/talend/en-US/components/8.0/taddlocationfromip/taddlocationfromip-component) (read 2026-09-27)