in endorsed/src/org.apache.sis.referencing/main/org/apache/sis/referencing/factory/sql/EPSGCodeFinder.java [278:480]
protected Set<String> getCodeCandidates(final IdentifiedObject object) throws FactoryException {
final TableInfo table; // Contains `codeColumn` and `table` names.
final Condition[] filters; // Conditions to put in the WHERE clause.
crs: if (isInstance(CoordinateReferenceSystem.class, object)) {
/*
* For compound CRS, the SQL statement may be something like below
*
* SELECT COORD_REF_SYS_CODE FROM "Coordinate Reference System"
* WHERE CAST(COORD_REF_SYS_KIND AS VARCHAR(80)) LIKE 'compound%'
* AND CMPD_HORIZCRS_CODE IN (?,…)
* AND CMPD_VERTCRS_CODE IN (?,…)
*/
table = TableInfo.CRS;
if (isInstance(CompoundCRS.class, object)) {
final List<CoordinateReferenceSystem> components = ((CompoundCRS) object).getComponents();
if (components != null) { // Paranoiac check.
final int n = components.size();
if (n == 2) {
filters = new Condition[2];
for (int i=0; i<=1; i++) {
if ((filters[i] = dependencies((i==0) ? "CMPD_HORIZCRS_CODE" : "CMPD_VERTCRS_CODE",
CoordinateReferenceSystem.class, components.get(i), false)) == null)
{
return Set.of();
}
}
break crs;
}
if (n == 1) { // Should not happen.
return getCodeCandidates(components.get(0));
}
}
}
/*
* For Coordinate Reference System, the SQL statement may be something like below
* (with DATUM_CODE replaced by SOURCE_GEOGCRS_CODE in a projected CRS):
*
* SELECT COORD_REF_SYS_CODE FROM "Coordinate Reference System"
* WHERE CAST(COORD_REF_SYS_KIND AS VARCHAR(80)) LIKE 'geographic%'
* AND DATUM_CODE IN (?,…) AND DEPRECATED=0
* ORDER BY COORD_REF_SYS_CODE
*/
final Condition filter;
if (object instanceof GeneralDerivedCRS) { // No need to use isInstance(Class, Object) from here.
filter = dependencies("SOURCE_GEOGCRS_CODE", CoordinateReferenceSystem.class, ((GeneralDerivedCRS) object).getBaseCRS(), true);
} else if (object instanceof GeodeticCRS) {
filter = dependencies("DATUM_CODE", GeodeticDatum.class, ((GeodeticCRS) object).getDatum(), true);
} else if (object instanceof VerticalCRS) {
filter = dependencies("DATUM_CODE", VerticalDatum.class, ((VerticalCRS) object).getDatum(), true);
} else if (object instanceof TemporalCRS) {
filter = dependencies("DATUM_CODE", TemporalDatum.class, ((TemporalCRS) object).getDatum(), true);
} else if (object instanceof SingleCRS) {
filter = dependencies("DATUM_CODE", Datum.class, ((SingleCRS) object).getDatum(), true);
} else {
return Set.of();
}
if (filter == null) {
return Set.of();
}
filters = new Condition[] {filter};
} else if (isInstance(Datum.class, object)) {
/*
* We currently have no better way to filter datum (or reference frames) than their names.
* Filtering must be at least as tolerant as AbstractDatum.isHeuristicMatchForName(String).
* The SQL statement will be something like below:
*
* SELECT DATUM_CODE FROM "Datum"
* WHERE ELLIPSOID_CODE IN (?,…)
* AND (LOWER(DATUM_NAME) LIKE '?%')
*/
table = TableInfo.DATUM;
if (isInstance(GeodeticDatum.class, object)) {
filters = new Condition[] {
dependencies("ELLIPSOID_CODE", Ellipsoid.class, ((GeodeticDatum) object).getEllipsoid(), true),
Condition.NAME
};
if (filters[0] == null) {
return Set.of();
}
} else {
filters = new Condition[] {
Condition.NAME
};
}
} else if (isInstance(Ellipsoid.class, object)) {
/*
* The SQL query will be something like below:
*
* SELECT ELLIPSOID_CODE FROM "Ellipsoid"
* WHERE SEMI_MAJOR_AXIS >= ?-ε AND SEMI_MAJOR_AXIS <= ?+ε
* ORDER BY ABS(SEMI_MAJOR_AXIS-?)
*/
table = TableInfo.ELLIPSOID;
filters = new Condition[] {
new FloatCondition("SEMI_MAJOR_AXIS", ((Ellipsoid) object).getSemiMajorAxis())
};
} else {
// Not a supported type. Returns all codes.
return super.getCodeCandidates(object);
}
/*
* At this point we collected the information needed for creating the main SQL query.
* If the filters include a filter by names, we will need to take aliases in account.
* The following block prepares in advance the SQL query that we will need to execute,
* but does not execute it now. Note that this block overwrites the `buffer` content,
* so that buffer shall not contain valuable information yet.
*/
final StringBuilder buffer = new StringBuilder(350); // Temporary buffer for building SQL query.
final Set<String> namePatterns;
final String aliasSQL;
if (ArraysExt.containsIdentity(filters, Condition.NAME)) {
namePatterns = new LinkedHashSet<>();
namePatterns.add(toDatumPattern(object.getName().getCode(), buffer));
for (final GenericName id : object.getAlias()) {
namePatterns.add(toDatumPattern(id.tip().toString(), buffer));
}
buffer.setLength(0);
buffer.append("SELECT OBJECT_CODE FROM [Alias] WHERE OBJECT_TABLE_NAME='").append(table.unquoted()).append("' AND ");
// PostgreSQL does not require explicit cast when the value is a literal instead of "?".
appendFilterByName(namePatterns, "ALIAS", buffer);
aliasSQL = dao.translator.apply(buffer.toString());
buffer.setLength(0);
} else {
namePatterns = null;
aliasSQL = null;
}
/*
* Prepare the first part of SQL statement, which may be like below:
*
* SELECT <codeColumn> FROM <table>
* WHERE CAST(<typeColumn> AS VARCHAR(80)) LIKE 'type%'
* AND <filter.column> IN (<filter.values>)
* AND (LOWER(<nameColumn>) LIKE '<name>%')
*
* The query is assembled in the `buffer`. The first WHERE condition specifies the desired type.
* That condition may be absent. The next conditions specify desired values. It may be EPSG codes
* of dependencies or parameter values as floating points. The last condition is on the object name.
* It may be absent (typically, only datums or reference frames have that condition).
*/
buffer.append("SELECT ").append(table.codeColumn).append(" FROM ").append(table.table);
table.where(object, buffer); // Unconditionally append a "WHERE" clause.
boolean isNext = false;
for (final Condition filter : filters) {
isNext |= filter.appendToWhere(buffer, isNext);
}
/*
* We did not finished to build the SQL query, but the remaining part may require a JDBC connection.
* We do not use PreparedStatement because the number of parameters varies, and we may need to use a
* Statement two times for completely different queries.
*/
try (Statement stmt = dao.connection.createStatement()) {
if (namePatterns != null) {
if (isNext) buffer.append(" AND ");
isNext = false;
appendFilterByName(namePatterns, table.nameColumn, buffer);
try (ResultSet result = stmt.executeQuery(aliasSQL)) {
while (result.next()) {
final int code = result.getInt(1);
if (!result.wasNull()) { // Should never be null but we are paranoiac.
if (!isNext) {
isNext = true;
buffer.append(" OR ").append(table.codeColumn).append(" IN (");
} else {
buffer.append(',');
}
buffer.append(code);
}
}
}
if (isNext) buffer.append(')');
}
buffer.append(getSearchDomain() == Domain.ALL_DATASET
? " ORDER BY ABS(DEPRECATED), "
: " AND DEPRECATED=0 ORDER BY "); // Do not put spaces around "=" - SQLTranslator searches for this exact match.
for (final Condition filter : filters) {
filter.appendToOrderBy(buffer);
}
buffer.append(table.codeColumn); // Only for making order determinist.
/*
* At this point the SQL query is complete. Run it, preserving order.
* Then sort the result by taking in account the supersession table.
*/
final Set<String> result = new LinkedHashSet<>(); // We need to preserve order in this set.
try (ResultSet r = stmt.executeQuery(dao.translator.apply(buffer.toString()))) {
while (r.next()) {
result.add(r.getString(1));
}
}
result.remove(null); // Should not have null element, but let be safe.
if (result.size() > 1) {
final Object[] id = result.toArray();
if (dao.sort(table.unquoted(), id)) {
result.clear();
for (final Object c : id) {
result.add((String) c);
}
}
}
return result;
} catch (SQLException exception) {
throw dao.databaseFailure(Identifier.class, String.valueOf(CollectionsExt.first(filters[0].values)), exception);
}
}