public static void main(String[] args) { DbInstance dbinstance = new DbInstance( false, // pooled "null", // datasourcePath "<REPLACE_WITH_DB_URL>", // dbUrl "qsmaster", null, "com.mysql.jdbc.Driver", "lsstdb"); DataSource ds = JdbcFactory.getDataSource(dbinstance); String sql = "select * from DeepSource where qserv_areaspec_box(0.4, 1.05, 0.5, 1.15)"; Connection conn = null; try { conn = DataSourceUtils.getConnection(ds); long cTime = System.currentTimeMillis(); Statement stmt = null; try { stmt = conn.createStatement(); _log.briefDebug("Executing SQL query: " + sql); // ResultSet rs = stmt.executeQuery(sql); // _log.briefDebug ("SELECT took "+(System.currentTimeMillis()-cTime)+"ms"); File file = new File("/tmp/lsstTest.tbl"); IpacTableExtractor.query(null, ds, file, 10000, sql); _log.briefDebug("SELECT took " + (System.currentTimeMillis() - cTime) + "ms"); } catch (Exception e) { System.out.println("Exception " + e.getMessage()); } finally { closeStatement(stmt); } } catch (Exception e) { System.out.println("Exception " + e.getMessage()); e.printStackTrace(); } finally { if (conn != null) { DataSourceUtils.releaseConnection(conn, ds); } } }
// qserv_areaspec_circle does not work at the moment, only qserv_areaspec_box works void doGetData(File oFile, List<Param> params, WorldPt wpt) throws DataAccessException { DbInstance dbinstance = new DbInstance( false, // pooled "null", // datasourcePath QSERV_URI, // dbUrl QSERV_USER, StringUtils.isEmpty(QSERV_PASS) ? null : QSERV_PASS, "com.mysql.jdbc.Driver", "lsstdb"); DataSource ds = JdbcFactory.getDataSource(dbinstance); String searchMethod = null; String catTable = null; String selectedColumns = null; String[] constraints = null; for (Param p : params) { String pname = p.getName(); if (pname.equals(CatalogRequest.SEARCH_METHOD)) { searchMethod = p.getValue(); } else if (pname.equals(ServerParams.REQUESTED_DATA_SET)) { catTable = p.getValue(); } else if (pname.equals(CatalogRequest.SELECTED_COLUMNS)) { selectedColumns = p.getValue(); } else if (pname.equals(CatalogRequest.CONSTRAINTS)) { String val = p.getValue(); if (!StringUtils.isEmpty(val)) { _log.briefDebug("CONSTRAINTS: " + val); constraints = val.split(","); } } } String update = getSelectedColumnsUpdate(selectedColumns); if (update != null) selectedColumns = update; String pname; String sql; if (searchMethod != null && searchMethod.equals(CatalogRequest.Method.CONE.getDesc())) { double radius = 0.0; for (Param p : params) { pname = p.getName(); if (pname.equals(CatalogRequest.RADIUS)) { radius = Double.parseDouble(p.getValue()); } } // qserv_areaspec_circle(ra, dec, radius) // sql="select * from "+catTable+" where qserv_areaspec_circle("+wpt.getLon()+", // "+wpt.getLat()+", "+radius+")"; // Per Serge, the above query only can not be applied to unpartitioned table sql = "select " + selectedColumns + " from " + catTable + " where scisql_s2PtInCircle(ra, decl, " + wpt.getLon() + ", " + wpt.getLat() + ", " + radius + ")=1"; } else if (searchMethod != null && searchMethod.equals(CatalogRequest.Method.BOX.getDesc())) { double size = 0.0; for (Param p : params) { pname = p.getName(); if (pname.equals(CatalogRequest.SIZE)) { size = Double.parseDouble(p.getValue()); } } double halfsize = size / 2.0; double lon1 = wpt.getLon() - halfsize; if (lon1 < 0) lon1 += 360; double lon2 = wpt.getLon() + halfsize; if (lon2 > 360) lon1 -= 360; double lat1 = wpt.getLat() - halfsize; if (lat1 < -90) lat1 = -180 - lat1; double lat2 = wpt.getLat() + halfsize; if (lat2 > 90) lat2 = 180 - lat2; // qserv_areaspec_box(raA, decA, raB, decB) // sql="select * from "+catTable+" where qserv_areaspec_box("+lon1+", "+lat1+", "+lon2+", // "+lat2+")"; // Per Serge, the above query only can not be applied to unpartitioned table sql = "select * from " + catTable + " where scisql_s2PtInBox(ra, decl, " + lon1 + ", " + lat1 + ", " + lon2 + ", " + lat2 + ")=1"; } else { throw new RuntimeException(searchMethod + " search is not Implemented"); } // add additional constraints if (constraints != null) { for (String constr : constraints) { sql += " and " + constr; } } // workaround for https://jira.lsstcorp.org/browse/DM-1841 sql += " LIMIT 3000"; Connection conn = null; Statement stmt = null; try { conn = DataSourceUtils.getConnection(ds); long cTime = System.currentTimeMillis(); stmt = conn.createStatement(); _log.briefDebug("Executing SQL query: " + sql); // ResultSet rs = stmt.executeQuery(sql); IpacTableExtractor.query(null, ds, oFile, 10000, sql); _log.briefDebug("SELECT took " + (System.currentTimeMillis() - cTime) + "ms"); } catch (Exception e) { _log.error(e); throw new DataAccessException("Query failed: " + e.getMessage()); } finally { if (stmt != null) { try { closeStatement(stmt); } catch (Exception e1) { } } if (conn != null) { DataSourceUtils.releaseConnection(conn, ds); } } }