利用st_geometry进行图形叠加分析--结合mybatis

最新推荐文章于 2023-08-04 11:12:49 发布

weixin_34068198

最新推荐文章于 2023-08-04 11:12:49 发布

阅读量739

点赞数

文章标签： java 数据库 json

原文链接：https://my.oschina.net/u/351612/blog/791504

版权

2019独角兽企业重金招聘Python工程师标准>>>

在上一遍利用st_geometry进行图形叠加分析文章的基础上，结合mybatis操作st_geometry，主要简单实现一下三个功能：利用st_geometry两个表之间图形表的叠加分析、WKT与图形表的叠加分析、将WKT存储至数据图形表。

两个表之间图形表的叠加分析

构造sql

两个表之间图形表的叠加分析，先来创建一个专门构造sql的类，然后利用mybatis创建构造叠加分析的sql语句的方法

package com.zifan.st_geometry.mapper;

import java.lang.reflect.Field;

import org.apache.ibatis.jdbc.SQL;


public class STGeomertySqlBuilder {
	
	/**
	 * 
	 * @param srcTableName 源表表名字
	 * @param targetTableName 目标表的表名
	 * @param fields 返回表字段数据，如果两个表明字段都一样，要求添加前缀T或者S，例如T.OBJECTID
	 * @param where 原表数据条件，例如 objectId=100
	 * @return
	 */
	public String buildQueryIntersectByWhere(final String srcTableName, final String targetTableName,
			final String[] fields, final String where) {
		return new SQL() {
			{
				SELECT("SDE.ST_AREA(SDE.ST_TRANSFORM(S.SHAPE, #{srId})) SAREA");
				SELECT("SDE.ST_ASTEXT(SDE.ST_INTERSECTION(SDE.ST_TRANSFORM(T.SHAPE, #{srId}),SDE.ST_TRANSFORM(S.SHAPE, #{srId}))) OVERLAPCOORDS");
				SELECT("SDE.ST_AREA(SDE.ST_INTERSECTION(SDE.ST_TRANSFORM(T.SHAPE, #{srId}),SDE.ST_TRANSFORM(S.SHAPE, #{srId}))) OVERLAPAREA");
				if (fields != null) {
					for (String field : fields) {
						SELECT(field);
					}
				}
				FROM(srcTableName + " S").FROM(targetTableName + " T");//添加表别名
				WHERE("SDE.ST_INTERSECTS(T.SHAPE, S.SHAPE) = 1").AND().WHERE(where);
			}
		}.toString();
	}
}

上面buildQueryIntersectByWhere方法返回的是根据一个源表某条或者多条的图形数据与需要叠加分析的表的sql语句。返回的sql语句出了会返回指定的字段数据外，默认会返回源图形面积、叠加面积以及叠加图形的坐标串WKT。

mapper接口类

接下来进行实际叠加分析操作写mybatis的Mapper接口

package com.zifan.st_geometry.mapper;

import java.util.HashMap;
import java.util.List;

import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.SelectProvider;
import org.apache.ibatis.type.ClobTypeHandler;


@Mapper
public interface STGeometryMapper {

	@Results({@Result(property = "OVERLAPCOORDS", column = "OVERLAPCOORDS", javaType = String.class, typeHandler = ClobTypeHandler.class) })
	@SelectProvider(type = STGeomertySqlBuilder.class, method = "buildQueryIntersectByWhere")
	List<HashMap<String, Object>> queryIntersectByWhere(String srcTableName, String targetTableName, String[] fields,
			String where, @Param("srId") int srId);

}

这个方法返回的是List的HashMap，通过mybatis的注解SelectProvider定义此方法的查询语句。对于返回数据的处理，利用注解Results将返回的数据中包含叠加的WKT坐标串就涉及到CLOB大字段通过ClobTypeHandler类将转换成String类型。mybatis提供一个TypeHandler接口类，实现该接口处理要插入到数据库或者数据库返回的字段，ClobTypeHandler是mybatis自带的一个clob字段处理类。

测试

@RunWith(SpringJUnit4ClassRunner.class)
@SpringBootTest
public class STGeometryTest {
	@Autowired
	private STGeometryMapper stGeometryMapper;	
	
	@Test
	public void test() {
		List<HashMap<String, Object>> map = stGeometryMapper.queryIntersectByWhere("TABLE1", "TABLE2",
				new String[] { "T.DL", "S.MC"}, "S.OBJECTID = 18133", 2);
		// System.out.println(net.minidev.json.JSONArray.toJSONString(map));
		Assert.assertEquals(4, map.size());

	}	
}

测试结果返回4条叠加数据，可以打印出来返回list的JSON数据看看，这里我省略的坐标的显示。

