geoip
geoip
geoip : IP-based geolocation query
Overview
| ID | Extension | Package | Version | Category | License | Language |
|---|---|---|---|---|---|---|
| 1560 | geoip
|
geoip
|
0.3.0 |
GIS
|
BSD 2-Clause
|
SQL
|
| Attribute | Has Binary | Has Library | Need Load | Has DDL | Relocatable | Trusted |
|---|---|---|---|---|---|---|
--s-d--
|
No
|
Yes
|
No
|
Yes
|
no
|
no
|
| Relationships | |
|---|---|
| Schemas | geoip |
| Requires | ip4r
|
| See Also | postgis
tzf
country
postgis_topology
postgis_raster
postgis_sfcgal
postgis_tiger_geocoder
address_standardizer
|
no pg17 on el9, no legacy branch on el8
Packages
| Type | Repo | Version | PG Major Compatibility | Package Pattern | Dependencies |
|---|---|---|---|---|---|
| EXT | MIXED
|
0.3.0 |
18
17
16
15
14
|
geoip |
ip4r |
| RPM | PIGSTY
|
0.3.0 |
18
17
16
15
14
|
geoip_$v |
- |
| DEB | PIGSTY
|
0.3.0 |
18
17
16
15
14
|
postgresql-$v-geoip |
- |
| Linux / PG | PG18 | PG17 | PG16 | PG15 | PG14 |
|---|---|---|---|---|---|
el8.x86_64
|
PIGSTY 0.3.0
|
PIGSTY 0.3.0
|
PIGSTY 0.3.0
|
PIGSTY 0.3.0
|
PIGSTY 0.3.0
|
el8.aarch64
|
PIGSTY 0.3.0
|
PIGSTY 0.3.0
|
PIGSTY 0.3.0
|
PIGSTY 0.3.0
|
PIGSTY 0.3.0
|
el9.x86_64
|
PIGSTY 0.3.0
|
PIGSTY 0.3.0
|
PIGSTY 0.3.0
|
PIGSTY 0.3.0
|
PIGSTY 0.3.0
|
el9.aarch64
|
PIGSTY 0.3.0
|
PIGSTY 0.3.0
|
PIGSTY 0.3.0
|
PIGSTY 0.3.0
|
PIGSTY 0.3.0
|
el10.x86_64
|
PIGSTY 0.3.0
|
PIGSTY 0.3.0
|
PIGSTY 0.3.0
|
PIGSTY 0.3.0
|
PIGSTY 0.3.0
|
el10.aarch64
|
PIGSTY 0.3.0
|
PIGSTY 0.3.0
|
PIGSTY 0.3.0
|
PIGSTY 0.3.0
|
PIGSTY 0.3.0
|
d12.x86_64
|
PIGSTY 0.3.0
|
PIGSTY 0.3.0
|
PIGSTY 0.3.0
|
PIGSTY 0.3.0
|
PIGSTY 0.3.0
|
d12.aarch64
|
PIGSTY 0.3.0
|
PIGSTY 0.3.0
|
PIGSTY 0.3.0
|
PIGSTY 0.3.0
|
PIGSTY 0.3.0
|
d13.x86_64
|
PIGSTY 0.3.0
|
PIGSTY 0.3.0
|
PIGSTY 0.3.0
|
PIGSTY 0.3.0
|
PIGSTY 0.3.0
|
d13.aarch64
|
PIGSTY 0.3.0
|
PIGSTY 0.3.0
|
PIGSTY 0.3.0
|
PIGSTY 0.3.0
|
PIGSTY 0.3.0
|
u22.x86_64
|
PIGSTY 0.3.0
|
PIGSTY 0.3.0
|
PIGSTY 0.3.0
|
PIGSTY 0.3.0
|
PIGSTY 0.3.0
|
u22.aarch64
|
PIGSTY 0.3.0
|
PIGSTY 0.3.0
|
PIGSTY 0.3.0
|
PIGSTY 0.3.0
|
PIGSTY 0.3.0
|
u24.x86_64
|
PIGSTY 0.3.0
|
PIGSTY 0.3.0
|
PIGSTY 0.3.0
|
PIGSTY 0.3.0
|
PIGSTY 0.3.0
|
u24.aarch64
|
PIGSTY 0.3.0
|
PIGSTY 0.3.0
|
PIGSTY 0.3.0
|
PIGSTY 0.3.0
|
PIGSTY 0.3.0
|
Source
pig build pkg geoip; # build rpm/debInstall
Make sure PGDG and PIGSTY repo available:
pig repo add pgsql -u # add both repo and update cacheInstall this extension with pig:
pig install geoip; # install via package name, for the active PG version
pig install geoip -v 18; # install for PG 18
pig install geoip -v 17; # install for PG 17
pig install geoip -v 16; # install for PG 16
pig install geoip -v 15; # install for PG 15
pig install geoip -v 14; # install for PG 14Create this extension with:
CREATE EXTENSION geoip CASCADE; -- requires ip4rUsage
This extension provides IP-based geolocation — you provide an IPv4 or IPv6 address and the extension looks up country, city, GPS coordinates, ASN and more. It requires the ip4r extension and GeoLite2 data from MaxMind.
CREATE EXTENSION ip4r;
CREATE EXTENSION geoip;Functions
| Function | Description |
|---|---|
geoip_country_code(ip4|ip6) |
Returns country code (2 chars) |
geoip_country(ip4|ip6) |
Returns all country info (code, name, network) |
geoip_city_location(ip4|ip6) |
Returns just the location ID (INT) |
geoip_city(ip4|ip6) |
Returns all city info (GPS, ZIP code, etc.) |
geoip_asn(ip4|ip6) |
Returns ASN name and IP range |
Examples
SELECT geoip_country_code('78.45.133.255'::ip4);
-- CZ
SELECT * FROM geoip.geoip_city('78.45.133.255'::ip4);
-- geoname_id | country_iso_code | city_name | postal_code | ...
-- ------------+------------------+-----------+-------------+----
-- 3066399 | CZ | Sardice | 696 13 | ...
SELECT * FROM geoip.geoip_country('78.45.133.255'::ip4);
-- network | country_iso_code | country_name
-- ----------------+------------------+--------------
-- 78.45.128.0/17 | CZ | Czechia
SELECT * FROM geoip.geoip_asn('78.45.133.255'::ip4);
-- network | asn_number | asn_name
-- --------------+------------+---------------------
-- 78.44.0.0/15 | 6830 | Liberty Global B.V.Loading Data
The extension requires GeoLite2 CSV data from MaxMind. Download the City, Country, and ASN datasets in CSV format from MaxMind GeoLite2, then load:
cat GeoLite2-Country-Locations-en.csv | \
psql $DBNAME -c 'COPY geoip.geoip_country_locations FROM stdin WITH (FORMAT CSV, HEADER)'
cat GeoLite2-Country-Blocks-IPv4.csv | \
psql $DBNAME -c 'COPY geoip.geoip_country_blocks FROM stdin WITH (FORMAT CSV, HEADER)'
cat GeoLite2-Country-Blocks-IPv6.csv | \
psql $DBNAME -c 'COPY geoip.geoip_country_blocks FROM stdin WITH (FORMAT CSV, HEADER)'
cat GeoLite2-City-Locations-en.csv | \
psql $DBNAME -c 'COPY geoip.geoip_city_locations FROM stdin WITH (FORMAT CSV, HEADER)'
cat GeoLite2-City-Blocks-IPv4.csv | \
psql $DBNAME -c 'COPY geoip.geoip_city_blocks FROM stdin WITH (FORMAT CSV, HEADER)'
cat GeoLite2-City-Blocks-IPv6.csv | \
psql $DBNAME -c 'COPY geoip.geoip_city_blocks FROM stdin WITH (FORMAT CSV, HEADER)'
cat GeoLite2-ASN-Blocks-IPv4.csv | \
psql $DBNAME -c 'COPY geoip.geoip_asn_blocks FROM stdin WITH (FORMAT CSV, HEADER)'
cat GeoLite2-ASN-Blocks-IPv6.csv | \
psql $DBNAME -c 'COPY geoip.geoip_asn_blocks FROM stdin WITH (FORMAT CSV, HEADER)'The “locations” files have multiple language variants — pick the one that works for you.
Last updated on