Java导入shapefile到postgis,提升geotools导入效率效率优化结构

66 阅读 0 评论 44 点赞

我是靠谱客的博主无心白云，最近开发中收集的这篇文章主要介绍Java导入shapefile到postgis,提升geotools导入效率效率优化结构，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

使用Java导入shapefile到postgis中，小菜一般采用geotools的方式导入。

代码如下：


/**
 * @author xiaocai
 * @DescInfo
 * @创建时间：2020/11/24
 * @类职责描述：Shapefile文件
 **/
public class DataItemShapefile{

    public DataItemShapefile() {
    }

    @Override
    public boolean inputData2DataBase( ){

        try {
            String strFilePath = this.getInputDataPath();

            ShapefileDataStore featureClassSource = ConnPostgis.connectShapefile(strFilePath);

            DataItemUtils pDataItemUtils=getDataItemUtils();

            String strUrl = pDataItemUtils.pgUrl;
            String strInfo = strUrl.substring("jdbc:postgresql://".length());
            Integer portIndex = strInfo.lastIndexOf(":");
            String strHost = strInfo.substring(0, portIndex);
            portIndex = strUrl.lastIndexOf(":");
            String strSecondHalf = strUrl.substring(portIndex + 1);
            Integer dataBaseIndex = strSecondHalf.lastIndexOf("/");
            String port = strSecondHalf.substring(0, dataBaseIndex);
            String strDataBaseName = strSecondHalf.substring(dataBaseIndex + 1);

            DataStore pgDatastore = ConnPostgis.getPostgisDataStore("postgis", strHost, port, strDataBaseName, getDataItemUtils().pgDatabaseSchema, getDataItemUtils().pgUserName, getDataItemUtils().pgPassword);
            if (pgDatastore != null) {
                List<AttributeDescriptor> attrList = featureClassSource.getSchema().getAttributeDescriptors();
                FeatureWriter<SimpleFeatureType, SimpleFeature> writer = createFeaClass(featureClassSource,pgDatastore, attrList, inputDataBaseItemName.toLowerCase());
                if (writer != null) {
                    FeatureSource<SimpleFeatureType, SimpleFeature> featureSource = null;
                    //getTypeNames:获取所有地理图层
                    String typeName = featureClassSource.getTypeNames()[0];
                    featureSource = (FeatureSource<SimpleFeatureType, SimpleFeature>) featureClassSource.getFeatureSource(typeName);
                    //一个用于处理FeatureCollection的实用工具类。提供一个获取FeatureCollection实例的机制
                    FeatureCollection<SimpleFeatureType, SimpleFeature> result = featureSource.getFeatures();

                    FeatureIterator<SimpleFeature> itertor = result.features();

                    SimpleFeature writeFeature = writer.next();
                    Integer index = 1;
                    while (itertor.hasNext()) {
                        SimpleFeature feature = itertor.next();

                        Collection<Property> p = feature.getProperties();
                        Iterator<Property> it = p.iterator();
                        while (it.hasNext()) {
                            Property pro = it.next();
                            writeFeature.setAttribute(pro.getName().toString().toLowerCase(), pro.getValue());
                        }
                        writeFeature.setAttribute(ConstField.CHECKUNIQUEID,index);
                        //写入
                        writer.write();
                        //再来一个点
                        writeFeature = writer.next();
                        index++;
                    }
                    itertor.close();
                    //关闭
                    writer.close();

                    pgDatastore.dispose();//使用之后必须关掉
                    featureClassSource.dispose();//使用之后必须关掉
                    return true;
                }
            }
            return false;
        } catch (IOException e) {
            e.printStackTrace();
        }
        return false;
    }

