簡介這份SQL文件面向地理信息系統(tǒng)開發(fā)者、C#應用工程師及需要位置服務的數(shù)據(jù)分析人員提供全球主要城市的經(jīng)緯度數(shù)據(jù)解決地圖服務、位置追蹤與地理統(tǒng)計中缺乏統(tǒng)一城市坐標基礎表的問題。壓縮包共1個文件為單個sql腳本體積約146KB內(nèi)含建表DDL與數(shù)據(jù)插入DML語句可直接導入數(shù)據(jù)庫使用。數(shù)據(jù)精確到城市級別包含中英文對照的城市名稱并保留國家、地區(qū)、州/省等層級關系便于區(qū)域分析與多語言應用開發(fā)。已有151人學習下載。讀者可借此快速構(gòu)建城市坐標表用于兩點距離計算、導航定位、地理統(tǒng)計分析并結(jié)合C#等語言開發(fā)具備位置服務能力的應用或集成到現(xiàn)有系統(tǒng)中擴展地理信息功能省去自行采集與整理全球城市經(jīng)緯度的工作量。1. 全球主要城市經(jīng)緯度數(shù)據(jù)一份 SQL 文件怎么把中英文和層級關系一次說清做地理相關的業(yè)務時最容易被低估的往往不是地圖渲染而是底層那份城市清單。你拿到一份「全球主要城市_經(jīng)緯度數(shù)據(jù)_中英文_層級關系_精確到城市_SQL文件.zip」第一反應可能是不就是一張表字段有城市名、經(jīng)緯度、國家、省份嗎真導入進去用起來問題才一個個冒出來——同一個城市中文名有「舊金山」和「圣弗朗西斯科」兩種寫法英文名帶不帶州后綴層級關系里「市」和「區(qū)」混在一列經(jīng)緯度精度在小數(shù)點后四位還是六位之間反復橫跳。這份數(shù)據(jù)的價值不在于它有多少行而在于它把中英文對照、行政層級、坐標精度這三件事同時壓進了一個可被 SQL 直接消費的結(jié)構(gòu)里。適合誰用做跨境物流地址校驗的、做多語言站點地區(qū)選擇器的、做數(shù)據(jù)看板里城市維度下鉆的以及任何需要「按城市聚合但不想自己維護一份臟清單」的團隊。下面按導入、理解結(jié)構(gòu)、查詢、避坑、進階的順序把這份 SQL 文件從解壓到跑通講透。2. 先看懂 SQL 文件里的表結(jié)構(gòu)和層級設計2.1 為什么層級關系不能只靠一張平表很多人拿到城市數(shù)據(jù)的第一反應是建一張寬表城市名、省份名、國家名、經(jīng)緯度全塞一行。這種設計在查詢「某個國家下所有城市」時很快但一旦要做「從洲到國家到省到市」的四級下鉆或者處理「一個城市屬于多個上級行政區(qū)」的邊界情況平表就會暴露冗余和更新異常。這份 SQL 文件通常采用自引用或分層編碼的方式來表達層級每一行有一個唯一 ID同時有一個 parent_id 指向上一級或者用類似level字段標記當前是洲、國家、省還是市。精確到城市意味著最細粒度那一層的 level 值固定往上聚合時只需要按 parent_id 遞歸或按層級編碼前綴匹配。常見做法是兩種鄰接表parent_id和路徑枚舉path 字段存/洲/國家/省/市。鄰接表寫入簡單、更新方便但遞歸查詢依賴數(shù)據(jù)庫的 CTE 支持路徑枚舉查詢快但移動節(jié)點時要批量改路徑。這份數(shù)據(jù)既然強調(diào)「層級關系」大概率在表里同時保留了 parent_id 和一個可讀的層級路徑或?qū)蛹壌a方便你按前綴 LIKE 查詢。導入前先確認你的數(shù)據(jù)庫版本支持遞歸 CTEMySQL 8.0 以上、PostgreSQL 全系、SQL Server 2005 以上都沒問題老版本 MySQL 5.7 就得靠自連接硬撐。2.2 中英文字段的命名習慣與編碼陷阱中英文對照不是簡單加兩列name_cn和name_en就完事。實際數(shù)據(jù)里常見的坑是英文列里混著本地語言轉(zhuǎn)寫中文列里夾著繁體或異體字還有的用name_zh、name_en、local_name三列并存。導入前先用文本編輯器或head命令看前幾十行 INSERT 語句確認列名和字符集。如果 SQL 文件里建表語句寫了CHARSETutf8mb4那中文和 emoji 都能存如果只寫了utf8MySQL 里其實是 utf8mb3遇到生僻字或某些符號會截斷或報錯。另一個高頻問題是排序規(guī)則。utf8mb4_general_ci和utf8mb4_unicode_ci對中英文混合排序結(jié)果不同做「按城市名排序」的列表時前者可能把中文排在英文后面且順序不符合拼音習慣。如果業(yè)務需要按拼音或筆畫排序光靠數(shù)據(jù)庫默認排序不夠得額外加一列name_pinyin或sort_key。這份數(shù)據(jù)如果沒帶拼音列你可以在導入后用腳本補但別指望 SQL 文件本身幫你解決。2.3 導入前的三步檢查編碼、分隔符、批量大小拿到.sql文件先別急著source。第一步用file -i看文件編碼常見是 UTF-8 帶 BOM 或不帶 BOM帶 BOM 時第一行建表語句可能報語法錯誤。第二步看 INSERT 語句是單行一條還是批量多值批量多值時注意max_allowed_packet是否夠大默認 4MB 或 16MB全球城市數(shù)據(jù)加上中英文和層級字段批量 INSERT 很容易超。第三步確認 SQL 文件里有沒有CREATE DATABASE和USE語句如果沒有你得先建庫再導入。# 檢查文件編碼和大小 file -i global_cities.sql ls -lh global_cities.sql # 查看前 50 行確認建表語句和字符集 head -n 50 global_cities.sql # 查看是否有 CREATE DATABASE / USE 語句 grep -n -E CREATE DATABASE|USE global_cities.sql | head上面命令的邏輯說明file -i輸出 MIME 類型和字符集如果顯示charsetbinary或unknown-8bit說明編碼不是標準 UTF-8需要轉(zhuǎn)碼后再導入。head -n 50讓你在不打開大文件的情況下看清表結(jié)構(gòu)定義重點看CHARSET和COLLATE。grep用來確認庫名避免導入到錯誤的數(shù)據(jù)庫。參數(shù)上如果文件超過 100MB建議用mysql命令行客戶端而不是圖形工具圖形工具在批量 INSERT 時容易超時或內(nèi)存溢出。3. 把 SQL 文件跑起來建庫、導入、驗證的最小閉環(huán)3.1 建庫與調(diào)整導入?yún)?shù)導入前先根據(jù)數(shù)據(jù)量調(diào)整幾個關鍵參數(shù)。max_allowed_packet決定單條 SQL 語句的最大長度批量 INSERT 多行時容易撞上限innodb_buffer_pool_size影響導入速度但生產(chǎn)環(huán)境別為了導入臨時調(diào)太大autocommit在導入時建議關閉用事務包住批量插入能快很多。下面是一套可抄的導入流程以 MySQL 為例。-- 創(chuàng)建數(shù)據(jù)庫字符集必須 utf8mb4 CREATE DATABASE IF NOT EXISTS geo_city DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_unicode_ci; USE geo_city; -- 導入前臨時調(diào)整會話參數(shù)只影響當前連接 SET SESSION sql_mode NO_AUTO_VALUE_ON_ZERO; SET SESSION autocommit 0; SET SESSION unique_checks 0; SET SESSION foreign_key_checks 0;邏輯說明utf8mb4是必須的否則中文城市名里的生僻字和某些符號會丟。sql_mode設為NO_AUTO_VALUE_ON_ZERO是為了防止自增列遇到 0 值時報錯有些數(shù)據(jù)文件會用 0 表示未知父級。關閉autocommit、unique_checks、foreign_key_checks是導入加速的常規(guī)操作導入完成后記得改回來。參數(shù)上unique_checks0只在確定數(shù)據(jù)無重復時用如果數(shù)據(jù)本身有重復主鍵導入后會留下隱患建議導入后跑一次去重檢查。3.2 用命令行導入并觀察錯誤# 在 shell 中執(zhí)行導入把錯誤輸出到單獨文件 mysql -u root -p --default-character-setutf8mb4 geo_city global_cities.sql 2 import_error.log # 查看錯誤日志里有沒有報錯 grep -i -E error|warning import_error.log | head -n 20邏輯說明--default-character-setutf8mb4確?？蛻舳撕头斩宋帐謺r用對字符集少了這個參數(shù)中文可能變問號。2 import_error.log把標準錯誤重定向到文件避免刷屏導入后集中看。如果錯誤日志里出現(xiàn)Packet too large就去調(diào)大max_allowed_packet出現(xiàn)Incorrect string value說明文件編碼和數(shù)據(jù)庫字符集不匹配需要轉(zhuǎn)碼或改列字符集。3.3 導入后的三張驗證查詢導入完成不等于數(shù)據(jù)可用。至少跑三類驗證行數(shù)是否符合預期、層級關系是否閉環(huán)、經(jīng)緯度是否在合法范圍。-- 驗證 1總行數(shù)和各層級行數(shù) SELECT level, COUNT(*) AS cnt FROM cities GROUP BY level ORDER BY level; -- 驗證 2找出沒有父級的非頂級節(jié)點層級斷裂 SELECT id, name_cn, level, parent_id FROM cities WHERE parent_id IS NULL AND level continent LIMIT 20; -- 驗證 3經(jīng)緯度合法性檢查 SELECT id, name_cn, latitude, longitude FROM cities WHERE latitude NOT BETWEEN -90 AND 90 OR longitude NOT BETWEEN -180 AND 180 LIMIT 20;邏輯說明第一條按層級分組計數(shù)能快速看出數(shù)據(jù)覆蓋是否完整比如城市層行數(shù)遠少于預期可能是導入中斷。第二條找層級斷裂parent_id為空但 level 不是頂級說明數(shù)據(jù)有缺失或字段映射錯了。第三條檢查坐標范圍緯度超出 ±90 或經(jīng)度超出 ±180 的記錄要么是數(shù)據(jù)錯誤要么是字段順序顛倒把經(jīng)度當緯度存了。參數(shù)上LIMIT 20只是抽樣看真要全量檢查就去掉 LIMIT 看總數(shù)。4. 按城市查經(jīng)緯度和中英文名的常用 SQL 寫法4.1 精確匹配與模糊匹配的取舍業(yè)務里查城市經(jīng)緯度最常用的是按名稱精確匹配。但中英文混合場景下「精確」本身就有歧義用戶輸入「北京」要能命中輸入「Beijing」也要能命中輸入「北京市」最好也能命中。如果表里只有name_cn和name_en兩列精確匹配就得寫兩個條件用 OR 連接或者用 UNION。更穩(wěn)的做法是建一個統(tǒng)一的搜索列把中英文和別名拼在一起加全文索引或前綴索引。-- 精確匹配中英文名含常見后綴變體 SELECT id, name_cn, name_en, latitude, longitude, level FROM cities WHERE level city AND (name_cn 北京 OR name_en Beijing OR name_cn 北京市) LIMIT 10; -- 模糊匹配適合搜索框聯(lián)想 SELECT id, name_cn, name_en, latitude, longitude FROM cities WHERE level city AND (name_cn LIKE 北% OR name_en LIKE Bei%) ORDER BY name_en LIMIT 20;邏輯說明第一條用 OR 枚舉常見變體適合已知輸入規(guī)范的場景。第二條用 LIKE 前綴匹配適合搜索框?qū)崟r聯(lián)想但注意LIKE %北京%這種前后都帶通配符的寫法無法走索引數(shù)據(jù)量大時會全表掃描。參數(shù)上level city是為了排除省和國家層同名干擾比如「吉林」既是省也是市不加層級過濾會返回多條。4.2 按層級下鉆從國家拿到所有城市層級關系的核心價值是下鉆。假設你要查「某個國家下所有城市及其經(jīng)緯度」用鄰接表遞歸 CTE 是最標準的寫法。下面以 PostgreSQL 和 MySQL 8.0 通用的遞歸 CTE 為例。-- 從國家節(jié)點遞歸拿到其下所有城市 WITH RECURSIVE city_tree AS ( -- 錨點先找到目標國家節(jié)點 SELECT id, name_cn, name_en, level, parent_id, latitude, longitude FROM cities WHERE name_en France AND level country UNION ALL -- 遞歸逐層向下找子節(jié)點 SELECT c.id, c.name_cn, c.name_en, c.level, c.parent_id, c.latitude, c.longitude FROM cities c INNER JOIN city_tree ct ON c.parent_id ct.id ) SELECT id, name_cn, name_en, latitude, longitude FROM city_tree WHERE level city ORDER BY name_en;邏輯說明錨點部分定位到國家節(jié)點遞歸部分通過c.parent_id ct.id逐層展開直到?jīng)]有子節(jié)點為止。最后過濾level city只取城市層。參數(shù)上如果數(shù)據(jù)里層級深度超過默認遞歸限制MySQL 默認cte_max_recursion_depth是 1000一般夠用如果數(shù)據(jù)用路徑枚舉而不是 parent_id就把遞歸部分換成WHERE path LIKE /France/%這種前綴匹配速度更快但依賴路徑字段的格式統(tǒng)一。4.3 中英文對照輸出的排序與去重做多語言站點時經(jīng)常需要「按當前語言排序但輸出中英文兩列」。如果直接ORDER BY name_cn中文排序結(jié)果取決于數(shù)據(jù)庫的 collation可能不是拼音順序。一個折中方案是加一列name_en作為次級排序因為英文排序穩(wěn)定且可預期。-- 按英文名排序輸出中英文對照去重同名城市 SELECT DISTINCT name_cn, name_en, latitude, longitude FROM cities WHERE level city AND name_en IS NOT NULL AND name_cn IS NOT NULL ORDER BY name_en ASC LIMIT 100;邏輯說明DISTINCT用來去掉中英文名和坐標完全相同的重復行有些數(shù)據(jù)文件在合并多個來源時會留下重復。name_en IS NOT NULL和name_cn IS NOT NULL過濾掉缺失翻譯的記錄保證輸出對照完整。參數(shù)上LIMIT 100只是示例實際分頁要用LIMIT offset, size或游標分頁避免大偏移量性能問題。5. 避坑導入和查詢這份城市數(shù)據(jù)時最容易翻車的 5 個點5.1 現(xiàn)象中文城市名導入后變成問號或亂碼原因SQL 文件本身是 GBK 或 Latin1 編碼但導入時客戶端用了 utf8mb4或者建表時列字符集沒跟上。解決先用file -i確認文件編碼如果是 GBK用iconv -f GBK -t UTF-8轉(zhuǎn)碼后再導入建表語句里所有文本列顯式寫CHARACTER SET utf8mb4別依賴庫默認。5.2 現(xiàn)象遞歸查詢報錯「Recursive query aborted after 1001 iterations」原因數(shù)據(jù)里存在循環(huán)引用A 的 parent 是 BB 的 parent 又是 A遞歸 CTE 陷入死循環(huán)。解決導入后跑一次循環(huán)檢測找出 parent_id 鏈上回到自身的記錄。MySQL 里可以臨時調(diào)大cte_max_recursion_depth看看到底循環(huán)在哪但根治方法是修數(shù)據(jù)把循環(huán)的 parent_id 置空或指向正確上級。-- 檢測簡單循環(huán)自己指向自己 SELECT id, name_cn, parent_id FROM cities WHERE id parent_id; -- 檢測兩級循環(huán)A-B-A SELECT a.id, a.name_cn, b.id AS parent_id, b.name_cn AS parent_name FROM cities a JOIN cities b ON a.parent_id b.id WHERE b.parent_id a.id;5.3 現(xiàn)象經(jīng)緯度查出來是 0,0 或者明顯偏移原因數(shù)據(jù)里用 0,0 表示未知坐標或者經(jīng)緯度列順序顛倒緯度存了經(jīng)度值。解決導入后跑范圍檢查把 0,0 的記錄單獨標記如果發(fā)現(xiàn)大量緯度絕對值大于 90基本可以確定列順序反了用 UPDATE 交換兩列。5.4 現(xiàn)象按城市名查詢返回多條分不清哪個是目標原因不同國家有同名城市比如「圣何塞」在美國和哥斯達黎加都有「劍橋」在英國和美國都有。解決查詢時必須帶上國家或省份過濾或者用層級路徑字段做前綴限定。如果業(yè)務允許用戶只輸城市名返回結(jié)果里要帶上國家名和經(jīng)緯度讓用戶自己選。5.5 現(xiàn)象導入速度極慢幾萬行跑了半小時原因autocommit 開著每條 INSERT 都刷盤或者批量 INSERT 的包太大反復重試。解決導入前關 autocommit用START TRANSACTION包住把大文件拆成多個小文件分批導入每批 5000 到 10000 行如果用的是 InnoDB導入期間臨時把innodb_flush_log_at_trx_commit設為 2導入后改回 1。6. 進階用法用這份數(shù)據(jù)做城市距離計算和區(qū)域聚合6.1 用經(jīng)緯度算球面距離的 SQL 寫法拿到城市經(jīng)緯度后一個高頻需求是「查附近城市」或「算兩個城市直線距離」。MySQL 里可以用 ST_Distance_Sphere 函數(shù)PostgreSQL 用 earthdistance 擴展或 PostGIS。下面是不依賴擴展的純 SQL 近似算法Haversine 公式適合快速驗證。-- 計算北京到上海的大圓距離單位公里 SELECT ROUND( 6371 * ACOS( COS(RADIANS(39.9042)) * COS(RADIANS(31.2304)) * COS(RADIANS(121.4737) - RADIANS(116.4074)) SIN(RADIANS(39.9042)) * SIN(RADIANS(31.2304)) ), 2 ) AS distance_km;邏輯說明6371 是地球平均半徑公里RADIANS把角度轉(zhuǎn)弧度ACOS里的表達式是球面余弦定理。參數(shù)上緯度用latitude經(jīng)度用longitude注意順序別寫反。這個公式在短距離幾百公里內(nèi)精度夠用跨半球或極地附近誤差會變大生產(chǎn)環(huán)境建議用 PostGIS 的ST_DistanceSphere或ST_Distance帶 geography 類型。6.2 按國家或洲聚合城市數(shù)量與中心點層級關系讓聚合變得簡單按 parent_id 分組就能統(tǒng)計每個國家有多少城市再往上按洲分組。如果要算某個區(qū)域的城市中心點可以對經(jīng)緯度取平均值但注意經(jīng)度在 ±180 附近跨線時平均值會出錯比如斐濟和新西蘭的城市經(jīng)度平均后可能跑到 0 度附近。更穩(wěn)的做法是用向量平均或直接取區(qū)域邊界框中心。-- 統(tǒng)計每個國家的城市數(shù)量按數(shù)量降序 SELECT p.name_cn AS country_name, COUNT(*) AS city_count FROM cities c JOIN cities p ON c.parent_id p.id WHERE c.level city AND p.level country GROUP BY p.id, p.name_cn ORDER BY city_count DESC LIMIT 20; -- 計算某國家所有城市的經(jīng)緯度平均值僅作粗略中心點 SELECT p.name_cn AS country_name, AVG(c.latitude) AS avg_lat, AVG(c.longitude) AS avg_lon FROM cities c JOIN cities p ON c.parent_id p.id WHERE c.level city AND p.level country AND p.name_en France GROUP BY p.id, p.name_cn;邏輯說明第一條自連接把城市和它的父級國家關聯(lián)起來按國家分組計數(shù)。第二條用 AVG 算平均坐標只適合經(jīng)度范圍不跨 ±180 的國家。參數(shù)上JOIN條件用c.parent_id p.id依賴層級數(shù)據(jù)完整如果 parent_id 有缺失這些城市會被排除在統(tǒng)計外跑之前先用第 3 章的驗證查詢確認層級閉環(huán)。6.3 把城市數(shù)據(jù)做成可緩存的查詢接口如果業(yè)務里頻繁按城市名查經(jīng)緯度每次走數(shù)據(jù)庫不是最優(yōu)解?？梢栽趯牒蟀裭evel city的記錄導出成一份輕量緩存比如 Redis 的 Hash 結(jié)構(gòu)key 用city:name_cn或city:name_envalue 存經(jīng)緯度和層級路徑。更新頻率低的話這份緩存可以長期有效數(shù)據(jù)有更新時重新導入 SQL 后刷新緩存即可。注意中英文 key 要分開存避免大小寫和空格差異導致命中失敗。我自己的習慣是拿到任何一份帶層級的城市數(shù)據(jù)先跑一遍第 3 章的驗證查詢確認行數(shù)、層級、坐標范圍三項都正常再開始寫業(yè)務查詢。這一步花五分鐘能省掉后面幾小時排查臟數(shù)據(jù)的后悔藥。希望幫到你。本文還有配套的精品資源點擊獲取