使用java对栅格数据的处理，对栅格文件进行导入导出

两点王爷

于 2024-09-09 16:00:00 发布

阅读量530

点赞数 10

文章标签：数据库 GIS 栅格 java postgres postgis

本文链接：https://blog.csdn.net/qq_43544074/article/details/141055638

版权

需求背景：

对栅格文件进行导入导出（使用代码的方式，非命令方式）；

当然也可以使用代码和GDAL的方式进行，但是GDAL配置部署不便捷，故选用GeoTools方式来实现。

ps：若是使用命令方式，首先打开PostgreSQL的安装目录【\PostgreSQL\14\bin】，然后使用如下命令即可实现把栅格文件导入到数据库中：

# 指定切片大小
raster2pgsql -s 4326 -I -C -M D:\model\tif\TDM1_DEM__30_N34E108_DEM.tif -F -t 256x256 sdx.tdm1_dem | psql -h 192.168.31.200 -p 5432 -U postgres -d testShp

# 4326 是ESP
# 256*256 是切片大小
# sdx.tdm1_dem 是哪个模式下的哪个表，也可以是public.tdm1_dem
# -h 192.168.31.200 是数据库地址
# -p 5432 端口
# -U postgres 用户名
# -d testShp 数据库名


# 自动切片大小
raster2pgsql -s 4326 -d -k -N -e -I -C -M "D:\model\tif\TDM1_DEM__30_N34E108_DEM.tif" -F -t auto public.raster_data | psql -h 192.168.31.200 -p 5432 -d testdb -U postgres

若是使用代码导入的思路：

配置好前置条件
读取栅格数据
连接到数据库
创建表
将数据插入到数据库中

实现：

1、配置好前置条件

确保你的PostgreSQL数据库已安装并启用了PostGIS扩展

-- 安装PostGIS扩展
CREATE EXTENSION postgis;

-- 安装PostGIS的栅格支持
CREATE EXTENSION postgis_raster;

确认PostGIS版本

确保你使用的是支持栅格数据的PostGIS版本。你可以使用以下命令来检查PostGIS版本：

SELECT POSTGIS_VERSION();
-- 查看函数
SELECT proname, proargtypes, prosrc
FROM pg_proc
JOIN pg_namespace ON pg_namespace.oid = pg_proc.pronamespace
WHERE proname = 'st_fromgdalraster';

SELECT proname, proargtypes
FROM pg_proc
JOIN pg_namespace ON pg_namespace.oid = pg_proc.pronamespace
WHERE proname LIKE 'st_f%' AND pg_namespace.nspname = 'public';

-- 查看扩展
SELECT postgis_full_version();

确保返回的版本信息显示PostGIS包含栅格支持

2、读取栅格数据、保存数据到数据库

