Foreword:
When importing spatial data into the database through GIS software in business, due to different data sources and software settings, the spatial table coordinate systems that may be imported into the database are various.
If you want to publish the database space table to GeoServer and unify the coordinate system, you just simply set the coordinate system on GeoServer, just change the definition and do not actually perform coordinate conversion, so you need to unify the coordinate system at the database level, and then publish it to GeoServer.
1, Preparation before development
1.1, data preparation
To prepare test data, you can refer to Importing Geospatial Tables.
I use arcgis pro to import sqlserver here. If you import postgresql, you need an enterprise database, that is, you need an offline certificate, which is troublesome.
I first import a projected coordinate of 4524, and the test is converted to 4490
1.2, Environmental preparation
Coordinate conversion needs to read the original coordinate system of the spatial table of the database first, and then convert to the target coordinate system according to the original coordinate system.
The conversion tool used is geotool.
Pom introduces the necessary dependencies, the geotools version is 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, read the original coordinate system of the space table
To use geotool to read the coordinate system of the spatial table, you need to use the method provided by geotool to create a DataStore. There is a sample code on the official website
https://docs.geotools.org/latest/userguide/library/jdbc/sqlserver.html
java.util.Map params = new java.util.HashMap(); params.put( "dbtype", "sqlserver"); //(huge pit) params. put( "host", "localhost"); params. put( "port", 4866); params. put( "user", "geotools"); params. put( "passwd", "geotools"); DataStore dataStore=DataStoreFinder.getDataStore(params);
This is a trap. The official statement is that Microsoft JDBC driver is supported after version 14. The dbtype should not need to use the jtds prefix. In fact, no error will be reported if it is not added.
Write a test method first, pass in the database connection information, table name, database type, and return the original table coordinate system
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());
// Extract the original table coordinate system
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("The coordinate system of the query space table is abnormal: {}", e.toString());
return -1;
} finally {
if (dataStore != null) {
dataStore.dispose();
}
}
}
Then look at the parsed coordinate information
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"]]
What I want is the projected coordinates we saw in arcgis pro before, which is located in the last EPSG of the crs information. Write a method to parse out the epsg for the crs information
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, perform coordinate transformation
I wrote the target coordinate system hard here, because the system needs to insert into the sqlserver to have a unified coordinate system, so it is directly updated in the original table.
If you want to keep the original table information, you can copy the table and update the coordinates in the copy table.
There are some differences between the spatial functions in sqlserver and postgresql, which need to be handled separately.
/**
* Geospatial table coordinate conversion
*
* @param sourceEpsg original table coordinate system
* @param config database connection information
* @param tableName table name dbo.ROAD
* @param geometryColumn spatial field
*/
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)) {
// splicing 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;
}
// Execute the SQL query operation using the connection
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); // Transform source coordinates into target coordinates and save them in 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())) {
//fix polygon error UPDATE dbo.ROAD SET Shape = Shape.MakeValid()
String updateSQL = "UPDATE " + tableName + " SET " + geometryColumn + " = " + geometryColumn + ".MakeValid()";
statement = connection. prepareStatement(updateSQL);
statement. executeUpdate();
//Specify the coordinate system 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 execution exception during coordinate conversion: {}", e.getMessage());
} catch (ParseException e) {
log.error("Exception in coordinate conversion: {}", e.getMessage());
}
}
The above code is only sqlservcer pro-test that the conversion of various coordinate systems is normal, and the converted table can be previewed normally and the focus position is correct when the converted table is published to geoserver and arcgis. Postgresql has yet to be tested
4, unit test
public static void main(String[] args) throws SQLException {
String tableName = "ROAD";
//Test sqlserver
DatabaseConfig databaseConfig = new DatabaseConfig(DatabaseType.SQLSERVER, "127.0.0.1", 1433, "Test Chinese database", "sa", "xxxx");
//Test postgresql
//DatabaseConfig databaseConfig = new DatabaseConfig(DatabaseType.POSTGRESQL, "127.0.0.1", 5432, "postgis20", "postgres", "xxxxxxx");
int sourceEpsg = TableEpsgUtil. getEpsg(databaseConfig, tableName);
System.out.println("Original table coordinates: " + sourceEpsg);
//If the coordinates of the original table are obtained and are not 4490, perform conversion
if (sourceEpsg > 0 & amp; & amp; sourceEpsg != 4490) {
epsgTo4490(sourceEpsg, databaseConfig, tableName, "Shape");
System.out.println("Coordinate conversion complete");
}
}