最近在测试客户给的csv数据时,发现了系统中处理的csv导入功能,存在内存处理不当的问题,问题背景是客户给的csv矢量点数据接近100万条,而我们工程师之前实现的时候,是将csv的数据全部查出到VO层,然后再分批插入,这种传统处理方式,在数据体量比较小的情况下,可以满足业务需求,但是当数据体量比较大时,就会造成内存的瞬间增长,严重影响系统使用。
针对这个问题,我们做了如下的处理,不仅不需要使用过多内存,业务层的代码也简化了,更重要的是效率也提升了。
今天就来总结下Postgresql在处理csv数据中存在海量数据时的解决思路。
第一、一定要使用Postgresql的copy命令来实现csv中大量数据的快速插入,关于postgresql的copy命令我在上一篇文章中已经介绍过,大家可以去查看。
第二、导入后的数据处理过程,一般csv(或者excel)中的几何信息都是以经纬度的信息录入,而在一般的项目中我们肯定是需要新建geom字段的,因此需要用到postgis的函数,如果是点数据则直接使用postgis函数 st_makepoint()来构造点的geom对象。
第三、对于线面数据,一般csv中的数据,也是按照点记录的,但是每一条线或每一个面都有多少个点组成,一般csv中都有一个字段来标识,只不过对于面数据而言,每一个面的组成点,起点和终点肯定是一样的,因为面需要闭合,而由点转化为线面的过程我们需要借助postgis的线面处理函数:
geometry ST_MakeLine(geometry geom1, geometry geom2); //输入起点和终点
geometry ST_MakeLine(geometry[] geoms_array); //参数是组成该线的点数据
geometry ST_MakeLine(geometry set geoms); //参数是一个聚合查询,必须使用order by 以确保点的顺序,这个也是我们遇到的问题
geometry ST_MakePolygon(geometry linestring); //参数接收闭合的线环
geometry ST_MakePolygon(geometry outerlinestring, geometry[] interiorlinestrings);
//参数介绍一个内外线环,构成一个带有空心的面数据
有了以上的理论知识,我们只需要将这个写理论知识,转成我们的sql语句即可,下面以test.csv为例,这是一个从globalmapper导出的矢量面数据,导出的数据是以点数据存储的,具体涉及到gid、name、lon、lat、fid这几个字段,其中gid是点数据的存储顺序(记住这个很重要),fid是面矢量数据的聚合字段,也就那些点构成一个面,接下俩我们只需要实在sql中执行如下命令就可将test.csv插入到postgresql:
CREATE TABLE IF NOT EXISTS public.test
(
gid int primary key,
name character varying(100),
lon double precision,
lat double precision,
fid character varying(5) ,
geom geometry(Polygon)
)
数据批量插入使用如下命令
Copy test(gid,name,lon,lat,fid) from 'D:/test.csv' csv header DELIMITER ','
数据插入后接下来就需要将点数据转换为面数据, 这个时候就需要用到上面的ST_MakeLine(geometry[] geoms_array)和ST_MakePolygon(geometry linestring)函数,如何将同一个面数据的点集合批量处理呢,这里我们要用到array_agg()函数,这个函数是postgresql的聚合为数组的函数(这个函数不在这里具体介绍),我们最终的具体sql如下:
select name,st_makepolygon(st_makeline(array_agg(st_makepoint(lon,lat) order by gid))) from test
group by fid,name
通过使用数据库来处理这种海量数据的批量插入,相比在业务代码层次处理,更加的高效。
- 数据 大批量 矢量 Postgresql Postgis数据 大批量 矢量postgresql postgresql插件postgis数据库 数据 矢量postgresql autocad postgresql postgis ubuntu postgresql centos7 postgis centos postgresql postgis ubuntu 18.04 postgresql ogr命令postgis timescaledb postgresql插件postgis postgresql postgis pgsql postgresql postgis centos7 centos