承德 网站建设,网站开发亿玛酷定制,温州企业网站建设费用,网站模板管理前言#xff1a;
业务上通过GIS软件将空间数据导入到数据库时#xff0c;因为不同的数据来源和软件设置#xff0c;可能导入到数据库的空间表坐标系是各种各样的。 如果要把数据库空间表发布到geoserver并且统一坐标系#xff0c;只是在geoserver单纯的设置坐标系只是改了…前言
业务上通过GIS软件将空间数据导入到数据库时因为不同的数据来源和软件设置可能导入到数据库的空间表坐标系是各种各样的。 如果要把数据库空间表发布到geoserver并且统一坐标系只是在geoserver单纯的设置坐标系只是改了定义并没有实际执行坐标转换所以需要在数据库层面统一好坐标系再发布到geoserver。
1,开发前准备
1.1,数据准备
要准备测试数据可以参考 地理空间表的导入。 我这里使用arcgis pro导入sqlserver如果导入postgresql需要企业数据库才行也就是需要离线证书比较麻烦。 我先导入一个4524的投影坐标测试转换为4490
1.2,环境准备
坐标转换需要先读取数据库的空间表原坐标系在根据原坐标系转换为目标坐标系。 使用的转换工具是geotool。 pom引入必要的依赖geotools版本是24.3
dependencygroupIdorg.geotools/groupIdartifactIdgt-main/artifactIdversion${geotools.version}/version
/dependency
dependencygroupIdorg.geotools/groupIdartifactIdgt-jdbc/artifactIdversion${geotools.version}/version/dependencydependencygroupIdorg.geotools.jdbc/groupIdartifactIdgt-jdbc-sqlserver/artifactIdversion${geotools.version}/version/dependencydependencygroupIdorg.geotools.jdbc/groupIdartifactIdgt-jdbc-postgis/artifactIdversion${geotools.version}/version/dependency2,读取空间表原坐标系
要使用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 dataStoreDataStoreFinder.getDataStore(params);这是一个坑官方说明是版本14之后支持Microsoft JDBC driverdbtype应该就不需要使用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 {MapString, 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 tableSimpleFeatureSource featureSource dataStore.getFeatureSource(tableName);// Get the feature type and its CRSSimpleFeatureType featureType featureSource.getSchema();CoordinateReferenceSystem crs featureType.getCoordinateReferenceSystem();// Print the CRS detailsif (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 storedataStore.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)) {//拼接sqlString 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 MathTransformCRSFactory 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 setwhile (resultSet.next()) {String shape resultSet.getString(Shape);int objectId resultSet.getInt(OBJECTID);// Convert the string representation of the geometry to a JTS Geometry objectWKTReader reader new WKTReader();Geometry geometry reader.read(shape);// Perform the coordinate transformation for each coordinate in the geometryfor (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 stringWKTWriter writer new WKTWriter();String transformedShape writer.write(geometry);// Update the original table with the transformed geometry using the primary keyString 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.STSrid4490updateSQL UPDATE tableName SET geometryColumn .STSrid4490;statement connection.prepareStatement(updateSQL);statement.executeUpdate();}// Close the resourcesstatement.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;//测试sqlserverDatabaseConfig 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(坐标转换完成);}}
