前言：
業(yè)務(wù)上通過GIS軟件將空間數(shù)據(jù)導(dǎo)入到數(shù)據(jù)庫時，因為不同的數(shù)據(jù)來源和軟件設(shè)置，可能導(dǎo)入到數(shù)據(jù)庫的空間表坐標系是各種各樣的。
如果要把數(shù)據(jù)庫空間表發(fā)布到geoserver并且統(tǒng)一坐標系，只是在geoserver單純的設(shè)置坐標系只是改了定義并沒有實際執(zhí)行坐標轉(zhuǎn)換，所以需要在數(shù)據(jù)庫層面統(tǒng)一好坐標系，再發(fā)布到geoserver。
1,開發(fā)前準備
1.1,數(shù)據(jù)準備
要準備測試數(shù)據(jù)，可以參考 地理空間表的導(dǎo)入。
我這里使用arcgis pro導(dǎo)入sqlserver，如果導(dǎo)入postgresql需要企業(yè)數(shù)據(jù)庫才行，也就是需要離線證書，比較麻煩。
我先導(dǎo)入一個4524的投影坐標，測試轉(zhuǎn)換為4490
1.2,環(huán)境準備
坐標轉(zhuǎn)換需要先讀取數(shù)據(jù)庫的空間表原坐標系，在根據(jù)原坐標系轉(zhuǎn)換為目標坐標系。
使用的轉(zhuǎn)換工具是geotool。
pom引入必要的依賴，geotools版本是24.3
<dependency>
<groupId>org.geotools</groupId>
<artifactId>gt-main</artifactId>
<version>${geotools.version}</version>
</dependency>
<dependency>
<groupId>org.geotools</groupId>
<artifactId>gt-jdbc</artifactId>
<version>${geotools.version}</version>
</dependency>
<dependency>
<groupId>org.geotools.jdbc</groupId>
<artifactId>gt-jdbc-sqlserver</artifactId>
<version>${geotools.version}</version>
</dependency>
<dependency>
<groupId>org.geotools.jdbc</groupId>
<artifactId>gt-jdbc-postgis</artifactId>
<version>${geotools.version}</version>
</dependency>
2,讀取空間表原坐標系
要使用geotool讀取空間表的坐標系，需要先使用geotool提供的方法創(chuàng)建DataStore,官網(wǎng)有一個示例代碼
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應(yīng)該就不需要使用jtds前綴了，實際上不加必報錯
先寫一個測試方法，傳入數(shù)據(jù)庫連接信息，表名，數(shù)據(jù)庫類型，返回原表坐標系
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<String, Object> 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內(nèi)，針對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,執(zhí)行坐標轉(zhuǎn)換
我這里目標坐標系寫死，因為系統(tǒng)需要插入到sqlserver中的都要統(tǒng)一坐標系，所以直接在原表更新了。
如果要保留原表信息可以復(fù)制表在副本表更新坐標。
sqlserver與postgresql中空間函數(shù)有些差異，需要區(qū)分處理。文章來源：http://www.zghlxwxcb.cn/news/detail-631339.html
/**
* 地理空間表坐標轉(zhuǎn)換
*
* @param sourceEpsg 原表坐標系
* @param config 數(shù)據(jù)庫連接信息
* @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;
}
// 使用連接執(zhí)行 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); // 將源坐標轉(zhuǎn)換為目標坐標，并保存在 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())) {
//修復(fù)多邊形錯誤 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("坐標轉(zhuǎn)換中sql執(zhí)行異常：{}", e.getMessage());
} catch (ParseException e) {
log.error("坐標轉(zhuǎn)換中異常：{}", e.getMessage());
}
}
上述代碼只是sqlservcer親測多種坐標系轉(zhuǎn)換正常，且轉(zhuǎn)換后的表發(fā)布到geoserver和arcgis都能正常預(yù)覽且聚焦位置正確，postgresql還有待測試文章來源地址http://www.zghlxwxcb.cn/news/detail-631339.html
4,單元測試
public static void main(String[] args) throws SQLException {
String tableName = "ROAD";
//測試sqlserver
DatabaseConfig databaseConfig = new DatabaseConfig(DatabaseType.SQLSERVER, "127.0.0.1", 1433, "測試中文數(shù)據(jù)庫", "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,則執(zhí)行轉(zhuǎn)換
if (sourceEpsg > 0 && sourceEpsg != 4490) {
epsgTo4490(sourceEpsg, databaseConfig, tableName, "Shape");
System.out.println("坐標轉(zhuǎn)換完成");
}
}
到了這里，關(guān)于坐標轉(zhuǎn)換-使用geotools讀取和轉(zhuǎn)換地理空間表的坐標系（sqlserver、postgresql）的文章就介紹完了。如果您還想了解更多內(nèi)容，請在右上角搜索TOY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！
