Simple Postgres dataset and functions for mapping h3 indices or coordinates to country codes.
The following instructions have been tested on Ubuntu 20.04 with Postgres 12 with server headers. You may run into compatibility issues with older versions. Setup assumes that you already have superuser access to a Postgres database.
-
On your Postgres server, install the
postgis
extension andpgxnclient
extension manager if not already:sudo apt-get install postgis pgxnclient cmake make
-
Use
pgxn
to install the Postgres h3 bindings:pgxn install h3
Note: If you see an error related to missing
<postgres.h>
, this means that you need to install the server headers for your version of postgres, e.g.sudo apt-get install postgresql-server-dev-12
. -
Create a copy of
.env.template
, call it.env
, and configure the application with your Postgres connection URL and the path to the countries GeoJSON dataset. -
Install dependencies:
pip install -r requirements.txt
-
Run the
load_borders.py
bootstrapping script:python load_borders.py
- This creates a table called
borders
with 3 columns:country_name (text)
: The full country name, e.g.United States of America
.country_iso_a3 (text)
: The country ISO A3 code, e.g.USA
.country_iso_a2 (text)
: The country ISO A2 code, e.g.US
.numeric_code (integer)
: The numeric country code.geometry (geometry)
: The coordinates list defining the country borders as a polygon. This enables a variety of geospatial queries viapostgis
, e.g.ST_CONTAINS
.
Simple macros leverage h3
and postgis
extensions to enable common queries.
h3_to_country_iso_a3 (_h3_index text)
andh3_to_country_iso_a2 (_h3_index text)
- Description: Given an h3 index, returns the country ISO code (A2 or A3) that the hex centroid lies in.
- Example Usage:
SELECT h3_to_country_iso_a3('88a819462bfffff');
->BRA
.
h3_to_country_name (_h3_index text)
- Description: Given an h3 index, returns the country name that the hex centroid lies in.
- Example Usage:
SELECT h3_to_country_name('88a819462bfffff');
->Brazil
.
coordinates_to_country_iso_a3 (_latitude numeric, _longitude numeric)
andcoordinates_to_country_iso_a2 (_latitude numeric, _longitude numeric)
- Description: Given a coordinate pair, returns the country ISO code that the point lies in.
- Example Usage:
SELECT coordinates_to_country_iso(24.4539, 54.3773);
->ARE
.
coordinates_to_country_name (_latitude numeric, _longitude numeric)
- Description: Given a coordinate pair, returns the country name that the point lies in.
- Example Usage:
SELECT coordinates_to_country_name(24.4539, 54.3773);
->United Arab Emirates
.