geoserver 坐标转换-使用geotools读取和转换地理空间表的坐标系（sqlserver、postgresql）

前言：

业务上通过GIS软件将空间数据导入到数据库时，因为不同的数据来源和软件设置，可能导入到数据库的空间表坐标系是各种各样的。如果要把数据库空间表发布到geoserver并且统一坐标系，只是在geoserver单纯的设置坐标系只是改了定义并没有实际执行坐标转换，所以需要在数据库层面统一好坐标系，再发布到geoserver。

1,开发前准备

1.1,数据准备

要准备测试数据，可以参考地理空间表的导入。我这里使用arcgis pro导入sqlserver，如果导入postgresql需要企业数据库才行，也就是需要离线证书，比较麻烦。我先导入一个4524的投影坐标，测试转换为4490

1.2,环境准备

坐标转换需要先读取数据库的空间表原坐标系，在根据原坐标系转换为目标坐标系。使用的转换工具是geotool。 pom引入必要的依赖，geotools版本是24.3

org.geotools

gt-main

${geotools.version}

org.geotools

gt-jdbc

${geotools.version}

org.geotools.jdbc

gt-jdbc-sqlserver

${geotools.version}

org.geotools.jdbc

gt-jdbc-postgis

${geotools.version}

2,读取空间表原坐标系

要使用geotool读取空间表的坐标系，需要先使用geotool提供的方法创建DataStore,官网有一个示例代码 https://docs.geotools.org/latest/userguide/library/jdbc/sqlserver.html

java.util.Map params = new java.util.HashMap();

params.put( "dbtype", "sqlserver"); //（巨坑）

params.put( "host", "localhost");

params.put( "port", 4866);

params.put( "user", "geotools");

params.put( "passwd", "geotools");

DataStore dataStore=DataStoreFinder.getDataStore(params);

这是一个坑，官方说明是版本14之后支持Microsoft JDBC driver，dbtype应该就不需要使用jtds前缀了，实际上不加必报错

先写一个测试方法，传入数据库连接信息，表名，数据库类型，返回原表坐标系

import org.geotools.data.DataStore;

import org.geotools.data.DataStoreFinder;

import org.geotools.data.simple.SimpleFeatureSource;

import org.geotools.jdbc.JDBCDataStoreFactory;

import org.opengis.feature.simple.SimpleFeatureType;

import org.opengis.referencing.crs.CoordinateReferenceSystem;