[
    {
        "SAREA": 19562.0006962064,
        "OVERLAPAREA": 2776.6041580972,
        "OVERLAPCOORDS": "POLYGON  (( ...))",
        "DL": "4419",
        "MC": "项目1"
    },
    {
        "SAREA": 19562.0006962064,
        "OVERLAPAREA": 14444.0513595036,
        "OVERLAPCOORDS": "MULTIPOLYGON  ((( ...)))",
        "DL": "4419",
        "MC": "项目2"
    },
    {
        "SAREA": 19562.0006962064,
        "OVERLAPAREA": 0.00205911626740205,
        "OVERLAPCOORDS": "POLYGON  (( ...))",
        "DL": "4419",
        "MC": "项目3"
    },
    {
        "SAREA": 19562.0006962064,
        "OVERLAPAREA": 2341.34313614271,
        "OVERLAPCOORDS": "POLYGON  (( ...))",
        "DL": "4419",
        "MC": "项目4"
    }
]

WKT与图形表的叠加分析

构造sql

同样通过mybatis来构造叠加分析的sql语句

    /**
	 * 
	 * @param targetTableName 叠加图层的表名
	 * @param fields 返回表字段数据
	 * @return
	 */
	public String buildQueryIntersectByWKT(final String targetTableName, final String[] fields) {
		return new SQL() {
			{
				SELECT("SDE.ST_AREA(SDE.ST_GEOMETRY(#{wkt,typeHandler=com.zifan.st_geometry.handler.STGeometryHandler}, #{srId})) SAREA");
				SELECT("SDE.ST_ASTEXT(SDE.ST_INTERSECTION(SDE.ST_TRANSFORM(SHAPE, #{srId}),SDE.ST_GEOMETRY(#{wkt,typeHandler=com.zifan.st_geometry.handler.STGeometryHandler}, #{srId}))) OVERLAPCOORDS");
				SELECT("SDE.ST_AREA(SDE.ST_INTERSECTION(SDE.ST_TRANSFORM(SHAPE, #{srId}),SDE.ST_GEOMETRY(#{wkt,typeHandler=com.zifan.st_geometry.handler.STGeometryHandler}, #{srId}))) OVERLAPAREA");
				if (fields != null) {
					for (String field : fields) {
						SELECT(field);
					}
				}
				FROM(targetTableName);
				WHERE("SDE.ST_INTERSECTS(SHAPE, SDE.ST_GEOMETRY(#{wkt,typeHandler=com.zifan.st_geometry.handler.STGeometryHandler}, #{srId})) = 1");
			}
		}.toString();
	}

自定义TypeHandler处理CLOB类型的WKT

上面方法中涉及到一个自定义的TypeHandler类，专门用来处理WKT字符转换成Clob，然后进行数据库操作。代码如下：

package com.zifan.st_geometry.mapper.handler;

import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;

import oracle.sql.CLOB;

public class STGeometryHandler extends BaseTypeHandler<String> {

	@Override
	public void setNonNullParameter(PreparedStatement ps, int i, String parameter, JdbcType jdbcType)
			throws SQLException {
		// TODO Auto-generated method stub
		CLOB clob = CLOB.createTemporary(ps.getConnection(), true, CLOB.DURATION_SESSION);
		clob.setString(1, parameter);//ojdbc14以上支持
		ps.setClob(i, clob);
	}

	@Override
	public String getNullableResult(ResultSet rs, String columnName) throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public String getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public String getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

}

mybatis的Mapper接口方法实现

@Results({@Result(property = "OVERLAPCOORDS", column = "OVERLAPCOORDS", javaType = String.class, typeHandler = ClobTypeHandler.class) })
@SelectProvider(type = STGeomertySqlBuilder.class, method = "buildQueryIntersectByWKT")
List<HashMap<String, Object>> queryIntersectByWKT(String targetTableName, String[] fields, @Param("wkt") String wkt,
			@Param("srId") int srId);

返回结果与第一个功能的结果一样

测试

    @Test
	public void testIntersectByWKT() {
		String shortWkt = "POLYGON ((3 3, 4 6, 5 3, 3 3))";
		List<HashMap<String, Object>> map = intersect.queryIntersectByWKT("TB2013",
				new String[] { "DL", "MC"}, shortWkt, 2);
		// System.out.println(net.minidev.json.JSONArray.toJSONString(map));
		Assert.assertEquals(80, map .size());
	}

使用这种方法进行叠加分析要注意的是如果WKT坐标串越长这种方法执行的时间就越长，主要是由于这里的sql构造涉及了四个一模一样CLOB内容的输入。所以在更多情况下还是建议将这种方法变换成第一种的分析方法为好，具体就是先将WKT存储到临时的图形表中，然后才用表与表之间的分析方法进行叠加产生结果。

将WKT存储至数据图形表

创建图形数据基础类

这里我们先设计一个基础类，所有图形数据表都继承它，类属性有图形表名字、空间参考ID、objectId、wkt、表所在的数据库用户。代码：

package com.zifan.st_geometry.model;

public abstract class STGeometry {
	
	//主要用于生成OBJECTID
	private String tableName;
	//主要用于生成OBJECTID
	private String owner;
	private long objectId;
	private int srId;
	private String wkt;

	public int getSrId() {
		return srId;
	}

	public void setSrId(int srId) {
		this.srId = srId;
	}

	public String getWkt() {
		return wkt;
	}

	public void setWkt(String wkt) {
		this.wkt = wkt;
	}

	public long getObjectId() {
		return objectId;
	}

