最近遇到空间数据的读取问题,于是就查询各种资料,最终搞定,实现简单的查询,能够读取简单的点数据。
oracle的空间数据类型GEOMETRY,读取的过程需要加入两个包:sdoutl.jar和sdoapi.jar。我的JAR包路径:F:\app\Administrator\product\11.2.0\dbhome_1\md\jlib。读者可自行对照。另外推荐读者参考一下oracle的官方文档,有很多函数可以用。
直接上代码:
<%@ page language="java"
//引入自己的数据库连接函数。
import = "java.util.Date,java.sql.* , DB.*"
import = "java.text.*"
import = "java.lang.Object"
import = "java.io.Serializable"
import = "java.lang.Cloneable"
//需要引入的文件
import = "oracle.sql.*"
import = "oracle.spatial.geometry.*"
pageEncoding="UTF-8"%>
<!-- 导入ojdbc14.jar -->
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<base href="<%=basePath%>">
<title>My JSP 'index.jsp' starting page</title>
<meta http-equiv="pragma" content="no-cache">
<meta http-equiv="cache-control" content="no-cache">
<meta http-equiv="expires" content="0">
<meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
<meta http-equiv="description" content="This is my page">
<!--
<link rel="stylesheet" type="text/css" href="styles.css" mce_href="styles.css">
-->
<style type="text/css">
/*重点:固定行头样式*/
.scrollRowThead{position: relative; left: expression(this.parentElement.parentElement.parentElement.parentElement.scrollLeft);z-index:0;}
/*重点:固定表头样式*/
.scrollColThead{position: relative; top: expression(this.parentElement.parentElement.parentElement.scrollTop);z-index:2;}
/*行列交叉的地方*/
.scrollCR { z-index:3;}
/*行头列头背景*/
.scrollRowThead,.scrollColThead td{background-color:rgb(188,232,246);font-weight: bold;}
/*表格的线*/
.scrolltable{border-bottom:1px solid #CCCCCC; border-right:1px solid #CCCCCC;}
/*单元格的线等*/
.scrolltable td{border-left: 1px solid #CCCCCC; border-top: 1px solid #CCCCCC; padding: 3px;}
</style>
<script type="text/javascript">
</script>
</head>
<body>
<%
//连接数据库
Connection conn = new ConnDB().getConn();
Statement stmt=conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
//查询点数据的GEOM,SQL语句可以自行进行变化
ResultSet result = stmt.executeQuery("SELECT GEOM FROM HPDAPPENDIX.NOB_BASIC t where nob_ftype='P'");
//STRUCT st = (STRUCT) result.getObject(1);
//JGeometry jgeom = JGeometry.load(st);
%>
<table width="100%" border="0" cellspacing="0" cellpadding="0" id="ServiceListTable" class="scrolltable">
<thead>
<tr class="scrollRowThead">
<th>维度</th>
<th>经度</th>
<th>纬度</th>
</tr>
</thead>
<tbody>
<tr onmouseover="rowOver(this)" onmouseout="rowOut(this)" onclick="selectRow(this)">
<%
<span style="white-space:pre"> </span>//求出有多少点数据
int num=0;
while(result.next())
{
<span style="white-space:pre"> </span>//构建STRUCT对象,读出数据
STRUCT st = (STRUCT) result.getObject(1);
JGeometry geom = JGeometry.load(st);
//得到纬度。
int dimensionality = geom.getDimensions();
//System.out.println("维度:"+dimensionality);
//得到点的坐标
double[] geomxy = geom.getFirstPoint();
%>
<td><%=dimensionality %></td>
<td><%=geomxy[0] %></td>
<td><%=geomxy[1] %></td>
</tr>
<%
//System.out.println("经度:"+geomxy[0]+";纬度:"+geomxy[1]);
num++;
}
System.out.println(num);
result.close();
conn.close();
%>
</tbody>
</table>
</body>
</html>