    /**
     * 通过Shapefile文件创建要素类
     * @param shapefileDataStore
     * @param dataStore
     * @param attrList
     * @param strFeaClassName
     * @return
     */
    public FeatureWriter<SimpleFeatureType, SimpleFeature> createFeaClass(ShapefileDataStore shapefileDataStore,DataStore dataStore,List<AttributeDescriptor> attrList,String strFeaClassName) {

        try {
            //SimpleFeatureTypeBuilder 构造简单特性类型的构造器
            SimpleFeatureTypeBuilder tBuilder = new SimpleFeatureTypeBuilder();
            tBuilder.setName(strFeaClassName);

            SimpleFeatureType schema = shapefileDataStore.getSchema();
            CoordinateReferenceSystem dataCrs = schema.getCoordinateReferenceSystem();
            tBuilder.setCRS(dataCrs);

            for (int i = 0; i < attrList.size(); i++) {
                AttributeDescriptor attributeDescriptor = attrList.get(i);
                Class<?> classType= (Class.forName(attributeDescriptor.getType().getBinding().getName()));
                if(java.lang.Double.class.isAssignableFrom(classType)||java.lang.Float.class.isAssignableFrom(classType))
                {
                    tBuilder.add(attributeDescriptor.getName().toString().toLowerCase(), BigDecimal.class);
                }
                else
                {
                    tBuilder.add(attributeDescriptor.getName().toString().toLowerCase(), classType);
                }
            }

            tBuilder.add(ConstField.CHECKUNIQUEID,Integer.class);


            //设置此数据存储的特征类型
            dataStore.createSchema(tBuilder.buildFeatureType());
            FeatureWriter<SimpleFeatureType, SimpleFeature> writer = dataStore.getFeatureWriterAppend(strFeaClassName, Transaction.AUTO_COMMIT);
            return writer;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }
}

但是上述入库的方式较慢，慢的代码如下：

//写入
writer.write();

其没写入一行都采用write和目标源进行交互，没有AE提供的每n条可以程序中进行Flush一次，批量写入到目标源中，Geotools中每一次write，当数据量较大时，导入速度非常的慢。

故采用sql的方式进行入库，但是需要在geotools中创建要素类，故将shapefile导入postgis的流程分为两部分，前半部分采用geotools的方式创建要素类到postgis中，后半部分采用sql的方式和postgresql进行交互，将数据导入到postgis中。

采用PreparedStatement的方式进行入库

可以采用参数的方式传入值，对于sql注入，参数拼接具有奇效
支持Batch批量写入目标源中，效率较高

借鉴地址为：
https://blog.csdn.net/weixin_43100896/article/details/90272926

代码如下：


/**
 * @author xiaocai
 * @DescInfo
 * @创建时间：2020/11/24
 * @类职责描述：Shapefile文件
 **/
public class DataItemShapefile extends BaseDataItem {

    public DataItemShapefile() {
    }