添加依赖

        <gt.version>20.0</gt.version>

        <!-- 添加GeoTools依赖 -->
        <dependency>
            <groupId>org.geotools</groupId>
            <artifactId>gt-opengis</artifactId>
            <version>${gt.version}</version>
        </dependency>

        <dependency>
            <groupId>org.geotools</groupId>
            <artifactId>gt-api</artifactId>
            <version>${gt.version}</version>
        </dependency>

        <dependency>
            <groupId>org.geotools</groupId>
            <artifactId>gt-data</artifactId>
            <version>${gt.version}</version>
        </dependency>

        <dependency>
            <groupId>org.geotools</groupId>
            <artifactId>gt-jdbc</artifactId>
            <version>${gt.version}</version>
        </dependency>

        <dependency>
            <groupId>org.geotools.jdbc</groupId>
            <artifactId>gt-jdbc-postgis</artifactId>
            <version>${gt.version}</version>
        </dependency>

        <dependency>
            <groupId>org.geotools</groupId>
            <artifactId>gt-main</artifactId>
            <version>${gt.version}</version>
        </dependency>

        <dependency>
            <groupId>org.geotools</groupId>
            <artifactId>gt-metadata</artifactId>
            <version>${gt.version}</version>
        </dependency>

        <dependency>
            <groupId>org.geotools</groupId>
            <artifactId>gt-referencing</artifactId>
            <version>${gt.version}</version>
        </dependency>

        <dependency>
            <groupId>org.geotools</groupId>
            <artifactId>gt-render</artifactId>
            <version>${gt.version}</version>
        </dependency>

        <dependency>
            <groupId>org.geotools</groupId>
            <artifactId>gt-shapefile</artifactId>
            <version>${gt.version}</version>
        </dependency>
        <dependency>
            <groupId>org.geotools</groupId>
            <artifactId>gt-coverage</artifactId>
            <version>${gt.version}</version>
        </dependency>
        <!-- 如果使用的 GeoTools 功能需要额外的依赖，如坐标转换，可能还需要添加这个 -->
        <dependency>
            <groupId>org.geotools</groupId>
            <artifactId>gt-epsg-hsql</artifactId>
            <version>${gt.version}</version>
        </dependency>
        <dependency>
            <groupId>org.geotools</groupId>
            <artifactId>gt-geojson</artifactId>
            <version>${gt.version}</version>
        </dependency>
        <dependency>
            <groupId>cn.hutool</groupId>
            <artifactId>hutool-all</artifactId>
            <version>5.7.15</version>
        </dependency>


<!-- 1 栅格相关依赖 -->
        <dependency>
            <groupId>org.geotools</groupId>
            <artifactId>gt-image</artifactId>
            <version>${gt.version}</version>
        </dependency>

        <dependency>
            <groupId>org.geotools</groupId>
            <artifactId>gt-swing</artifactId>
            <version>${gt.version}</version>
        </dependency>

        <dependency>
            <groupId>org.geotools</groupId>
            <artifactId>gt-process</artifactId>
            <version>${gt.version}</version>
        </dependency>

        <dependency>
            <groupId>org.geotools</groupId>
            <artifactId>gt-process-raster</artifactId>
            <version>${gt.version}</version>
        </dependency>

        <dependency>
            <groupId>org.geotools</groupId>
            <artifactId>gt-geotiff</artifactId>
            <version>${gt.version}</version>
        </dependency>

        <!-- https://mvnrepository.com/artifact/org.geotools/gt-swing -->
        <dependency>
            <groupId>org.geotools</groupId>
            <artifactId>gt-swing</artifactId>
            <version>${gt.version}</version>
        </dependency>

        <!--<dependency>
            <groupId>org.jdal</groupId>
            <artifactId>jdal-core</artifactId>
            <version>2.0.0</version>
        </dependency>
-->
        <dependency>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-imaging</artifactId>
            <version>1.0-alpha3</version>
        </dependency>

        <!--        <dependency>
                    <groupId>org.geotools</groupId>
                    <artifactId>gt-netcdf</artifactId>
                    <version>20.0</version>
                </dependency>-->

        <!-- https://mvnrepository.com/artifact/edu.ucar/netcdf -->
        <dependency>
            <groupId>edu.ucar</groupId>
            <artifactId>netcdf</artifactId>
            <version>4.3.22</version>
        </dependency>

        <dependency>
            <groupId>org.geotools</groupId>
            <artifactId>gt-geometry</artifactId>
            <version>20.0</version>
        </dependency>

        <!-- TwelveMonkeys ImageIO TIFF -->
        <dependency>
            <groupId>com.twelvemonkeys.imageio</groupId>
            <artifactId>imageio-tiff</artifactId>
            <version>3.8.0</version>
        </dependency>

        <!-- Maven example for JAI TIFF plugin -->
        <dependency>
            <groupId>javax.media</groupId>
            <artifactId>jai-core</artifactId>
            <version>1.1.3</version>
        </dependency>

        <!--gdal-->
        <dependency>
            <groupId>org.gdal</groupId>
            <artifactId>gdal</artifactId>
            <version>3.9.0</version>
        </dependency>

        <!-- https://mvnrepository.com/artifact/net.postgis/postgis-jdbc -->
        <dependency>
            <groupId>net.postgis</groupId>
            <artifactId>postgis-jdbc</artifactId>
            <version>2024.1.0</version>
        </dependency>



        <!-- 11 栅格相关依赖 -->

