[PG] postgis 使用ST_Within圈选某一个几何内的POI

àì夳堔傛蜴生んèń 2022-02-17 13:45 195阅读 0赞

前言

之前有一个需求，需要统计一批门店周边几公里餐饮，娱乐，购物，零售等类型的门店数量。

准备的数据有门店表 bj：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDkwMjUyNw_size_16_color_FFFFFF_t_70][]

geom字段是根据门店坐标计算的到达圈

北京某分类的poi表 poi0419：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDkwMjUyNw_size_16_color_FFFFFF_t_70 1][]

到达圈都需要提前刷好~

具体java实现demo

package com.demo;
    
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.SQLException;
    import java.sql.Statement;
    import java.util.ArrayList;
    import java.util.List;
    import java.util.Map;
    import com.demo.DbHelper;
    
    public class POICountLY {
    	
    	public static void main(String[] args) throws ClassNotFoundException {
    		DbHelper db= new DbHelper();
    //		 String url = "jdbc:mysql://localhost:3306/dataclean?"
    //        + "user=root&password=root&useUnicode=true&characterEncoding=UTF8";  
    //POSTGRES数据库
    		String url = "jdbc:postgresql://192.168.8.133:5432/cxl?"
    			 + "allowMultiQueries=true&useUnicode=true&characterEncoding=utf8"; 
    		String user = "";
    		String psw = "";
    		try {
    			Class.forName("org.postgresql.Driver");   // mysql 8
    			//Class.forName("com.mysql.jdbc.Driver");      //mysql 5
    			Connection	conn = DriverManager.getConnection(url,user,psw);
    			Statement stmt = conn.createStatement();
    			List<String> geoms = new ArrayList<String>();
    			List<String> kinds = new ArrayList<String>();
    			List<String> ids = new ArrayList<String>();
    			//提取poi分类
    			String sql ="select kind from ly_kind";
    			List<Map<String, String>> data = db.queryForList(conn, sql);
    			for(Map<String, String> aMap : data ){
    				String fls = aMap.get("kind"); 
    				if(fls != null && !"".contentEquals(fls)) {
    					kinds.add(fls);
    				}
    			}
    			//提取门店 id ，geom
    			String sql1 ="select id,geom from bj";
    			List<Map<String, String>> data1 = db.queryForList(conn, sql1);
    			for(Map<String, String> aMap : data1 ){
    				String id = aMap.get("id"); 
    				String geom = aMap.get("geom"); 
    				if(geom != null && !"".contentEquals(geom)) {
    					ids.add(id);
    					geoms.add(geom);	
    				}
    			}
    			
    			if (geoms != null && geoms.size() > 0) {
    				
    				for(int j = 0; j < geoms.size(); j++) {
    					String geom = geoms.get(j);
    					String id = ids.get(j);
    					String sql3 = "select count(*) n,kind from poi0419 where ST_Within(geom::geometry,'"+geom+"'::geometry) and citycode='110099' group by kind";
    					System.out.println(sql3);
    					List<Map<String, String>> data3 = db.queryForList(conn, sql3);
    					String sql4 = "insert into ly_bj  values("+id+")"; 
    					stmt.execute(sql4);
    					if(data3 != null && data3.size() > 0) {
    						for (int x = 0; x < data3.size(); x++) {
    							try {
    								Map<String,String> temp = data3.get(x);
    								String num = temp.get("n");
    								String kind = temp.get("kind");
    								boolean flag = false;
    								for(int n = 0; n < kinds.size(); n++) {
    									if(kind.equals(kinds.get(n))){ flag = true;}
    								}
    								if(flag){
    									String sql2 = "update ly_bj set t"+kind+"="+num+" where id="+ id +"";
    									stmt.addBatch(sql2);
    									if(x%100==0){
    										//批量处理
    										System.out.println("第"+x/100+"次执行语句");
    										stmt.executeBatch();
    										//清除stmt中积攒的参数列表
    										stmt.clearBatch();
    										System.out.println("第"+x/100+"次清除batch");
    									}
    								}
    							} catch (Exception e) {
    								System.out.println("错误信息:"+e);
    								continue;
    							}
    							
    						}
    						stmt.executeBatch();
    					}
    					
    				}
    			}
    		System.out.println("更新完毕");
    		} catch (SQLException e) {
    			e.printStackTrace();
    		}
    		
    	}
    	/**
            MYSQL数据库
    
        try {
    			Class.forName("com.mysql.cj.jdbc.Driver");   // mysql 8
    			//Class.forName("com.mysql.jdbc.Driver");      //mysql 5
    			Connection	conn = DriverManager.getConnection(url,user,psw);
    			Statement stmt = conn.createStatement();
    			List<String> citycodes = new ArrayList<String>();
    			List<String> kinds = new ArrayList<String>();
    			String sql ="select distinct citycode from poi order by citycode";
    			String sql1 ="select distinct kind from poi order by kind";
    			List<Map<String, String>> data = db.queryForList(conn, sql);
    			List<Map<String, String>> data1 = db.queryForList(conn, sql1);
    			for(Map<String, String> aMap : data ){
    				String citycode = aMap.get("citycode"); 
    				if(citycode != null && !"".contentEquals(citycode)) {
    					citycodes.add(citycode);
    				}
    			}
    			
    			for(Map<String, String> aMap : data1 ){
    				String kind = aMap.get("kind"); 
    				if(kind != null && !"".contentEquals(kind)) {
    					kinds.add(kind);
    				}
    			}
    
    			if (citycodes != null && citycodes.size() > 0) {
    				for(int j = 0; j < citycodes.size(); j++) {
    					String citycode = citycodes.get(j);
    					String sql3 = "select count(*) n, kind from poi where citycode='" +citycode+ "' group by kind";
    					List<Map<String, String>> data3 = db.queryForList(conn, sql3);
    					String sql4 = "insert into citykind(city) values('"+citycode+"')"; 
    					stmt.execute(sql4);
    					
    					if(data3 != null && data3.size() > 0) {
    						for (int x = 0; x < data3.size(); x++) {
    							Map<String,String> temp = data3.get(x);
    							String num = temp.get("n");
    							String kind = temp.get("kind");
    							String sql2 = "update citykind set `"+kind+"`='"+num+"' where city='"+ citycode +"'";
    							stmt.addBatch(sql2);
    							if(x%100==0){
    
    								//批量处理
    								System.out.println("第"+x/100+"次执行语句");
    								stmt.executeBatch();
    								//清除stmt中积攒的参数列表
    								stmt.clearBatch();
    								System.out.println("第"+x/100+"次清除batch");
    							}
    						}
    						stmt.executeBatch();
    					}
    					
    				}
    			}
    			
    		System.out.println("更新完毕");
    		} catch (SQLException e) {
    			e.printStackTrace();
    		}
    		
    		
    	}
    	
    
    
    **/
    }
    
    
    
    DbHelper.class
    package com.demo;
    
    import java.sql.Connection;
    import java.sql.PreparedStatement;
    import java.sql.ResultSet;
    import java.sql.ResultSetMetaData;
    import java.sql.SQLException;
    import java.sql.Statement;
    import java.util.ArrayList;
    import java.util.HashMap;
    import java.util.LinkedList;
    import java.util.List;
    import java.util.Map;
    
    
    public class DbHelper {
    	
    	public List<Map<String, String>> queryForList(Connection conn, String sql, Object... params) {
    		PreparedStatement stat = null;
    		ResultSet rs = null;
    		try {
    //			params = Arrays.copyOf(params, params.length + 2);
    //			params[params.length - 2] = (pageIndex * pageSize);
    //			params[params.length - 1] = pageSize;
    			stat = prepared(conn, sql, params);
    			rs = stat.executeQuery();
    			ResultSetMetaData meta = rs.getMetaData();
    			List<String> cols = new ArrayList<String>();
    			for (int i = 0, count = meta.getColumnCount(); i < count; i++) {
    				cols.add(meta.getColumnLabel(i+1));
    			}
    			final String[] label = cols.toArray(new String[cols.size()]);
    			List<Map<String, String>> list = new LinkedList<Map<String, String>>();
    			while (rs.next()) {
    				final Map<String, String> row = new HashMap<String, String>(label.length);
    				for (int i = 0, count = label.length; i < count; i++) {
    					row.put(label[i], rs.getString(i+1));
    				}
    				list.add(row);
    			}
    			return list;
    		} catch (Exception e) {
    			throw new RuntimeException(e);
    		} finally {
    			close(stat, rs);
    		}
    	}
    	
    	public long queryForLong(Connection conn, String sql, Object... params) {
    		PreparedStatement stat = null;
    		ResultSet rs = null;
    		try {
    			stat = prepared(conn, sql, params);
    			rs = stat.executeQuery();
    			rs.next();
    			Object obj = rs.getObject(1);
    			if (obj instanceof Long) {
    				return ((Long) obj).longValue();
    			} else if (obj instanceof Integer) {
    				return ((Integer) obj).longValue();
    			} else {
    				return Long.valueOf(obj.toString()).longValue();
    			}
    		} catch (Exception e) {
    			throw new RuntimeException(e);
    		} finally {
    			close(stat, rs);
    		}
    	}
    
    	public int queryForInt(Connection conn, String sql, Object... params) {
    		PreparedStatement stat = null;
    		ResultSet rs = null;
    		try {
    			stat = prepared(conn, sql, params);
    			rs = stat.executeQuery();
    			rs.next();
    			Object obj = rs.getObject(1);
    			if (obj instanceof Long) {
    				return ((Long) obj).intValue();
    			} else if (obj instanceof Integer) {
    				return ((Integer) obj).intValue();
    			} else {
    				return Integer.valueOf(obj.toString()).intValue();
    			}
    		} catch (Exception e) {
    			throw new RuntimeException(e);
    		} finally {
    			close(stat, rs);
    		}
    	}
    
    	
    
    	public PreparedStatement prepared(Connection conn, String sql,
    			Object... params) {
    		try {
    			PreparedStatement stat = conn.prepareStatement(sql);
    			if (params.length > 0) {
    				for (int i = 0, count = params.length; i < count; i++) {
    					stat.setObject(i + 1, params[i]);
    				}
    			}
    			return stat;
    		} catch (SQLException e) {
    			throw new RuntimeException("fail to generate preparedStatement", e);
    		}
    	}
    
    	public void close(Connection conn, Statement st) {
    		if (st != null) {
    			try {
    				st.close();
    			} catch (SQLException e) {
    			}
    			st=null;
    		}
    		if (conn != null) {
    			try {
    				conn.close();
    			} catch (SQLException e) {
    			}
    			conn=null;
    		}
    	}
    
    	public void close(Statement st, ResultSet rs) {
    		if (rs != null) {
    			try {
    				rs.close();
    			} catch (SQLException e) {
    			}
    			rs=null;
    		}
    		if (st != null) {
    			try {
    				st.close();
    			} catch (SQLException e) {
    			}
    			st = null;
    		}
    	}
    
    	public void close(Connection conn, Statement st, ResultSet rs) {
    		if (rs != null) {
    			try {
    				rs.close();
    			} catch (SQLException e) {
    			}
    			rs=null;
    		}
    		close(conn, st);
    	}
    
    }

结果表 ly\_bj：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDkwMjUyNw_size_16_color_FFFFFF_t_70 2][]

ST\_Within：

`boolean ST_Within(`geometry A, geometry B`)`;

对比两个几何是否相等，返回布尔值~

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDkwMjUyNw_size_16_color_FFFFFF_t_70]: /images/20220217/499fa6bc22ab4f0d86ff8261283b9a8b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDkwMjUyNw_size_16_color_FFFFFF_t_70 1]: /images/20220217/a1704482e72447179348e8abb3271b8c.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDkwMjUyNw_size_16_color_FFFFFF_t_70 2]: /images/20220217/fe3952971ebc4c47b5d956d959f9a218.png