如果数据库中的某条数据是经纬度的时候处理方法

写一个工具类

1 连上PipeGisInfo库

2 查出所有表名

写一个函数 参数为 （point p1,point p2）-------------point是属性为经纬度的点

3 遍历每个表 判断每个表的是否有the_geom字段；如果有：遍历条数据 判断该the_geom的第一个点 是否在p1 p2之间；如果在 继续下次循环；如果不在，删除此条数据

这是我的工作任务，由于自己掌握的java技术不太强硬，所以难免有错误，请大家帮忙提问修改。

1/直接上代码，不解释。实体类有经纬度，因为数据库存储的就是the_geom  xx(22.22 33.22)格式的

package com.entity;import java.io.Serializable;public class Point implements Serializable {/** *  */private static final long serialVersionUID = -1602091081457600614L;private double lon; // 经度private double lat; // 维度public Point() {}public Point(double lon, double lat) {super();this.lon = lon;this.lat = lat;}public double getLon() {return lon;}public void setLon(double lon) {this.lon = lon;}public double getLat() {return lat;}public void setLat(double lat) {this.lat = lat;}}

2.工具类 PropertiesUtil 

package com.util;import java.io.IOException;import java.util.Properties;public class PropertiesUtil {private static Properties pro = null;public static Properties getProperties(){if(null == pro){pro = new Properties();}try {pro.load(PropertiesUtil.class.getResourceAsStream("/jdbc.properties"));} catch (IOException e) {e.printStackTrace();}return pro;}}

工具类 SqlHelper

package com.util;import java.sql.Connection;import java.sql.DriverManager;import java.sql.SQLException;import java.util.Properties;public class SqlHelper {private static Properties pro = PropertiesUtil.getProperties();static{try {Class.forName(pro.getProperty("driverClassName"));} catch (ClassNotFoundException e) {e.printStackTrace();}}public static Connection getConnection(){java.sql.Connection con = null;try {con = DriverManager.getConnection(pro.getProperty("url"),pro.getProperty("userName"),pro.getProperty("password"));} catch (SQLException e) { e.printStackTrace(); }return con;}}

3.service层数据处理

package com.service;import java.sql.Connection;import java.sql.ResultSet;import java.sql.ResultSetMetaData;import java.sql.SQLException;import java.sql.Statement;import com.entity.Point;import com.util.SqlHelper;public class CheckService {public boolean checkPoint(Point p1, Point p2) {Connection con = SqlHelper.getConnection();Statement stmt = null;try {stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);// 获得所有的表信息ResultSet rsTable = con.getMetaData().getTables(null, null, null, new String[] { "TABLE" });while (rsTable.next()) { // 循环所有的表// 得到表名String tableName = rsTable.getString(3);boolean hasGeom = validateColumn(tableName, stmt);if (hasGeom) {String sql = "select *,ST_asText(the_geom) from \"" + tableName + "\"";ResultSet rs = stmt.executeQuery(sql);// 删除该表System.out.println("======================点的集合Start=======================");while (rs.next()) {String the_geom = rs.getString(1);boolean flag = validatePoint(p1, p2, the_geom);if (!flag) {// 如果第一点在两点之外则删除数据库中的数据rs.deleteRow();String sqldelete = "delete from \"" + tableName + "\" where ST_asText(the_geom) = " + the_geom;System.out.println(sqldelete);}}System.out.println("======================点的集合End=======================");}System.out.println("---------------------------------");}} catch (SQLException e) {e.printStackTrace();}return true;}/** * 判断点the_geom的第一个点是否在给定的两点之间 *  * @param p1 * @param p2 * @param theGeom * @return */private boolean validatePoint(Point p1, Point p2, String theGeom) {Point p = getPointFromGeom(theGeom);if (p1.getLon() < p.getLon() && p2.getLon() > p.getLon() && p1.getLat() < p.getLat() && p2.getLat() > p.getLat()) {return true;}return false;}/** * 根据the_geom字符串的第一个点构造Point *  * @param theGeom * @return */private Point getPointFromGeom(String theGeom) {// 首先判断theGeom的开头是(点线面)Point p = new Point();String firstPoint = null;if (null != theGeom) {/*数据格式 * POINT(1 1) MULTIPOINT(1 1, 3 4, -1 3) LINESTRING(1 1, 2 2, 3 4) * POLYGON((0 0, 0 1, 1 1, 1 0, 0 0)) MULTIPOLYGON((0 0, 0 1, 1 1, 1 * 0, 0 0), (5 5, 5 6, 6 6, 6 5, 5 5)) MULTILINESTRING((1 1, 2 2, 3 * 4),(2 2, 3 3, 4 5)) */if (theGeom.startsWith("MULTILINESTRING((")) {firstPoint = theGeom.substring("MULTILINESTRING((".length(), theGeom.indexOf(","));} else if (theGeom.startsWith("MULTIPOLYGON(((")) {firstPoint = theGeom.substring("MULTIPOLYGON(((".length(), theGeom.indexOf(","));} else if (theGeom.startsWith("POINT(")) {firstPoint = theGeom.substring("POINT(".length(), theGeom.indexOf(")"));} else if (theGeom.startsWith("MULTIPOINT(")) {firstPoint = theGeom.substring("MULTIPOINT(".length(), theGeom.indexOf(","));} else if (theGeom.startsWith("LINESTRING(")) {firstPoint = theGeom.substring("LINESTRING(".length(), theGeom.indexOf(","));} else if (theGeom.startsWith("POLYGON((")) {firstPoint = theGeom.substring("POLYGON((".length(), theGeom.indexOf(","));}String[] firstPointArr = firstPoint.split(" ");p.setLon(Double.parseDouble(firstPointArr[0]));p.setLat(Double.parseDouble(firstPointArr[1]));}return p;}private boolean validateColumn(String tableName, Statement stmt) {boolean hasGeom = false;try {String sql = "select * from \"" + tableName + "\"";ResultSet rs;rs = stmt.executeQuery(sql);ResultSetMetaData meta = rs.getMetaData();int count = meta.getColumnCount();// 表是否有the_geom字段for (int i = 0; i < count; i++) {String colName = meta.getColumnName(i + 1);if ("the_geom".equalsIgnoreCase(colName)) {hasGeom = true;break;}}} catch (SQLException e) {e.printStackTrace();}return hasGeom;}}

4.进行测试

package com.test;import com.entity.Point;import com.service.CheckService;public class Test {public static void main(String args[]) {CheckService cs = new CheckService();Point p1 = new Point(111.112804,36.7943374);Point p2 = new Point(121.102804,39.7243374);cs.checkPoint(p1, p2);}}

ps：个人觉得还不错，自己的技术得到了很大的提升，因为里面的知识点很多，任何一个部分都基本上是一个知识点，希望大家能认真阅读。

大神求别笑。