全国省市区经纬度MySQL数据导入与LBS查询实战

简介这份 MySQL 数据资源面向需要省、市、区三级行政区划经纬度坐标的开发者与数据分析人员可用于地图打点、区域聚合统计、地址解析、物流配送范围计算等场景帮助省去逐条采集与清洗地理坐标的繁琐工作。压缩包内共 1 个文件为单个 sql 脚本整体约 127KB导入后即可直接建表并批量写入省市区记录字段结构清晰便于按层级关联查询或与业务表做联表匹配。目前已有 2974 人学习下载说明其在同类数据中具备一定参考价值。数据覆盖全国范围的省、市、区三级划分并附带对应经纬度信息适合作为基础地理字典表长期复用也能为可视化大屏、区域热力图、门店选址分析等提供底层坐标支撑减少重复造轮子的时间成本。1. 全国省市区经纬度 MySQL 数据一份能直接跑进业务库的行政区划底表做 LBS 相关业务的人迟早会撞上一件事用户下单要按「省市区」聚合配送范围要按「区」算距离后台报表要按「市」画热力图结果翻遍项目里只有一张user_address表省市区全是用户手填的字符串同一个「杭州市」能写出七八种写法。这时候你需要的不是继续在业务表里GROUP BY而是一份干净的、带经纬度的全国省市区行政区划底表。这次拆的这份资源就是一份可以直接导入 MySQL 的全国省市区经纬度数据。它的核心价值在于三点层级完整省—市—区三级、带中心点经纬度能直接算距离、做聚合、结构适配 MySQL建表语句和字段类型都是现成的。适合谁用做电商配送、门店选址、本地生活、数据看板、用户地域分析的开发者尤其是那种「不想接第三方地图 API 的行政区划接口只想在本地库里 join 一下」的场景。下面我按「先看结构、再导入、再查询、最后避坑」的顺序把这份数据怎么落地讲透。2. 先看清表结构和字段语义别急着 INSERT拿到一份 SQL 或 CSV 数据最忌讳的就是直接source进去。先搞清楚它有几张表、字段是什么类型、经纬度是哪个坐标系后面能省掉大量返工。2.1 三级行政区划的典型表设计全国省市区数据最常见的组织方式有两种单表自关联一张表用parent_id串起三级和分表存储省、市、区各一张表。这份资源走的是单表方案字段大致如下字段名类型含义备注idINT / BIGINT主键自增或业务编码parent_idINT父级 ID省级为 0 或 NULLnameVARCHAR(50)行政区名称如「杭州市」levelTINYINT层级1 省 / 2 市 / 3 区lngDECIMAL(10,6)经度中心点latDECIMAL(10,6)纬度中心点codeVARCHAR(12)行政区划代码如 330100这里有两个参数值得单独说。第一是经纬度用DECIMAL(10,6)而不是FLOAT因为浮点数在WHERE lng xxx这种等值查询里会出现精度玄学DECIMAL定点存储更稳。第二是level字段它决定了你查询时怎么过滤没有这个字段你就得靠parent_id递归写起来很别扭。提示如果你的业务只需要省和市导入后可以只保留level 2的数据能显著减小表体积查询也更快。2.2 经纬度坐标系先确认是 WGS84 还是 GCJ02这是最容易翻车的地方。国内地图服务用的坐标系不统一GPS 原始数据是 WGS84而很多地图展示用的是经过偏移的坐标系。如果你拿这份数据的经纬度去和地图 SDK 返回的坐标做距离计算坐标系不一致会导致几百米的偏差。判断方法很简单随便挑一个你熟悉的城市中心点比如某市市政府所在地把数据里的经纬度和地图上拾取的坐标对比。如果差了几百米基本就是坐标系不同。常见做法是数据入库时统一存一种坐标系在应用层做转换而不是在数据库里存两套。-- 建表时给经纬度留足精度并加上联合索引 CREATE TABLE region ( id INT NOT NULL AUTO_INCREMENT, parent_id INT NOT NULL DEFAULT 0, name VARCHAR(50) NOT NULL, level TINYINT NOT NULL COMMENT 1省 2市 3区, lng DECIMAL(10,6) DEFAULT NULL, lat DECIMAL(10,6) DEFAULT NULL, code VARCHAR(12) DEFAULT NULL, PRIMARY KEY (id), KEY idx_parent (parent_id), KEY idx_level (level) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;这段建表语句的关键点utf8mb4保证行政区名称里的生僻字不乱码idx_parent让「查某省下所有市」这种递归查询走得动索引idx_level用于按层级过滤。经纬度允许为 NULL因为部分区级数据可能缺失中心点硬设 NOT NULL 会导致导入失败。3. 导入 MySQL 的完整流程从文件到可查询结构确认完接下来是实操。导入方式取决于你拿到的是 SQL 文件还是 CSV两种都讲一遍。3.1 SQL 文件直接导入如果资源里是.sql文件最省事# 先建库字符集要和表一致 mysql -u root -p -e CREATE DATABASE region_db DEFAULT CHARSET utf8mb4; # 导入注意指定字符集否则中文可能变问号 mysql -u root -p --default-character-setutf8mb4 region_db region.sql # 验证导入行数 mysql -u root -p -e SELECT level, COUNT(*) FROM region_db.region GROUP BY level;逻辑说明--default-character-setutf8mb4这个参数不能省很多人导入后中文全是???就是客户端字符集和表字符集不匹配。最后那条GROUP BY level是快速验证——正常应该看到省级 30 多条、市级 300 多条、区级 2800 条左右数量级不对说明文件被截断了。3.2 CSV 文件用 LOAD DATA 导入如果是 CSV用LOAD DATA INFILE比逐行 INSERT 快一个数量级LOAD DATA LOCAL INFILE /path/to/region.csv INTO TABLE region CHARACTER SET utf8mb4 FIELDS TERMINATED BY , OPTIONALLY ENCLOSED BY LINES TERMINATED BY \n IGNORE 1 LINES (parent_id, name, level, lng, lat, code);参数说明LOCAL关键字允许从客户端读文件如果报The MySQL server is running with --secure-file-priv错误要么把文件放到服务器允许的目录要么加上LOCAL。OPTIONALLY ENCLOSED BY 处理字段里带逗号的情况行政区名称一般不会但code字段有时会被引号包起来。IGNORE 1 LINES跳过表头。注意导入前先SET GLOBAL local_infile 1;否则LOAD DATA LOCAL会被拒绝。这个开关在 MySQL 8.0 默认是关的属于典型的环境坑。3.3 导入后的数据校验导入完别急着用跑几条校验 SQL-- 检查有没有孤儿节点parent_id 指向不存在的记录 SELECT c.* 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(*) FROM region WHERE lng IS NULL OR lat IS NULL GROUP BY level;第一条查的是层级断裂——如果某个区的parent_id指向一个不存在的市你的三级联动就会在这里断掉。第二条查经纬度缺失区级数据缺失中心点是常态心里要有数业务上做好兜底比如用所属市的中心点代替。4. 典型查询场景三级联动、距离计算、区域聚合数据进库了真正体现价值的是查询。下面三个场景基本覆盖了 80% 的 LBS 业务需求。4.1 省市区三级联动查询前端下拉框联动本质是两次按parent_id查询-- 查所有省 SELECT id, name FROM region WHERE level 1 ORDER BY id; -- 根据省 id 查市 SELECT id, name FROM region WHERE parent_id ? AND level 2 ORDER BY id; -- 根据市 id 查区 SELECT id, name FROM region WHERE parent_id ? AND level 3 ORDER BY id;这三条 SQL 走idx_parent索引单次查询在毫秒级。如果并发高常见做法是把整份数据加载进 Redis用region:children:{parentId}做缓存数据库只做兜底。数据量不大三千多条全量缓存内存占用可以忽略。4.2 用经纬度算两点距离有了中心点经纬度就能在 SQL 里直接算距离不用调外部接口-- 计算某用户位置到各区的球面距离单位公里取最近的 5 个 SELECT id, name, 6371 * ACOS( COS(RADIANS(?)) * COS(RADIANS(lat)) * COS(RADIANS(lng) - RADIANS(?)) SIN(RADIANS(?)) * SIN(RADIANS(lat)) ) AS distance_km FROM region WHERE level 3 HAVING distance_km 10 ORDER BY distance_km LIMIT 5;参数说明6371是地球平均半径公里两个?分别是用户纬度和经度。HAVING而不是WHERE因为distance_km是计算列。这个公式是球面余弦定理在几十公里范围内精度够用如果要更精确换成 Haversine 公式。注意这种全表计算无法走索引区级三千条数据没问题数据量再大就得先用经纬度范围粗筛WHERE lng BETWEEN ? AND ?再精算。4.3 按区域聚合业务数据报表场景经常要「按省统计订单量」这时候底表的作用是补全名称SELECT r.name AS province, COUNT(o.id) AS order_cnt FROM orders o JOIN region r ON o.province_id r.id WHERE o.created_at 2024-01-01 GROUP BY r.id, r.name ORDER BY order_cnt DESC;这里的前提是业务表存的是province_id而不是省名字符串。如果历史数据存的是名字就得先做一次名称清洗映射这也是为什么建议新项目一律存 ID。5. 避坑与排查导入和使用中最容易翻车的五件事这一章是我自己踩过和帮别人排查过的真实问题按「现象 → 原因 → 解决」列出来。现象一导入后中文全是问号。原因客户端连接字符集不是 utf8mb4或者建库时用了 latin1。解决建库、建表、连接三处字符集统一成 utf8mb4导入命令加--default-character-setutf8mb4。现象二LOAD DATA LOCAL报错 1148。原因MySQL 8.0 默认关闭local_infile。解决SET GLOBAL local_infile 1;连接串上加allowLoadLocalInfiletrueJDBC 场景。生产环境如果安全策略不允许就改用mysqlimport或分批 INSERT。现象三三级联动到区级查不出来。原因区级数据的parent_id和市级id对不上通常是数据源本身层级编码不一致。解决用第 3.3 节的孤儿节点查询定位然后按code前六位重新关联行政区划代码前两位省、中间两位市、后两位区这个规则很稳。现象四距离计算结果偏差几百米。原因坐标系不一致数据是 WGS84地图 SDK 返回的是偏移后的坐标。解决统一坐标系在应用层做转换别在库里混存。现象五DECIMAL经纬度等值查询查不到。原因导入时精度被截断比如存进去是120.123456查询写120.1234。解决查询用范围BETWEEN而不是等值或者统一保留六位小数。提示每次导入新版本数据前先RENAME TABLE region TO region_bak;留个后悔药验证没问题再删。行政区划每年都有调整数据更新是常态。6. 进阶技巧把底表用出花来的三个习惯数据导入只是起点真正拉开差距的是怎么用它。分享三个我长期养成的习惯。第一个习惯是给经纬度加空间索引。MySQL 支持POINT类型和SPATIAL INDEX把lng、lat合成一个POINT字段后范围查询能走空间索引比全表算距离快得多ALTER TABLE region ADD COLUMN location POINT SRID 4326; UPDATE region SET location ST_GeomFromText(CONCAT(POINT(, lng, , lat, )), 4326); CREATE SPATIAL INDEX idx_location ON region(location); -- 查询某点 10 公里内的区 SELECT id, name FROM region WHERE ST_Distance_Sphere(location, ST_GeomFromText(POINT(120.15 30.28), 4326)) 10000;SRID 4326是 WGS84 的空间参考标识ST_Distance_Sphere直接返回米。这个方案比手写余弦公式干净而且能走索引。第二个习惯是定期核对行政区划变更。每年都有撤县设区、合并乡镇的情况底表不更新业务上就会出现「用户选的区在库里查不到」。我一般每季度拉一次最新数据用code做主键比对只更新变化的行而不是整表重导。第三个习惯是把常用查询封装成视图。比如「省市区全路径」这种需求每次都写递归太累建个视图一劳永逸CREATE VIEW v_region_full AS SELECT c.id AS region_id, c.name AS region_name, p.name AS city_name, g.name AS province_name, c.lng, c.lat FROM region c JOIN region p ON c.parent_id p.id JOIN region g ON p.parent_id g.id WHERE c.level 3;这样业务查询直接SELECT * FROM v_region_full WHERE region_id ?省市区名称一次拿全不用在应用层拼三次查询。从那以后我每次拿到新的行政区划数据都强制走一遍「建表 → 导入 → 孤儿校验 → 坐标系确认 → 视图封装」这五步再急也不跳过校验。希望这份底表能帮你把地域相关的需求一次做扎实帮到你。本文还有配套的精品资源点击获取