How to use the reverse geocoding offline database?

The database is available on this page. Both CSV and SQLite formats are available.

First, we take a look at the GPS-to-country database in CSV format. The country.csv file contains the following columns:

x, y1,    y2,    lon,    lat1,    lat2,   id,     iso_code, name_en
0, 25558, 27121, -180.0, -19.803, -15.51, 571747, FJ,       Fiji
0, 29278, 29408, -180.0, -9.586,  -9.229, 2177266,TV,       Tuvalu
0, 51625, 51751, -180.0, 51.792,  52.138, 148838, US,       United States
0, 55543, 55660, -180.0, 62.553,  62.875, 60189,  RU,       Russia
0, 56341, 57995, -180.0, 64.745,  69.288, 60189,  RU,       Russia
0, 58515, 58891, -180.0, 70.716,  71.749, 60189,  RU,       Russia

Only the x, y1, y2, id columns are used for reverse geocoding. Other columns are included in the CSV file for convenience.

The x, y1, y2 columns are GPS coordinates rescaled linearly to integer range [0, 65536). The id column is the ID of the administrative area, and is used to look up metadata in the country_tags.csv file.

Steps to reverse geocoding:

Rescale the input GPS coordinates into integer range [0, 65536). The formula is
x = (longitude + 180) / 360 * 65536, y = (latitude + 90) / 180 * 65536.
Find the row in the database where x = longitude and y1 ≤ latitude ≤ y2. (The CSV file is sorted by (x, y1).)
Use the id column to look up metadata.

Take the GPS coordinates (-17.0, -180.0) as an example:

(-17.0, -180.0) is converted to x = 0, y = 26578.
Find the row where x = 0 and y1 ≤ 26578 ≤ y2, the id in this row is 571747.
Lookup the id 571747 in `country_tags.csv`, this is the ID for Fiji country.

The GPS to city database is the same, except the result is a list of IDs for each sub-division.

Use the SQLite format

Let's take a look at the country.sqlite3 file:

sqlite> .tables
rgc_country       rgc_country_tags
sqlite> .schema rgc_country
CREATE TABLE rgc_country (
            x integer NOT NULL,
            y1 integer NOT NULL,
            y2 integer NOT NULL,
            id integer NOT NULL,
            PRIMARY KEY (x, y1)
        );
sqlite> .schema rgc_country_tags
CREATE TABLE rgc_country_tags (
            id integer NOT NULL,
            key varchar NOT NULL,
            val varchar NOT NULL,
            PRIMARY KEY (id, key)
        );

The structure of the SQLite database is the same as the CSV format, which is described in the last section.

Use the following SQL to perform the reverse geocoding:

select id from rgc_country
    where x = cast((longitude + 180) / 360 * 65536 as int)
        and y1 <= cast((latitude + 90) / 180 * 65536 as int)
        and y2 >= cast((latitude + 90) / 180 * 65536 as int)
limit 1;

After obtaining the id column, we can look up the rgc_country_tags table for metadata.

The above SQL query is efficient since the primary key is (x, y1). If you import the data into other database systems, also add an index for (x, y1).