Oracle Metadata
For Oracle spatial tables and views to be recognized by the FME:
- Oracle Spatial Reader,
- FeatureReader,
- SQLCreator,
- SQLExecutor
there needs to be metadata entries in the ALL_SDO_GEOM_METADATA view. This only applies to spatial queries. If you use a WHERE to do an attribute query, this problem does not arise.
If you get an FME error message, something like:
|ERROR |Execution of statement `
SELECT "PARCEL_VIEW"."PARCEL_ID", "PARCEL_VIEW"."LANDUSE", "PARCEL_VIEW"."GEOM"
FROM ( SELECT * FROM "PARCEL_VIEW" A
WHERE MDSYS.SDO_RELATE( A.GEOM, mdsys.sdo_geometry(2003,NULL,null,mdsys.sdo_elem_info_array(1,1003,3),mdsys.sdo_ordinate_array(3118280.75,10081402,3137693.75,10098911)),'mask=ANYINTERACT querytype=WINDOW') = 'TRUE' ) "PARCEL_VIEW" WHERE 1 = 1'
did not succeed; error was `ORA-29902: error in executing ODCIIndexStart() routineThen the spatial metadata probably does not exist. The highlighted NULL entry in the message above should be the SRID. SRIDs are added to the metadata table, which represents the coordinate system of the stored geometry data.
Writing Spatial Data and Metadata Creation with FME
FME can create this spatial metadata when creating the spatial table. It cannot 'add' metadata after the fact, only when executing the Create Table command. For the writer, under General > Table Handling, the two settings that enable this are Create If Needed and Drop and Create.
When either of the above options is enabled, the Table Creation section (below) becomes editable. This section drives what is set in the metadata for the extents of the spatial data being inserted into the geometry column.
Ensure you review this section to customize these settings, and consider if you need a Z coordinate (3D data).
Be sure to consider the Comparison Tolerance for the coordinates (or the granularity at which spatial queries will operate on the spatial data).
Also, ensure that the Spatial SRID is set to the correct value for the spatial data being written to the database. You can also customize the Spatial Index Name and other parameters used for creating the spatial index.
Spatial Tables
When Oracle spatial tables are created using SQL*Plus or another tool, there may not be entries in the ALL_SDO_GEOM_METADATA table that define the extents and coordinate system of the data. You can check for your metadata entries using:
SELECT * FROM ALL_SDO_GEOM_METADATA
WHERE TABLE_NAME='<table name>'If you don't receive any values, you need to insert the metadata. If you see more than one entry, be sure to confirm the metadata is for the correct owner of the table you are looking to run a spatial query against. There may be more than one schema holding the same-named table.
For example, SUPPORT.SUPPORT_DATA and SUPPORT2.SUPPORT_DATA can have entries in the ALL_SDO_GEOM_METADATA table; therefore, it is essential to review the OWNER information.
A revised query might be
SELECT *
FROM ALL_SDO_GEOM_METADATA
WHERE TABLE_NAME='<table name>'
AND OWNER = '<owner name>'To add entries, issue an SQL command that is similar to this for 2D geometry (assuming you are connected as the table owner):
INSERT INTO USER_SDO_GEOM_METADATA ( TABLE_NAME, COLUMN_NAME, DIMINFO, SRID )
VALUES ('TABLE_NAME','GEOM_COLUMN_NAME',
MDSYS.SDO_DIM_ARRAY(
MDSYS.SDO_DIM_ELEMENT('X',80000,100000,0.005),
MDSYS.SDO_DIM_ELEMENT('Y',425000,450000,0.005)
), 32039
); For 3D geometry (assuming you are connected as the table owner):
INSERT INTO USER_SDO_GEOM_METADATA ( TABLE_NAME, COLUMN_NAME, DIMINFO, SRID )
VALUES ('TABLE_NAME', 'GEOM_COLUMN_NAME',
MDSYS.SDO_DIM_ARRAY(
MDSYS.SDO_DIM_ELEMENT('X',-180,180,0.0005),
MDSYS.SDO_DIM_ELEMENT('Y',-90,90,0.0005),
MDSYS.SDO_DIM_ELEMENT('Z',0,12000,0.05)
), 4326
);In the 2D example...where the X, Y values define the minimum bounding box of the spatial data, or the expected maximum extents. In this case, the values are minX=80000, minY=425000, maxX=100000, maxY=450000, and the SRID value is 32039.
For the 3D example, the additional Z value represents elevation, with values ranging from minZ=0 to maxZ=12000.
Spatial Views
If you create a spatial view in an Oracle database, you also need to add information to the metadata table for the view to be defined. If you don't do this, you will see the view in a table list with the Oracle (non-spatial) reader, but not the Oracle Spatial Object reader.
To add the metadata information for a view, use a command similar to this (assuming you are connected as the view owner):
-- If inserting as the OWNER of the VIEW
INSERT INTO USER_SDO_GEOM_METADATA
USING SELECT 'RT43495_VIEW', COLUMN_NAME, DIMINFO, SRID
FROM ALL_SDO_GEOM_METADATA
WHERE OWNER = 'DEV' AND TABLE_NAME = 'RT43495'; In this example, the metadata entry is taken from the metadata entry for the underlying table, which is being used to generate the view. Don't forget to COMMIT the changes!!
Adding Metadata for a Spatial Table You Do Not Own
You can insert this metadata as another user, provided you have the necessary privileges, such as the DBA role.
It is recommended to use the schema owner to insert the metadata for a spatial table, as seen in the above example.
ALL_SDO_GEOM_METADATA is actually a view based on MDSYS.SDO_GEOM_METADATA_TABLE table. Since we can't enter metadata directly into the ALL_SDO_GEOM_METADATA view, we must insert the data into the MDSYS.SDO_GEOM_METADATA_TABLE table in this scenario.
To add the metadata information for a view (when you are not the owner of the view), assuming you are connected as another user, other than DEV, but you want to insert metadata for a DEV-owned view:
INSERT INTO MDSYS.SDO_GEOM_METADATA_TABLE
USING SELECT OWNER,'RT43495_VIEW', COLUMN_NAME, DIMINFO, SRID
FROM ALL_SDO_GEOM_METADATA
WHERE OWNER = 'DEV' AND TABLE_NAME = 'RT43495';This will create an entry in the ALL_SDO_GEOM_METADATA view for the view DEV.RT43495_VIEW, owned by DEV.
These days, tools like Quest Toad & Oracle's SQL Developer can also help you create spatial metadata, so be sure to check them out as alternative solutions when you are missing metadata.