public static int getEpsg(DatabaseConfig databaseConfig, String tableName) {

DataStore dataStore = null;

try {

Map params = new HashMap<>();

// params.put(JDBCDataStoreFactory.SCHEMA.key, "dbo");

if (DatabaseType.SQLSERVER.equals(databaseConfig.getDatabaseType())) {

params.put(JDBCDataStoreFactory.DBTYPE.key, "jtds-sqlserver");

} else {

params.put(JDBCDataStoreFactory.DBTYPE.key, "jtds-postgis");

}

params.put(JDBCDataStoreFactory.HOST.key, databaseConfig.getHost());

params.put(JDBCDataStoreFactory.PORT.key, databaseConfig.getPort());

params.put(JDBCDataStoreFactory.DATABASE.key, databaseConfig.getDatabaseName());

params.put(JDBCDataStoreFactory.USER.key, databaseConfig.getUsername());

params.put(JDBCDataStoreFactory.PASSWD.key, databaseConfig.getPassword());

dataStore = DataStoreFinder.getDataStore(params);

if (dataStore == null) {

System.out.println("Failed to connect to the database.");

return -1;

}

// Get the feature source for the "aa" table

SimpleFeatureSource featureSource = dataStore.getFeatureSource(tableName);

// Get the feature type and its CRS

SimpleFeatureType featureType = featureSource.getSchema();

CoordinateReferenceSystem crs = featureType.getCoordinateReferenceSystem();

// Print the CRS details

if (crs != null) {

System.out.println("Spatial Reference System: " + crs.getName());

System.out.println("EPSG Code: " + crs.getName().getCode());

System.out.println("crs : " + crs.toString());

//抽取原表坐标系

int result = extractEPSG(crs.toString());

System.out.println("Result: " + result);

return result;

}

// Close the data store

dataStore.dispose();

return 0;

} catch (IOException e) {

log.error("查询空间表坐标系异常：{}", e.toString());

return -1;

} finally {

if (dataStore != null) {

dataStore.dispose();

}

然后看一下解析出来坐标信息

Spatial Reference System: EPSG:CGCS2000 / 3-degree Gauss-Kruger zone 36

EPSG Code: CGCS2000 / 3-degree Gauss-Kruger zone 36

crs : PROJCS["CGCS2000 / 3-degree Gauss-Kruger zone 36",

GEOGCS["China Geodetic Coordinate System 2000",

DATUM["China 2000",

SPHEROID["CGCS2000", 6378137.0, 298.257222101, AUTHORITY["EPSG","1024"]],

AUTHORITY["EPSG","1043"]],

PRIMEM["Greenwich", 0.0, AUTHORITY["EPSG","8901"]],

UNIT["degree", 0.017453292519943295],

AXIS["Geodetic latitude", NORTH],

AXIS["Geodetic longitude", EAST],

AUTHORITY["EPSG","4490"]],

PROJECTION["Transverse_Mercator", AUTHORITY["EPSG","9807"]],

PARAMETER["central_meridian", 108.0],

PARAMETER["latitude_of_origin", 0.0],

PARAMETER["scale_factor", 1.0],

PARAMETER["false_easting", 36500000.0],

PARAMETER["false_northing", 0.0],

UNIT["m", 1.0],

AXIS["Northing", NORTH],

AXIS["Easting", EAST],

AUTHORITY["EPSG","4524"]]

我想要的是之前我们在arcgis pro中看到的投影坐标，位于crs信息的最后一个EPSG内，针对crs信息写一个方法解析出epsg

public static int extractEPSG(String input) {

Pattern pattern = Pattern.compile("AUTHORITY\\[\"EPSG\",\"(\\d+)\"\\]");

Matcher matcher = pattern.matcher(input);

int lastEPSG = 0;

while (matcher.find()) {

lastEPSG = Integer.parseInt(matcher.group(1));

}

return lastEPSG;

}

3,执行坐标转换

我这里目标坐标系写死，因为系统需要插入到sqlserver中的都要统一坐标系，所以直接在原表更新了。如果要保留原表信息可以复制表在副本表更新坐标。 sqlserver与postgresql中空间函数有些差异，需要区分处理。

/**

* 地理空间表坐标转换

* @param sourceEpsg 原表坐标系

* @param config 数据库连接信息

* @param tableName 表名 dbo.ROAD

* @param geometryColumn 空间字段

public static void epsgTo4490(int sourceEpsg, DatabaseConfig config, String tableName, String geometryColumn) {

String sourceEPSG = "EPSG:" + sourceEpsg;

String targetEPSG = "EPSG:4490";

ResultSet resultSet = null;

try (Connection connection = DatabaseConnection.getConnection(config)) {

//拼接sql

String sql;

if (config.getDatabaseType().SQLSERVER.equals(config.getDatabaseType())) {

sql = "SELECT " + geometryColumn + ".STAsText() as Shape,OBJECTID FROM " + tableName;

} else {

//ST_AsText(columns)

sql = "SELECT ST_AsText(" + geometryColumn + ") as Shape,OBJECTID FROM " + tableName;

}

// 使用连接执行 SQL 查询操作

PreparedStatement statement = connection.prepareStatement(sql);

resultSet = statement.executeQuery();

// Create MathTransform

CRSFactory crsFactory = new CRSFactory();

org.osgeo.proj4j.CoordinateReferenceSystem sourceCRS = crsFactory.createFromName(sourceEPSG);

org.osgeo.proj4j.CoordinateReferenceSystem targetCRS = crsFactory.createFromName(targetEPSG);

CoordinateTransformFactory transformFactory = new CoordinateTransformFactory();

CoordinateTransform transform = transformFactory.createTransform(sourceCRS, targetCRS);

// Process each row of the result set

while (resultSet.next()) {

String shape = resultSet.getString("Shape");

int objectId = resultSet.getInt("OBJECTID");

// Convert the string representation of the geometry to a JTS Geometry object

WKTReader reader = new WKTReader();

Geometry geometry = reader.read(shape);

// Perform the coordinate transformation for each coordinate in the geometry

for (int i = 0; i < geometry.getCoordinates().length; i++) {

Coordinate srcCoord = geometry.getCoordinates()[i];

ProjCoordinate targetCoord = new ProjCoordinate(srcCoord.getX(), srcCoord.getY());

transform.transform(targetCoord, targetCoord); // 将源坐标转换为目标坐标，并保存在 targetCoord 中

srcCoord.setX(targetCoord.x);

srcCoord.setY(targetCoord.y);

}

// Convert the transformed geometry back to a string

WKTWriter writer = new WKTWriter();

String transformedShape = writer.write(geometry);

// Update the original table with the transformed geometry using the primary key

String updateSQL;

if (DatabaseType.SQLSERVER.equals(config.getDatabaseType())) {

updateSQL = "UPDATE " + tableName + " SET " + geometryColumn + " = ? WHERE OBJECTID = ?";

} else {

//UPDATE "public"."ROAD" SET Shape = ST_SetSRID(ST_GeomFromText("Shape"), 4490);

updateSQL = "UPDATE " + tableName + " SET " + geometryColumn + " = ST_SetSRID(?,4490) WHERE OBJECTID = ?";

}

statement = connection.prepareStatement(updateSQL);

statement.setString(1, transformedShape);

statement.setInt(2, objectId);

statement.executeUpdate();

statement.clearParameters();

}

if (DatabaseType.SQLSERVER.equals(config.getDatabaseType())) {

//修复多边形错误 UPDATE dbo.ROAD SET Shape = Shape.MakeValid()

String updateSQL = "UPDATE " + tableName + " SET " + geometryColumn + " = " + geometryColumn + ".MakeValid()";

statement = connection.prepareStatement(updateSQL);

statement.executeUpdate();

//指定坐标系 UPDATE dbo.ROAD SET Shape.STSrid=4490

updateSQL = "UPDATE " + tableName + " SET " + geometryColumn + ".STSrid=4490";

statement = connection.prepareStatement(updateSQL);

statement.executeUpdate();

}

// Close the resources

statement.close();

resultSet.close();

} catch (SQLException e) {

log.error("坐标转换中sql执行异常：{}", e.getMessage());

} catch (ParseException e) {

log.error("坐标转换中异常：{}", e.getMessage());

}

上述代码只是sqlservcer亲测多种坐标系转换正常，且转换后的表发布到geoserver和arcgis都能正常预览且聚焦位置正确，postgresql还有待测试

4,单元测试

public static void main(String[] args) throws SQLException {

String tableName = "ROAD";

//测试sqlserver

DatabaseConfig databaseConfig = new DatabaseConfig(DatabaseType.SQLSERVER, "127.0.0.1", 1433, "测试中文数据库", "sa", "xxxx");

//测试postgresql

//DatabaseConfig databaseConfig = new DatabaseConfig(DatabaseType.POSTGRESQL, "127.0.0.1", 5432, "postgis20", "postgres", "xxxxxxx");

int sourceEpsg = TableEpsgUtil.getEpsg(databaseConfig, tableName);

System.out.println("原表坐标：" + sourceEpsg);

//如果获取到原表坐标并且不是4490,则执行转换

if (sourceEpsg > 0 && sourceEpsg != 4490) {

epsgTo4490(sourceEpsg, databaseConfig, tableName, "Shape");

System.out.println("坐标转换完成");

}

文章来源

评论可见，请评论后查看内容，谢谢！！！

您阅读本篇文章共花了：

金钥匙

geoserver 坐标转换-使用geotools读取和转换地理空间表的坐标系（sqlserver、postgresql）

运维智能制造系统的自动化与智能化的结合

数据库 Ubuntu 安装postgresql

发表评论取消回复

金钥匙

geoserver 坐标转换-使用geotools读取和转换地理空间表的坐标系（sqlserver、postgresql）

运维 智能制造系统的自动化与智能化的结合

数据库 Ubuntu 安装postgresql

相关文章

发表评论取消回复

运维智能制造系统的自动化与智能化的结合