全部产品

查询数据

更新时间:2019-04-26 14:19:45

某时间段上,BBOX范围查询。其它空间范围查询SQL参考:空间对象关系函数

  1. import java.sql.Connection;
  2. import java.sql.DriverManager;
  3. import java.sql.ResultSet;
  4. import java.sql.Statement;
  5. public class PostgreSQLJDBC {
  6. public static void main(String args[]) {
  7. Connection conn = null;
  8. Statement stmt = null;
  9. try{
  10. Class.forName("org.postgresql.Driver");
  11. conn = DriverManager.getConnection("jdbc:postgresql://ts-uf6sx115kjk117156.tsdbgaia.rds.aliyuncs.com:3242/postgres","singo", "Tsdb2gaia");
  12. conn.setAutoCommit(false);
  13. stmt = conn.createStatement();
  14. ResultSet rs = stmt.executeQuery( "SELECT uid,time,ST_AsText(position) AS pos FROM tsdb_test " +
  15. "WHERE time > '2019-03-10 18:30:15' AND time < '2019-03-15 18:30:15' AND " +
  16. "ST_Contains(ST_SetSRID(ST_MakeBox2D(ST_Point(10, 20),ST_Point(35,41)),4326),position)" );
  17. while ( rs.next() ) {
  18. long id = rs.getLong("uid");
  19. String time = rs.getString("time");
  20. String position = rs.getString("pos");
  21. System.out.println( "uid = " + id + ", time = " + time + ", position = " + position );
  22. System.out.println();
  23. }
  24. rs.close();
  25. stmt.close();
  26. conn.commit();
  27. conn.close();
  28. } catch (Exception e) {
  29. System.err.println(e.getClass().getName() + " : " + e.getMessage());
  30. System.exit(0);
  31. }
  32. System.out.println("select successfully");
  33. }
  34. }