实现类


    public String importRasterData(GridDataDto gridDataDto) throws IOException {
        // 1. 连接数据库，创建模式、表相关内容 实现导入栅格数据的逻辑
        GridData gridData = gridDataDto.getParameter();
        String sourceFilePath = gridData.getSourceFilePath();
        String targetDatasource = gridData.getTargetDatasource();
        String targetDatasetName = gridData.getTargetDatasetName().toLowerCase();
        String importMode = gridData.getImportMode();
        // 解析 PostgreSQL 数据库 连接参数
        Map<String, Object> dataSourceMap = parseDatabaseParameters(targetDatasource);
        log.info("dataSourceMap = " + dataSourceMap);
        // 创建 PostgreSQL/PostGIS 表
        JDBCDataStoreFactory dsf = new PostgisNGDataStoreFactory();
        JDBCDataStore datastore = dsf.createDataStore(dataSourceMap);
        // 如果不存该模式，则创建模式schema 确保 schema 存在
        String schemaName = dataSourceMap.get("schema").toString();
        try (Connection connection = datastore.getDataSource().getConnection()) {
            Statement stmt = connection.createStatement();
            // 创建依赖扩展
            String[] extensions = {"postgis", "postgis_raster"};
            for (String extension : extensions) {
                String createExtensionSql = "CREATE EXTENSION IF NOT EXISTS " + extension;
                stmt.execute(createExtensionSql);
            }
            log.info("Extensions created successfully");

            // 检查 schema 是否存在
            String checkSchemaSql = "SELECT schema_name FROM information_schema.schemata WHERE schema_name = '" + schemaName + "'";
            ResultSet rs = stmt.executeQuery(checkSchemaSql);
            if (!rs.next()) {
                // 如果 schema 不存在，创建 schema
                String createSchemaSql = "CREATE SCHEMA " + schemaName;
                stmt.execute(createSchemaSql);
                log.info("schema created successfully");
            }
            // 确保表存在 根据importMode的值NONE、OVERWRITE、APPEND进行相关操作
            if ("NONE".equals(importMode) || "OVERWRITE".equals(importMode)) {
                // 删除表
                String dropTableSql = "DROP TABLE IF EXISTS " + schemaName + "." + targetDatasetName;
                stmt.execute(dropTableSql);
                // 如果表不存在，创建表
                // String createTableSql = "CREATE TABLE IF NOT EXISTS " + schemaName + "." + targetDatasetName + " (rid INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, rast raster, srid text, filename text)";
                // 如果表不存在，创建表
                String createTableSql = "CREATE TABLE IF NOT EXISTS " + schemaName + "." + targetDatasetName +
                        " (rid SERIAL PRIMARY KEY, rast raster, srid text, filename text)";
                stmt.execute(createTableSql);
                log.info("Table created successfully");
            } else if ("APPEND".equals(importMode)) {
                // 检查表是否存在，不存在则创建
                String checkTableSql = "SELECT to_regclass('" + schemaName + "." + targetDatasetName + "')";
                rs = stmt.executeQuery(checkTableSql);
                if (!rs.next() || rs.getString(1) == null) {
                    // 如果表不存在，创建表
                    // String createTableSql = "CREATE TABLE IF NOT EXISTS " + schemaName + "." + targetDatasetName +" (rid INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, rast raster, srid text)";
                    String createTableSql = "CREATE TABLE IF NOT EXISTS " + schemaName + "." + targetDatasetName + " (rid SERIAL PRIMARY KEY, rast raster, srid text)";
                    stmt.execute(createTableSql);
                    log.info("Table created successfully");
                }
            }
        } catch (SQLException e) {
            log.error("Failed to ensure schema exists", e);
            throw new IOException("Failed to ensure schema exists", e);
        }

        // 保存到记录表中
        ServerImportInfo serverImportInfo = new ServerImportInfo();
        BeanUtils.copyProperties(gridData, serverImportInfo);
        Date now = new Date();
        serverImportInfo.setCreateTime(now);
        serverImportInfo.setStartTime(now);
        serverImportInfo.setUpdateTime(now);
        serverImportInfo.setState("RUNNING");
        serverImportInfo.setDataType("栅格-导入");
        serverImportInfoService.save(serverImportInfo);
        // 数据库相关连接和判断完成，下面实现
        // 2. 读取栅格数据
        try {
            File file = new File(gridData.getSourceFilePath());
            GeoTiffReader reader = new GeoTiffReader(file);
            GridCoverage2D coverage = reader.read(null);
            System.out.println("coverage = " + coverage);
            log.info("栅格数据读取完成");
            if (coverage != null) {
                // 获取和打印坐标参考系统信息
                CoordinateReferenceSystem crs = coverage.getCoordinateReferenceSystem();
                System.out.println("CRS: " + crs);
                // 获取栅格图像
                RenderedImage image = coverage.getRenderedImage();
                System.out.println("image = " + image);
                // 获取栅格的范围（Envelope）
                Envelope2D envelope = coverage.getEnvelope2D();
                System.out.println("Raster bounds: " + envelope.toString());
                System.out.println("Raster read successfully!");
            } else {
                System.err.println("Failed to read raster data.");
            }
            // 3. 导入栅格数据
            Integer saveRaster = storeRasterIntoDatabase(datastore, sourceFilePath, schemaName, targetDatasetName);
            System.out.println("saveRaster = " + saveRaster);
            log.info("栅格数据导入完成");
            // 4. 更新导入状态 判断是否大于0
            if (saveRaster > 0) {
                serverImportInfo.setState("FINISHED");
            } else {
                serverImportInfo.setState("FAILED");
            }
            // 5. 保存导入信息
            saveGridInfo(serverImportInfo);
            log.info("导入信息保存完成");

        } catch (Exception e) {
            log.error("导入栅格数据失败", e);
        } finally {
            // 关闭数据源
            datastore.dispose();
            log.info("数据源已关闭");
        }
        return String.valueOf(serverImportInfo.getId());
    }

