Postgresql实现csv中的大批量矢量点数据转换为线面数据

最近在测试客户给的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

通过使用数据库来处理这种海量数据的批量插入，相比在业务代码层次处理，更加的高效。