postgis 使用ST_Within圈选某一个几何内的POI
前言
之前有一个需求,需要统计一批门店周边几公里餐饮,娱乐,购物,零售等类型的门店数量。
准备的数据有门店表 bj:
geom字段是根据门店坐标计算的到达圈
北京某分类的poi表 poi0419:
到达圈都需要提前刷好~
具体实现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:
ST_Within:
boolean ST_Within(
geometry A, geometry B)
;
对比两个几何是否相等,返回布尔值~