尧图网页设计 
简介美国城市数据库压缩包面向需要美国城市地理空间信息的开发者、数据分析师与GIS学习者覆盖地图可视化、位置查询、距离计算、人口统计与商业选址等常见场景。包内共4个文件以SQL数据文件为核心包含城市名称、州名、邮政编码、经纬度等结构化字段另附README说明文档、txt说明文件与LICENSE许可证整体仅560KB体积轻便便于快速导入MySQL、PostgreSQL等数据库也可转为CSV或JSON格式处理并能直接衔接Python、R或GIS工具进行数据清洗与可视化。目前已有49人学习下载。通过导入或解析该数据集使用者可直接获得一套可用、结构清晰的美国城市基础数据省去自行爬取与整理的时间配合文档还能快速掌握字段含义与许可约束适合作为地理数据入门练习也可为区域分析、物流配送或学术研究等项目提供扎实的数据支撑。1. 美国城市SQL存档一个zip包里藏着什么做地图可视化或物流系统时最缺的往往不是工具而是一份干净的城市基础表。这个名为 US-Cities-Database-master.zip 的压缩包有点反直觉解压后没有诱人的CSV或JSON只有一个us_cities.sql文件、一份README和LICENSE。完整数据都藏在这个SQL文件里按城市名、州名、县名、邮编、经纬度等字段组织。适合做GIS演示、区域分析、前端地图点位的初始化数据。对工程师来说导入即可用省去一轮清洗也可以作为SQL地理查询的练习样本。后续就从解压勘察讲起覆盖导入、验证、查询最后用Python把它导出为GeoJSON。2. zip解包与表结构勘察从README到us_cities.sql2.1 先看清单再解压别急着双击拿到zip包的第一反应是双击解压但我建议先列清单unzip -l US-Cities-Database-master.zip列出文件列表而不是立刻释放能确认里面是否存在目录穿越路径或异常大的文件。确认安全后再解压mkdir -p ~/data/us_cities unzip US-Cities-Database-master.zip -d ~/data/us_cities参数说明-d指定目标目录避免文件直接散落在当前目录。如果 zip 内自带US-Cities-Database-master/目录解压后会进入该子目录。若服务器没有 unzip也可以安装或用jar xf代替但 unzip 对普通文本文件最直观。2.2 读README和a.txt确认数据许可与说明解压后第一件事不是导入数据库而是看 README.md 和 a.txt。这两个文件决定你可不可以把数据用于商用项目。head -n 40 US-Cities-Database-master/README.md file US-Cities-Database-master/a.txt US-Cities-Database-master/us_cities.sqlhead查看说明开头file检查文件编码。输出常见情况README.md 记录数据来源、字段含义和更新日期。a.txt 可能是原始下载说明或简短的数据样本。LICENSE 文件决定数据许可例如 CC0、MIT 还是仅研究用途。需要注意编码如果file显示ISO-8859或with BOM导入前应转码否则城市名中的重音字符可能变成乱码。如果 a.txt 里写着下载地址和生成日期说明它就是临时说明文件直接忽略即可。2.3 用grep和awk只看不跑SQL来解析表结构不建议直接执行整个 SQL先看它使用的是 MySQL 还是 PostgreSQL 方言。grep -E CREATE TABLE|CREATE DATABASE|INSERT INTO us_cities.sql | head -20 grep -n CREATE TABLE us_cities.sql第一个命令列出关键语句第二个输出建表语句所在行号。接着看建表语句的完整字段sed -n /CREATE TABLE/,/;/p us_cities.sql | head -30如果看到反引号、ENGINEInnoDB、AUTO_INCREMENT基本可以确定是 MySQL 方言如果看到SERIAL或::则是 PostgreSQL 方言。我还会用 awk 提取字段名awk /CREATE TABLE/{flag1;next} flag/;/{flag0} flag{print} us_cities.sql | grep -o [^]* | tr \n 这段命令把反引号包裹的字段名全部提取到一行。它依赖反引号方言如果 SQL 文件没有反引号可以改用单引号或直接看 sed 输出。这样能在导入前生成字段清单避免导入后才发现字段名与预想不符。2.4 字段字典与数据格式从城市名到经纬度根据摘要和同类城市数据库的常见设计us_cities.sql 的核心字段通常包括字段名类型示例说明idINT/BIGINT1主键cityVARCHAR(100)New York城市名stateVARCHAR(50)New York州全名state_idCHAR(2)NY州缩写countyVARCHAR(100)Queens县或郡zipVARCHAR(5)10001邮政编码latDECIMAL(10,7)/DOUBLE40.7128纬度lngDECIMAL(10,7)/DOUBLE-74.0060经度注意zip应当作为字符串而不应作为整数因为美国邮编存在前导零比如马萨诸塞州的02139。经纬度通常使用十进制数纬度范围是 [-90, 90]经度范围是 [-180, 180]。如果导入后出现大量越界值多半是字符串解析或字符集截断导致需要在导入阶段处理。3. 数据库导入实战让us_cities.sql在MySQL里跑起来3.1 先建库后落表字符集与排序规则的选择导入之前先建立数据库否则重定向导入时会遇到Unknown database错误。CREATE DATABASE IF NOT EXISTS us_cities CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;参数说明utf8mb4能完整覆盖四字节 UTF-8 字符。美国城市名中常见ñ、é等拉丁字母部分社区名称也包含特殊字符用 utf8mb4 最稳妥。utf8mb4_unicode_ci控制字符串比较规则ORDER BY或去重时不会因大小写产生歧义。如果 SQL 文件内部已经包含CREATE DATABASE语句可以跳过这一步否则建议手工建库。3.2 正式导入source与mysql重定向的取舍两种常见方式mysql -u app_user -p -h 127.0.0.1 us_cities us_cities.sql或者进入客户端后执行USE us_cities; SOURCE /path/to/us_cities.sql;参数说明-u指定用户-p提示密码-h指定主机把 SQL 文件重定向给 mysql 客户端。SOURCE适合交互式调试但用重定向可以配合日志做批量排查。导入时如果遇到一堆警告可以用下面的命令显式显示mysql --show-warnings -u root -p us_cities us_cities.sql--show-warnings会在导入结束后列出所有 warning比如Incorrect string value或Data truncated这些警告在大文件导入时容易被淹没显式打开很有必要。如果目标是 PostgreSQL命令对应为psql -d us_cities -U postgres -f us_cities.sql-f与 mysql 的等价但 psql 默认遇到错误会继续执行容易留下一批不完整表。一般会加-v ON_ERROR_STOP1让错误立即停止。提示导入大 SQL 文件时不要用 GUI 工具直接拖拽调试不方便且容易内存溢出命令行重定向更可控。3.3 导入后的完整性验证行数、空值、坐标边界导入完成不等于数据正确。跑一组检查 SQLSELECT COUNT(*) AS total_rows, COUNT(DISTINCT city) AS distinct_cities, SUM(CASE WHEN zip IS NULL THEN 1 ELSE 0 END) AS null_zips, SUM(CASE WHEN lat -90 OR lat 90 THEN 1 ELSE 0 END) AS bad_lats, SUM(CASE WHEN lng -180 OR lng 180 THEN 1 ELSE 0 END) AS bad_lngs FROM us_cities;COUNT(*)是全表行数COUNT(DISTINCT city)统计不重复城市名SUM(CASE WHEN ...)把满足条件的行记为 1 再求和从而统计空邮编、越界坐标的数量。如果bad_lats或bad_lngs不为 0说明导入过程有截断或原始文件本身存在异常。全美城市去重后通常在两万行左右具体行数以实际文件为准。还可以抽查前五行确认字段对应关系SELECT city, state_id, zip, lat, lng FROM us_cities LIMIT 5;3.4 常见报错与排查从语法错误到数据截断导入失败有几种高频错ERROR 1366 (HY000): Incorrect string value ERROR 1136 (21S01): Column count doesnt match value count ERROR 1064 (42000): You have an error in your SQL syntaxIncorrect string value说明 SQL 文件编码与目标库字符集不一致。常见做法是先转码再导入iconv -f latin1 -t utf8 us_cities.sql us_cities_utf8.sql mysql -u root -p us_cities us_cities_utf8.sqlColumn count doesnt match说明 INSERT 语句的列数与 VALUES 数量不匹配。此时查看报错行附近的上下文sed -n 55,60p us_cities.sql重点检查字符串内是否包含未转义的逗号或者某一行在手工编辑时被误删。若是用 Excel 改过再导出的 SQL这类问题非常普遍。4. 查询与分析按州统计与经纬度距离计算的SQL写法4.1 按州统计城市数量与邮政编码去重数据导入后最直接的统计是按州聚合SELECT state, COUNT(*) AS city_count, COUNT(DISTINCT zip) AS zip_count FROM us_cities GROUP BY state ORDER BY city_count DESC LIMIT 10;COUNT(*)统计每个州的行数COUNT(DISTINCT zip)统计该州内不重复的邮编。同一城市可能对应多个邮编因此两者差值越大说明该州邮编分布越细。使用group by state而不是state_id可以显示完整的州名称需要更紧凑的输出时再改用state_id。4.2 用半正矢公式在SQL里计算城市间距离计算两个城市球面距离时常用半正矢公式。以纽约到洛杉矶为例SET lat1 40.7128; SET lng1 -74.0060; SET lat2 34.0522; SET lng2 -118.2437; SELECT 6371 * 2 * ASIN( SQRT( POWER(SIN(RADIANS((lat2 - lat1) / 2)), 2) COS(RADIANS(lat1)) * COS(RADIANS(lat2)) * POWER(SIN(RADIANS((lng2 - lng1) / 2)), 2) ) ) AS distance_km;逻辑说明先求两点纬度和经度差的一半用RADIANS把角度转为弧度再计算正弦平方和最后用地球平均半径 6371 公里换算成实际距离。需要英里时将 6371 换成 3959。这个计算可以嵌套到表中例如找出某点周边 50 公里内的城市SELECT c2.city, c2.state_id, 6371 * 2 * ASIN( SQRT( POWER(SIN(RADIANS((c2.lat - c1.lat) / 2)), 2) COS(RADIANS(c1.lat)) * COS(RADIANS(c2.lat)) * POWER(SIN(RADIANS((c2.lng - c1.lng) / 2)), 2) ) ) AS distance_km FROM us_cities c1 JOIN us_cities c2 ON c1.city New York AND c1.state_id NY AND c2.city c1.city HAVING distance_km 50 ORDER BY distance_km;这里使用自连接c1作为中心点c2是被匹配的城市。HAVING用于过滤计算后的别名列但在 MySQL 中允许在HAVING中引用 SELECT 别名。注意必须加上c1.city定位条件否则会生成笛卡尔积查询行数瞬间爆炸。4.3 经纬度范围检索以某点为中心圈选城市不需要精确距离时可以用矩形范围快速过滤SELECT city, state_id, lat, lng FROM us_cities WHERE lat BETWEEN 40.6 AND 40.9 AND lng BETWEEN -74.1 AND -73.7 ORDER BY lat, lng;BETWEEN是闭合区间会包含边界值。这个查询会返回纽约市区附近的全部城市。它的性能优于半正矢公式因为可以走索引但边缘误差在几十公里量级适合作为粗筛或地图初始化加载。后续要精确圈选再在外面套距离公式。4.4 给常用过滤条件添加索引城市表即使只有几万行不加索引也很流畅但面向并发访问或反复做范围查询时索引就必不可少CREATE INDEX idx_us_cities_state ON us_cities (state); CREATE INDEX idx_us_cities_state_zip ON us_cities (state, zip); CREATE INDEX idx_us_cities_lat_lng ON us_cities (lat, lng);idx_us_cities_state用于按州聚合和等值过滤idx_us_cities_state_zip适合按州和邮编组合查询idx_us_cities_lat_lng用于经纬度范围过滤。复合索引的列顺序需要从左到右匹配所以把常用于等值条件的state放在前面邮编放后面。对于HAVING distance_km 50这类基于计算列的过滤普通索引无法直接命中可以先依靠经纬度范围粗筛再计算精确距离。提示索引不是越多越好写入和更新都会变慢。这张表如果只读不写三个索引可以保留如果频繁批量更新保留最常用的一个即可。5. 进阶用Python将城市表导出GeoJSON并快速可视化5.1 使用pymysql读取数据并生成GeoJSON验证完查询下一步是把数据放到前端地图上。GeoJSON 是最通用的点数据格式。下面这个脚本会把整张表导出为 FeatureCollectionimport pymysql import json conn pymysql.connect( host127.0.0.1, userapp_user, passwordyour_password, databaseus_cities, charsetutf8mb4, cursorclasspymysql.cursors.DictCursor ) features [] with conn.cursor() as cur: cur.execute( SELECT city, state, state_id, zip, lat, lng FROM us_cities WHERE lat BETWEEN -90 AND 90 AND lng BETWEEN -180 AND 180 ) for row in cur.fetchall(): features.append({ type: Feature, properties: { name: row[city], state: row[state_id], zip: row[zip] }, geometry: { type: Point, coordinates: [float(row[lng]), float(row[lat])] } }) geojson { type: FeatureCollection, features: features } with open(us_cities.geojson, w, encodingutf-8) as f: json.dump(geojson, f, ensure_asciiFalse, indent2) print(fExported {len(features)} features to us_cities.geojson)参数说明charsetutf8mb4必须与数据库字符集一致否则city字段会乱码DictCursor让每行以字典返回字段名可以直接引用。GeoJSON 的坐标顺序是[经度, 纬度]容易写反。使用ensure_asciiFalse可以保证城市名直接以 UTF-8 字符落盘而不是被转成\uXXXX转义序列。5.2 用folium在浏览器里渲染城市点生成 GeoJSON 后用 folium 可以快速验证地图效果。首先安装依赖pip install folium然后加载刚导出的文件import folium m folium.Map(location[39.5, -98.35], zoom_start5) folium.GeoJson( us_cities.geojson, nameus_cities, popupfolium.GeoJsonPopup( fields[name, state, zip], aliases[City, State, ZIP] ) ).add_to(m) m.save(us_cities_map.html)location[39.5, -98.35]大致位于美国大陆中心zoom_start5能显示全国分布GeoJsonPopup让地图点击城市点时显示名称、州和邮编。全量点数据超过两万后folium 直接渲染会比较慢建议按州过滤或做点聚合再生成 HTML 文件给团队验证。这个脚本配合idx_us_cities_lat_lng在大表上也只扫一次过滤后的数据导出过程不会拖垮数据库。本文还有配套的精品资源点击获取 从SQL存档到GeoJSON：美国城市数据库的导入与实战
简介美国城市数据库压缩包面向需要美国城市地理空间信息的开发者、数据分析师与GIS学习者覆盖地图可视化、位置查询、距离计算、人口统计与商业选址等常见场景。包内共4个文件以SQL数据文件为核心包含城市名称、州名、邮政编码、经纬度等结构化字段另附README说明文档、txt说明文件与LICENSE许可证整体仅560KB体积轻便便于快速导入MySQL、PostgreSQL等数据库也可转为CSV或JSON格式处理并能直接衔接Python、R或GIS工具进行数据清洗与可视化。目前已有49人学习下载。通过导入或解析该数据集使用者可直接获得一套可用、结构清晰的美国城市基础数据省去自行爬取与整理的时间配合文档还能快速掌握字段含义与许可约束适合作为地理数据入门练习也可为区域分析、物流配送或学术研究等项目提供扎实的数据支撑。1. 美国城市SQL存档一个zip包里藏着什么做地图可视化或物流系统时最缺的往往不是工具而是一份干净的城市基础表。这个名为 US-Cities-Database-master.zip 的压缩包有点反直觉解压后没有诱人的CSV或JSON只有一个us_cities.sql文件、一份README和LICENSE。完整数据都藏在这个SQL文件里按城市名、州名、县名、邮编、经纬度等字段组织。适合做GIS演示、区域分析、前端地图点位的初始化数据。对工程师来说导入即可用省去一轮清洗也可以作为SQL地理查询的练习样本。后续就从解压勘察讲起覆盖导入、验证、查询最后用Python把它导出为GeoJSON。2. zip解包与表结构勘察从README到us_cities.sql2.1 先看清单再解压别急着双击拿到zip包的第一反应是双击解压但我建议先列清单unzip -l US-Cities-Database-master.zip列出文件列表而不是立刻释放能确认里面是否存在目录穿越路径或异常大的文件。确认安全后再解压mkdir -p ~/data/us_cities unzip US-Cities-Database-master.zip -d ~/data/us_cities参数说明-d指定目标目录避免文件直接散落在当前目录。如果 zip 内自带US-Cities-Database-master/目录解压后会进入该子目录。若服务器没有 unzip也可以安装或用jar xf代替但 unzip 对普通文本文件最直观。2.2 读README和a.txt确认数据许可与说明解压后第一件事不是导入数据库而是看 README.md 和 a.txt。这两个文件决定你可不可以把数据用于商用项目。head -n 40 US-Cities-Database-master/README.md file US-Cities-Database-master/a.txt US-Cities-Database-master/us_cities.sqlhead查看说明开头file检查文件编码。输出常见情况README.md 记录数据来源、字段含义和更新日期。a.txt 可能是原始下载说明或简短的数据样本。LICENSE 文件决定数据许可例如 CC0、MIT 还是仅研究用途。需要注意编码如果file显示ISO-8859或with BOM导入前应转码否则城市名中的重音字符可能变成乱码。如果 a.txt 里写着下载地址和生成日期说明它就是临时说明文件直接忽略即可。2.3 用grep和awk只看不跑SQL来解析表结构不建议直接执行整个 SQL先看它使用的是 MySQL 还是 PostgreSQL 方言。grep -E CREATE TABLE|CREATE DATABASE|INSERT INTO us_cities.sql | head -20 grep -n CREATE TABLE us_cities.sql第一个命令列出关键语句第二个输出建表语句所在行号。接着看建表语句的完整字段sed -n /CREATE TABLE/,/;/p us_cities.sql | head -30如果看到反引号、ENGINEInnoDB、AUTO_INCREMENT基本可以确定是 MySQL 方言如果看到SERIAL或::则是 PostgreSQL 方言。我还会用 awk 提取字段名awk /CREATE TABLE/{flag1;next} flag/;/{flag0} flag{print} us_cities.sql | grep -o [^]* | tr \n 这段命令把反引号包裹的字段名全部提取到一行。它依赖反引号方言如果 SQL 文件没有反引号可以改用单引号或直接看 sed 输出。这样能在导入前生成字段清单避免导入后才发现字段名与预想不符。2.4 字段字典与数据格式从城市名到经纬度根据摘要和同类城市数据库的常见设计us_cities.sql 的核心字段通常包括字段名类型示例说明idINT/BIGINT1主键cityVARCHAR(100)New York城市名stateVARCHAR(50)New York州全名state_idCHAR(2)NY州缩写countyVARCHAR(100)Queens县或郡zipVARCHAR(5)10001邮政编码latDECIMAL(10,7)/DOUBLE40.7128纬度lngDECIMAL(10,7)/DOUBLE-74.0060经度注意zip应当作为字符串而不应作为整数因为美国邮编存在前导零比如马萨诸塞州的02139。经纬度通常使用十进制数纬度范围是 [-90, 90]经度范围是 [-180, 180]。如果导入后出现大量越界值多半是字符串解析或字符集截断导致需要在导入阶段处理。3. 数据库导入实战让us_cities.sql在MySQL里跑起来3.1 先建库后落表字符集与排序规则的选择导入之前先建立数据库否则重定向导入时会遇到Unknown database错误。CREATE DATABASE IF NOT EXISTS us_cities CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;参数说明utf8mb4能完整覆盖四字节 UTF-8 字符。美国城市名中常见ñ、é等拉丁字母部分社区名称也包含特殊字符用 utf8mb4 最稳妥。utf8mb4_unicode_ci控制字符串比较规则ORDER BY或去重时不会因大小写产生歧义。如果 SQL 文件内部已经包含CREATE DATABASE语句可以跳过这一步否则建议手工建库。3.2 正式导入source与mysql重定向的取舍两种常见方式mysql -u app_user -p -h 127.0.0.1 us_cities us_cities.sql或者进入客户端后执行USE us_cities; SOURCE /path/to/us_cities.sql;参数说明-u指定用户-p提示密码-h指定主机把 SQL 文件重定向给 mysql 客户端。SOURCE适合交互式调试但用重定向可以配合日志做批量排查。导入时如果遇到一堆警告可以用下面的命令显式显示mysql --show-warnings -u root -p us_cities us_cities.sql--show-warnings会在导入结束后列出所有 warning比如Incorrect string value或Data truncated这些警告在大文件导入时容易被淹没显式打开很有必要。如果目标是 PostgreSQL命令对应为psql -d us_cities -U postgres -f us_cities.sql-f与 mysql 的等价但 psql 默认遇到错误会继续执行容易留下一批不完整表。一般会加-v ON_ERROR_STOP1让错误立即停止。提示导入大 SQL 文件时不要用 GUI 工具直接拖拽调试不方便且容易内存溢出命令行重定向更可控。3.3 导入后的完整性验证行数、空值、坐标边界导入完成不等于数据正确。跑一组检查 SQLSELECT COUNT(*) AS total_rows, COUNT(DISTINCT city) AS distinct_cities, SUM(CASE WHEN zip IS NULL THEN 1 ELSE 0 END) AS null_zips, SUM(CASE WHEN lat -90 OR lat 90 THEN 1 ELSE 0 END) AS bad_lats, SUM(CASE WHEN lng -180 OR lng 180 THEN 1 ELSE 0 END) AS bad_lngs FROM us_cities;COUNT(*)是全表行数COUNT(DISTINCT city)统计不重复城市名SUM(CASE WHEN ...)把满足条件的行记为 1 再求和从而统计空邮编、越界坐标的数量。如果bad_lats或bad_lngs不为 0说明导入过程有截断或原始文件本身存在异常。全美城市去重后通常在两万行左右具体行数以实际文件为准。还可以抽查前五行确认字段对应关系SELECT city, state_id, zip, lat, lng FROM us_cities LIMIT 5;3.4 常见报错与排查从语法错误到数据截断导入失败有几种高频错ERROR 1366 (HY000): Incorrect string value ERROR 1136 (21S01): Column count doesnt match value count ERROR 1064 (42000): You have an error in your SQL syntaxIncorrect string value说明 SQL 文件编码与目标库字符集不一致。常见做法是先转码再导入iconv -f latin1 -t utf8 us_cities.sql us_cities_utf8.sql mysql -u root -p us_cities us_cities_utf8.sqlColumn count doesnt match说明 INSERT 语句的列数与 VALUES 数量不匹配。此时查看报错行附近的上下文sed -n 55,60p us_cities.sql重点检查字符串内是否包含未转义的逗号或者某一行在手工编辑时被误删。若是用 Excel 改过再导出的 SQL这类问题非常普遍。4. 查询与分析按州统计与经纬度距离计算的SQL写法4.1 按州统计城市数量与邮政编码去重数据导入后最直接的统计是按州聚合SELECT state, COUNT(*) AS city_count, COUNT(DISTINCT zip) AS zip_count FROM us_cities GROUP BY state ORDER BY city_count DESC LIMIT 10;COUNT(*)统计每个州的行数COUNT(DISTINCT zip)统计该州内不重复的邮编。同一城市可能对应多个邮编因此两者差值越大说明该州邮编分布越细。使用group by state而不是state_id可以显示完整的州名称需要更紧凑的输出时再改用state_id。4.2 用半正矢公式在SQL里计算城市间距离计算两个城市球面距离时常用半正矢公式。以纽约到洛杉矶为例SET lat1 40.7128; SET lng1 -74.0060; SET lat2 34.0522; SET lng2 -118.2437; SELECT 6371 * 2 * ASIN( SQRT( POWER(SIN(RADIANS((lat2 - lat1) / 2)), 2) COS(RADIANS(lat1)) * COS(RADIANS(lat2)) * POWER(SIN(RADIANS((lng2 - lng1) / 2)), 2) ) ) AS distance_km;逻辑说明先求两点纬度和经度差的一半用RADIANS把角度转为弧度再计算正弦平方和最后用地球平均半径 6371 公里换算成实际距离。需要英里时将 6371 换成 3959。这个计算可以嵌套到表中例如找出某点周边 50 公里内的城市SELECT c2.city, c2.state_id, 6371 * 2 * ASIN( SQRT( POWER(SIN(RADIANS((c2.lat - c1.lat) / 2)), 2) COS(RADIANS(c1.lat)) * COS(RADIANS(c2.lat)) * POWER(SIN(RADIANS((c2.lng - c1.lng) / 2)), 2) ) ) AS distance_km FROM us_cities c1 JOIN us_cities c2 ON c1.city New York AND c1.state_id NY AND c2.city c1.city HAVING distance_km 50 ORDER BY distance_km;这里使用自连接c1作为中心点c2是被匹配的城市。HAVING用于过滤计算后的别名列但在 MySQL 中允许在HAVING中引用 SELECT 别名。注意必须加上c1.city定位条件否则会生成笛卡尔积查询行数瞬间爆炸。4.3 经纬度范围检索以某点为中心圈选城市不需要精确距离时可以用矩形范围快速过滤SELECT city, state_id, lat, lng FROM us_cities WHERE lat BETWEEN 40.6 AND 40.9 AND lng BETWEEN -74.1 AND -73.7 ORDER BY lat, lng;BETWEEN是闭合区间会包含边界值。这个查询会返回纽约市区附近的全部城市。它的性能优于半正矢公式因为可以走索引但边缘误差在几十公里量级适合作为粗筛或地图初始化加载。后续要精确圈选再在外面套距离公式。4.4 给常用过滤条件添加索引城市表即使只有几万行不加索引也很流畅但面向并发访问或反复做范围查询时索引就必不可少CREATE INDEX idx_us_cities_state ON us_cities (state); CREATE INDEX idx_us_cities_state_zip ON us_cities (state, zip); CREATE INDEX idx_us_cities_lat_lng ON us_cities (lat, lng);idx_us_cities_state用于按州聚合和等值过滤idx_us_cities_state_zip适合按州和邮编组合查询idx_us_cities_lat_lng用于经纬度范围过滤。复合索引的列顺序需要从左到右匹配所以把常用于等值条件的state放在前面邮编放后面。对于HAVING distance_km 50这类基于计算列的过滤普通索引无法直接命中可以先依靠经纬度范围粗筛再计算精确距离。提示索引不是越多越好写入和更新都会变慢。这张表如果只读不写三个索引可以保留如果频繁批量更新保留最常用的一个即可。5. 进阶用Python将城市表导出GeoJSON并快速可视化5.1 使用pymysql读取数据并生成GeoJSON验证完查询下一步是把数据放到前端地图上。GeoJSON 是最通用的点数据格式。下面这个脚本会把整张表导出为 FeatureCollectionimport pymysql import json conn pymysql.connect( host127.0.0.1, userapp_user, passwordyour_password, databaseus_cities, charsetutf8mb4, cursorclasspymysql.cursors.DictCursor ) features [] with conn.cursor() as cur: cur.execute( SELECT city, state, state_id, zip, lat, lng FROM us_cities WHERE lat BETWEEN -90 AND 90 AND lng BETWEEN -180 AND 180 ) for row in cur.fetchall(): features.append({ type: Feature, properties: { name: row[city], state: row[state_id], zip: row[zip] }, geometry: { type: Point, coordinates: [float(row[lng]), float(row[lat])] } }) geojson { type: FeatureCollection, features: features } with open(us_cities.geojson, w, encodingutf-8) as f: json.dump(geojson, f, ensure_asciiFalse, indent2) print(fExported {len(features)} features to us_cities.geojson)参数说明charsetutf8mb4必须与数据库字符集一致否则city字段会乱码DictCursor让每行以字典返回字段名可以直接引用。GeoJSON 的坐标顺序是[经度, 纬度]容易写反。使用ensure_asciiFalse可以保证城市名直接以 UTF-8 字符落盘而不是被转成\uXXXX转义序列。5.2 用folium在浏览器里渲染城市点生成 GeoJSON 后用 folium 可以快速验证地图效果。首先安装依赖pip install folium然后加载刚导出的文件import folium m folium.Map(location[39.5, -98.35], zoom_start5) folium.GeoJson( us_cities.geojson, nameus_cities, popupfolium.GeoJsonPopup( fields[name, state, zip], aliases[City, State, ZIP] ) ).add_to(m) m.save(us_cities_map.html)location[39.5, -98.35]大致位于美国大陆中心zoom_start5能显示全国分布GeoJsonPopup让地图点击城市点时显示名称、州和邮编。全量点数据超过两万后folium 直接渲染会比较慢建议按州过滤或做点聚合再生成 HTML 文件给团队验证。这个脚本配合idx_us_cities_lat_lng在大表上也只扫一次过滤后的数据导出过程不会拖垮数据库。本文还有配套的精品资源点击获取