全国228189个矿产地CSV数据清洗与PostGIS空间入库实战

1. 全国矿产地数据集的整体设计与数据价值拆解1.1 这份数据到底装了什么第一次拿到“全国33个省228189个矿产地位置分布数据”这个标题的时候我脑子里第一反应是这个体量的数据如果字段设计合理基本可以支撑起一个省级到全国尺度的矿产资源空间分析项目。228189条记录覆盖33个省级行政区核心字段是经纬度坐标格式是CSV。这几个关键词组合在一起说明它不是一个简单的统计表格而是一个带有空间属性的点位数据集。从数据结构的角度来推这类矿产地数据集通常包含以下几类字段矿区名称、所属省份、行政区划代码、矿种类型煤、铁、铜、金、铅锌等、矿床规模大型、中型、小型、经纬度坐标经度Lon、纬度Lat、海拔高程部分数据集会有、数据来源或备注。228189这个数字意味着它大概率是全国矿产资源潜力评价、矿业权实地核查或者地质调查项目积累下来的汇总成果。33个省而不是34个说明统计口径上可能没有包含港澳台或者某些省级单位的数据被合并处理了。CSV格式的选择非常务实。相比Shapefile或者GeoJSONCSV在跨平台交换上几乎没有障碍——Excel能打开Python的pandas能读PostgreSQL能导入甚至连一些低代码平台都能直接解析。经纬度坐标以十进制小数形式存储通常是WGS84或者CGCS2000坐标系这为后续的空间分析和可视化提供了最基础的条件。1.2 谁需要这份数据用来做什么我接触过不少用这类数据的人大致可以分成几类。第一类是做地质科研的高校师生他们需要基于矿产地分布做成矿规律分析、空间聚类、核密度估计第二类是做国土空间规划或矿业权管理的技术人员需要把矿产地落到地图上和生态红线、基本农田、城镇开发边界做叠加分析第三类是做数据可视化的开发者想拿这份数据做一个全国矿产资源分布的地图应用第四类是做投资研究或行业分析的人想从矿种分布和规模结构里看出区域资源禀赋的差异。不同的人对这份数据的使用方式完全不同。科研人员关心的是坐标精度和矿种分类的准确性规划人员关心的是行政区划字段是否完整、能否和现有GIS图层做关联开发者关心的是CSV的编码格式、字段分隔符、有没有表头、经纬度的顺序是“经度在前”还是“纬度在前”。这些细节看起来琐碎但实际用起来任何一个不对都会导致整个流程卡住。1.3 为什么是CSV而不是其他格式这里我想多说一句格式选型的问题。很多人拿到CSV会觉得“太原始了”想要Shapefile或者GeoJSON。但实际工作中CSV反而是最稳妥的中间格式。原因有三一是CSV的通用性最强任何数据处理工具都能读写二是CSV便于版本管理和差异对比Git能直接diff三是CSV在导入数据库时最灵活你可以自己控制字段类型、索引和约束。当然CSV也有它的短板。它不存储坐标系信息不存储几何类型不存储字段的数据类型。这意味着你拿到一份CSV坐标数据必须自己确认坐标系是什么经纬度字段的顺序是什么有没有缺失值有没有重复记录。这些确认工作不做后面所有的分析都是空中楼阁。提示拿到任何CSV坐标数据第一步永远是抽样检查。用文本编辑器打开前50行确认分隔符是逗号还是制表符确认表头字段名确认经纬度的数值范围是否合理国内经度大约在73到135之间纬度大约在3到53之间。2. 数据获取后的核心细节解析与实操要点2.1 字段识别与坐标系确认假设你已经拿到了这份CSV文件第一步不是急着导入而是先做字段识别。用Python的pandas读前几行看看结构import pandas as pd df pd.read_csv(mineral_sites.csv, nrows5, encodingutf-8) print(df.columns.tolist()) print(df.head()) print(df.dtypes)如果编码报错换成gbk或者gb18030再试。国内很多地质调查项目导出的CSV默认是GBK编码直接用UTF-8读会乱码。这个问题我踩过不止一次尤其是从老系统导出的数据。字段识别完之后最关键的是确认坐标系。国内常见的坐标系统有几种WGS84GPS原始坐标、CGCS2000国家大地坐标系、北京54、西安80。如果数据来源是近十年的地质调查项目大概率是CGCS2000或者WGS84两者在国内尺度下差异很小普通可视化可以忽略。但如果要做高精度叠加分析就必须确认清楚。怎么判断坐标系一个实用的方法是找几个已知矿产地比如鞍山铁矿、白云鄂博稀土矿把CSV里的坐标和已知的真实坐标做对比。如果偏差在几十米以内基本就是WGS84或CGCS2000如果偏差几百米可能是北京54或西安80。2.2 经纬度顺序与数值范围校验经纬度顺序是另一个高频坑。国内很多数据集习惯用“纬度,经度”的顺序而国际惯例是“经度,纬度”。如果你不确认就导入GIS会发现所有点都跑到非洲或者南极去了。校验方法很简单# 假设字段名是 lat 和 lon print(df[lon].describe()) print(df[lat].describe())如果lon的范围在73到135之间lat在3到53之间说明顺序正确。如果反过来lon的范围在3到53lat在73到135那就需要交换字段。数值范围校验还能发现异常值。比如经度出现0或者负数纬度出现90以上这些大概率是缺失值填充或者录入错误。我一般会把这些记录单独导出人工核查后再决定是否保留。2.3 缺失值与重复记录处理228189条记录里难免有缺失和重复。缺失值可能出现在矿种类型、规模、坐标等字段。坐标缺失的记录基本没法用于空间分析建议直接剔除或者单独存放。矿种和规模缺失的记录可以保留但在做分类统计时要标记为“未知”。重复记录的判断标准要看业务需求。如果两条记录的矿区名称、坐标、矿种完全一致基本可以判定为重复。但如果只是名称相同、坐标略有差异可能是同一矿区的不同矿段不能简单去重。# 基于名称和坐标的重复检测 dup_mask df.duplicated(subset[矿区名称, 经度, 纬度], keepFalse) dups df[dup_mask] print(f疑似重复记录数: {len(dups)})处理重复记录时我通常的做法是保留第一条把重复的导出到一个单独文件人工确认后再决定是否合并或删除。2.4 数据加密与访问控制的实际考量热搜词里出现了“写完怎么加密”说明有人关心数据的安全性问题。对于这类矿产地数据如果涉及敏感信息确实需要做访问控制。但加密本身不是目的目的是控制谁能看、谁能改、谁能导出。实际工作中我一般建议分层处理原始CSV文件放在受控的服务器目录通过数据库导入后用视图和权限控制不同角色的访问范围。如果确实需要对CSV文件本身加密可以用7-Zip打包加密或者GPG加密但要注意密钥管理否则时间一长自己都打不开。注意加密后的CSV文件无法被pandas直接读取需要先解密到临时目录。如果是在自动化流程中使用建议把解密步骤做成独立的脚本密钥通过环境变量传入不要硬编码在代码里。3. 从CSV到空间数据库的完整实操流程3.1 用pandas做数据清洗与预处理在导入数据库之前我习惯先用pandas做一轮清洗。这一步的目标是把数据整理成“干净、一致、可直接入库”的状态。import pandas as pd import numpy as np # 读取数据 df pd.read_csv(mineral_sites.csv, encodinggb18030) # 重命名列根据实际字段名调整 df df.rename(columns{ 矿区名称: mine_name, 省份: province, 矿种: mineral_type, 规模: scale, 经度: longitude, 纬度: latitude }) # 坐标数值转换 df[longitude] pd.to_numeric(df[longitude], errorscoerce) df[latitude] pd.to_numeric(df[latitude], errorscoerce) # 剔除坐标缺失的记录 df df.dropna(subset[longitude, latitude]) # 范围过滤国内合理范围 df df[(df[longitude] 73) (df[longitude] 136)] df df[(df[latitude] 3) (df[latitude] 54)] # 去除首尾空格 df[mine_name] df[mine_name].str.strip() df[province] df[province].str.strip() # 导出清洗后的数据 df.to_csv(mineral_sites_clean.csv, indexFalse, encodingutf-8) print(f清洗后记录数: {len(df)})这段代码看起来简单但每一步都有讲究。errorscoerce能把无法转换的值变成NaN避免程序崩溃。范围过滤能剔除明显错误的坐标。去空格能避免后续关联时的匹配失败。3.2 PostgreSQL建表与导入PostgreSQL是我最推荐的空间数据存储方案配合PostGIS扩展能直接做空间查询和分析。建表的时候字段类型要设计好CREATE TABLE mineral_sites ( id SERIAL PRIMARY KEY, mine_name VARCHAR(200), province VARCHAR(50), mineral_type VARCHAR(100), scale VARCHAR(20), longitude NUMERIC(10, 6), latitude NUMERIC(10, 6), geom GEOMETRY(Point, 4326) ); CREATE INDEX idx_mineral_sites_geom ON mineral_sites USING GIST(geom); CREATE INDEX idx_mineral_sites_province ON mineral_sites(province); CREATE INDEX idx_mineral_sites_type ON mineral_sites(mineral_type);导入的时候先用\copy命令把CSV灌进去psql -U postgres -d mineral_db -c \copy mineral_sites(mine_name, province, mineral_type, scale, longitude, latitude) FROM mineral_sites_clean.csv WITH CSV HEADER ENCODING UTF8然后更新geom字段UPDATE mineral_sites SET geom ST_SetSRID(ST_MakePoint(longitude, latitude), 4326) WHERE longitude IS NOT NULL AND latitude IS NOT NULL;这里有个细节ST_MakePoint的参数顺序是“经度, 纬度”不是“纬度, 经度”。我见过不少人在这里搞反结果所有点都跑到海里去了。3.3 空间索引与查询性能优化228189条记录不算大但如果没有空间索引做范围查询或者最近邻查询时会明显变慢。GIST索引建好之后下面这类查询基本是毫秒级-- 查询某个矩形范围内的矿产地 SELECT mine_name, province, mineral_type FROM mineral_sites WHERE geom ST_MakeEnvelope(116.0, 39.0, 117.0, 40.0, 4326); -- 查询距离某点50公里内的矿产地 SELECT mine_name, province, ST_Distance(geom::geography, ST_MakePoint(116.4, 39.9)::geography) / 1000 AS distance_km FROM mineral_sites WHERE ST_DWithin(geom::geography, ST_MakePoint(116.4, 39.9)::geography, 50000) ORDER BY distance_km;ST_DWithin配合geography类型能直接按米计算距离不用自己换算坐标系。这个技巧在做“某城市周边矿产资源”分析时特别实用。3.4 按省份和矿种的聚合统计数据入库之后最常见的分析就是按省份和矿种做聚合。比如统计各省的矿产地数量和主要矿种-- 各省矿产地数量排名 SELECT province, COUNT(*) AS site_count FROM mineral_sites GROUP BY province ORDER BY site_count DESC; -- 各省主要矿种取数量最多的前三种 SELECT province, mineral_type, COUNT(*) AS type_count FROM mineral_sites GROUP BY province, mineral_type ORDER BY province, type_count DESC;如果要在Python里做更灵活的分析可以直接用pandas的groupbyprovince_stats df.groupby(province).agg( site_count(mine_name, count), mineral_types(mineral_type, nunique) ).sort_values(site_count, ascendingFalse) print(province_stats.head(10))这种统计能快速看出哪些省份矿产地密集哪些省份矿种丰富。比如内蒙古、新疆、云南这些地方矿产地数量和矿种多样性通常都排在前列。4. 常见问题与排查技巧实录4.1 CSV打开乱码怎么办这是最高频的问题。国内地质调查项目导出的CSV编码可能是GBK、GB18030、UTF-8 with BOM等多种。Excel打开乱码不代表文件坏了只是编码没对上。排查步骤先用file命令看文件编码Linux/Mac或者用Notepad打开看右下角显示的编码。如果是GBK用Excel的“数据-从文本导入”功能手动选择GBK编码。如果是UTF-8 with BOM用Python读取时加encodingutf-8-sig。# 尝试多种编码 encodings [utf-8, gbk, gb18030, utf-8-sig] for enc in encodings: try: df pd.read_csv(mineral_sites.csv, encodingenc, nrows5) print(f成功编码: {enc}) break except UnicodeDecodeError: continue4.2 坐标点偏移到国外怎么排查如果导入GIS后发现点全部偏移到国外大概率是经纬度顺序反了或者坐标系搞错了。排查顺序先看数值范围确认经度在73-136、纬度在3-54之间再看字段名确认哪个是经度哪个是纬度最后看坐标系定义确认GIS项目里的坐标系和数据的坐标系一致。还有一种情况是坐标本身是投影坐标比如高斯克吕格不是经纬度。这种数据的特点是数值很大经度可能是6位数纬度可能是7位数。遇到这种情况需要先做投影反算转成经纬度再用。4.3 导入数据库时字段类型不匹配PostgreSQL导入CSV时如果字段类型定义和实际数据不匹配会报错。常见的有经纬度字段定义成INTEGER但实际有小数省份字段定义成VARCHAR(10)但实际有“内蒙古自治区”这种长名称矿种字段有特殊字符导致解析失败。解决办法建表时经纬度用NUMERIC(10,6)名称类字段用VARCHAR(200)留足余量导入前先用pandas做一轮类型转换和截断。4.4 大数据量CSV的读取性能优化228189条记录用pandas读取如果字段多、内存小可能会比较慢。优化方法有几个只读需要的列usecols参数指定字段类型dtype参数避免自动推断分块读取chunksize参数处理超大文件。# 只读需要的列指定类型 df pd.read_csv( mineral_sites.csv, usecols[矿区名称, 省份, 矿种, 经度, 纬度], dtype{省份: category, 矿种: category}, encodinggb18030 )把省份和矿种设成category类型能大幅减少内存占用做groupby时也更快。4.5 常见问题速查表问题现象可能原因排查方法解决方案Excel打开乱码编码不匹配用Notepad看编码用GBK或utf-8-sig读取点偏移到国外经纬度顺序反了检查数值范围交换经纬度字段点偏移几百米坐标系不一致对比已知点坐标做坐标系转换导入报类型错误字段类型不匹配看报错字段调整建表语句读取速度慢字段多、类型未指定看内存占用用usecols和dtype重复记录多数据合并未去重按名称和坐标查重去重或人工确认提示处理这类数据我最大的体会是“先验证再批量”。拿10条记录做全流程测试确认坐标对了、字段对了、导入对了再处理全量数据。这样能避免跑完半小时才发现坐标顺序反了。5. 数据可视化与进阶分析思路5.1 用Python做快速可视化清洗完的数据用matplotlib或者folium能快速出图。matplotlib适合做静态的散点图folium适合做交互式地图。import matplotlib.pyplot as plt fig, ax plt.subplots(figsize(12, 8)) ax.scatter(df[longitude], df[latitude], s1, alpha0.3, csteelblue) ax.set_xlabel(Longitude) ax.set_ylabel(Latitude) ax.set_title(National Mineral Sites Distribution) plt.tight_layout() plt.savefig(mineral_sites_map.png, dpi150)这段代码能生成一张全国矿产地分布散点图。点的密度能直观反映矿产资源的空间聚集特征。如果按矿种分类着色还能看出不同矿种的区域分布差异。5.2 核密度分析与热点识别如果想进一步识别矿产资源的热点区域可以用核密度估计KDE。Python的scipy或者sklearn都有现成的实现from sklearn.neighbors import KernelDensity import numpy as np # 准备坐标数组 coords df[[longitude, latitude]].values # 核密度估计 kde KernelDensity(bandwidth0.5, metrichaversine) kde.fit(np.radians(coords)) # 在网格上评估密度 lon_grid np.linspace(73, 136, 200) lat_grid np.linspace(3, 54, 150) lon_mesh, lat_mesh np.meshgrid(lon_grid, lat_grid) grid_coords np.radians(np.vstack([lon_mesh.ravel(), lat_mesh.ravel()]).T) density np.exp(kde.score_samples(grid_coords)).reshape(lon_mesh.shape)bandwidth参数控制平滑程度值越大越平滑。metrichaversine确保在经纬度上计算的是球面距离而不是平面距离。这个分析能帮你找出矿产地最密集的区域对成矿规律研究很有价值。5.3 按省份和矿种的交叉分析除了空间分布按省份和矿种的交叉分析也能挖出不少信息。比如# 透视表省份 x 矿种 pivot pd.pivot_table( df, valuesmine_name, indexprovince, columnsmineral_type, aggfunccount, fill_value0 ) # 找出每个省份数量最多的矿种 top_mineral pivot.idxmax(axis1) print(top_mineral)这个分析能快速回答“哪个省以什么矿为主”这类问题。比如山西以煤为主辽宁以铁为主云南以有色金属为主这些结论从数据里直接就能看出来。5.4 导出为其他格式的注意事项有时候需要把数据导出成Shapefile或者GeoJSON给GIS同事用。导出时要注意坐标系和字段名长度。Shapefile的字段名不能超过10个字符中文字段名容易出问题建议导出前先改成英文。import geopandas as gpd from shapely.geometry import Point # 转成GeoDataFrame geometry [Point(xy) for xy in zip(df[longitude], df[latitude])] gdf gpd.GeoDataFrame(df, geometrygeometry, crsEPSG:4326) # 导出GeoJSON gdf.to_file(mineral_sites.geojson, driverGeoJSON) # 导出Shapefile注意字段名长度 gdf_export gdf.rename(columns{ mine_name: name, province: prov, mineral_type: mtype, scale: scale }) gdf_export.to_file(mineral_sites.shp, encodingutf-8)导出Shapefile时encodingutf-8能避免中文乱码但有些老版本ArcGIS对UTF-8支持不好可能需要用GBK。6. 个人实操经验与后续扩展方向这份数据我前前后后处理过几次最大的感受是数据质量比数据量重要得多。228189条记录听起来很多但如果坐标缺失、矿种分类混乱、省份字段不统一实际能用的可能只有一半。所以在清洗阶段多花时间后面分析阶段就能少踩坑。另一个体会是CSV虽然简单但“简单”不等于“随便”。字段命名、编码格式、坐标顺序、缺失值表示方式这些细节如果一开始不统一后面每换一个工具就要重新处理一遍。我的做法是拿到原始CSV后先做一轮标准化输出一个“干净版”CSV后续所有分析都基于这个干净版不再直接碰原始文件。后续如果想扩展有几个方向可以考虑。一是把矿产地数据和地质构造图、成矿带图做叠加分析成矿规律二是把数据按时间维度展开看不同时期的勘探重点变化三是把数据接入Web地图做一个可交互的全国矿产资源分布查询系统。这些方向都需要在现有数据基础上补充其他数据源但核心的坐标和分类字段已经具备扩展起来不会太吃力。最后分享一个小技巧处理这类数据时我习惯在CSV旁边放一个README.md记录数据来源、坐标系、字段说明、清洗步骤和已知问题。过几个月再回头看这个README能省下大量回忆时间。数据本身会说话但前提是你得记得它说的是什么。