public Integer storeRasterIntoDatabase(JDBCDataStore dataSource, String filePath, String schemaName, String targetDatasetName) throws IOException, SQLException, FactoryException {
        File file = new File(filePath);
        GeoTiffReader reader = new GeoTiffReader(file);
        GridCoverage2D coverage = reader.read(null);
        if (coverage == null) {
            throw new IOException("Failed to read raster data.");
        }
        RenderedImage image = coverage.getRenderedImage();
        Envelope2D envelope = coverage.getEnvelope2D();
        CoordinateReferenceSystem crs = coverage.getCoordinateReferenceSystem();
        int width = image.getWidth();
        int height = image.getHeight();
        double xPixelSize = envelope.getWidth() / width;
        double yPixelSize = envelope.getHeight() / height;
        double upperLeftX = envelope.getMinimum(0);
        double upperLeftY = envelope.getMaximum(1);
        // 获取srid
        int srid = CRS.lookupEpsgCode(crs, true);
        log.info("srid = " + srid);

        log.info("Raster size: " + width + " x " + height);
        log.info("Pixel size: " + xPixelSize + " x " + yPixelSize);
        log.info("Upper-left corner: " + upperLeftX + ", " + upperLeftY);
        String tableName = schemaName + "." + targetDatasetName;
        // Convert GridCoverage2D to image
        File tempFile = File.createTempFile("raster", ".tif");
        GeoTiffWriter writer = null;
        try {
            writer = new GeoTiffWriter(tempFile);
            writer.write(coverage, null);
        } finally {
            if (writer != null) {
                // Dispose of the writer to release resources
                writer.dispose();
            }
        }
        byte[] rasterData = convertCoverageToByteArray(coverage);
        //System.out.println("rasterData1 = " + Arrays.toString(rasterData));
        // 将栅格数据转换为字节数组
        Integer saveraster = 0;
        // Insert into PostGIS
        try (Connection connection = dataSource.getDataSource().getConnection()) {
            // raster ST_FromGDALRaster(bytea gdaldata, integer srid=NULL);
            //String sql = "INSERT INTO " + tableName + " (rast) VALUES (st_fromgdalraster(?::bytea,?))";
            String sql = "INSERT INTO " + tableName + " (rast) VALUES (st_fromgdalraster(?,?))";
            try (PreparedStatement stmt = connection.prepareStatement(sql)) {
                // 将字节数组作为二进制数据插入
                stmt.setBinaryStream(1, new ByteArrayInputStream(rasterData));
                // 设置栅格数据的SRID（可选）
                stmt.setInt(2, srid);
                log.info("stmt = " + stmt);
                stmt.executeUpdate();
                ++saveraster;
            }
            log.info("Inserted {} raster(s) into {}", saveraster, tableName);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            reader.dispose();
            dataSource.dispose();
        }

        return saveraster;
    }

    private byte[] convertCoverageToByteArray(GridCoverage2D coverage) throws IOException {
        // Create a temporary file to store the GeoTIFF
        File tempFile = File.createTempFile("coverage", ".tif");
        tempFile.deleteOnExit();

        GeoTiffWriter writer = null;
        try {
            writer = new GeoTiffWriter(tempFile);
            writer.write(coverage, null);
        } finally {
            if (writer != null) {
                // Dispose of the writer to release resources
                writer.dispose();
            }
        }
        // Read GeoTIFF file into byte array
        try (ByteArrayOutputStream baos = new ByteArrayOutputStream()) {
            Files.copy(tempFile.toPath(), baos);
            return baos.toByteArray();
        }
    }

