Import GeoNames into D1

The dataset is not stored in Git. Import commands do not download source data or write a database. They generate immutable SQL chunks and manifests from your local GeoNames files; seed:apply applies those files to local or remote D1.

Inputs and provenance

Keep one coherent GeoNames snapshot together. The importer expects these five source files:

• allCountries.zip, containing allCountries.txt (places, including alternate names)
• featureCodes_en.txt
• countryInfo.txt
• admin1CodesASCII.txt
• admin2Codes.txt

The reference importer reads the four text files; the places importer reads allCountries.txt from the ZIP. This pipeline does not import alternateNamesV2. Do not combine reference files from one snapshot with allCountries.zip from another.

Initial local seed

Commands below start at the repository root. Replace /path/to/geonames-inputs with the directory containing all five inputs. Install dependencies once, then run all remaining commands from apps/service.

bun install
cd apps/service
bun run db:migrations:local
bun run import:reference -- --data-dir /path/to/geonames-inputs --output-dir .import/seed/reference
bun run import:places -- --archive /path/to/geonames-inputs/allCountries.zip --output-dir .import/seed/places
bun run seed:apply -- --manifest .import/seed/reference/feature_codes-featureCodes_en/manifest.json --local
bun run seed:apply -- --manifest .import/seed/reference/countries-countryInfo/manifest.json --local
bun run seed:apply -- --manifest .import/seed/reference/admin1_codes-admin1CodesASCII/manifest.json --local
bun run seed:apply -- --manifest .import/seed/reference/admin2_codes-admin2Codes/manifest.json --local
bun run seed:apply -- --manifest .import/seed/places/manifest.json --local
bun run db:post -- --phase indexes --local
bun run db:post -- --phase fts --local
bun run seed:fts -- --local
bun run db:post -- --phase triggers --local
bun run seed:verify -- --local

The two base migrations create tables and seed_chunks before loading rows. Post-load, create the ordinary indexes, create the FTS tables, populate FTS in bounded batches, then install triggers and verify. The indexes are locations_name_norm_idx on normalized_name, locations_admin_idx on the two admin codes, locations_population_idx on descending population, and locations_feature_idx on feature class/code and descending population. FTS stores searchable place name, ASCII name, normalized aliases, and admin codes; feature-code FTS indexes feature names and descriptions. Triggers maintain these search structures on later row changes, so install them after bulk loading to avoid per-row trigger work during the seed. Verification checks the expected table and FTS content counts, indexes, triggers, seed-marker coverage, and representative searches.

Resume an interrupted seed

Keep the exact generated chunk files and manifests for a snapshot. Each manifest records chunk number, source, table, source-line range, row count, filename, and SHA-256; seed_chunks stores the committed chunk identity and hash. Before applying a chunk, the runner checks its marker; an identical marker means skip. After an apply reports failure or times out, it checks the marker again because the database may have committed even if the client did not receive success. A matching marker means the chunk is treated as applied; without one the runner retries (up to two attempts) and then fails. Remote application records its marker after the SQL succeeds. These markers are safe retry state for those generated files, not proof that a newly generated snapshot matches old rows.

On timeout or interruption, inspect the command output and database seed_chunks markers, then rerun the same seed:apply command with the same manifest and unchanged chunk files. Never skip a guessed number of lines or regenerate chunks in place and assume the result is a safe resume. Import generators produce new chunks from current inputs; preserve the original output directory as the retry artifact.

Remote D1 initial seed

These commands mutate the configured Cloudflare database. Confirm the intended account and database before running. From apps/service, apply the base migrations remotely, then apply the same manifests generated above with --remote in the same order:

bun run db:migrations:remote
bun run seed:apply -- --manifest .import/seed/reference/feature_codes-featureCodes_en/manifest.json --remote
bun run seed:apply -- --manifest .import/seed/reference/countries-countryInfo/manifest.json --remote
bun run seed:apply -- --manifest .import/seed/reference/admin1_codes-admin1CodesASCII/manifest.json --remote
bun run seed:apply -- --manifest .import/seed/reference/admin2_codes-admin2Codes/manifest.json --remote
bun run seed:apply -- --manifest .import/seed/places/manifest.json --remote
bun run db:index:remote
bun run db:post -- --phase fts --remote
bun run seed:fts -- --remote
bun run db:post -- --phase triggers --remote
bun run seed:verify -- --remote

The remote index builder is required. A direct full-table CREATE INDEX over the 13-million-row locations table exceeds Cloudflare D1’s memory limit. db:index:remote creates all four indexes on an empty shadow table, copies bounded ID ranges, checks source/destination counts, and swaps only after parity; it can resume an interrupted copy. Do not run db:post -- --phase indexes --remote. The FTS population is also bounded, and verification checks its content-row count against the locations count. Do not deploy a Worker against a rebuilt remote database until verification succeeds.

Reseeding a different snapshot

The procedure above resumes the same snapshot; it is not a replacement strategy. The location importer uses INSERT OR IGNORE, and seed markers identify source chunks, so changed source rows or deletions will not reliably replace old data. For a different snapshot, build a fresh D1 database and import that snapshot from its own five inputs. Do not present regenerating chunks into an existing seeded database as a safe reseed. Preserve the old database until the replacement is verified, then follow the project’s authorized database/deployment process.

After a dataset rebuild, bump the service-owned cache version in apps/service/src/search/cache.ts. Deploy the service first, then deploy consumers only after the user has authorized deployment. Do not change the shared KV namespace. For local work, stop the demo while importing and retain the correct Wrangler local state at apps/service/.wrangler/global-state/; it is separate from the old India-only .wrangler/state/. Avoid resetting local state or pointing commands at the wrong state directory. Start the demo from the repository root with bun run dev.

For system design and measured search behavior, see Search architecture and Search benchmarking; the API contract is in API reference.