使用 RESTful 接口实现在 Postgis 中建立临时新表并在 Geoserver 中发布图层

说明

本文代码基于作者之前写的 SpringBoot + MyBatis-Plus 读取 PostGIS 数据 中的代码，两篇章共用同一个 Gitee 仓库存放代码

完善服务类

在服务类的接口 IPointTestService 中，我要们新增这个服务

Map<String, Object> autoPublishLayer(Long id);

实现新建临时表的功能

首先先在服务类最开头加入

@Autowired
private JdbcTemplate jdbcTemplate;

实现建表操作

private String createTemporaryTable(String tableName) {
    try {
        String sql = String.format(
            "CREATE TABLE IF NOT EXISTS %s AS SELECT * FROM point_test WHERE 1=0;",
            tableName
        );
        jdbcTemplate.execute(sql);
        return "临时表" + tableName + "创建成功";
    } catch (Exception e) {
        throw new RuntimeException("创建临时表失败：" + e.getMessage());
    }
}

实现删表操作

private boolean dropTemporaryTable(String tableName) {
    try {
        String sql = String.format("DROP TABLE IF EXISTS %s", tableName);
        jdbcTemplate.execute(sql);
        return true;
    } catch (Exception e) {
        throw new RuntimeException(String.format("删除临时表'%s'失败", tableName));
    }
}

实现 Geoserver 相应的功能

在 config 软件包下添加 RestTemplate 的 Bean

package org.example.testgis.config;

import org.springframework.boot.web.client.RestTemplateBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.web.client.RestTemplate;

@Configuration
public class RestTemplateConfig {

    @Bean
    public RestTemplate restTemplate(RestTemplateBuilder builder) {
        return builder.build();
    }
}

在服务类开头加入 restTemplate 以便后续使用 REST 接口

@Autowired
private RestTemplate restTemplate;

在实现类前面加入 Geoserver 相关的基本配置信息

@Value("http://localhost:8081/geoserver")  // 这里注意 geoserver 端口号
private String geoserverUrl;

@Value("admin")
private String geoserverUsername;

@Value("geoserver")
private String geoserverPassword;

@Value("point-temp-workspace")
private String geoserverWorkspace;

实现自动创建 HttpHeaders

private HttpHeaders createAuthHeaders() {
    HttpHeaders headers = new HttpHeaders();
    String auth = geoserverUsername + ":" + geoserverPassword;
    String encodedAuth = java.util.Base64.getEncoder().encodeToString(auth.getBytes());
    headers.set("Authorization", "Basic " + encodedAuth);
    return headers;
}

实现创建新存储的功能，注意此处的 JSON 键不要设错，还有数据库连接相关信息不要设错

private void createDataStore(String tableName, String workspaceName) {
    String url = String.format("%s/rest/workspaces/%s/datastores", 
        geoserverUrl, workspaceName);

    Map<String, Object> dataStore = new HashMap<>();
    Map<String, Object> dataStoreInfo = new HashMap<>();
    dataStoreInfo.put("name", tableName + "_store");
    dataStoreInfo.put("type", "PostGIS");

    Map<String, String> connectionParameters = new HashMap<>();
    connectionParameters.put("host", "localhost");
    connectionParameters.put("port", "5432");
    connectionParameters.put("database", "heitu");
    connectionParameters.put("user", "postgres");
    connectionParameters.put("passwd", "postgres");
    connectionParameters.put("dbtype", "postgis");

    dataStoreInfo.put("connectionParameters", connectionParameters);
    dataStore.put("dataStore", dataStoreInfo);

    HttpHeaders headers = createAuthHeaders();
    headers.setContentType(MediaType.APPLICATION_JSON);
    HttpEntity<Map<String, Object>> entity = new HttpEntity<>(dataStore, headers);

    restTemplate.postForEntity(url, entity, String.class);
}

实现发布新的图层的功能，通过创建新的 featuretype，Geoserver 会自动将其作为新图层发布

private boolean publishLayer(String tableName, String layerName) {
    String url = String.format("%s/rest/workspaces/%s/datastores/%s_store/featuretypes",
            geoserverUrl, geoserverWorkspace, tableName);

    Map<String, Object> featureType = new HashMap<>();
    Map<String, Object> featureTypeInfo = new HashMap<>();
    featureTypeInfo.put("name", layerName);
    featureTypeInfo.put("nativeName", tableName);
    featureTypeInfo.put("title", layerName);
    featureTypeInfo.put("srs", "EPSG:4326");

    featureType.put("featureType", featureTypeInfo);

    HttpHeaders headers = createAuthHeaders();
    headers.setContentType(MediaType.APPLICATION_JSON);
    HttpEntity<Map<String, Object>> entity = new HttpEntity<>(featureType, headers);

    ResponseEntity<String> response = restTemplate.postForEntity(url, entity, String.class);
    return response.getStatusCode().is2xxSuccessful();
}

实现自动发布接口

@Override
public Map<String, Object> autoPublishLayer(Long id) {
    Map<String, Object> result = new HashMap<>();
    String timestamp = String.valueOf(System.currentTimeMillis());
    String tableName = "temp_" + String.valueOf(id) + "_" + timestamp;
    String layerName = String.valueOf(id) + "_layer_" + timestamp;

    // 查询数据是否为空
    PointTest pointTest = findGeoJsonById(id);
    if (pointTest == null) {
        result.put("success", false);
        result.put("message", String.format("找不到id为'%s'的数据", id));
        return result;
    }

    // 创建临时表
    String createTemporaryTableResult = createTemporaryTable(tableName);
    result.put("creatingTemporaryTableResult", createTemporaryTableResult);

    // 复制数据
    String copyDataSQL = String.format(
            "INSERT INTO %s SELECT * FROM point_test WHERE id = $d",
            tableName, id
    );
    result.put("dataCopied", jdbcTemplate.update(copyDataSQL));

    // 发布到 Geoserver
    createDatastore(tableName);
    boolean success = publishLayer(tableName, layerName);
    if (success) {
        result.put("success", true);
        result.put("message", String.format("id为%d的图层发布成功", id));
        result.put("tmpTable", tableName);
        result.put("workspace", geoserverWorkspace);
        result.put("layerName", layerName);
    } else {
        dropTemporaryTable(tableName);
        result.put("success", false);
        result.put("message", String.format("id为%d的图层发布成功", id));
    }

    return result;
}

实现一个简单的 Controller 并测试结果

@GetMapping("/auto-publish")
public ResponseEntity<Map<String, Object>> autoPublish(@RequestParam Long id) {
    Map<String, Object> result = pointTestService.autoPublishLayer(id);
    if ((boolean)result.get("success")) {
        return ResponseEntity.ok(result);
    } else {
        return ResponseEntity.badRequest().body(result);
    }
}	

使用前记得先创建工作空间 point-temp-workspace