这个是整体导入的示例，拓展一下也可以切片导入

既然可以导入栅格，那么反过来也是可以导出栅格数据变成栅格文件的，示例代码如下：

public String exportRasterData(GridDataDto gridDataDto) throws IOException {
        GridData gridData = gridDataDto.getParameter();
        // 导出目标文件的路径信息
        String targetFilePath = gridData.getTargetFilePath();
        // 导出的源数据集
        String sourceDataset = gridData.getSourceDataset();
        // 解析 PostgreSQL 数据库 连接参数
        Map<String, Object> dataSourceMap = parseDatabaseParameters(sourceDataset);
        log.info("Data source map: {}", dataSourceMap);
        // 创建 PostgreSQL/PostGIS 表
        JDBCDataStoreFactory dsf = new PostgisNGDataStoreFactory();
        JDBCDataStore datastore = dsf.createDataStore(dataSourceMap);
        // 如果不存该模式，则给出提示
        String schemaName = dataSourceMap.get("schema").toString();
        String tableName = dataSourceMap.get("dataset").toString();
        try (Connection connection = datastore.getDataSource().getConnection()) {
            Statement stmt = connection.createStatement();
            String checkSchemaSql = "SELECT schema_name FROM information_schema.schemata WHERE schema_name = '" + schemaName + "'";
            ResultSet rs = stmt.executeQuery(checkSchemaSql);
            if (!rs.next()) {
                log.error("schema notexist, create schema: {}", schemaName);
            }
            // 检查表是否存在，不存在则给出提示
            String checkTableSql = "SELECT to_regclass('" + schemaName + "." + tableName + "')";
            rs = stmt.executeQuery(checkTableSql);
            if (!rs.next() || rs.getString(1) == null) {
                log.error("Table notexist, create table: {}", tableName);
            }
            // 把数据库中的栅格数据导出成一个文件
            exportRasterFromDatabaseToImageFile(connection, schemaName, tableName, targetFilePath);

            // 保存到记录表中
            ServerImportInfo serverImportInfo = new ServerImportInfo();
            BeanUtils.copyProperties(gridData, serverImportInfo);
            Date now = new Date();
            serverImportInfo.setCreateTime(now);
            serverImportInfo.setStartTime(now);
            serverImportInfo.setUpdateTime(now);
            serverImportInfo.setState("RUNNING");
            serverImportInfo.setDataType("栅格-导出");
            serverImportInfo.setState("FINISHED");
            serverImportInfoService.save(serverImportInfo);
            log.info("导出信息保存完成");

            return String.valueOf(serverImportInfo.getId());

        } catch (SQLException e) {
            log.error("Failed to ensure schema exists", e);
            throw new IOException("Failed to ensure schema exists", e);
        } finally {
            datastore.dispose();
        }

    }

