Buckets:
AnonymousSpottedToad/candidate_bucket / codebase /python /CIRISAI_CIRISAgent /tools /build_geo_db.py
| #!/usr/bin/env python3 | |
| """Build SQLite database from GeoNames cities15000 data. | |
| Usage: | |
| python -m tools.build_geo_db | |
| Creates ciris_engine/data/geo/cities.db with: | |
| - cities table: ~26K cities with population > 15,000 | |
| - admin1 table: states/regions | |
| - countries table: country names and codes | |
| """ | |
| import sqlite3 | |
| from pathlib import Path | |
| GEO_DIR = Path(__file__).parent.parent / "ciris_engine" / "data" / "geo" | |
| DB_PATH = GEO_DIR / "cities.db" | |
| CITIES_FILE = GEO_DIR / "cities15000.txt" | |
| ADMIN1_FILE = GEO_DIR / "admin1CodesASCII.txt" | |
| COUNTRY_FILE = GEO_DIR / "countryInfo.txt" | |
| def build_database() -> None: | |
| """Build the cities database from GeoNames data.""" | |
| # Remove existing database | |
| if DB_PATH.exists(): | |
| DB_PATH.unlink() | |
| conn = sqlite3.connect(str(DB_PATH)) | |
| cursor = conn.cursor() | |
| # Create tables | |
| cursor.execute( | |
| """ | |
| CREATE TABLE countries ( | |
| code TEXT PRIMARY KEY, | |
| name TEXT NOT NULL, | |
| currency_code TEXT, | |
| currency_name TEXT, | |
| languages TEXT | |
| ) | |
| """ | |
| ) | |
| cursor.execute( | |
| """ | |
| CREATE TABLE admin1 ( | |
| code TEXT PRIMARY KEY, | |
| name TEXT NOT NULL, | |
| country_code TEXT NOT NULL | |
| ) | |
| """ | |
| ) | |
| cursor.execute( | |
| """ | |
| CREATE TABLE cities ( | |
| id INTEGER PRIMARY KEY, | |
| name TEXT NOT NULL, | |
| ascii_name TEXT NOT NULL, | |
| country_code TEXT NOT NULL, | |
| admin1_code TEXT, | |
| latitude REAL NOT NULL, | |
| longitude REAL NOT NULL, | |
| population INTEGER NOT NULL, | |
| timezone TEXT, | |
| FOREIGN KEY (country_code) REFERENCES countries(code) | |
| ) | |
| """ | |
| ) | |
| # Create indexes for fast search | |
| cursor.execute("CREATE INDEX idx_cities_name ON cities(name COLLATE NOCASE)") | |
| cursor.execute("CREATE INDEX idx_cities_ascii ON cities(ascii_name COLLATE NOCASE)") | |
| cursor.execute("CREATE INDEX idx_cities_country ON cities(country_code)") | |
| cursor.execute("CREATE INDEX idx_cities_population ON cities(population DESC)") | |
| # Load countries | |
| print("Loading countries...") | |
| country_count = 0 | |
| with open(COUNTRY_FILE, encoding="utf-8") as f: | |
| for line in f: | |
| if line.startswith("#") or not line.strip(): | |
| continue | |
| parts = line.strip().split("\t") | |
| if len(parts) >= 15: | |
| code = parts[0] | |
| name = parts[4] | |
| currency_code = parts[10] if len(parts) > 10 else "" | |
| currency_name = parts[11] if len(parts) > 11 else "" | |
| languages = parts[15] if len(parts) > 15 else "" | |
| cursor.execute( | |
| "INSERT OR IGNORE INTO countries (code, name, currency_code, currency_name, languages) VALUES (?, ?, ?, ?, ?)", | |
| (code, name, currency_code, currency_name, languages), | |
| ) | |
| country_count += 1 | |
| print(f" Loaded {country_count} countries") | |
| # Load admin1 codes (states/regions) | |
| print("Loading admin1 codes...") | |
| admin1_count = 0 | |
| with open(ADMIN1_FILE, encoding="utf-8") as f: | |
| for line in f: | |
| parts = line.strip().split("\t") | |
| if len(parts) >= 2: | |
| code = parts[0] # Format: "US.CA" | |
| name = parts[1] | |
| country_code = code.split(".")[0] if "." in code else "" | |
| cursor.execute( | |
| "INSERT OR IGNORE INTO admin1 (code, name, country_code) VALUES (?, ?, ?)", | |
| (code, name, country_code), | |
| ) | |
| admin1_count += 1 | |
| print(f" Loaded {admin1_count} admin1 codes") | |
| # Load cities | |
| print("Loading cities...") | |
| city_count = 0 | |
| with open(CITIES_FILE, encoding="utf-8") as f: | |
| for line in f: | |
| parts = line.strip().split("\t") | |
| if len(parts) >= 15: | |
| geoname_id = int(parts[0]) | |
| name = parts[1] | |
| ascii_name = parts[2] | |
| latitude = float(parts[4]) | |
| longitude = float(parts[5]) | |
| country_code = parts[8] | |
| admin1_code = f"{country_code}.{parts[10]}" if parts[10] else None | |
| population = int(parts[14]) if parts[14] else 0 | |
| timezone = parts[17] if len(parts) > 17 else None | |
| cursor.execute( | |
| """INSERT INTO cities (id, name, ascii_name, country_code, admin1_code, latitude, longitude, population, timezone) | |
| VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)""", | |
| ( | |
| geoname_id, | |
| name, | |
| ascii_name, | |
| country_code, | |
| admin1_code, | |
| latitude, | |
| longitude, | |
| population, | |
| timezone, | |
| ), | |
| ) | |
| city_count += 1 | |
| print(f" Loaded {city_count} cities") | |
| # Create FTS5 virtual table for fast text search | |
| print("Creating FTS5 search index...") | |
| cursor.execute( | |
| """ | |
| CREATE VIRTUAL TABLE cities_fts USING fts5( | |
| name, | |
| ascii_name, | |
| content='cities', | |
| content_rowid='id' | |
| ) | |
| """ | |
| ) | |
| # Populate FTS table | |
| cursor.execute( | |
| """ | |
| INSERT INTO cities_fts(rowid, name, ascii_name) | |
| SELECT id, name, ascii_name FROM cities | |
| """ | |
| ) | |
| conn.commit() | |
| # Report stats | |
| cursor.execute("SELECT COUNT(*) FROM cities") | |
| total_cities = cursor.fetchone()[0] | |
| cursor.execute("SELECT COUNT(*) FROM countries") | |
| total_countries = cursor.fetchone()[0] | |
| cursor.execute("SELECT COUNT(*) FROM admin1") | |
| total_admin1 = cursor.fetchone()[0] | |
| print(f"\nDatabase created at: {DB_PATH}") | |
| print(f" Cities: {total_cities:,}") | |
| print(f" Countries: {total_countries}") | |
| print(f" Admin1 regions: {total_admin1:,}") | |
| # Check file size | |
| db_size = DB_PATH.stat().st_size | |
| print(f" Database size: {db_size / 1024 / 1024:.2f} MB") | |
| conn.close() | |
| if __name__ == "__main__": | |
| build_database() | |
Xet Storage Details
- Size:
- 6.26 kB
- Xet hash:
- 6f7dfb8c65e5f8fbcb4628a8a4f73f942cb760de390b4c565a05cd93d429b63f
·
Xet efficiently stores files, intelligently splitting them into unique chunks and accelerating uploads and downloads. More info.