On Oracle NG client on Geoserver on the OracleNGDialect does not include “GEOMETRY” as an expected column name.
The update to the dialect file would be
private static final Set GEOM_NAMES = Set.of(
“GEOM”,
“THE_GEOM”,
“SHAPE”,
“WKB_GEOM”,
“WKT_GEOM”,
“GEOMETRY”
);
I can fix in my copy but a permanent fix would be great
Kind Regards
Johan Nel
Hi Johan,
I cannot match the class you’re talking about with the current GeoServer/GeoTools code bases (and nothing in the recent history either). Which version of GeoServer are you using? And, are we talking about the Oracle datastore, or something else?
Currently recognition of the geometry column is based on metadata, there is no list of “well known names”.
Cheers
Andrea
As far as I know the names of the geometry columns are queried from MDSYS.ALL_SDO_GEOM_METADATA and the names can be whatever, so no special support for “GEOMETRY” is needed. Or do you have tables which are not registered into metadata?
-Jukka Rahkonen-
Let me explain my problem in a bit more detail
I am trying to make a table WFST compliant. When configuring it to point geometry metadata table under the store, an pointing to MDSYS.ALL_GEOMETRY_METADATA table I get an error
Failed to load attribute list, internal error is: ORA-00904: “F_TABLE_SCHEMA”: invalid identifier.
In the log I get the following error
Caused by: Error : 904, Position : 100, SQL = SELECT TYPE FROM MDSYS.ALL_SDO_GEOM_METADATA WHERE F_TABLE_NAME = :1 AND F_GEOMETRY_COLUMN = :2 AND F_TABLE_SCHEMA = :3 , Original SQL = SELECT TYPE FROM MDSYS.ALL_SDO_GEOM_METADATA WHERE F_TABLE_NAME = ? AND F_GEOMETRY_COLUMN = ? AND F_TABLE_SCHEMA = ?, Error Message = ORA-00904: “F_TABLE_SCHEMA”: invalid identifier
In geotools 33.2 OracleDialect.java line 282 - 300 is
/** Tries to use the geometry metadata table, if available */
private Class<?> lookupGeometryOnMetadataTable(Connection cx, String tableName, String columnName, String schema)
throws SQLException {
if (geometryMetadataTable == null) {
return null;
}
List<String> parameters = new ArrayList<>();
// setup the sql to use for the ALL_SDO table
String metadataTableStatement =
"SELECT TYPE FROM " + geometryMetadataTable + " WHERE F_TABLE_NAME = ?" + " AND F_GEOMETRY_COLUMN = ?";
parameters.add(tableName);
parameters.add(columnName);
if (schema != null && !"".equals(schema)) {
metadataTableStatement += " AND F_TABLE_SCHEMA = ?";
parameters.add(schema);
The table structure for MDSYS.ALL_DSO_GEOM_METADATA is
OWNER
TABLE_NAME
COLUMN_NAME
DIMINFO
SRID
I think the query being done is coming from ALL_GEOMETRY_COLUMNS that is referencing F_TABLE_SCHEMA and F_TABLE_NAME. The ALL_GEOMETRY_COLUMNS is not returning any data so the query being done is not referencing the right table structure hence the error
The metadata table in this context does not mean the Oracle metadata table MDSYS.ALL_SDO_GEOM_METADATA but the one that is documented here Oracle — GeoServer 2.28.0 User Manual.
Starting with GeoServer 2.1.4 the administrator can address the above issues by manually creating a geometry metadata table describing each geometry column. Its presence is indicated via the Oracle datastore connection parameter named Geometry metadata table (which may be a simple table name or a schema-qualified one). The table has the following structure (the table name is flexible, just specify the one chosen in the data store connection parameter)
If you do not have such manually created metadata table, leave that connection parameter empty and GeoServer will use the default MDSYS.ALL_GEOMETRY_METADATA.
Could you explain what file you were planning to edit for making the “permanent fix” that you mentioned in your question? Was the edit perhaps suggested by AI?
-Jukka Rahkonen-
Hi Jukka
Yes it was an AI hallucination. Apologies for that. Will definitely go and (RTFM) study the manual. Will test without the table and see
Kind Regards
Johan Nel
Read also this Controlling feature ID generation in spatial databases — GeoServer 2.28.x User Manual if you plan to use WFS-T.
-Jukka-
Thanks
You saved me a few hours
Johan