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_CODEof 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 fromclient_utils TRUSTED_PROXIES), the PECLgeoipextension, a MaxMind.mmdbfile read by themaxminddbextension 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: thegeoip2/geoip2library, the built-in reader only for the open databases below), and the legacyshare/geo_ip_binary_db.datfile. 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 tablesgeo_city_blocks/geo_city_locationare read without the write togeo_skip_ipthatcommon()->_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
.mmdbwith 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
geoipupdatetool with a/etc/GeoIP.confsample and its cron line, the web server variable (nginx and Apache), a proxy header withTRUSTED_PROXIES, andmain 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 --yesupdates the databases ofgeoip_update.SOURCESolder thanSTALE_DAYS(7);geo:geoip --lookup=1.2.3.4looks one IP up. - MaxMind GeoLite2 update: for servers where
geoipupdatecannot be installed. Off untiladmin_geo.MMDB_UPDATEis 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 downloadsMMDB_EDITION(GeoLite2-Country or GeoLite2-City) astar.gzwith HTTP basic auth, checks the.mmdbin it and puts it instorage/geoip/in place of the old one (a rename, so readers never see half a file).yf_geoip_statusreads 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:
| Table | File | Notes |
|---|---|---|
geo_geoname | allCountries.txt or XX.txt | places; population is a BIGINT (Earth has more than 2^31) |
geo_alternate_name | alternateNamesV2.txt (or the scripts' alternateNames.txt), alternatenames/XX.txt | names in other languages; the six name fixes of yf_geonames_db_patch.sql are applied (PATCHES) |
geo_country, geo_language, geo_feature, geo_timezone | countryInfo.txt, iso-languagecodes.txt, featureCodes_en.txt, timeZones.txt | small reference tables; time zone offsets keep quarters (Nepal 5.75) |
geo_admin1, geo_admin2 | admin1CodesASCII.txt, admin2Codes.txt | regions and districts |
geo_hierarchy | hierarchy.txt | parent / child links; the id is the line number |
geo_continent | continentCodes.txt | written by the import, as the shell scripts do |
geo_postal_code | postalCodes/allCountries.txt or postalCodes/XX.txt | the 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 mostgeonames_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 toEXTRACT_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.