    @Override
    public boolean inputData2DataBase( ) {

        try {
            String strFilePath = this.getInputDataPath();

            ShapefileDataStore featureClassSource = ConnPostgis.connectShapefile(strFilePath);

            DataItemUtils pDataItemUtils = getDataItemUtils();

            String strUrl = pDataItemUtils.pgUrl;
            String strInfo = strUrl.substring("jdbc:postgresql://".length());
            Integer portIndex = strInfo.lastIndexOf(":");
            String strHost = strInfo.substring(0, portIndex);
            portIndex = strUrl.lastIndexOf(":");
            String strSecondHalf = strUrl.substring(portIndex + 1);
            Integer dataBaseIndex = strSecondHalf.lastIndexOf("/");
            String port = strSecondHalf.substring(0, dataBaseIndex);
            String strDataBaseName = strSecondHalf.substring(dataBaseIndex + 1);
            Connection connection = null;
            PreparedStatement preparedStatement = null;

            DataStore pgDatastore = ConnPostgis.getPostgisDataStore("postgis", strHost, port, strDataBaseName, getDataItemUtils().pgDatabaseSchema, getDataItemUtils().pgUserName, getDataItemUtils().pgPassword);
            if (pgDatastore != null) {
                List<AttributeDescriptor> attrList = featureClassSource.getSchema().getAttributeDescriptors();
                FeatureWriter<SimpleFeatureType, SimpleFeature> writer = createFeaClass(featureClassSource, pgDatastore, attrList, inputDataBaseItemName.toLowerCase());
                SimpleFeatureStore pgFeatureDatastore = (SimpleFeatureStore) pgDatastore.getFeatureSource(inputDataBaseItemName.toLowerCase());
                if (writer != null) {
                    connection = DbConnectionUtil.getConnection(DbType.POSTGRE_SQL, pDataItemUtils.pgUrl, pDataItemUtils.pgUserName, pDataItemUtils.pgPassword);
                    FeatureSource<SimpleFeatureType, SimpleFeature> featureSource = null;
                    //getTypeNames:获取所有地理图层
                    String typeName = featureClassSource.getTypeNames()[0];
                    featureSource = (FeatureSource<SimpleFeatureType, SimpleFeature>) featureClassSource.getFeatureSource(typeName);
                    //一个用于处理FeatureCollection的实用工具类。提供一个获取FeatureCollection实例的机制
                    FeatureCollection<SimpleFeatureType, SimpleFeature> result = featureSource.getFeatures();
                    FeatureIterator<SimpleFeature> itertor = result.features();

                    SimpleFeature writeFeature = writer.next();
                    Map<String, Object> mapValues = new LinkedHashMap<>();
                    List<AttributeDescriptor> pgAttrList = pgFeatureDatastore.getSchema().getAttributeDescriptors();
                    String strInsertSql = getPgClassInsertSql(inputDataBaseItemName.toLowerCase(), pgAttrList, mapValues);
                    preparedStatement = connection.prepareStatement(strInsertSql);
                    Integer index = 1;
                    while (itertor.hasNext()) {
                        SimpleFeature feature = itertor.next();
                        Collection<Property> p = feature.getProperties();
                        Iterator<Property> it = p.iterator();
                        while (it.hasNext()) {
                            Property pro = it.next();
                            String strFieldName=pro.getName().toString().toLowerCase();
                            if (mapValues.containsKey(strFieldName)) {
                                mapValues.put(strFieldName, pro.getValue());
                            }
                        }
                        mapValues.put(ConstField.FID, index);
                        mapValues.put(ConstField.CHECKUNIQUEID, index);

                        Integer MapIndex = 1;
                        for (String key : mapValues.keySet()) {
                            if(key.toString().equals(ConstField.THEGEOM)) {
                                preparedStatement.setString(MapIndex++, mapValues.get(key).toString());
                            }
                            else
                            {
                                preparedStatement.setObject(MapIndex++, mapValues.get(key));
                            }
                        }
                        preparedStatement.addBatch();
                        index++;
                        if(index%10000==0)
                        {
                            System.out.println(index.toString());
                            preparedStatement.executeBatch();
                            preparedStatement.clearBatch();
                        }
                    }
                    itertor.close();
                    preparedStatement.executeBatch();

                    pgDatastore.dispose();//使用之后必须关掉
                    featureClassSource.dispose();//使用之后必须关掉
                    return true;
                }
            }
            return false;
        } catch (IOException | SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    private String getPgClassInsertSql(String strFeaClassName,List<AttributeDescriptor> pgAttrList,Map<String,Object> mapValues) {
        List<String> listValues = new ArrayList<>();
        //FID字段
        listValues.add("?");
        mapValues.put(ConstField.FID,null);

        for (int i = 0; i < pgAttrList.size(); i++) {
            AttributeDescriptor attributeDescriptor = pgAttrList.get(i);
            if(attributeDescriptor.getName().toString().equals(ConstField.THEGEOM))
            {
                listValues.add("st_geomfromText(?)");
            }
            else
            {
                listValues.add("?");
            }
            mapValues.put(attributeDescriptor.getName().toString(),null);
        }
        String strInsertSql = String.format("insert into %s values(%s)", strFeaClassName, String.join(",", listValues));
        return strInsertSql;
    }

    /**
     * 通过Shapefile文件创建要素类
     * @param shapefileDataStore
     * @param dataStore
     * @param attrList
     * @param strFeaClassName
     * @return
     */
    public FeatureWriter<SimpleFeatureType, SimpleFeature> createFeaClass(ShapefileDataStore shapefileDataStore,DataStore dataStore,List<AttributeDescriptor> attrList,String strFeaClassName) {

        try {
            //SimpleFeatureTypeBuilder 构造简单特性类型的构造器
            SimpleFeatureTypeBuilder tBuilder = new SimpleFeatureTypeBuilder();
            tBuilder.setName(strFeaClassName);

            SimpleFeatureType schema = shapefileDataStore.getSchema();
            CoordinateReferenceSystem dataCrs = schema.getCoordinateReferenceSystem();
            tBuilder.setCRS(dataCrs);

            for (int i = 0; i < attrList.size(); i++) {
                AttributeDescriptor attributeDescriptor = attrList.get(i);
                Class<?> classType= (Class.forName(attributeDescriptor.getType().getBinding().getName()));
                if(java.lang.Double.class.isAssignableFrom(classType)||java.lang.Float.class.isAssignableFrom(classType))
                {
                    tBuilder.add(attributeDescriptor.getName().toString().toLowerCase(), BigDecimal.class);
                }
                else
                {
                    tBuilder.add(attributeDescriptor.getName().toString().toLowerCase(), classType);
                }
            }

            tBuilder.add(ConstField.CHECKUNIQUEID,Integer.class);

            //设置此数据存储的特征类型
            dataStore.createSchema(tBuilder.buildFeatureType());
            FeatureWriter<SimpleFeatureType, SimpleFeature> writer = dataStore.getFeatureWriterAppend(strFeaClassName, Transaction.AUTO_COMMIT);
            return writer;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }
}

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <parent>
        <artifactId>xxxxxxxxxxx</artifactId>
        <groupId>xxxxxxxxxxxxxxxxxxxxxx</groupId>
        <version>1.0-SNAPSHOT</version>
    </parent>
    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <geotools.version>25-RC</geotools.version>
    </properties>
    <modelVersion>4.0.0</modelVersion>

    <artifactId>xxxxxxxxxxx</artifactId>

    <dependencies>
        <dependency>
            <groupId>org.geotools</groupId>
            <artifactId>gt-shapefile</artifactId>
            <version>${geotools.version}</version>
        </dependency>
        <dependency>
            <groupId>org.geotools</groupId>
            <artifactId>gt-swing</artifactId>
            <version>${geotools.version}</version>
        </dependency>
        <dependency>
            <groupId>org.geotools</groupId>
            <artifactId>gt-epsg-hsql</artifactId>
            <version>${geotools.version}</version>
        </dependency>
        <dependency>
            <groupId>org.geotools.jdbc</groupId>
            <artifactId>gt-jdbc-postgis</artifactId>
            <version>${geotools.version}</version>
        </dependency>
    </dependencies>


    <repositories>
        <repository>
            <id>osgeo</id>
            <name>OSGeo Release Repository</name>
            <url>https://repo.osgeo.org/repository/release/</url>
            <snapshots><enabled>false</enabled></snapshots>
            <releases><enabled>true</enabled></releases>
        </repository>
        <repository>
            <id>osgeo-snapshot</id>
            <name>OSGeo Snapshot Repository</name>
            <url>https://repo.osgeo.org/repository/snapshot/</url>
            <snapshots><enabled>true</enabled></snapshots>
            <releases><enabled>false</enabled></releases>
        </repository>
        <repository>
            <id>GeoSolutions</id>
            <name>GeoSolutions</name>
            <url>http://maven.geo-solutions.it/</url>
            <snapshots><enabled>false</enabled></snapshots>
            <releases><enabled>true</enabled></releases>
        </repository>
        <repository>
            <id>javadbf</id>
            <name>javadbf</name>
            <url>https://mvnrepository.com/artifact/com.linuxense/javadbf</url>
            <snapshots><enabled>false</enabled></snapshots>
            <releases><enabled>true</enabled></releases>
        </repository>

    </repositories>

</project>