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

写一个工具类

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

大神求别笑。