	public void setObjectId(long objectId) {
		this.objectId = objectId;
	}

	public String getTableName() {
		if (tableName == null) {
			// 默认获取类名称
			tableName = this.getClass().getSimpleName();
		}
		return tableName;
	}

	public void setTableName(String tableName) {
		this.tableName = tableName;
	}

	public String getOwner() {
		return owner;
	}

	public void setOwner(String owner) {
		this.owner = owner;
	}

}

这里有两个字段需要说明一下，tableName、Owner，他们主要是用于产生图形数据中的主键OBJECTID的，查找下一个OBJECTID的sql语句：

SELECT SDE.VERSION_USER_DDL.NEXT_ROW_ID(UPPER('gis')
                                        (SELECT REGISTRATION_ID
                                           FROM SDE.TABLE_REGISTRY
                                          WHERE TABLE_NAME = UPPER('tb')
                                            AND OWNER = UPPER('gis'))) AS OBJECTID

构造sql

public String buildInsertSql(final STGeometry geometry)
			throws NoSuchMethodException, IllegalAccessException, InvocationTargetException {
		return new SQL() {
			{
				INSERT_INTO(geometry.getTableName());
				Field[] fieldList = geometry.getClass().getDeclaredFields();
				for (Field field : fieldList) {
					String methodName = "get".concat(field.getName().substring(0, 1).toUpperCase())
							.concat(field.getName().substring(1));
					Method method = geometry.getClass().getMethod(methodName);
					if (method.invoke(geometry) != null) {
						VALUES(field.getName(), "#{" + field.getName() + "}");
					}
				}
				VALUES("SHAPE",
						"SDE.ST_GEOMETRY(#{wkt,typeHandler=com.zifan.st_geometry.handler.STGeometryHandler}, #{srId})");
				VALUES("OBJECTID", "#{objectId}");
			}

		}.toString();
	}

这个方法可以根据继承STGeometry类的类动态返回插入图形数据的sql语句。

mybatis的Mapper接口方法实现

    final static String GET_NEXT_OBJECTID_SQL = "SELECT SDE.VERSION_USER_DDL.NEXT_ROW_ID(UPPER(#{owner,typeHandler=com.zifan.st_geometry.handler.DBOwnerHandler}),(SELECT REGISTRATION_ID FROM SDE.TABLE_REGISTRY WHERE TABLE_NAME = UPPER(#{tableName}) AND OWNER = UPPER(#{owner,typeHandler=com.zifan.st_geometry.handler.DBOwnerHandler}))) AS OBJECTID FROM DUAL";

    @SelectKey(statement = GET_NEXT_OBJECTID_SQL, keyProperty = "objectId", before = true, resultType = long.class)
	@InsertProvider(type = STGeomertySqlBuilder.class, method = "buildInsertSql")
	void saveGeometryToTable(STGeometry object);

SelectKey注解主要进行插入数据前先查找下个OBJECTID值。其中上面涉及到一个TypeHandler，主要用户获取表默认所在的数据库用户名

package com.zifan.st_geometry.handler;

import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.apache.ibatis.type.JdbcType;
import org.apache.ibatis.type.TypeHandler;

public class DBOwnerHandler implements TypeHandler<String> {

	public void setParameter(PreparedStatement ps, int i, String parameter, JdbcType jdbcType) throws SQLException {
		// TODO Auto-generated method stub
		if (parameter == null) {
			parameter = ps.getConnection().getMetaData().getUserName();
		}
		ps.setString(i, parameter);
	}

	public String getResult(ResultSet rs, String columnName) throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

	public String getResult(ResultSet rs, int columnIndex) throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

	public String getResult(CallableStatement cs, int columnIndex) throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

}

测试

先来创建一个STGeometry子类，这里有两个属性对应的是数据库的字段。

package com.zifan.st_geometry.model;

public class SubGeometry extends STGeometry {

	private String ysdm;
	private String djth;

	public String getYsdm() {
		return ysdm;
	}

	public void setYsdm(String ysdm) {
		this.ysdm = ysdm;
	}

	public String getDjth() {
		return djth;
	}

	public void setDjth(String djth) {
		this.djth = djth;
	}
}

测试方法

    @Test
	public void testInsert() {
		String shortWkt = "POLYGON ((3 3, 4 6, 5 3, 3 3))";
		SubGeometry czdj = new SubGeometry();
		czdj.setDjth("长");
		czdj.setYsdm("华");
		czdj.setWkt(shorWkt);
		czdj.setSrId(2);
		czdj.setTableName("CZDJ2");
		stGeometryMapper.saveGeometryToTable(czdj);
		Assert.assertEquals(6, czdj.getObjectId());

	}

执行后存储的图形数据djth和ysdm两个属性是存在值的。

至此通过mybatis利用st_geometry就完成了对arcgis图形数据的叠加分析和存储。

以上涉及到的mybatis的使用可以查看其API，此外是通过spring-boot与mybatis结合进行的开发测试。有兴趣可以查看一下关于spring-boot的更多知识。

工程代码见码云通过spring-boot+mybatis+swagger构建。

转载于:https://my.oschina.net/u/351612/blog/791504