public void exportRasterFromDatabaseToImageFile(Connection connection, String schemaName, String targetDatasetName, String imagePath) {
        try {
            // Retrieve raster data from the database
            byte[] rasterData = getRasterDataFromDB(connection, schemaName, targetDatasetName);
            // log.info("rasterData = " + Arrays.toString(rasterData));
            log.info("Converting raster data to images...");
            convertRasterDataToImages(rasterData, imagePath);
            log.info("Conversion complete.");
        } catch (SQLException | IOException e) {
            e.printStackTrace();
        }
    }

  private byte[] getRasterDataFromDB(Connection connection, String schemaName, String targetDatasetName) throws SQLException, IOException {
        String tableName = schemaName + "." + targetDatasetName;
        String sql = "SELECT ST_AsGDALRaster(rast, 'GTiff') FROM " + tableName + " LIMIT 1";
        log.info("Executing SQL: " + sql);
        try (PreparedStatement stmt = connection.prepareStatement(sql);
             ResultSet rs = stmt.executeQuery()) {
            if (rs.next()) {
                log.info("Retrieved raster data from database.");
                try (InputStream is = rs.getBinaryStream(1); ByteArrayOutputStream baos = new ByteArrayOutputStream()) {
                    byte[] buffer = new byte[1024];
                    int bytesRead;
                    while ((bytesRead = is.read(buffer)) != -1) {
                        baos.write(buffer, 0, bytesRead);
                    }
                    return baos.toByteArray();
                }
            } else {
                throw new SQLException("No raster data found.");
            }
        }
    }

 private void convertRasterDataToImages(byte[] rasterData, String outTiffPath) throws IOException {
        File tempFile = File.createTempFile("raster", ".tif");
        tempFile.deleteOnExit();
        try (FileOutputStream fos = new FileOutputStream(tempFile)) {
            fos.write(rasterData);
        }
        AbstractGridFormat format = GridFormatFinder.findFormat(tempFile);
        GridCoverage2DReader reader = format.getReader(tempFile, null);
        GridCoverage2D coverage = reader.read(null);
        log.info("Coverage name: {}", coverage.getName());
        writeTiff(coverage, outTiffPath);
    }

 public static void writeTiff(Coverage coverage, String outTiffPath) throws IOException {
        File file = new File(outTiffPath);
        GeoTiffWriter geoTiffWriter = new GeoTiffWriter(file);
        final GeoTiffFormat format = new GeoTiffFormat();
        final GeoTiffWriteParams wp = new GeoTiffWriteParams();
        // 设置写出参数
        wp.setCompressionMode(GeoTiffWriteParams.MODE_DEFAULT);
        wp.setTilingMode(GeoToolsWriteParams.MODE_DEFAULT);
        ParameterValueGroup paramWrite = format.getWriteParameters();
        paramWrite.parameter(AbstractGridFormat.GEOTOOLS_WRITE_PARAMS.getName().toString()).setValue(wp);
        geoTiffWriter.write((GridCoverage) coverage, paramWrite.values().toArray(new GeneralParameterValue[4]));
        geoTiffWriter.dispose();
    }

至此！即可实现栅格数据通过代码进行导入导出了！