private List<Map<String, Object>> selectCmsData_Level3_ForActualAmt(String yyyymm) {
String sql =
"SELECT corp_scale as corpscale, guar as guartype ,SUM(AMT) as amt FROM ( "
+ "select CODE,CNAME,ORG_CODE,CREDIT_CARDNO,CORP_SCALE,CREDENCENO,TYPE, "
+ " to_char(PAYDATE, 'yyyymmdd') paydate,CURCD, "
+ " case substr(a.type,2,2) when '03' then -AMT else AMT end AMT, "
+ " case a.CURCD when 'CNY' then 0 when 'USD' then ROUND(AMT) "
+ " else round(AMT * (select CURRRATE from CURRRATE "
+ " where CURCD = a.CURCD and CURRRATE_DATE = "
+ " (select max(CURRRATE_DATE) from CURRRATE "
+ " where curcd = a.curcd)) / "
+ " (select CURRRATE from CURRRATE "
+ " where CURCD = 'USD' and CURRRATE_DATE = "
+ " (select max(CURRRATE_DATE) from CURRRATE where curcd = 'USD'))) "
+ " end tousd, "
+ " case a.CURCD when 'CNY' then round(AMT) "
+ " else round(AMT * (select CURRRATE "
+ " from CURRRATE "
+ " where CURCD = a.CURCD "
+ " and CURRRATE_DATE = "
+ " (select max(CURRRATE_DATE) from CURRRATE "
+ " where curcd = a.curcd))) "
+ " end toCNY, GUAR "
+ " from LOAN_PAY_AND_RTN_LOG a "
+ " WHERE REPORTNAME = 'C001FC5007437"
+ yyyymm
+ ".txt' "
+ " and type LIKE '%D0%' and curcd = 'CNY' "
+ " ) "
+ " GROUP BY corp_scale,guar "
+ " ORDER BY corp_scale,guar";
logger.info(sql);
return cmsSimpleJdbcTemplate.queryForList(sql);
}
private void backfillNodesTables() {
// If complete ways is set, select all nodes contained by the ways into
// the node temp table.
LOG.finer("Selecting all nodes for selected ways.");
jdbcTemplate.update("CREATE TEMPORARY TABLE bbox_way_nodes (id bigint) ON COMMIT DROP");
jdbcTemplate.queryForList("SELECT unnest_bbox_way_nodes()");
jdbcTemplate.update(
"CREATE TEMPORARY TABLE bbox_missing_way_nodes ON COMMIT DROP AS "
+ "SELECT buwn.id FROM (SELECT DISTINCT bwn.id FROM bbox_way_nodes bwn) buwn "
+ "WHERE NOT EXISTS ("
+ " SELECT * FROM bbox_nodes WHERE id = buwn.id"
+ ");");
jdbcTemplate.update(
"ALTER TABLE ONLY bbox_missing_way_nodes"
+ " ADD CONSTRAINT pk_bbox_missing_way_nodes PRIMARY KEY (id)");
jdbcTemplate.update("ANALYZE bbox_missing_way_nodes");
int rowCount =
jdbcTemplate.update(
"INSERT INTO bbox_nodes "
+ "SELECT n.* FROM nodes n INNER JOIN bbox_missing_way_nodes bwn ON n.id = bwn.id;");
LOG.finer(rowCount + " rows affected.");
LOG.finer("Updating query analyzer statistics on the temporary nodes table.");
jdbcTemplate.update("ANALYZE bbox_nodes");
}
/**
* 按企业规模类型 和 担保方式 汇总
*
* @param yyyymm
* @return
*/
private List<Map<String, Object>> selectCmsData_Level3_ForBalance(String yyyymm) {
String sql =
"SELECT corp_scale as corpscale, guartype, SUM(LNBAL) as balamt "
+ " FROM (select CODE, "
+ " CNAME, "
+ " ORG_CODE, "
+ " CORP_TYPE, "
+ " CREDIT_CARD_NO, "
+ " REGISTER_ADDRESS, "
+ " CORP_SCALE, "
+ " CREDENCENO, "
+ " STATUS, "
+ " to_char(PAYDATE, 'yyyymmdd'), "
+ " to_char(ENDDATE, 'yyyymmdd'), "
+ " CURCD, "
+ " round(LNBAL) LNBAL, "
+ " round(USDBAL), "
+ " ROUND(CNYBAL), "
+ " INTRATE, "
+ " LOANCLASS, "
+ " GUARTYPE, "
+ " CORP_OWERINFO, "
+ " LOANPROPERTY, "
+ " LOANTYPE1, "
+ " LOANTYPE2, "
+ " GROPFLAG, "
+ " REPORT_DATE "
+ " from LOAN_BAL_INFO_LOG "
+ " where REPORT_DATE = ? and curcd ='CNY' and substr(status,1,1) = 'D' "
+ " ) "
+ " GROUP BY CORP_SCALE, GUARTYPE "
+ " order by CORP_SCALE, GUARTYPE";
logger.info(sql);
return cmsSimpleJdbcTemplate.queryForList(sql, yyyymm);
}
public List<Map<String, Object>> findByProdCode(String prodCode) {
SimpleJdbcTemplate stj = getSimpleJdbcTemplate();
String sql =
"select Distinct PKG_CODE as PKG_CODE from PIDB_CSP where "
+ getAssertEmptyString("PKG_CODE");
logger.debug(sql);
return stj.queryForList(sql, new Object[] {});
}
/**
* 根据分支机构编码查询分支机构信息
*
* @param username
* @return object[]:result[0]是分支机构id，result[1]是分支机构所在租户id
*/
public Object[] getBranchInfoByCode(String companyCode) {
List<Map<String, Object>> list = jdbcTemplate.queryForList(DEPARTMENT_SQL, companyCode);
if (list.isEmpty()) return null;
Object[] result = new Object[2];
result[0] = list.get(0).get("id");
result[1] = list.get(0).get("fk_company_id");
return result;
}
public List<Map<String, Object>> findByProjCode(String projCode) {
SimpleJdbcTemplate stj = getSimpleJdbcTemplate();
String sql =
"select Distinct ptk.PKG_CODE as PKG_CODE from PIDB_CSP ptk left join PIDB_PROJECT_CODE ppc on ptk.PROJ_NAME = ppc.PROJ_NAME where "
+ getAssertEmptyString("ptk.PKG_CODE")
+ " and ppc.PROJ_CODE = ? order by ptk.PKG_CODE";
logger.debug(sql);
return stj.queryForList(sql, new Object[] {projCode});
}
public Boolean checkNameExists(SimpleJdbcTemplate template, String rsltName) throws Exception {
log.info("checkNameExists enter----> ");
boolean namePrsnt = false;
String getRsltSet = "SELECT * FROM LAU_QUERY_RESULT_SETS WHERE RESULT_SET_NAME = ?";
List<Map<String, Object>> rows = template.queryForList(getRsltSet, rsltName);
log.info("Size of name list----> " + rows.size());
int listSize = rows.size();
if (listSize != 0) {
namePrsnt = true;
}
return namePrsnt;
}
public List<String> findPkgTypeByPkgCode(final String pkgCode) {
SimpleJdbcTemplate stj = getSimpleJdbcTemplate();
String sql = "";
List<Map<String, Object>> result;
if (pkgCode != null && pkgCode.length() > 0) {
sql = "select distinct TRAD_PKG_TYPE from PIDB_CSP where PKG_CODE = ? order by TRAD_PKG_TYPE";
logger.debug(sql);
result = stj.queryForList(sql, new Object[] {pkgCode});
} else {
sql = "select distinct TRAD_PKG_TYPE from PIDB_CSP order by TRAD_PKG_TYPE";
logger.debug(sql);
result = stj.queryForList(sql, new Object[] {});
}
List<String> pkgTypes = new ArrayList<String>();
if (result != null && result.size() > 0) {
for (Map<String, Object> item : result) {
pkgTypes.add((String) item.get("CSP_TYPE"));
}
}
return pkgTypes;
}
public List<String> findDistAllProdCode() {
SimpleJdbcTemplate stj = getSimpleJdbcTemplate();
String sql = "select distinct PROD_CODE from PIDB_CSP order by PROD_CODE";
logger.debug(sql);
List<Map<String, Object>> result = stj.queryForList(sql, new Object[] {});
List<String> prodCodes = new ArrayList<String>();
if (result != null && result.size() > 0) {
for (Map<String, Object> item : result) {
prodCodes.add((String) item.get("PROD_CODE"));
}
}
return prodCodes;
}
/**
* 在总公司（非分支机构内）内查询用户ID和用户姓名
*
* @param username
* @return null 用户不存在
*/
public Object[] getUser(String username, Long companyId) {
List<Map<String, Object>> list =
jdbcTemplate.queryForList(USER_COMPANY_SQL, username, companyId);
if (list.size() <= 0) return null;
Object[] result = new Object[6];
result[0] = list.get(0).get("id");
result[1] = list.get(0).get("name");
result[2] = list.get(0).get("fk_company_id");
result[3] = list.get(0).get("sub_company_id");
result[4] = list.get(0).get("password");
result[5] = list.get(0).get("login_name");
return result;
}
public List<String> findProjCust() {
SimpleJdbcTemplate stj = getSimpleJdbcTemplate();
String sql = "select distinct CUST from PIDB_PROJECT_CODE order by CUST";
logger.debug(sql);
List<Map<String, Object>> result = stj.queryForList(sql, new Object[] {});
List<String> projCusts = new ArrayList<String>();
if (result != null && result.size() > 0) {
for (Map<String, Object> item : result) {
projCusts.add((String) item.get("CUST"));
}
}
return projCusts;
}
public List<String> findPkgCodeByPkgCode(final String pkgCode) {
SimpleJdbcTemplate stj = getSimpleJdbcTemplate();
String sql =
"select distinct PKG_CODE from PIDB_CSP where ("
+ super.getSmartSearchQueryString("PKG_CODE", pkgCode)
+ ") order by PKG_CODE";
logger.debug(sql);
List<Map<String, Object>> result = stj.queryForList(sql, new Object[] {});
List<String> pkgCodes = new ArrayList<String>();
if (result != null && result.size() > 0) {
for (Map<String, Object> item : result) {
pkgCodes.add((String) item.get("PKG_CODE"));
}
}
return pkgCodes;
}
public List<String> findFab() {
SimpleJdbcTemplate stj = getSimpleJdbcTemplate();
String sql =
"select distinct FAB from PIDB_PROJECT where "
+ getAssertEmptyString("FAB")
+ " order by FAB";
logger.debug(sql);
List<Map<String, Object>> result = stj.queryForList(sql, new Object[] {});
List<String> ret = new ArrayList<String>();
if (result != null && result.size() > 0) {
for (Map<String, Object> item : result) {
ret.add((String) item.get("FAB"));
}
}
return ret;
}
public void dbupdate() {
String sql =
" SELECT version FROM oncows_db_version "
+ " WHERE type='structure' ORDER BY version DESC ";
log.debug(sql);
// if(true) return;
Map<String, Object> map = simpleJdbc.queryForList(sql).get(0);
Timestamp lastUpdate = (Timestamp) map.get("version");
log.debug(lastUpdate);
setFlushModeCommit();
for (DbUpdate dbUpdate1 : getDbUpdateL()) {
if (dbUpdate1.getDbUpdateDate().after(lastUpdate)) {
log.debug(dbUpdate1.getDbUpdateDate());
if (dbUpdate1 instanceof DbIndexes) {
DbIndexes dbUpdate2 = (DbIndexes) dbUpdate1;
dbUpdate2.updateIndexes();
} else dbUpdate1.update("structure");
}
}
}
/**
* 根据用户名查询公司id
*
* @param username
* @return
*/
public Long getCompanyId(String username) {
List<Map<String, Object>> list = jdbcTemplate.queryForList(COMPANY_ID_SQL, username);
if (list.isEmpty()) return null;
Object obj = list.get(0).get("fk_company_id");
return Long.valueOf(obj.toString());
}
/** {@inheritDoc} */
public ReleasableIterator<EntityContainer> iterateBoundingBox(
double left, double right, double top, double bottom, boolean completeWays) {
List<Bound> bounds;
Point[] bboxPoints;
Polygon bboxPolygon;
int rowCount;
List<ReleasableIterator<EntityContainer>> resultSets =
new ArrayList<ReleasableIterator<EntityContainer>>();
if (!initialized) {
initialize();
}
// Build the bounds list.
bounds = new ArrayList<Bound>();
bounds.add(new Bound(right, left, top, bottom, "Osmosis " + OsmosisConstants.VERSION));
// PostgreSQL sometimes incorrectly chooses to perform full table scans,
// these options prevent this. Note that this is not recommended
// practice according to documentation but fixing this would require
// modifying the table statistics gathering configuration to produce
// better plans.
jdbcTemplate.update("SET enable_seqscan = false");
jdbcTemplate.update("SET enable_mergejoin = false");
jdbcTemplate.update("SET enable_hashjoin = false");
// Build a polygon representing the bounding box.
// Sample box for query testing may be:
// GeomFromText('POLYGON((144.93912192855174 -37.82981987499741,
// 144.93912192855174 -37.79310006709244, 144.98188026000003
// -37.79310006709244, 144.98188026000003 -37.82981987499741,
// 144.93912192855174 -37.82981987499741))', -1)
bboxPoints = new Point[5];
bboxPoints[0] = new Point(left, bottom);
bboxPoints[1] = new Point(left, top);
bboxPoints[2] = new Point(right, top);
bboxPoints[3] = new Point(right, bottom);
bboxPoints[4] = new Point(left, bottom);
bboxPolygon = PolygonBuilder.createPolygon(bboxPoints);
// Select all nodes inside the box into the node temp table.
LOG.finer("Selecting all nodes inside bounding box.");
rowCount =
jdbcTemplate.update(
"CREATE TEMPORARY TABLE bbox_nodes ON COMMIT DROP AS"
+ " SELECT * FROM nodes WHERE ST_Intersects(geom, ?)",
new PGgeometry(bboxPolygon));
LOG.finer("Adding a primary key to the temporary nodes table.");
jdbcTemplate.update(
"ALTER TABLE ONLY bbox_nodes ADD CONSTRAINT pk_bbox_nodes PRIMARY KEY (id)");
LOG.finer("Updating query analyzer statistics on the temporary nodes table.");
jdbcTemplate.update("ANALYZE bbox_nodes");
// Select all ways inside the bounding box into the way temp table.
if (capabilityChecker.isWayLinestringSupported()) {
LOG.finer("Selecting all ways inside bounding box using way linestring geometry.");
// We have full way geometry available so select ways
// overlapping the requested bounding box.
String sql =
"CREATE TEMPORARY TABLE bbox_ways ON COMMIT DROP AS"
+ " SELECT * FROM ways WHERE ST_Intersects(linestring, ?)";
LOG.info("Exec SQL: " + sql + " -- args: " + bboxPolygon);
rowCount = jdbcTemplate.update(sql, new PGgeometry(bboxPolygon));
} else if (capabilityChecker.isWayBboxSupported()) {
LOG.finer(
"Selecting all ways inside bounding box using dynamically built"
+ " way linestring with way bbox indexing.");
// The inner query selects the way id and node coordinates for
// all ways constrained by the way bounding box which is
// indexed.
// The middle query converts the way node coordinates into
// linestrings.
// The outer query constrains the query to the linestrings
// inside the bounding box. These aren't indexed but the inner
// query way bbox constraint will minimise the unnecessary data.
rowCount =
jdbcTemplate.update(
"CREATE TEMPORARY TABLE bbox_ways ON COMMIT DROP AS"
+ " SELECT w.* FROM ("
+ " SELECT c.id AS id, First(c.version) AS version, First(c.user_id) AS user_id,"
+ " First(c.tstamp) AS tstamp, First(c.changeset_id) AS changeset_id, First(c.tags) AS tags,"
+ " First(c.nodes) AS nodes, MakeLine(c.geom) AS way_line FROM ("
+ " SELECT w.*, n.geom AS geom FROM nodes n"
+ " INNER JOIN way_nodes wn ON n.id = wn.node_id"
+ " INNER JOIN ways w ON wn.way_id = w.id"
+ " WHERE (w.bbox && ?) ORDER BY wn.way_id, wn.sequence_id"
+ " ) c "
+ " GROUP BY c.id"
+ " ) w "
+ "WHERE (w.way_line && ?)",
new PGgeometry(bboxPolygon),
new PGgeometry(bboxPolygon));
} else {
LOG.finer("Selecting all way ids inside bounding box using already selected nodes.");
// No way bbox support is available so select ways containing
// the selected nodes.
rowCount =
jdbcTemplate.update(
"CREATE TEMPORARY TABLE bbox_ways ON COMMIT DROP AS"
+ " SELECT w.* FROM ways w"
+ " INNER JOIN ("
+ " SELECT wn.way_id FROM way_nodes wn"
+ " INNER JOIN bbox_nodes n ON wn.node_id = n.id GROUP BY wn.way_id"
+ ") wids ON w.id = wids.way_id");
}
LOG.finer(rowCount + " rows affected.");
LOG.finer("Adding a primary key to the temporary ways table.");
jdbcTemplate.update("ALTER TABLE ONLY bbox_ways ADD CONSTRAINT pk_bbox_ways PRIMARY KEY (id)");
LOG.finer("Updating query analyzer statistics on the temporary ways table.");
jdbcTemplate.update("ANALYZE bbox_ways");
// Select all relations containing the nodes or ways into the relation
// table.
LOG.finer("Selecting all relation ids containing selected nodes or ways.");
rowCount =
jdbcTemplate.update(
"CREATE TEMPORARY TABLE bbox_relations ON COMMIT DROP AS"
+ " SELECT r.* FROM relations r"
+ " INNER JOIN ("
+ " SELECT relation_id FROM ("
+ " SELECT rm.relation_id AS relation_id FROM relation_members rm"
+ " INNER JOIN bbox_nodes n ON rm.member_id = n.id WHERE rm.member_type = 'N' "
+ " UNION "
+ " SELECT rm.relation_id AS relation_id FROM relation_members rm"
+ " INNER JOIN bbox_ways w ON rm.member_id = w.id WHERE rm.member_type = 'W'"
+ " ) rids GROUP BY relation_id"
+ ") rids ON r.id = rids.relation_id");
LOG.finer(rowCount + " rows affected.");
LOG.finer("Adding a primary key to the temporary relations table.");
jdbcTemplate.update(
"ALTER TABLE ONLY bbox_relations ADD CONSTRAINT pk_bbox_relations PRIMARY KEY (id)");
LOG.finer("Updating query analyzer statistics on the temporary relations table.");
jdbcTemplate.update("ANALYZE bbox_relations");
// Include all relations containing the current relations into the
// relation table and repeat until no more inclusions occur.
do {
LOG.finer("Selecting parent relations of selected relations.");
rowCount =
jdbcTemplate.update(
"INSERT INTO bbox_relations "
+ "SELECT r.* FROM relations r INNER JOIN ("
+ " SELECT rm.relation_id FROM relation_members rm"
+ " INNER JOIN bbox_relations br ON rm.member_id = br.id"
+ " WHERE rm.member_type = 'R' AND NOT EXISTS ("
+ " SELECT * FROM bbox_relations br2 WHERE rm.relation_id = br2.id"
+ " ) GROUP BY rm.relation_id"
+ ") rids ON r.id = rids.relation_id");
LOG.finer(rowCount + " rows affected.");
} while (rowCount > 0);
LOG.finer("Updating query analyzer statistics on the temporary relations table.");
jdbcTemplate.update("ANALYZE bbox_relations");
// If complete ways is set, select all nodes contained by the ways into
// the node temp table.
if (completeWays) {
LOG.finer("Selecting all nodes for selected ways.");
jdbcTemplate.update("CREATE TEMPORARY TABLE bbox_way_nodes (id bigint) ON COMMIT DROP");
jdbcTemplate.queryForList("SELECT unnest_bbox_way_nodes()");
jdbcTemplate.update(
"CREATE TEMPORARY TABLE bbox_missing_way_nodes ON COMMIT DROP AS "
+ "SELECT buwn.id FROM (SELECT DISTINCT bwn.id FROM bbox_way_nodes bwn) buwn "
+ "WHERE NOT EXISTS ("
+ " SELECT * FROM bbox_nodes WHERE id = buwn.id"
+ ");");
jdbcTemplate.update(
"ALTER TABLE ONLY bbox_missing_way_nodes"
+ " ADD CONSTRAINT pk_bbox_missing_way_nodes PRIMARY KEY (id)");
jdbcTemplate.update("ANALYZE bbox_missing_way_nodes");
rowCount =
jdbcTemplate.update(
"INSERT INTO bbox_nodes "
+ "SELECT n.* FROM nodes n INNER JOIN bbox_missing_way_nodes bwn ON n.id = bwn.id;");
LOG.finer(rowCount + " rows affected.");
}
LOG.finer("Updating query analyzer statistics on the temporary nodes table.");
jdbcTemplate.update("ANALYZE bbox_nodes");
// Create iterators for the selected records for each of the entity
// types.
LOG.finer("Iterating over results.");
resultSets.add(
new UpcastIterator<EntityContainer, BoundContainer>(
new BoundContainerIterator(
new ReleasableAdaptorForIterator<Bound>(bounds.iterator()))));
resultSets.add(
new UpcastIterator<EntityContainer, NodeContainer>(
new NodeContainerIterator(nodeDao.iterate("bbox_"))));
resultSets.add(
new UpcastIterator<EntityContainer, WayContainer>(
new WayContainerIterator(wayDao.iterate("bbox_"))));
resultSets.add(
new UpcastIterator<EntityContainer, RelationContainer>(
new RelationContainerIterator(relationDao.iterate("bbox_"))));
// Merge all readers into a single result iterator and return.
return new MultipleSourceIterator<EntityContainer>(resultSets);
}
/**
* 根据公司编码查询公司id
*
* @param username
* @return
*/
public Long getCompanyIdByCode(String companyCode) {
List<Map<String, Object>> list = jdbcTemplate.queryForList(COMPANY_SQL, companyCode);
if (list.isEmpty()) return null;
Object obj = list.get(0).get("id");
return Long.valueOf(obj.toString());
}
public ReleasableIterator<EntityContainer> iterateSelectedWays(
List<? extends Selector> tagSelectors) {
int rowCount;
List<ReleasableIterator<EntityContainer>> resultSets =
new ArrayList<ReleasableIterator<EntityContainer>>();
ArrayList<Bound> bounds = new ArrayList<Bound>();
if (!initialized) {
initialize();
}
String whereStr = buildSelectorWhereClause(tagSelectors);
if (capabilityChecker.isWayLinestringSupported()) {
whereStr = whereStr.replace("geom", "linestring");
} else if (capabilityChecker.isWayBboxSupported()) {
whereStr = whereStr.replace("geom", "bbox");
}
List<Object> whereObj = buildSelectorWhereParameters(tagSelectors);
for (Selector selector : tagSelectors) {
if (selector instanceof Selector.Polygon) {
Selector.Polygon boundingBox = (Selector.Polygon) selector;
double right = boundingBox.getRight();
double left = boundingBox.getLeft();
double top = boundingBox.getTop();
double bottom = boundingBox.getBottom();
bounds.add(new Bound(right, left, top, bottom, "Osmosis " + OsmosisConstants.VERSION));
}
}
// PostgreSQL sometimes incorrectly chooses to perform full table scans,
// these options prevent this. Note that this is not recommended
// practice according to documentation but fixing this would require
// modifying the table statistics gathering configuration to produce
// better plans.
jdbcTemplate.update("SET enable_seqscan = false");
jdbcTemplate.update("SET enable_mergejoin = false");
jdbcTemplate.update("SET enable_hashjoin = false");
LOG.finer("Creating empty nodes table.");
rowCount =
jdbcTemplate.update(
"CREATE TEMPORARY TABLE bbox_nodes ON COMMIT DROP AS"
+ " SELECT * FROM nodes WHERE FALSE");
// Select all ways inside the bounding box into the way temp table.
LOG.finer("Selecting all ways inside bounding box using way linestring geometry.");
// We have full way geometry available so select ways
// overlapping the requested bounding box.
String sql =
"CREATE TEMPORARY TABLE bbox_ways ON COMMIT DROP AS SELECT * FROM ways WHERE " + whereStr;
rowCount = jdbcTemplate.update(sql, whereObj.toArray());
LOG.finer(rowCount + " rows affected.");
LOG.finer("Adding a primary key to the temporary ways table.");
jdbcTemplate.update("ALTER TABLE ONLY bbox_ways ADD CONSTRAINT pk_bbox_ways PRIMARY KEY (id)");
LOG.finer("Updating query analyzer statistics on the temporary ways table.");
jdbcTemplate.update("ANALYZE bbox_ways");
LOG.finer("Selecting all nodes for selected ways.");
jdbcTemplate.update("CREATE TEMPORARY TABLE bbox_way_nodes (id bigint) ON COMMIT DROP");
jdbcTemplate.queryForList("SELECT unnest_bbox_way_nodes()");
jdbcTemplate.update(
"CREATE TEMPORARY TABLE bbox_missing_way_nodes ON COMMIT DROP AS "
+ "SELECT buwn.id FROM (SELECT DISTINCT bwn.id FROM bbox_way_nodes bwn) buwn "
+ "WHERE NOT EXISTS ("
+ " SELECT * FROM bbox_nodes WHERE id = buwn.id"
+ ");");
jdbcTemplate.update(
"ALTER TABLE ONLY bbox_missing_way_nodes"
+ " ADD CONSTRAINT pk_bbox_missing_way_nodes PRIMARY KEY (id)");
jdbcTemplate.update("ANALYZE bbox_missing_way_nodes");
rowCount =
jdbcTemplate.update(
"INSERT INTO bbox_nodes "
+ "SELECT n.* FROM nodes n INNER JOIN bbox_missing_way_nodes bwn ON n.id = bwn.id;");
LOG.finer(rowCount + " rows affected.");
LOG.finer("Updating query analyzer statistics on the temporary nodes table.");
jdbcTemplate.update("ANALYZE bbox_nodes");
// Create iterators for the selected records for each of the entity
// types.
LOG.finer("Iterating over results.");
resultSets.add(
new UpcastIterator<EntityContainer, BoundContainer>(
new BoundContainerIterator(
new ReleasableAdaptorForIterator<Bound>(bounds.iterator()))));
resultSets.add(
new UpcastIterator<EntityContainer, NodeContainer>(
new NodeContainerIterator(nodeDao.iterate("bbox_"))));
resultSets.add(
new UpcastIterator<EntityContainer, WayContainer>(
new WayContainerIterator(wayDao.iterate("bbox_"))));
// Merge all readers into a single result iterator and return.
return new MultipleSourceIterator<EntityContainer>(resultSets);
}
public ReleasableIterator<EntityContainer> iterateWays(List<Long> ids) {
int rowCount;
List<ReleasableIterator<EntityContainer>> resultSets =
new ArrayList<ReleasableIterator<EntityContainer>>();
if (!initialized) {
initialize();
}
// PostgreSQL sometimes incorrectly chooses to perform full table scans,
// these options prevent this. Note that this is not recommended
// practice according to documentation but fixing this would require
// modifying the table statistics gathering configuration to produce
// better plans.
jdbcTemplate.update("SET enable_seqscan = false");
jdbcTemplate.update("SET enable_mergejoin = false");
jdbcTemplate.update("SET enable_hashjoin = false");
LOG.finer("Creating empty nodes table.");
rowCount =
jdbcTemplate.update(
"CREATE TEMPORARY TABLE bbox_nodes ON COMMIT DROP AS"
+ " SELECT * FROM nodes WHERE FALSE");
String idsSql = buildListSql(ids);
rowCount =
jdbcTemplate.update(
"CREATE TEMPORARY TABLE bbox_ways ON COMMIT DROP AS SELECT * FROM ways WHERE id IN "
+ idsSql,
ids.toArray());
LOG.finer(rowCount + " rows affected.");
LOG.finer("Updating query analyzer statistics on the temporary ways table.");
jdbcTemplate.update("ANALYZE bbox_ways");
LOG.finer("Selecting all nodes for selected ways.");
jdbcTemplate.update("CREATE TEMPORARY TABLE bbox_way_nodes (id bigint) ON COMMIT DROP");
jdbcTemplate.queryForList("SELECT unnest_bbox_way_nodes()");
jdbcTemplate.update(
"CREATE TEMPORARY TABLE bbox_missing_way_nodes ON COMMIT DROP AS "
+ "SELECT buwn.id FROM (SELECT DISTINCT bwn.id FROM bbox_way_nodes bwn) buwn "
+ "WHERE NOT EXISTS ("
+ " SELECT * FROM bbox_nodes WHERE id = buwn.id"
+ ");");
jdbcTemplate.update(
"ALTER TABLE ONLY bbox_missing_way_nodes"
+ " ADD CONSTRAINT pk_bbox_missing_way_nodes PRIMARY KEY (id)");
jdbcTemplate.update("ANALYZE bbox_missing_way_nodes");
rowCount =
jdbcTemplate.update(
"INSERT INTO bbox_nodes "
+ "SELECT n.* FROM nodes n INNER JOIN bbox_missing_way_nodes bwn ON n.id = bwn.id;");
LOG.finer(rowCount + " rows affected.");
LOG.finer("Updating query analyzer statistics on the temporary nodes table.");
jdbcTemplate.update("ANALYZE bbox_nodes");
// Create iterators for the selected records for each of the entity
// types.
LOG.finer("Iterating over results.");
resultSets.add(
new UpcastIterator<EntityContainer, NodeContainer>(
new NodeContainerIterator(nodeDao.iterate("bbox_"))));
resultSets.add(
new UpcastIterator<EntityContainer, WayContainer>(
new WayContainerIterator(wayDao.iterate("bbox_"))));
// Merge all readers into a single result iterator and return.
return new MultipleSourceIterator<EntityContainer>(resultSets);
}
