Geo

The admin page Tools / Geo (?object=manage_geo, super-admins only) brings every geo piece of yf together: which GeoIP sources this server has and what is missing, a lookup of one IP through each of them side by side, the setup of each missing piece, the optional update of the MaxMind GeoLite2 database, the import of the GeoNames data into the geo_* tables with its progress and log, a browser of the imported places, and which framework features use geo data and whether they work here. The engines are _class('admin_geo') (yf_admin_geo), _class('geonames_import') (yf_geonames_import) and _class('geoip_status') (yf_geoip_status, shared with the online users, the admin login rules and the doctor); they live in the geo and common plugins.

GeoIP tab

  • Sources: the web server variable (GEOIP_COUNTRY_CODE of the nginx / Apache geoip2 modules), the country header of a trusted proxy (CF-IPCountry, CloudFront-Viewer-Country, X-Geo-Country, read only when the request comes from client_utils TRUSTED_PROXIES), the PECL geoip extension, a MaxMind .mmdb file read by the maxminddb extension when it is loaded, else by the built-in PHP reader (yf_mmdb_reader, no extension or library needed; geoip_status.PHP_READER, on; off keeps the old order: the geoip2/geoip2 library, the built-in reader only for the open databases below), and the legacy share/geo_ip_binary_db.dat file. The versions of the extensions, the trusted proxies and the country headers of this request are listed too.
  • Test lookup: an IP (yours by default) through every source that is there, side by side: country, city when the source has one, the time it took. The first row is what the framework answers (yf_geoip_status::country(), the first source that answers). The web server and proxy rows describe the current visitor only. The legacy city tables geo_city_blocks / geo_city_location are read without the write to geo_skip_ip that common()->_get_geo_data_from_db() does. When a city is found and GeoNames is imported, its GeoNames place is shown (id, population, time zone, coordinates). Nothing leaves the server.
  • Database files: each GeoIP file found with its size, age (older than 60 days is flagged) and, for an .mmdb with a reader, its edition, build date, IP version and node count.
  • Setup: one box per piece, done or to do: the MaxMind GeoLite2 database (free account, licence key, a reader), the geoipupdate tool with a /etc/GeoIP.conf sample and its cron line, the web server variable (nginx and Apache), a proxy header with TRUSTED_PROXIES, and main USE_GEO_IP.
  • Open databases (no account, no key): one button each downloads an open database into storage/geoip/ over https: ip-location-db user-country (public domain, PDDL 1.0, no credit needed, updated daily, 8 MB, the closest to GeoLite2 of the free databases; from the project's GitHub releases with its sha256, which catches a broken download but not a file replaced at the source: $PROJECT_CONF['geoip_update']['PINNED_SHA256'] accepts only one exact file), DB-IP Country Lite and DB-IP City Lite (CC BY 4.0, monthly; City Lite adds the city and coordinates, 130 MB). A new file is used only after its checks (the published sha256, a MaxMind DB the built-in reader opens, an IPv6 tree, an answer for test IPs, a city for a City database), by one rename over the old file. DB-IP's licence asks for a link to DB-IP on the pages that use its results: _class('geoip_status')->attribution_html() gives it (the GeoLite2 notice for a GeoLite2 file, nothing for user-country). From the console and cron, as the web server user: geo:geoip --update --if-stale --yes updates the databases of geoip_update.SOURCES older than STALE_DAYS (7); geo:geoip --lookup=1.2.3.4 looks one IP up.
  • MaxMind GeoLite2 update: for servers where geoipupdate cannot be installed. Off until admin_geo.MMDB_UPDATE is on (Settings, tab Geo) and the site config has the credentials, which the Settings page never saves: $PROJECT_CONF['admin_geo']['MAXMIND_ACCOUNT_ID'] and ['MAXMIND_LICENSE_KEY'] (the page shows the key masked). The button downloads MMDB_EDITION (GeoLite2-Country or GeoLite2-City) as tar.gz with HTTP basic auth, checks the .mmdb in it and puts it in storage/geoip/ in place of the old one (a rename, so readers never see half a file). yf_geoip_status reads that folder after /usr/share/GeoIP/ and the other system folders.

GeoNames tab

GeoNames publishes every place of the world (about 13 million), their names in other languages (about 15 million), regions, countries, time zones, languages and postal codes, as tab separated files under https://download.geonames.org/export/ (licence CC BY 4.0). The import loads them into the tables of the .dev/scripts/geonames-db-import/ shell scripts, with the same names and columns:

TableFileNotes
geo_geonameallCountries.txt or XX.txtplaces; population is a BIGINT (Earth has more than 2^31)
geo_alternate_namealternateNamesV2.txt (or the scripts' alternateNames.txt), alternatenames/XX.txtnames in other languages; the six name fixes of yf_geonames_db_patch.sql are applied (PATCHES)
geo_country, geo_language, geo_feature, geo_timezonecountryInfo.txt, iso-languagecodes.txt, featureCodes_en.txt, timeZones.txtsmall reference tables; time zone offsets keep quarters (Nepal 5.75)
geo_admin1, geo_admin2admin1CodesASCII.txt, admin2Codes.txtregions and districts
geo_hierarchyhierarchy.txtparent / child links; the id is the line number
geo_continentcontinentCodes.txtwritten by the import, as the shell scripts do
geo_postal_codepostalCodes/allCountries.txt or postalCodes/XX.txtthe id is the line number, so places that share a postal code are all kept

The tables are in the site database with its prefix. A site that keeps them in the shell scripts' own database sets $PROJECT_CONF['geonames_import']['DATABASE'] = 'geonames' (MySQL / MariaDB, same server and user). They are not installer tables: a site that never imports gets none. The framework's own geo_countries, geo_regions, geo_cities (the select boxes, Other / Geo) are separate and not touched.

Actions

  • Update: downloads what changed on GeoNames, then reloads the tables of the changed files (the first time, all of them).
  • Download: the files only, into storage/geonames/data/ (DATA_DIR; the folder the shell scripts download to works too). A file whose size and date on the server are the same as at its last download is skipped. Big files come in range requests of 16 MB, so a download goes on where it stopped (at most geonames_http MAX_BYTES, 2 GB, per file); zips are extracted and then removed, as the scripts do. A zip member is written in one step of up to EXTRACT_SECONDS (45; the console: no limit), because a zip cannot be read from the middle: a step that goes on reads the part already written again.
  • Import: every table from the downloaded files.
  • Check: exact row counts against the last import, minimum counts of a full import, integrity checks (places of an unknown country, alternate names without their place, regions of an unknown country, each bounded by LIMIT 1000) and sample lookups (a country, its capital, the capital's alternate names, regions, postal codes).
  • Truncate: empties every GeoNames table after you type TRUNCATE; the downloaded files stay.

Every action is a POST with the session token and a confirm, for super-admins only, refused with manage_geo.READ_ONLY, and written to the admin wall (manage_geo geonames_import: started (15 jobs), ... done: geo_geoname 13012345, ...).

How a run works

A run is a list of jobs kept in storage/geonames/state.json: downloads, extractions, the creation of import tables (geo_*_imp), a load per file, the cleanup of filtered places, the name fixes, and the swap. The page asks the server for one step at a time (a POST as soon as the last one answered, while the page is open); each step works STEP_SECONDS (8) and saves the state after every chunk of CHUNK_ROWS rows, so no request hits a web server or PHP timeout. Loads read the file from the saved byte offset and insert with INSERT IGNORE on the primary key: a request that dies after an insert and before saving inserts that chunk again without duplicates. The live tables stay readable during the whole import; at the end the import tables replace them in one RENAME TABLE (atomic on MySQL) and the old ones are dropped. A lock file lets one step run at a time (two tabs, or the page and the console).

Closing the page pauses the run; the panel then shows Continue and Cancel (which drops the import tables; the live ones stay). A failed job shows its error and Retry from where it failed. The run log (last 30 lines, and the Live view of the log streaming page, source geonames) says what each job did. A page that only watches a run refreshes its panel at the interval of the ⟳ control.

Sizes for every country: the zips are about 400 MB (places), 200 MB (alternate names) and 20 MB (postal codes); the tables take a few GB. The first full import takes from tens of minutes to a few hours depending on the server, which is why the console is the better place for it:

php .dev/console/yf.php geo:geonames                  # tables, rows, files, the last run
php .dev/console/yf.php geo:geonames --update --yes    # download what changed and reload those tables (cron)
php .dev/console/yf.php geo:geonames --continue --yes  # finish the run the page started
php .dev/console/yf.php geo:geonames --check           # counts, integrity, samples (--json)

Run the command as the web server user (sudo -u www-data php .dev/console/yf.php geo:geonames ...): the folders are created with mode 0750 and the files belong to whoever writes them, so files written by root or a deploy user stop the page from continuing that run. The lock, the state, the log and the .part files are never followed through a symbolic link, and a run only takes jobs whose files, URLs and tables are the module's own (a state file edited by hand with anything else fails the run).

Settings

Settings, tab Geo: geonames_import.COUNTRIES (per-country files instead of allCountries: a few MB instead of hundreds), FEATURE_CLASSES (for example A and P: countries, regions, cities, villages, about a third of the places; the alternate names of the left-out places are removed after the load), WITH_ALTERNATE_NAMES, WITH_POSTAL_CODES, WITH_HIERARCHY, STALE_DAYS. Site config only: ALT_LANGUAGES (keep only these languages of the alternate names), DATABASE, DATA_DIR, WORK_DIR, CHUNK_ROWS, STEP_SECONDS, EXTRACT_SECONDS, BASE_URL (a mirror), PATCHES; geonames_http.MAX_BYTES (largest download, 2 GB) and admin_geo.MMDB_MAX_BYTES (largest .mmdb taken out of the MaxMind archive, 512 MB).

The shell scripts

.dev/scripts/geonames-db-import/ keeps working as before (geonames_do_all.sh, geonames_importer.sh -a ..., LOAD DATA LOCAL INFILE into the geonames database). The page reads tables the scripts loaded when DATABASE names their database, and an import from the page can use the files they downloaded (DATA_DIR pointing at their data/ folder). Differences: the page needs no local_infile, no mysql client and no stored procedure, keeps the tables readable during an import, and keeps every postal code (the scripts' primary key kept the first place of a shared postal code). The scripts still load into tables the page created (the ids of geo_hierarchy and geo_postal_code are AUTO_INCREMENT). The other way round, an import from the page with DATABASE set to the scripts' database replaces their tables with its own at the swap, without a backup: utf8mb4, VARCHAR columns, wider types, its own indexes and the postal code primary key id. Back up the geonames database first.

Places tab

The imported places of a country and / or a name prefix, sorted by population, name or id, paged on the server (table2 in htmx mode).

Usage tab

Which framework features use geo data and whether each works with this setup, read from the settings and the tables (nothing is looked up): the online users' country and flag, the admin login country rules, the city of user logins (auth_user TRACK_GEO_LOCATION, legacy city tables), ads by country (advertising COUNTRY_BY_IP, legacy .dat file), the user geo block (geo_content), the country / region / city select boxes (geo_countries, geo_regions, geo_cities), the GeoNames tables (for site code: no framework page reads them yet) and the shop (the customer's address country; no lookup).

The online users (COUNTRY geoip), ads by country, login geo tracking and geo_content read the legacy .dat file and city tables until their flag is on: online_users, advertising, auth_user and geo_content USE_GEOIP_STATUS (Settings, tab Geo; off by default) make them ask yf_geoip_status, so the open database (and a City database for the place) is enough.

Doctor and admin home

The doctor's GeoIP check (Environment) and the GeoNames check (Database: a failed or paused run, tables missing or empty, data older than STALE_DAYS; nothing for a site that never imported) both count on the module bar's health badge of this page. The admin home has a Geo card (super-admins): the GeoIP sources, the GeoNames rows, the last import and a run in progress.