全国省市区经纬度MySQL数据建模与查询优化实战-北京尧图网络

简介这份MySQL数据资源面向需要省、市、区三级行政区划经纬度坐标的开发者与数据分析人员可用于地图标注、区域检索、地址解析、物流配送范围计算等场景帮助解决行政区划与地理坐标匹配的基础数据缺失问题。压缩包内共1个SQL文件整体约127KB导入后即可获得结构化的省市区经纬度记录便于直接建表查询或与业务表关联使用。目前已有2974人学习下载说明该数据在同类需求中具备一定参考价值。数据以标准SQL语句组织字段涵盖行政区名称与对应经纬度层级关系清晰适合快速落地到后台系统、可视化大屏或本地测试环境减少自行采集与清洗坐标的工作量。对于需要轻量级、可直接导入的全国行政区划坐标数据的读者这份资源能提供较为实用的基础支撑。1. 全国省市区经纬度 MySQL 数据一张表撑起定位、分单与地图落点做本地生活、门店选址、物流分单或者地图打点的同学迟早会撞上同一个需求给全国省市区配上经纬度落到 MySQL 里能按名字查、能按坐标反查、能算距离。听起来像是一份静态字典真动手才发现坑不少——省市区三级编码对不上、同名区县跨省重名、直辖市层级特殊、坐标精度参差、坐标系还不统一。我见过太多项目一开始随手建张region表等到要做「附近 5 公里的门店」时才发现索引没建对、坐标是火星坐标系、区县边界点取的是政府驻地还是几何中心也没人说得清。这篇就把这套数据从建模、导入、查询到避坑讲透适合正在做定位、分单、地图落点、区域统计的后端和数据分析同学新手能照着建表跑通熟手能对参数和边界心里有数。2. 省市区三级表怎么建字段、编码与坐标系的选型理由2.1 为什么用一张自关联表而不是三张表最常见的做法是省、市、区各建一张表靠外键串起来。我一般不建议这么干原因有三个。第一查询路径变长做「按区县名反查完整地址」时要 join 三次写起来烦、跑起来慢。第二行政区划会变撤县设区、合并乡镇这类调整三张表要同步改容易漏。第三很多业务其实只需要「拿到某一级的坐标」一张表加个level字段就能覆盖。所以主流方案是一张自关联表每条记录有唯一id用parent_id指向上一级level标记是省(1)、市(2)还是区(3)。这样任意一级都能独立查询也能递归出完整路径。代价是递归查询需要应用层或 CTE 处理但换来的是维护简单。CREATE TABLE region ( id INT UNSIGNED NOT NULL COMMENT 行政区划唯一ID, parent_id INT UNSIGNED NOT NULL DEFAULT 0 COMMENT 父级ID省级为0, name VARCHAR(64) NOT NULL COMMENT 名称如某区, short_name VARCHAR(32) DEFAULT NULL COMMENT 简称如某区去掉后缀, level TINYINT NOT NULL COMMENT 层级1省 2市 3区县, code VARCHAR(12) NOT NULL COMMENT 行政区划编码, lng DECIMAL(10,6) DEFAULT NULL COMMENT 经度WGS84, lat DECIMAL(10,6) DEFAULT NULL COMMENT 纬度WGS84, pinyin VARCHAR(64) DEFAULT NULL COMMENT 拼音便于搜索, PRIMARY KEY (id), UNIQUE KEY uk_code (code), KEY idx_parent (parent_id), KEY idx_level_name (level, name), KEY idx_lng_lat (lng, lat) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT全国省市区经纬度表;字段说明几个关键点。code用行政区划编码长度留 12 位因为区县一级编码通常是 6 位但有些统计口径会带下级后缀留宽一点不亏。lng/lat用DECIMAL(10,6)经度范围 -180 到 180纬度 -90 到 906 位小数大约精确到 0.1 米对省市区这种粒度绰绰有余比FLOAT稳不会出现116.399999这种浮点误差。idx_lng_lat这个联合索引是为后面「按坐标框选」准备的单列索引在范围查询里帮不上忙。2.2 坐标系必须先定死WGS84、GCJ02 与 BD09 的差别这是最容易翻车的地方。同一栋楼用不同坐标系量出来的经纬度能差几百米。常见三种坐标系用途特点WGS84GPS 原始、国际标准全球通用国内地图直接叠加会有偏移GCJ02国内互联网地图常用在 WGS84 基础上做了非线性偏移BD09某地图厂商自有在 GCJ02 上再偏移一次血泪经验是入库时统一存 WGS84展示或对接地图时再按需转换。因为 WGS84 是基准转换是单向可逆的GCJ02 转 WGS84 有误差但可近似反过来存 GCJ02 再想还原 WGS84 就会丢精度。表里lng/lat注释写死 WGS84团队里谁也别偷偷塞 GCJ02 进来。import math # WGS84 转 GCJ02 的常用近似实现仅用于展示层 def wgs84_to_gcj02(lng, lat): a 6378245.0 # 长半轴 ee 0.00669342162296594323 # 偏心率平方 dlat _transform_lat(lng - 105.0, lat - 35.0) dlng _transform_lng(lng - 105.0, lat - 35.0) radlat lat / 180.0 * math.pi magic math.sin(radlat) magic 1 - ee * magic * magic sqrtmagic math.sqrt(magic) dlat (dlat * 180.0) / ((a * (1 - ee)) / (magic * sqrtmagic) * math.pi) dlng (dlng * 180.0) / (a / sqrtmagic * math.cos(radlat) * math.pi) return lng dlng, lat dlat def _transform_lat(x, y): ret -100.0 2.0*x 3.0*y 0.2*y*y 0.1*x*y 0.2*math.sqrt(abs(x)) ret (20.0*math.sin(6.0*x*math.pi) 20.0*math.sin(2.0*x*math.pi)) * 2.0/3.0 ret (20.0*math.sin(y*math.pi) 40.0*math.sin(y/3.0*math.pi)) * 2.0/3.0 ret (160.0*math.sin(y/12.0*math.pi) 320*math.sin(y*math.pi/30.0)) * 2.0/3.0 return ret def _transform_lng(x, y): ret 300.0 x 2.0*y 0.1*x*x 0.1*x*y 0.1*math.sqrt(abs(x)) ret (20.0*math.sin(6.0*x*math.pi) 20.0*math.sin(2.0*x*math.pi)) * 2.0/3.0 ret (20.0*math.sin(x*math.pi) 40.0*math.sin(x/3.0*math.pi)) * 2.0/3.0 ret (150.0*math.sin(x/12.0*math.pi) 300.0*math.sin(x/30.0*math.pi)) * 2.0/3.0 return ret这段代码里a和ee是地球椭球参数_transform_lat/_transform_lng是那套公开的偏移多项式。注意它只在国境内有效境外坐标不该套用否则会越转越偏。参数别乱改改了就不是标准 GCJ02 了。2.3 坐标取政府驻地还是几何中心省市区这一级的「经纬度」到底指哪个点业务上要提前说清。常见两种口径一是政府驻地坐标二是行政区域几何中心。做门店选址、分单归属用政府驻地更贴近「这个区在哪」的直觉做地图撒点、区域热力几何中心更均匀。我一般两个都存加个point_type字段区分或者干脆存驻地几何中心按需另算。如果只存一个默认存政府驻地并在文档里写死避免下游各取各的。3. 数据导入与清洗从原始文件到可查询的 MySQL 表3.1 原始数据常见的四种脏法拿到手的省市区数据几乎不会一次干净。我总结过四类高频问题。第一层级错位直辖市下面直接挂区没有「市」这一层或者把「省直辖县级行政区」挂错父级。第二重名全国叫「某城区」「某郊区」的区县不止一个光靠名字查会返回多条。第三编码不一致有的用 6 位国标有的带统计用区划代码后缀。第四坐标缺失或明显错误比如经纬度写反、落在境外、精度只有两位小数。清洗的核心原则是以行政区划编码为主键锚点名字只做展示和模糊搜索。编码是唯一的名字不是。import csv import pymysql # 假设原始文件是 csv列code,name,level,parent_code,lng,lat def load_region(csv_path): conn pymysql.connect(host127.0.0.1, userroot, passwordyourpass, dbgeo, charsetutf8mb4) cur conn.cursor() rows [] with open(csv_path, encodingutf-8) as f: reader csv.DictReader(f) for r in reader: # 坐标合法性校验经度 -180~180纬度 -90~90 try: lng float(r[lng]) if r[lng] else None lat float(r[lat]) if r[lat] else None except ValueError: lng lat None if lng is not None and not (-180 lng 180): lng None if lat is not None and not (-90 lat 90): lat None rows.append(( int(r[code]), int(r[parent_code] or 0), r[name].strip(), int(r[level]), r[code], lng, lat )) sql (INSERT INTO region (id,parent_id,name,level,code,lng,lat) VALUES (%s,%s,%s,%s,%s,%s,%s) ON DUPLICATE KEY UPDATE nameVALUES(name), lngVALUES(lng), latVALUES(lat)) cur.executemany(sql, rows) conn.commit() cur.close() conn.close()逻辑上做了三件事坐标越界直接置空而不是丢弃整行保证层级结构完整用ON DUPLICATE KEY UPDATE支持重复导入时更新而非报错id直接用编码省得再维护一套自增和编码的映射。参数上executemany批量提交几万行数据一次跑完别一行一条 insert慢得让人怀疑人生。3.2 用 LOAD DATA 加速大批量导入如果数据量到几十万行比如细到街道Python 逐行还是慢直接上 MySQL 的LOAD DATA LOCAL INFILE。LOAD DATA LOCAL INFILE /tmp/region.csv INTO TABLE region FIELDS TERMINATED BY , ENCLOSED BY LINES TERMINATED BY \n IGNORE 1 LINES (id, parent_id, name, level, code, lng, lat) SET lng NULLIF(lng, ), lat NULLIF(lat, );FIELDS TERMINATED BY和ENCLOSED BY要和导出文件严格一致否则字段会串位。SET子句里把空字符串转成 NULL避免DECIMAL字段插入空串报错。注意LOCAL关键字需要客户端开启local_infile服务端也要放行生产环境按安全策略决定是否开。3.3 导入后必做的三条校验导完别急着用先跑三条 SQL 验一验。第一条查孤儿节点parent_id指向的 id 不存在SELECT c.id, c.name, c.parent_id FROM region c LEFT JOIN region p ON c.parent_id p.id WHERE c.parent_id 0 AND p.id IS NULL;第二条查坐标缺失比例按层级分组SELECT level, COUNT(*) AS total, SUM(lng IS NULL OR lat IS NULL) AS missing FROM region GROUP BY level;第三条查重名同一层级同名但编码不同的记录SELECT name, level, COUNT(*) AS cnt FROM region GROUP BY name, level HAVING cnt 1 ORDER BY cnt DESC;重名不一定是错但必须心里有数因为下游按名字查的时候会踩到。4. 查询与距离计算按名字查、按坐标反查、算附近4.1 按名字模糊查要防重名最常见的查询是「用户输入一个区县名返回它的坐标」。直接WHERE name ?在重名时会返回多条前端拿到就懵了。稳妥做法是带上层级和父级过滤或者返回列表让用户选。-- 按名字查带层级返回可能的多条 SELECT id, name, level, parent_id, lng, lat FROM region WHERE name LIKE CONCAT(?, %) AND level 3 LIMIT 20;LIKE CONCAT(?, %)是前缀匹配能走idx_level_name索引如果写成LIKE %?%就全表扫了。参数level由调用方指定查区县传 3查市传 2。返回多条时前端做二级选择别在后端硬猜。4.2 按坐标反查最近区县给一个经纬度想知道落在哪个区县最朴素的做法是算到所有区县驻地的距离取最小。数据量几千条时完全够用但要注意别在 SQL 里逐行算三角函数那样用不上索引。-- 先用外接矩形粗筛再在应用层精算距离 SELECT id, name, lng, lat, 6371000 * 2 * ASIN(SQRT( POWER(SIN((? - lat) * PI() / 360), 2) COS(? * PI() / 180) * COS(lat * PI() / 180) * POWER(SIN((? - lng) * PI() / 360), 2) )) AS distance_m FROM region WHERE level 3 AND lat BETWEEN ? - 0.5 AND ? 0.5 AND lng BETWEEN ? - 0.5 AND ? 0.5 ORDER BY distance_m LIMIT 1;这里6371000是地球平均半径米ASIN(SQRT(...))是 haversine 公式的等价写法。BETWEEN那两行是外接矩形粗筛0.5 度大约 55 公里能把候选集从几千压到几十再精算。参数顺序要和占位符一一对应写错了距离会算成天文数字。注意这只是「最近驻地」不是「行政边界归属」真要判断点在哪个多边形内得上空间索引或 GeoJSON 边界数据那是另一个量级的事。4.3 算两个区县之间的距离分单场景经常要算两个区县驻地之间的直线距离用来估运费或时效。SELECT 6371000 * 2 * ASIN(SQRT( POWER(SIN((b.lat - a.lat) * PI() / 360), 2) COS(a.lat * PI() / 180) * COS(b.lat * PI() / 180) * POWER(SIN((b.lng - a.lng) * PI() / 360), 2) )) AS distance_m FROM region a, region b WHERE a.code ? AND b.code ?;a和b是同一张表的两个别名用编码定位。结果是米除以 1000 得公里。直线距离不等于实际路程做运费估算时通常再乘一个 1.2 到 1.4 的绕路系数具体看业务。5. 避坑与排查经纬度数据落地最常见的五个问题5.1 现象附近查询结果忽远忽近同一地址两次算出来不一样原因基本是坐标系混用。库里存了 WGS84前端展示时套了 GCJ02 的底图用户看到的点和实际算距离的点差了偏移量。解决全链路统一坐标系入库 WGS84展示层转换距离计算永远用库里的原始值别拿转换后的值去算。5.2 现象按区县名查询返回空但数据明明在多半是名字带了后缀差异比如库里存「某区」用户输入「某区」带空格或者存的是全称「某某区」而输入是简称。解决入库时同时存name和short_name查询时对输入做TRIM并同时匹配两个字段必要时加拼音字段兜底。5.3 现象导入报错Incorrect decimal value原始文件里经纬度是空字符串或-这类占位符直接插DECIMAL会报错。解决导入前统一清洗空值转 NULL非法值置 NULL别指望 MySQL 帮你兜。用LOAD DATA时在SET子句里做NULLIF。5.4 现象距离算出来是负数或几万公里haversine 公式里经纬度单位是度但SIN/COS要弧度忘了乘PI()/180就会算出离谱结果。另一个常见错是经纬度传反把纬度当经度传进去。解决封装成函数或视图参数顺序固定调用方别自己拼公式。5.5 现象行政区划调整后数据对不上撤县设区、编码变更后老数据还在库里新数据导入变成两条。解决以编码为唯一键调整时更新而非新增保留一个is_active字段标记失效记录查询默认过滤历史订单仍能关联到旧记录。6. 进阶用生成列和空间索引把附近查询压到毫秒级前面那套外接矩形加 haversine 的写法在几千条区县数据上够用但如果你的表细到街道、乡镇或者要频繁做「附近 N 公里」查询就该上 MySQL 的空间能力了。MySQL 5.7 以后支持POINT类型和SPATIAL INDEX配合生成列能把坐标查询压到毫秒级。思路是加一个POINT类型的生成列由lng/lat自动算出再在它上面建空间索引。ALTER TABLE region ADD COLUMN geo_point POINT GENERATED ALWAYS AS (ST_SRID(POINT(lng, lat), 4326)) STORED, ADD SPATIAL INDEX idx_geo (geo_point);ST_SRID(..., 4326)指定 WGS84 坐标系STORED表示物化存储空间索引必须建在STORED生成列上。建好后附近查询可以先用ST_Distance_Sphere配合MBRContains粗筛SET center ST_SRID(POINT(?, ?), 4326); SET box ST_MakeEnvelope( ST_Longitude(center) - 0.1, ST_Latitude(center) - 0.1, ST_Longitude(center) 0.1, ST_Latitude(center) 0.1 ); SELECT id, name, ST_Distance_Sphere(geo_point, center) AS distance_m FROM region WHERE level 3 AND MBRContains(box, geo_point) ORDER BY distance_m LIMIT 10;ST_MakeEnvelope造一个矩形框MBRContains走空间索引快速过滤ST_Distance_Sphere算球面距离单位是米。0.1 度大约 11 公里按你的查询半径调整。这套写法比手写 haversine 快代码也更干净代价是生成列占一点存储、空间索引对写入有一点开销静态字典数据完全值得。一个我踩过的坑ST_SRID的坐标系编号别乱填4326 是 WGS84填错了ST_Distance_Sphere结果会偏。另外POINT(lng, lat)的顺序是经度在前、纬度在后和很多人直觉相反写反了索引照样建但查出来全是错的而且不报错属于典型的黑匣子问题。我的习惯是建完索引先拿一个已知坐标跑一遍确认距离在合理范围再用。最后说个验证技巧拿几个你熟悉的城市比如自己所在区县的驻地坐标手动算一下到邻市的直线距离和地图上量出来的对一对误差在 1% 以内就说明坐标系和公式都没问题。这个后悔药比上线后才发现偏移几百米强得多。希望帮到你。本文还有配套的精品资源点击获取

全国省市区经纬度MySQL数据建模与查询优化实战

相关推荐

Python变量与运算符深度解析：从内存绑定到实战避坑

智谱GLM-5发布：开源最强，中国芯适配，编程对齐Claude Opus 4.5，TaoToken统一Key接入实测

C++手写DBMS内核：从B+树到TPC-C的完整实现路径

更多资讯

【单线图的系统级微电网仿真】基于 PQ 的可再生能源和柴油发电机组微电网仿真附Simulink仿真

【轮式机器人惯性导航系统INS】路面倾斜角（Wheel-INS估计的机器人横滚角镜像）作为地形特征，粒子滤波器实现环路闭合附Matlab代码

C#通过OPC读取WinCC数据：从DCOM配置到订阅采集实战

curl库32位bin选型与集成：从DLL依赖到HTTPS证书避坑指南

Container Lines 技能实战：用垂直容器边界线与角落小方块构建结构化网页布局

Oracle 19c Solaris x86 客户端 home 部署与连接实战

今日更新

再见，SSE！你好，Streamable HTTP！轻松开发 Streamable HTTP MCP Server 并接入 TaoToken

工业软件标准化路线图：国产替代的落地施工图

VCMI安卓版实操指南：原生运行英雄无敌3的3步技术落地

本周精选

CMOS设计三大物理效应：体效应、沟道长度调制与亚阈值导电

微信小程序在线阅读毕设全解析：Java后端+MySQL+避坑实战

Android音频问题五层分析法：从App到Codec的系统化诊断

本月热门

我发现了一个新思路：用 Remotion + Claude Code 像写代码一样自动化生成短视频

Windows下 Codex 中 Chrome 和 Computer Use 插件不可用问题排查及解决参考方式：TaoToken 统一 Key 配置与验证

2026 大模型集体涨价：用 Python 做企业 Token 成本测算与选型避坑（附配置）

读完文章，想聊聊您的网站？