The cliff-notes were that I couldn't bootstrap a fresh Oracle XE installation with spatial data because the create data store UI failed and as a result I had to fallback to Oracle's SQLPlus CLI to create the necessary oracle user with appropriate granted permissions and then proceeded to use ogr2ogr to copy the SHP file into the Oracle database.
What caused me to revisit?
- Turns out the King Oracle provider doesn't implement the FDO create data store command! That's why the UI will ultimately not work, despite the error message in the referenced post actually referring to something else.
- The actual "creating" of the data store is a series of SQL commands executed in SQLPlus.
- I used ogr2ogr to copy that SHP file because I had given up on using FDO Toolbox for any of the remaining setup steps until the data was in.
Because FdoCmd supports executing pass-through SQL queries/commands if the provider supports it (King Oracle does) and I didn't even try the actual copying of data, with the advent of FdoCmd this was something worth revisiting and trying again.
So for this post, we begin from this starting position.
- We have a freshly pulled down Oracle XE docker image (in this example. the running container is listening on 192.168.0.3)
- We have the latest FDO Toolbox that comes with the FdoCmd CLI utility
And our end goal is:
- We can create the required oracle user through FdoCmd
- We can grant that oracle user the necessary permissions through FdoCmd
- We can "context switch" to this created user by running some test SQL commands through FdoCmd with this user's credentials
- And then the main event: We can bulk copy a SHP file of parcel data into this data store, with FdoCmd automatically creating whatever schemas/classes required
- Finally, we can preview the data afterwards in both FdoCmd and FDO Toolbox and that the data is consumable from MapGuide
Creating the mapguide user
The very first thing we need to do is create the mapguide user and grant it the necessary permissions needed for the King Oracle provider to do what it needs to do, which can be done as follows:
FdoCmd.exe execute-sql-command --provider OSGeo.KingOracle --connect-params Username system Password oracle Service //192.168.0.3/xe --sql "CREATE USER mapguide IDENTIFIED BY mapguide"
FdoCmd.exe execute-sql-command --provider OSGeo.KingOracle --connect-params Username system Password oracle Service //192.168.0.3/xe --sql "GRANT CREATE SESSION, ALTER SESSION, CREATE DATABASE LINK, CREATE MATERIALIZED VIEW, CREATE PROCEDURE, CREATE PUBLIC SYNONYM, CREATE ROLE, CREATE SEQUENCE, CREATE SYNONYM, CREATE TABLE, CREATE TRIGGER, CREATE TYPE, CREATE VIEW, UNLIMITED TABLESPACE TO mapguide"
Testing the created Oracle user works
To test that created Oracle user works, we can do a schema listing with our new Oracle user credentials
FdoCmd.exe list-schemas --provider OSGeo.KingOracle --connect-params Username mapguide Password mapguide OracleSchema mapguide Service //192.168.0.3/xe
This should print out KingOra as the schema name. Even though we haven't created any tables yet, the act of attempting to do a schema listing requires opening a King Oracle FDO connection with our user credentials first.
Copying our SHP file
Unlike for other FDO providers, there are several quirks unique to the King Oracle provider that we have to negotiate around.
- By default, FDO class names are transcoded to a weird tilde-delimited format. Presumably, this format is the provider's way to support tables with multiple geometry columns or allowing for tables of the same names across different schemas.
- FDO spatial contexts are automatically inferred from the name, which is of the form OracleSridXXXX. When creating spatial contexts for King Oracle, we don't bother filling all the information we'd normally provide for a spatial context (CS, extents, etc) as the provider disregards this information.
With these quirks now known, here's how we can negotiate around them.
Firstly, we run the expected copy-class command but with a --setup-only flag specified.
FdoCmd.exe copy-class --src-provider OSGeo.SHP --src-connect-params DefaultFileLocation D:\fdo-4.1\Providers\SHP\TestData\Sheboygan --dst-provider OSGeo.KingOracle --dst-connect-params Username mapguide Password mapguide OracleSchema mapguide Service //192.168.0.3/xe --src-schema Default --src-class Parcels --dst-schema KingOra --dst-class Parcels --override-sc-name "WGS84 Lat/Long's, Degrees, -180 ==> +180" --override-sc-target-name OracleSrid4326 --setup-only
The string "WGS84 Lat/Long's, Degrees, -180 ==> +180" happens to be the spatial context name of our source SHP file.
When --setup-only flag is specified, only the setup portion of the bulk copy is executed, which in our case means:
- The Parcels feature class will be created in Oracle under its transcoded name
- That feature class will be associated to the 4326 SRID through the OracleSrid4326 spatial context name that we're overriding from the source
Now that the table has been created, and knowing how the provider transcodes FDO feature class names (in our case Parcels -> MAPGUIDE~PARCELS~GEOMETRY), we can re-run the above command but:
- Omitting the --setup-only flag
- Using the provider-transcoded FDO class name instead of our normal class name
FdoCmd.exe copy-class --src-provider OSGeo.SHP --src-connect-params DefaultFileLocation D:\fdo-4.1\Providers\SHP\TestData\Sheboygan --dst-provider OSGeo.KingOracle --dst-connect-params Username mapguide Password mapguide OracleSchema mapguide Service //192.168.0.3/xe --src-schema Default --src-class Parcels --dst-schema KingOra --dst-class MAPGUIDE~PARCELS~GEOMETRY --override-sc-name "WGS84 Lat/Long's, Degrees, -180 ==> +180" --override-sc-target-name OracleSrid4326
And the bulk copy should commence.
One thing that you'll notice and why this post will sadly be nothing more than an intellectual exercise rather than a practical how-to is that the bulk copy is extremely slow. Although the provider supports batched inserts which would allow for greater throughput, the implementation has proven to be buggy when bulk copying my various example test data files.
KingFdoClass setup
At this point the Oracle data store is ready to be consumed in any FDO client application (eg. MapGuide). However the data store provides the ugly transcoded class names by default which complicates use cases like exposing King Oracle feature classes as WFS layers in MapGuide.
Fortunately, the provider supports a feature called the KingFdoClass which is a special table in your Oracle schema to register spatial tables as FDO feature classes. With this table present and specified as a connection property, it augment the default FDO schema/class listing behavior with extra classes based on the contents of this table. The names of these feature classes will be based on whatever's registered in the KingFdoClass table, so no more ugly tilde-fied class names!
The following command will create a table named FDO_FEATURE_CLASSES which we'll nominate as the KingFdoClass table.
FdoCmd.exe execute-sql-command --provider OSGeo.KingOracle --connect-params Username mapguide Password mapguide OracleSchema mapguide Service //192.168.0.3/xe --sql "CREATE TABLE FDO_FEATURE_CLASSES( FDO_UNIQUE_ID NUMBER(*,0),FDO_ORA_OWNER VARCHAR2(64 BYTE), FDO_ORA_NAME VARCHAR2(64 BYTE), FDO_ORA_GEOMCOLUMN VARCHAR2(1024 BYTE), FDO_SPATIALTABLE_OWNER VARCHAR2(64 BYTE), FDO_SPATIALTABLE_NAME VARCHAR2(64 BYTE), FDO_SPATIALTABLE_GEOMCOLUMN VARCHAR2(1024 BYTE), FDO_CLASS_NAME VARCHAR2(256 BYTE), FDO_SRID NUMBER, FDO_DIMINFO MDSYS.SDO_DIM_ARRAY , FDO_CS_NAME VARCHAR2(256 BYTE), FDO_WKTEXT VARCHAR2(2046 BYTE), FDO_LAYER_GTYPE VARCHAR2(64 BYTE), FDO_SEQUENCE_NAME VARCHAR2(64 BYTE), FDO_IDENTITY VARCHAR2(1024 BYTE), FDO_SDO_ROOT_MBR MDSYS.SDO_GEOMETRY , FDO_POINT_X_COLUMN VARCHAR2(128 BYTE), FDO_POINT_Y_COLUMN VARCHAR2(128 BYTE), FDO_POINT_Z_COLUMN VARCHAR2(128 BYTE), FDO_SPATIAL_CONTEXT VARCHAR2(128 BYTE))"
Then we'll register our freshly copied parcels table with a class name of "Parcels"
FdoCmd.exe execute-sql-command --provider OSGeo.KingOracle --connect-params Username mapguide Password mapguide OracleSchema mapguide Service //192.168.0.3/xe --sql "INSERT INTO FDO_FEATURE_CLASSES ( fdo_class_name, fdo_ora_owner, fdo_ora_name, fdo_ora_geomcolumn, fdo_identity ) values ( 'Parcels', 'MAPGUIDE', 'PARCELS', 'GEOMETRY', 'FEATID' )"
When we run our list-classes command by default
FdoCmd.exe list-classes --provider OSGeo.KingOracle --connect-params Username mapguide Password mapguide OracleSchema mapguide Service //192.168.0.3/xe
We get our default ugly class name
MAPGUIDE~PARCELS~GEOMETRY
Now when we run the same command but with the KingFdoClass property set
FdoCmd.exe list-classes --provider OSGeo.KingOracle --connect-params Username mapguide Password mapguide OracleSchema mapguide Service //192.168.0.3/xe KingFdoClass FDO_FEATURE_CLASSES
We get the "clean" class names in addition to the ugly ones
MAPGUIDE~PARCELS~GEOMETRY
Parcels
In conclusion
While we are able to finally bootstrap a new Oracle data store end-to-end solely with FdoCmd.exe, the slow bulk copy performance means I cannot seriously recommend the use of FdoCmd.exe when copying spatial data files of non-trivial size into Oracle with this provider.
If/when the batch insert issue is finally addressed, we may revisit this process to see if it now actually viable, rather than an intellectual curiosity as it currently stands.
Posts
No comments:
Post a Comment