将数据库中存放的空间数据导出为包含shape文件的压缩包

本文链接：https://blog.youkuaiyun.com/qq_43582366/article/details/124691710

一、场景

数据库中某一张表存放了一些MultiPolygon数据，需要提供接口，根据这些数据生成对应的（.shp）文件，再压缩放在下载链接中导出。

二、思路

1.查询出这些数据
2.将数据封装成一个JSONArray
3.在固定路径创建一个shp文件
4.将the_geom数据封装为Geometry对象
5.将Geometry对象封装成Polygon[]数组
6.使用GeometryFactory().createMultiPolygon创建Geometry对象
7.将数据写入文件
8.将文件添加到压缩包输出

三、代码实现

生成压缩文件的工具类如下：

package ktw.micro.service.topicrepo.center.util;

/**
 * @ClassName: ShapeHelper
 * @Description:
 * @author: zhang zihao
 * @date: 2022/5/10  11:51
 */
import com.alibaba.fastjson.JSON;
import com.vividsolutions.jts.geom.*;
import com.vividsolutions.jts.io.WKTReader;
import ktw.micro.service.gisutil.GeometryUtil;
import ktw.micro.service.gisutil.serializer.WKTToByteArrayDeserializer;
import org.geotools.data.FeatureWriter;
import org.geotools.data.Transaction;
import org.geotools.data.shapefile.ShapefileDataStore;
import org.geotools.data.shapefile.ShapefileDataStoreFactory;
import org.geotools.feature.simple.SimpleFeatureTypeBuilder;
import org.geotools.geometry.jts.JTSFactoryFinder;
import org.geotools.referencing.crs.DefaultGeographicCRS;
import org.opengis.feature.simple.SimpleFeature;
import org.opengis.feature.simple.SimpleFeatureType;

import java.io.*;
import java.nio.charset.Charset;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.zip.ZipEntry;
import java.util.zip.ZipOutputStream;

public class ShapeHelper {

    public ShapeHelper(){
        super();

    }

    public void write2ShapeFile(String shpPath, String[] header, List data){
        try{
            //创建shape文件对象
            File file = new File(shpPath);
            Map<String, Serializable> params = new HashMap<String, Serializable>();
            params.put( ShapefileDataStoreFactory.URLP.key, file.toURI().toURL() );
            ShapefileDataStore ds = (ShapefileDataStore) new ShapefileDataStoreFactory().createNewDataStore(params);
            //定义图形信息和属性信息
            SimpleFeatureTypeBuilder tb = new SimpleFeatureTypeBuilder();
            tb.setCRS(DefaultGeographicCRS.WGS84);
            tb.setName("shapefile");
            tb.add("the_geom", MultiPolygon.class);
            for(int i= 0;i<header.length;i++){
                String field = header[i];
                tb.add(field.toUpperCase(), String.class);
            }
            ds.createSchema(tb.buildFeatureType());
            //设置编码
            Charset charset = Charset.forName("GBK");
            ds.setCharset(charset);
            //设置Writer
            FeatureWriter<SimpleFeatureType, SimpleFeature> writer = ds.getFeatureWriter(ds.getTypeNames()[0], Transaction.AUTO_COMMIT);
            //写入文件信息
            List geometries=new ArrayList();
            for (Object object:data) {
                Geometry geometry = GeometryUtil.wktToGeometry(JSON.toJSONString(object));
                geometries.add(geometry);
            }
            for(int i=0;i<data.size();i++){
                SimpleFeature feature = writer.next();
                Map<String, Object> row = (Map)data.get(i);
                WKTReader reader = new WKTReader();
                Geometry geometry = reader.read(row.get("the_geom").toString());
                int geoUnm = geometry.getNumGeometries();  // 一个geometry可能含有n个geometry
                Polygon[] polygons = new Polygon[geoUnm];
                for (int j = 0; j < geoUnm; j++) {
                    Polygon singleGeo =(Polygon) geometry.getGeometryN(j); //获取其中每一个geometry
                    int numGeometries = singleGeo.getNumGeometries();
                    LinearRing exteriorRing = (LinearRing)singleGeo.getExteriorRing();
                    int numInteriorRing = singleGeo.getNumInteriorRing();
                    LinearRing holes[] = new LinearRing[numInteriorRing];
                    for (int k = 0; k < numInteriorRing; k++) {
                        LinearRing interiorRing = (LinearRing)singleGeo.getInteriorRingN(k);
                        holes[k] = interiorRing;
                    }
                    Polygon Polygon1 = new GeometryFactory().createPolygon(exteriorRing, holes);
                    Polygon1.setSRID(4490);
                    //Coordinate[] coords = singleGeo.getCoordinates();
                    polygons[j] = Polygon1;
                }
                Geometry geom = new GeometryFactory().createMultiPolygon(polygons);
                feature.setAttribute("the_geom", geom);
                for (Map.Entry entry : row.entrySet()) {
                    feature.setAttribute(entry.getKey().toString().toUpperCase()
                            , entry.getValue());
                }
            }
            writer.write();
            writer.close();
            ds.dispose();

            //添加到压缩文件
            zipShapeFile(shpPath);
        }catch (Exception e){
            e.printStackTrace();
        }
    }

