MySQL美国城市数据建模：从清洗到空间查询的完整指南

简介这是一份面向开发者的美国城市地区MySQL数据库资源收录美国50个州及华盛顿特区共4.3万余条城市与地区明细涵盖城市名称、邮政编码、经纬度、人口统计与行政区划等信息可直接用于地图服务、房地产平台、物流配送及市场分析等场景的数据查询与业务开发。包内涵盖2个文件SQL脚本为完整的建表与数据填充脚本在本地MySQL环境执行即可批量导入并重建全部表结构TXT说明文档则针对字段含义、数据来源、导入步骤与使用注意事项作了详细说明方便用户对照操作。压缩包约378KB轻量易迁移。已有1977人学习下载对于需要处理美国地理信息或希望通过真实数据集练习SQL查询、关系型建模的开发者能省去大量自行采集与清洗数据的时间是一份可直接落地的实用资源。1. 美国城市地区MySQL数据库拿真实城市数据建模前先想清楚的五件事拿到一堆美国城市地区的数据第一反应往往是建几张表塞进MySQL就完事。真正动手后你会发现卡住你的根本不是SQL而是数据本身的口径问题同一个城市名在不同州反复出现经纬度有的带负号有的不带人口字段里混着10,235这种带逗号的字符串更麻烦的是你根本不知道哪些城市数据能信、哪些早就过期了。本文要讲的是如何用MySQL把这堆看起来规整、实际到处是坑的美国城市地区数据建成一个能支撑地理查询、能做聚合统计、不会在数据量上去之后性能崩掉的数据库方案。适合正在为城市数据分析、地图可视化、区域运营报表搭建底层数据的人阅读也适合准备把本地城市数据迁到MySQL的团队参考——这套建模思路不依赖任何特定数据集拿你自己手里的城市清单也能直接套。2. 先说清楚建模思路一张城市主表不够为什么还要拆分2.1 美国城市数据区别于一般行政区数据的三层结构美国城市地区的数据天然存在三层行政区划关系州、县、市。很多拿到数据的同学第一件事就是把州名、县名、城市名全塞进一张大宽表里美其名曰查询方便实际用起来全是灾难。为什么因为美国有大量重名城市。我处理过的数据里Springfield在不同州出现不下三十次Portland在东西海岸各有一个更不用提那些县和市同名的情况。如果你不把行政区划拆开单靠城市名去关联业务表轻则数据错位重则统计报表直接翻车。我的建议是三张核心表州表state、县表county、城市表city县表带州ID外键城市表带县ID外键。这样做的直接好处有两个。第一查询时可以沿着州到县到市的层级做钻取不需要靠字符串匹配去猜归属关系第二重名城市可以通过上级行政区划来唯一锁定不会出现关联错乱。至于为什么要保留县这一级而不是直接从州跳到市——因为美国很多统计口径是按县发布的你后续如果要把人口、经济、气候数据join进来县这一级是你最常遇到的对齐粒度。2.2 空间字段还是普通字段经纬度到底该怎么存这是建模时第一个绕不开的选型问题。MySQL从 5.7 开始原生支持空间数据类型提供了 POINT 类型和对应的空间索引也提供了 ST_Distance、ST_Contains 这类空间计算函数。但实际落地时我的判断标准很简单如果只是展示地图打点、按城市名查询经纬度用两个 DECIMAL 字段存 latitude 和 longitude 完全够用查询快、导入方便、排查数据一眼就能看懂如果后续要做距离我3公里内的城市有哪些这类空间范围查询或者要做多边形围栏判断那就必须上 POINT 类型加空间索引否则全表扫描加Haversine公式计算数据量一过十万条就明显变慢。这里还涉及一个坐标系的选择问题。我习惯用 SRID 4326也就是 WGS84 坐标系因为绝大多数公开的美国城市数据源给的经纬度就是WGS84不需要做坐标系转换。你们内部如果有自己的底图且用投影坐标系那导入时需要做转换这一步千万不要图省事跳过否则后面做空间计算时结果会偏差到完全没法用。空间字段不是越多越好一张表有一个 POINT 字段用于核心查询就够了有的人习惯再单独存两个 DECIMAL 字段用于直观展示这属于冗余我的建议是不要因为数据同步时很容易出现两边不一致。2.3 一个推荐的建表设计从零搭建三张核心表下面给一个我常用的建表模板你们可以按自己的业务字段做增减。首先是州表CREATE TABLE state ( state_id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, state_code CHAR(2) NOT NULL COMMENT 州缩写如 CA、NY, state_name VARCHAR(50) NOT NULL COMMENT 州全称, region VARCHAR(30) DEFAULT NULL COMMENT 所属大区如 West、South, UNIQUE KEY uk_state_code (state_code) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT美国州维度表;然后是县表CREATE TABLE county ( county_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, state_id SMALLINT UNSIGNED NOT NULL, county_name VARCHAR(100) NOT NULL, county_fips VARCHAR(5) DEFAULT NULL COMMENT FIPS编码5位数字, UNIQUE KEY uk_county (state_id, county_name), CONSTRAINT fk_county_state FOREIGN KEY (state_id) REFERENCES state (state_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT县维度表;最后是城市表也是业务上直接关联最多的那张表CREATE TABLE city ( city_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, county_id INT UNSIGNED NOT NULL, city_name VARCHAR(100) NOT NULL COMMENT 城市名不含州后缀, city_name_ascii VARCHAR(100) GENERATED ALWAYS AS (city_name) STORED, latitude DECIMAL(8, 5) DEFAULT NULL COMMENT 纬度范围-90到90, longitude DECIMAL(8, 5) DEFAULT NULL COMMENT 经度范围-180到180, population INT UNSIGNED DEFAULT NULL COMMENT 人口注意可能滞后, timezone VARCHAR(50) DEFAULT NULL COMMENT IANA时区如 America/New_York, geom_point POINT SRID 4326 DEFAULT NULL COMMENT 空间字段仅需要空间查询时启用, SPATIAL KEY idx_geom (geom_point), KEY idx_city_name (city_name), CONSTRAINT fk_city_county FOREIGN KEY (county_id) REFERENCES county (county_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT城市维度表;这里有一个容易被忽略的参数需要解释DECIMAL(8, 5) 的精度设计。纬度范围是 -90 到 90整数部分最宽 2 位加上符号5 位小数对应约 1.1 米的精度这个精度对城市级的地理查询绰绰有余。如果你用 FLOAT 存经纬度导入倒是省事但浮点数的底层误差会导致你做空间计算时出现细微偏移数据对齐时定位问题会非常痛苦。另一个点是 timezone 字段建议直接存 IANA 时区名而不是 UTC 偏移量因为美国有夏令时偏移量会随季节变化存名称才是正确的做法。city_name_ascii 字段是给后续可能做英文模糊搜索预留的如果你的数据源里城市名带有特殊字符这个字段会很有用。3. 数据从零到入库清洗、装载、验收一气呵成3.1 拿到一份城市CSV后先做结构审查再做过滤拿到一份美国城市地区的CSV不要急着导入MySQL我见过太多人是直接 LOAD DATA 然后发现库里一堆乱数据再回头洗。常见的数据源给你的是这样一个结构城市名、州缩写、县名、纬度、经度、人口、有时还带ZIP Code。第一件事不是写导入脚本而是打开文件看三样东西表头有没有不可见字符、分隔符到底是逗号还是分号、有没有引号包裹的字段。这三个细节决定了你 LOAD DATA 的语法怎么写也决定了你会不会在第一轮导入时就报错。接下来是我强烈建议的一个过滤步骤检查城市数据的时空一致性。什么叫时空一致性就是经纬度是否落在该州的实际边界范围内。这一步可以在清洗脚本里做粗略的判断比如同一个州代码下所有城市的纬度应该落在相近的区间内如果有人把夏威夷的经纬度错标到了某个中西部州的代码下一眼就能看出来。开源数据经常有这种低级错误之前我处理某份数据时发现有一部分城市的经纬度全部被整体偏移了约 0.1 度原因就是数据生产方在拼接时错位了行列这种问题靠肉眼看不出来靠空间范围过滤才能拦住。3.2 用Python清洗并用LOAD DATA批量导入一条命令代替逐行INSERT清洗完的数据最好落成一份标准化的CSV再做批量导入。逐行 INSERT 不要考虑几万条城市数据逐行插入的时间足够你去喝两杯咖啡了。MySQL 的 LOAD DATA 语句在这个场景下是最合适的选择。下面是一个清洗和导入的完整方案先用Python把原始数据处理成目标格式再用MySQL的LOAD DATA导入。import csv import re raw_path raw_us_cities.csv clean_path clean_us_cities.csv with open(raw_path, r, encodingutf-8-sig) as fin, \ open(clean_path, w, encodingutf-8) as fout: reader csv.DictReader(fin) fieldnames [state_code, county_name, city_name, latitude, longitude, population, timezone] writer csv.DictWriter(fout, fieldnamesfieldnames) writer.writeheader() for row in reader: # 基础字段校验 if not re.fullmatch(r[A-Z]{2}, row[state].strip().upper()): continue # 州代码必须是两位字母 lat float(row[latitude]) lon float(row[longitude]) if not (-90 lat 90 and -180 lon 180): continue # 非法经纬度直接丢弃 # 人口字段里的逗号去除 pop_raw row[population].strip().replace(,, ) pop int(pop_raw) if pop_raw.isdigit() else None writer.writerow({ state_code: row[state].strip().upper(), county_name: row[county].strip(), city_name: row[city].strip(), latitude: f{lat:.5f}, longitude: f{lon:.5f}, population: pop if pop is not None else , timezone: row[timezone].strip() if timezone in row else , })这段Python脚本里的几个过滤条件你不要觉得多余。state 字段用正则强制两位大写字母拦截掉那些带空格、带数字的脏数据经纬度范围校验是最基础的物理约束能把数据坐标写反、写越界的脏数据挡在库外人口字段去掉逗号是必须的否则MySQL里字符串转整数时会报错或得到0。跑完脚本后检查输出的 clean_us_cities.csv 行数如果和源数据行数差异过大说明你的源数据质量堪忧需要回到前一步看具体是哪些行被过滤了而不是直接改脚本放行脏数据——放行一时爽后面排查火葬场。清洗完成之后执行导入LOAD DATA LOCAL INFILE /data/clean_us_cities.csv INTO TABLE city FIELDS TERMINATED BY , OPTIONALLY ENCLOSED BY LINES TERMINATED BY \n IGNORE 1 LINES (state_code, county_name, city_name, latitude, longitude, population, timezone)但这里有一个问题city 表的结构是 county_id 外键CSV里只有州代码和县名没有对应ID。直接把CSV装载进去会外键报错。正确做法是先导入州表和县表再通过SQL把CSV临时数据映射成ID导入常见做法是装载到一个临时表再用 INSERT INTO city SELECT 语句做关联映射。这一步很多人容易卡住我的建议是在清洗脚本里直接生成州和县的字典映射先把两级维度数据用脚本单独输出成两个CSV依次导入后再处理城市表。3.3 装载后的数据验收清单四个必查项导入完成不等于数据可用我每次装载后都会跑一组验收查询确认数据是可信的。第一项是查重城市表里不该出现同一个县下完全同名的城市记录第二项是查空值密度经纬度或人口为空的记录比例不能超过某个阈值比如百分之五第三项是查层级完整性每一条城市记录必须能关联到一个县每个县必须关联到一个州第四项是抽样人工核对抽三五个你知道准确答案的城市肉眼核对经纬度是否与公开地图一致。这四项全过这套数据才能进入业务查询阶段。这组查询写起来不难但它是防止脏数据污染上层报表的关键防线建议你们不管数据量大小都跑一遍。4. 城市数据的空间查询从按名查到按距离查4.1 业务查询和空间查询分开走什么时候走空间索引有了稳定的库表结构下一步就是让数据真正产生价值。城市数据库的典型业务查询一般是三类按城市名精确查一个城市的经纬度、按州统计城市数量和人口总量、按给定经纬度找最近的N个城市。前两类走普通索引就能搞定不涉及空间计算第三类是空间查询是选择POINT字段和空间索引的核心动机。必须强调一下如果你的业务只停留在前两类那之前建的空间字段就是纯浪费你完全可以用 DECIMAL 方案。空间查询真正发力的场景是给定一个点找出附近的城市或者给定一个范围找出范围内的所有城市这是非空间索引方案很难高效做到的。4.2 用 ST_Distance_Sphere 实现最近城市搜索一条SQL解决从 MySQL 5.7 开始我们可以用 ST_Distance_Sphere 计算球面距离结果单位是米不需要自己去实现Haversine公式。这是一个我非常推荐的函数精度足够且写法简洁。下面是一条典型的最近城市查询SQLSELECT c.city_name, s.state_code, ROUND(ST_Distance_Sphere(c.geom_point, ST_SRID(POINT(-122.4194, 37.7749), 4326)) / 1000, 1) AS distance_km FROM city c JOIN county ct ON c.county_id ct.county_id JOIN state s ON ct.state_id s.state_id WHERE c.geom_point IS NOT NULL ORDER BY distance_km ASC LIMIT 10;这条SQL的逻辑是以旧金山某点的经纬度为圆心遍历城市表中所有带坐标的城市用 ST_Distance_Sphere 算出球面距离按距离升序取前十名。参数上要注意三个点第一ST_SRID 函数把构造出的 POINT 声明为 SRID 4326如果不加这一步MySQL 会把它当成 SRID 0 来处理空间索引匹配不上查询直接退化为全表扫描第二关于半径筛选建议在 ORDER BY 之前加一层 WHERE 过滤——但注意 ST_Distance_Sphere 是函数包裹列这本身会让索引失效所以如果你的城市表有几十万条数据这类查询应该加一个包围盒预过滤用经纬度范围先粗筛一遍再算精确距离这是空间查询性能优化的核心思路第三如果要限定查找范围在某个州内把州筛选条件放到最里层子查询中先缩小数据集能省掉大量无效计算。切记空间索引用的是 R-Tree 结构它的弹性和普通 BTree 完全不同一个函数包住字段就会让索引彻底失效SQL 的性能就看你有没有把粗筛和精算分开。4.3 使用 MBRContains 做范围圈选多边形里到底有哪些城市另一类高频需求是给定一个矩形范围或者不规则多边形找出落在其中的城市。MBRContains 处理的是矩形范围ST_Contains 处理的是多边形范围。实际项目中矩形范围最常见比如地图拉框选区域、后台按边界圈选城市群。下面是一个用 MBRContains 实现的矩形范围查询SELECT c.city_name, c.latitude, c.longitude FROM city c WHERE MBRContains( ST_SRID( ST_GeomFromText(POLYGON(( -122.5 37.7, -121.5 37.7, -121.5 38.1, -122.5 38.1, -122.5 37.7))), 4326), c.geom_point );这个SQL里最容易被忽视的是多边形闭合规则第一个点和最后一个点必须相同否则 MySQL 不会直接报错而是返回空结果集。另外MBRContains 的边界判断是包含边界的如果你的业务要求严格排除落在边界上的点需要用 ST_Within 而不是 MBRContains。实际使用中还有一个性能调优点如果矩形范围特别大比如覆盖了半个美国这条 SQL 即使走了空间索引也会扫描掉大量数据。这时候应该先用 state 表做主级过滤比如先限定了几个州再在候选集上做空间范围匹配整体响应时间可以下降一个数量级。不要因为有了空间索引就忽视常规字段的过滤空间索引不是银弹两层索引结合使用才是正确姿势。5. 避坑指南美国城市数据入MySQL的六个常见翻车现场5.1 经纬度精度和坐标系不一致导致的空间错位现象地图上打点时城市位置偏移了个几十公里肉眼可见的不对。原因数据源A用的是WGS84数据源B用的是NAD83两者在大部分区域差别不大但在某些高纬度区域偏移明显还有一种情况是某份数据把经纬度写反了比如把纬度写到了经度列里。解决导入前必须确认数据源的坐标系说明没有说明的原宁可不用也不要猜写反经纬度的数据用经度范围应该在-180到-120或-70附近这个常识就能筛出大部分异常更稳妥的做法是在验收环节随机抽城市坐标和公开逆向地理编码结果比对偏差超过一定阈值直接废弃该批次数据。5.2 城市重名不加行政区划限定导致关联错乱现象关联城市维度到业务表后部分记录的州归属和预期不符。原因城市表里存在大量跨州重名比如某个城市名在加州、德州、宾州都有你按城市名关联时MySQL取了其中任意一条业务数据就错位了。解决关联必须走 city_id 外键而不是城市名。如果在业务系统里不得不按城市名和州名组合查询务必在城市表上建立 (state_code, city_name) 的联合唯一索引让数据模型从根源上约束重名问题。5.3 LOAD DATA 导入时外键约束突然报错现象导入城市表时报外键约束失败原因是 county_id 对应不上。原因CSV里的县名往往没法直接映射到 county 表里的ID因为县名在不同州会重复比如有上百个县都叫Washington County。解决正确顺序是先装载州表再装载县表最后装载城市表在城市表装载时用 INSERT INTO city SELECT 从临时表关联县表关联条件用 (state_code, county_name) 双字段匹配不要在脚本里手工指定一个不存在的县ID更不要为了省事临时关掉外键检查。5.4 ST_Distance_Sphere 返回结果相差千里现象两个明显相邻的城市用 ST_Distance_Sphere 算出来的距离是几千公里。原因POINT 构造时没有声明 SRID坐标系单位被MySQL当成了平面坐标来运算或者在构造 POINT 时把经纬度参数顺序写反了。解决构造空间对象时统一用 ST_SRID(POINT(lon, lat), 4326) 包裹注意是经度在前、纬度在后这个顺序和人们日常说的纬度经度相反极容易搞混。顺带提一句MySQL 的 POINT(longitude, latitude) 是 X 代表经度、Y 代表纬度凡是看到结果不对的第一反应就检查这个顺序。5.5 人口等数值字段混入字符串造成统计翻车现象SUM(population) 的结果明显偏小或者直接报错。原因原始数据里的数字字段带了千位分隔符、百分号或货币符号导入时MySQL按字符串接收后隐式转换部分无法转换的记录直接变成0。解决清洗阶段统一用正则把非数字字符剔除导入后用 SELECT COUNT(*) FROM city WHERE population 0 这类查询做全面体检后续增量导入时在装载SQL中加一个 CHECK 约束从数据库层面卡住非法数据防止脏数据从ETL链路溜进来。5.6 空间索引失效导致查询越来越慢现象数据量从几万涨到几十万后同样一条空间查询从毫秒级劣化到了秒级。原因最常见的两类——第一查询SQL里用了函数包裹空间字段比如 ST_X(geom_point) 直接作为WHERE条件R-Tree索引失效第二空间字段允许NULL且有大量NULL值索引选择性下降。解决在查询SQL中用 MBRContains 这类函数直接作用于空间字段本身不要在字段上套函数做计算后再参与过滤建表时给空间字段设置 NOT NULL导入时无法提供坐标的记录不进城市主表而是进一个单独的待补全表靠定时任务巡检补数。6. 进阶实践把空间索引和分区表配合让城市数据查询从秒级进到毫秒级当城市表的行数从几万级增长到百万级或者你要支撑的查询频繁到秒级响应都不够用时前面讲的方案就需要再做一次升级。我通常会在两个方向上做优化一是分区表二是空间索引的进一步调整很多同学以为空间索引建了就万事大吉实际上它的性能边界很快就到。先说分区。美国城市数据天然适合按区域分区但如果你把 chunk 设得太大分区优势就没意义了。常见的做法是按州或按大区做 RANGE 分区但这里有个细节分区列必须是主键的一部分所以 city 表的主键需要从单独的 city_id 改成 (city_id, state_id) 联合主键。这样改造后查询如果带上了 state_id 条件MySQL 的分区裁剪机制会直接跳过无关分区扫描的数据量大幅减少。空间索引再加在这个分区表上效果不是叠加而是相乘——默认规避了跨区的大范围扫描空间查询只在目标分区内执行响应时间能快一个量级。再配合一个覆盖索引的战略。虽然空间索引是R-Tree但如果你高频执行的是按州统计城市数量这类聚合查询MySQL 仍然会走空间索引加回表的路径成本也不低。这时候应该给 (state_id, city_name) 建一个普通覆盖索引让聚合查询完全走索引不回表。空间查询和普通聚合查询的服务路径完全不同不要指望一个空间索引扛住所有业务。最后我强烈建议把高频的空间查询封装成视图必要的话做成存储过程。视图能稳定你的查询计划存储过程能让你把上面的优化手段固化下来业务方只需要调用不需要理解空间索引和分区裁剪。封装完之后把执行计划打出来 EXPLAIN ANALYZE 确认走了预期的索引和分区而不是看着快就觉得没问题。我自己的习惯是每做一个优化都保留优化前后两次查询的耗时记录这样下次调整有据可依不会凭感觉改参数。走完这套从建表、清洗、导入到空间优化的流程你会发现美国城市数据用MySQL做底层其实是件相当顺手的事前提是你尊重数据的边界、坐标系和层级关系。做数据的人如果在这类细节上偷懒后面迟早要花十倍时间还债。希望这套实践步骤和避坑经验能真正帮到正在搭建区域数据底座的你。本文还有配套的精品资源点击获取