32万条全国旅游景点数据：SQL底表导入与文旅分析实战

简介这份资源是面向旅游信息化开发、地理数据研究及LBS应用搭建者的全国景点SQL数据包可直接导入MySQL等数据库用于景点检索、地图标注、区域统计与POI分析等场景。压缩包内共1个文件为单个sql脚本整体约11.01MB导入后即可获得结构清晰的景点数据表。字段涵盖景点名称、联系电话、详细地址、景点类型、行政区划编码、POI标识以及经纬度坐标等兼顾名称检索与空间定位需求。数据总量达324498项覆盖全国各地景点含景点名、电话、类型及各类型地理位置标识便于按区域、类型或坐标维度做筛选与聚合。目前已有4653人学习下载适合需要快速获取全国景点基础数据、搭建旅游类应用或开展地理数据分析的开发者与研究者使用。1. 32万条全国旅游景点数据一份能直接跑进数据库的底表做旅游类应用、区域文旅分析或者本地生活推荐时最头疼的往往不是算法而是手里没有一份像样的景点底表。网上能搜到的景点清单要么只有几百条要么字段残缺要么格式混乱到没法直接入库。这份 32 万条全国旅游景点数据核心价值就在于它是一份已经整理好的结构化数据配套 SQL 文件拿到手就能导入 MySQL、PostgreSQL 或者 SQLite省掉从零爬取和清洗的大半工作量。它覆盖的是全国范围的景点 POI 信息量级到了 32 万条这个体量意味着它不只是几个热门城市的抽样而是能支撑起区域密度分析、城市文旅画像、路线推荐冷启动这类真实场景。适合谁用做旅游 SaaS 的、搞文旅数据可视化的、写推荐系统需要景点侧特征的以及做地理信息分析想找一份现成底表的从业者。如果你只是想找几个景点写篇游记这份数据反而过重了。2. 先看清数据长什么样字段、格式与导入前的判断2.1 从 7z 压缩包到 SQL 文件的结构推断拿到一个.7z包第一步不是急着解压而是先确认里面到底是什么。常见做法是先用7z l列出内容看清楚是单个 SQL 文件、多个分卷还是附带 CSV 和说明文档。这一步能帮你判断后续导入策略——如果是单个大 SQL直接 source 就行如果是分表导出就得考虑合并或者分批导入。# 列出压缩包内容不实际解压 7z l 32万条全国旅游景点数据.7z # 确认无误后解压到当前目录 7z x 32万条全国旅游景点数据.7z -o./scenic_data7z l只读目录不会改动文件适合先探路。-o参数指定输出目录避免解压后文件散落一地。如果包里有多个 SQL 文件通常按省份或按数据批次拆分导入时要注意字符集是否统一。2.2 字段含义与建表语句的对应关系SQL 文件里一般会带CREATE TABLE和INSERT INTO两部分。导入前先打开文件头部看一眼建表语句确认字段名和类型。常见的景点数据字段包括景点名称、所在省市、详细地址、经纬度、景点等级如 5A、4A、类型标签、联系电话、开放时间等。不同来源的数据字段命名差异很大比如name和scenic_name、lng和longitude这些在后续查询时都要留意。常见字段可能命名类型建议用途景点名称name / title / scenic_nameVARCHAR(200)主检索字段省份province / provVARCHAR(50)区域聚合城市city / areaVARCHAR(50)城市维度分析经纬度lng,lat / longitude,latitudeDECIMAL(10,6)地图打点、距离计算等级level / gradeVARCHAR(10)筛选 5A/4A类型category / typeVARCHAR(100)标签分类如果 SQL 文件里没有建表语句只有裸数据那就需要自己根据列顺序建表。我一般会先head -n 50看一眼 INSERT 语句的字段顺序再写对应的 DDL避免导入后字段错位。2.3 导入前的字符集与编码检查中文数据最容易翻车的地方就是编码。SQL 文件可能是 UTF-8、GBK 甚至 GB2312导入时如果客户端和数据库字符集不一致就会出现乱码。先用file命令看文件编码再用iconv转成统一 UTF-8。# 查看文件编码 file -i scenic_data.sql # 如果是 GBK转成 UTF-8 iconv -f GBK -t UTF-8 scenic_data.sql -o scenic_data_utf8.sqlfile -i会输出 MIME 编码信息charsetutf-8就说明没问题。iconv转换时如果遇到无法映射的字符会报错可以加//IGNORE跳过但建议先看看是哪些字符避免丢数据。导入时在 MySQL 客户端里先执行SET NAMES utf8mb4;再 source 文件能减少大部分乱码问题。3. 把 32 万条数据跑起来导入、索引与基础查询3.1 MySQL 导入的两种方式与速度对比导入 32 万条数据方式选不对可能等上半小时。常见做法有两种一是用source命令在客户端里执行 SQL 文件二是用LOAD DATA INFILE导入 CSV。如果资源本身就是 SQL 文件source最直接但速度受限于逐条 INSERT。如果 SQL 里是批量 INSERT一条语句插几百行速度会快很多。-- 创建数据库并指定字符集 CREATE DATABASE scenic_db DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci; USE scenic_db; -- 如果 SQL 文件里没有建表语句先建表 CREATE TABLE scenic_spot ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(200) NOT NULL, province VARCHAR(50), city VARCHAR(50), address VARCHAR(300), lng DECIMAL(10,6), lat DECIMAL(10,6), level VARCHAR(10), category VARCHAR(100), INDEX idx_province (province), INDEX idx_city (city), INDEX idx_level (level) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4; -- 导入数据 SOURCE /path/to/scenic_data_utf8.sql;建表时先把索引加上比导入完再建索引慢一些但能避免导入后忘记建索引导致查询全表扫描。如果数据量特别大可以先去掉索引导入完再ALTER TABLE ADD INDEX这样整体更快。SOURCE命令后面跟绝对路径相对路径容易找不到文件。3.2 导入后必做的三件事计数、去重、坐标校验导入完成不代表数据就能用。先SELECT COUNT(*)确认条数是否对得上 32 万。然后检查景点名称是否有重复重复可能是数据源合并导致的。最后校验经纬度是否在合理范围内——中国境内大致是经度 73 到 135纬度 3 到 54超出这个范围的基本是脏数据。-- 确认总数 SELECT COUNT(*) FROM scenic_spot; -- 检查重复景点名称 SELECT name, COUNT(*) AS cnt FROM scenic_spot GROUP BY name HAVING cnt 1 ORDER BY cnt DESC LIMIT 20; -- 校验经纬度范围 SELECT COUNT(*) FROM scenic_spot WHERE lng NOT BETWEEN 73 AND 135 OR lat NOT BETWEEN 3 AND 54;重复名称不一定是错误不同城市可能有同名景点所以去重时要结合省市字段一起判断。经纬度校验能快速筛出明显错误比如经纬度写反了、或者用了其他坐标体系。如果发现大量坐标偏移可能是 GCJ-02 和 WGS-84 的差异需要根据用途决定是否转换。3.3 几个能直接用的查询按省统计、按等级筛选、附近景点数据导入后最常用的查询无非是区域聚合和条件筛选。按省份统计景点数量能快速看出数据分布是否均匀按等级筛选能拿到 5A、4A 景区清单附近景点查询则需要用到经纬度计算MySQL 里可以用简单的球面距离公式。-- 按省份统计景点数量降序排列 SELECT province, COUNT(*) AS spot_count FROM scenic_spot GROUP BY province ORDER BY spot_count DESC; -- 筛选 5A 级景区 SELECT name, province, city, address FROM scenic_spot WHERE level 5A ORDER BY province, city; -- 查询某个坐标附近 10 公里内的景点简化球面距离 SELECT name, province, city, ROUND(6371 * ACOS( COS(RADIANS(30.25)) * COS(RADIANS(lat)) * COS(RADIANS(lng) - RADIANS(120.15)) SIN(RADIANS(30.25)) * SIN(RADIANS(lat)) ), 2) AS distance_km FROM scenic_spot HAVING distance_km 10 ORDER BY distance_km LIMIT 50;第一个查询能帮你判断数据覆盖是否合理如果某个省份只有几十条可能是数据缺失。第二个查询直接可用但要注意level字段的值是否统一有的数据可能写的是“5A级”而不是“5A”。第三个查询里的 6371 是地球半径公里数RADIANS把角度转弧度HAVING过滤计算后的距离。这个公式在数据量大的时候性能一般生产环境建议用空间索引或者 Redis GEO。4. 避坑与排查导入和使用中最容易翻车的五个点4.1 导入报错 “Incorrect string value”现象导入过程中报Incorrect string value: \xE6\x9D\xAD... for column name数据只导入了一部分就中断。原因数据库或表的字符集不是utf8mb4而数据里包含 emoji 或者生僻汉字utf8三字节存不下。解决建库建表时统一用utf8mb4导入前执行SET NAMES utf8mb4;。如果表已经建好了用ALTER TABLE scenic_spot CONVERT TO CHARACTER SET utf8mb4;改掉。4.2 导入速度慢到怀疑人生现象SOURCE执行了十几分钟还没结束磁盘 IO 一直跑满。原因SQL 文件里是逐条 INSERT每条都触发一次事务提交。或者autocommit是开的每条语句都刷盘。解决导入前临时关闭自动提交SET autocommit 0;导入完再COMMIT;。如果文件里是逐条 INSERT可以考虑用脚本合并成批量 INSERT每 500 条一组速度能提升一个数量级。4.3 经纬度字段为空或者全是 0现象查询附近景点时结果为空检查发现lng和lat大量为 NULL 或 0。原因原始数据采集时就没有坐标或者导出时坐标字段丢失。也可能是坐标存成了字符串导入时被截断。解决先统计空值比例如果超过三成这份数据做地理分析就受限了。可以尝试用地址字段去调地图 API 补全但那是另一个工作量。导入时确认字段类型是DECIMAL而不是INT否则小数部分会被丢掉。4.4 省份字段里混着“省”“市”“自治区”现象按省份聚合时出现“广东省”“广东”“广东省广州市”混在一起统计结果对不上。原因数据来源不统一有的只写到省有的写到市有的带了后缀。解决导入后做一次标准化清洗用UPDATE把“广东”统一成“广东省”或者用CASE WHEN做映射。更稳妥的做法是单独建一张省份映射表查询时 JOIN 一下。4.5 数据量对不上少了或者多了现象COUNT(*)出来只有 28 万或者超过 32 万。原因少了可能是导入中断、编码报错跳过了部分行多了可能是 SQL 文件里包含了重复的 INSERT或者多次导入同一文件。解决先看导入日志有没有报错再检查 SQL 文件里是否有INSERT IGNORE或REPLACE INTO。如果是多次导入先TRUNCATE TABLE清空再重新导入。导入前用grep -c INSERT INTO数一下 INSERT 语句数量心里有个底。5. 进阶用法用这份数据做区域文旅热力与路线冷启动数据导入只是起点真正体现价值的是怎么用它。我一般会先做一张省份维度的热力聚合表把景点数量、5A 数量、平均密度算出来这样一眼就能看出哪些省份是文旅资源富集区。具体做法是建一张汇总表用INSERT INTO ... SELECT从明细表里聚合。-- 创建省份汇总表 CREATE TABLE province_summary ( province VARCHAR(50) PRIMARY KEY, total_spots INT, level_5a_count INT, level_4a_count INT, avg_lng DECIMAL(10,6), avg_lat DECIMAL(10,6) ); -- 从明细表聚合写入 INSERT INTO province_summary SELECT province, COUNT(*) AS total_spots, SUM(CASE WHEN level 5A THEN 1 ELSE 0 END) AS level_5a_count, SUM(CASE WHEN level 4A THEN 1 ELSE 0 END) AS level_4a_count, AVG(lng) AS avg_lng, AVG(lat) AS avg_lat FROM scenic_spot WHERE province IS NOT NULL GROUP BY province;这张汇总表的好处是查询快做可视化的时候不用每次扫 32 万条。CASE WHEN用来条件计数AVG算省份中心点后续可以拿这个中心点做地图标注。注意WHERE province IS NOT NULL过滤掉省份缺失的记录否则汇总表里会出现空省份。另一个实用场景是路线冷启动。当用户没有历史行为时可以基于景点等级和类型标签做规则推荐。比如用户选了“自然风光”标签就从category里匹配包含“山”“湖”“森林”的景点再按等级排序优先推 5A 和 4A。-- 按类型关键词和等级做冷启动推荐 SELECT name, province, city, level, category FROM scenic_spot WHERE category LIKE %自然% OR category LIKE %山% OR category LIKE %湖% ORDER BY CASE level WHEN 5A THEN 1 WHEN 4A THEN 2 ELSE 3 END, province, city LIMIT 100;这个查询里LIKE做模糊匹配ORDER BY里的CASE把等级转成排序权重。实际使用时可以把结果缓存起来避免每次请求都查库。如果数据里category字段质量不高可以先用SELECT DISTINCT category看看有哪些值再决定匹配规则。验证数据是否真的可用我习惯做一件事随机抽 20 个景点把名称和地址丢进地图应用里搜一下看能不能对上。如果大部分都能准确定位说明这份数据的地址和坐标质量过关如果一半以上搜不到那就要谨慎用于生产环境。这个习惯是从一次翻车经历来的——当时直接拿了一份没校验的数据上线结果用户反馈“导航到荒山野岭”后来每次拿到新数据都强制走一遍抽样验证。希望这份 32 万条全国旅游景点数据能帮你省掉从零攒底表的功夫把时间花在真正产生价值的地方。本文还有配套的精品资源点击获取