    //生成压缩包
    public void zipShapeFile(String shpPath){
        try{
            File shpFile = new File(shpPath);
            String shpRoot = shpFile.getParentFile().getPath(),
                    _shpName = shpFile.getName(),
                    shpName = _shpName.substring(0, _shpName.lastIndexOf("."));

            String zipPath = shpRoot+File.separator+shpName+".zip";
            File zipFile = new File(zipPath);
            InputStream input = null;
            ZipOutputStream zipOut = new ZipOutputStream(new FileOutputStream(zipFile));
            // zip的名称为
            zipOut.setComment(shpName);
            String[] shpFiles = new String[]{
                    shpRoot+File.separator+shpName+".dbf",
                    shpRoot+File.separator+shpName+".prj",
                    shpRoot+File.separator+shpName+".shp",
                    shpRoot+File.separator+shpName+".shx",
            };
            for(int i=0;i<shpFiles.length;i++){
                File _file = new File(shpFiles[i]);
                input = new FileInputStream(_file);
                zipOut.putNextEntry(new ZipEntry(_file.getName()));
                int temp = 0;
                while ((temp = input.read()) != -1) {
                    zipOut.write(temp);
                }
                input.close();
            }
            zipOut.close();
        }catch (Exception e){
            e.printStackTrace();
        }
    }
}

Service层逻辑实现如下：

public void exportSpatialService(HttpServletResponse response,String dbid, String tablename) throws Exception {
        //设置请求头
        response.setContentType("application/x-msdownload");
        response.setHeader("Content-Disposition",
                "attachment; filename=" + URLEncoder.encode((tablename) + ".zip",
                        "UTF-8"));
        response.setCharacterEncoding("UTF-8");
        //首先从数据库中查出数据
        DatabaseEntity databaseEntity = databaseService.getById(dbid);
        ISQLHelper isqlHelper = SQLHelper.get(databaseEntity.convertTo());
        String sql = "SELECT * FROM \"public\".\"" + tablename.toUpperCase() + "\"";
        JSONArray objects = isqlHelper.queryBySql(sql);
        //创建固定路径
        String tmpDir = System.getProperty("java.io.tmpdir");
        String tmpFile = tmpDir + tablename+".shp";
        //调用ShapeHelper创建包含shape文件的压缩包
        HashMap hashMap = JSONObject.parseObject(JSONObject.toJSONString(objects.get(0)),HashMap.class);
        String[] str = (String[])hashMap.keySet().toArray(new String[hashMap.keySet().size()]);
        new ShapeHelper().write2ShapeFile(tmpFile,str,objects);
        //输出文件
        File file=new File(tmpDir + tablename+".zip");
        //通过文件路径得到一个输入流
        String path = file.getPath();
        FileInputStream fis = new FileInputStream(path);
        //创建字节输出流,这里我们使用Servlet的输出流
        ServletOutputStream sos = response.getOutputStream();
        //执行输出操作
        int len = 1;
        byte[] b = new byte[1024]; //创建一个字节数组
        while( (len = fis.read(b)) != -1) {
            sos.write(b, 0, len);
        }
        sos.close();
        fis.close();
        //删除本地文件
        List<String> filelist=new ArrayList<>();
        filelist.add(".zip");
        filelist.add(".shp");
        filelist.add(".dbf");
        filelist.add(".prj");
        filelist.add(".shx");
        filelist.add(".fix");
        for (String s:filelist) {
            FileUtil.delete(tmpDir + tablename+s);
        }
    }

Controller层调用如下：

/**
    * @param dbid,tablename
    * @description 根据数据源id和数据表名称导出数据
    * @return
    * @author zhang zihao
    * @date 2022/5/5
    **/
    @GetMapping("/exportdata/{dbid}/{tablename}")
    public void exportData(@PathVariable String dbid, @PathVariable String tablename) throws Exception {
        //首先判断当前表是空间数据表还是业务数据表
        DatabaseEntity databaseEntity = databaseService.getById(dbid);
        ISQLHelper isqlHelper = SQLHelper.get(databaseEntity.convertTo());
        String databaseSql = "select count(*) from information_schema.columns where table_name = '" + tablename.toUpperCase() + "' and column_name = 'the_geom'";
        JSONArray execute = isqlHelper.queryBySql(databaseSql);
        String count = execute.getJSONObject(0).getString("count");
        if (count.equals("1")) {
            //空间数据
            RequestAttributes ra = RequestContextHolder.currentRequestAttributes();
            repoInfoService.exportSpatialService(((ServletRequestAttributes) ra).getResponse(),dbid, tablename);
        } else {
            //业务数据
            structureService.exportBusinessData(tablename, dbid);
        }
    }