How Open Source King.Oracle FDO provider uses Oracle Spatial

This document describe how King.Oracle provider will read Oracle Spatial Tables and how to configure them for optimal use.


Quick Reference to Oracle Spatial

Oracle Spatial Geometry Type

Oracle Spatial supports object relational model for representing geometry. Geometry is stored in native spatial data type MDSYS.SDO_GEOMETRY.

Oracle Geometry Metadata Views

To create an Spatial Index for an SDO_GEOMETRY column there has to be a row in USER_SDO_GEOM_METADATA view. Row in USER_SDO_GEOM_METADATA contains Table Name, Column Name of geometry, information about lower and upper bound of every dimension, tolerance of every dimension and SRID Coordinate System (Spatial Reference System) identifier.

Oracle Spatial Index

Oracle Spatial lets you create Spatial Index on geometry column. For Spatial Index R-tree indexing is used (Oracle supports also quadtree indexing but it is discouraged). Spatial Index has layer geometry type parameter which defines which type of geometries are included in layer. It can be point, line, polygon or combination of those.


An SRID (Spatial Reference Identifier) is an integer value a key to MDSYS.CR_SYS table describing coordinate systems. If no coordinate system is defined SRID is NULL value.


How King.Oracle uses Oracle Spatial

King.Oracle provider will read ALL_SDO_GEOM_METADATA View and find tables and columns with SDO_GEOMETRY data type. For each pair Table-Column King.Oracle will create an FDO Feature class. If you want a FDO Feature class to be created based on Oracle View than you need to add a row in USER_SDO_GEOM_METADATA for that View. Configuration parameter for King.Oracle provider "OracleSchema" determines if all raws from ALL_SDO_GEOM_METADATA will be used or just rows with Owner equal to "OracleSchema". Name of FDO Feature class is created based on Oracle Schema, Table Name and Column Name of the geometry (Schema~TableName~ColumnName).

Spatial Index and Layer Geometry Type

When creating Spatial index in Oracle you can set parameter layer_gtype which defines type of geometry used in geometry column. Type can be one of: POINT, MULTIPOINT, LINE, MULTILINE, POLYGON, MULTIPOLYGON, COLLECTION or you can create index without specifying geometry type. If you define layer type you can ensure that all geometries inserted into table are of that type. King.Oracle will use this type to define correct layer type for MapGuide. So if this parameter is defined King.Oracle provider will read it and setup correct layer type in MapGuide ( point, line, polygon). You can use Fdo2Fdo tool to find right layer type and create a spatial index in Oracle.

Layer Extent and SDO_GEOM_METADATA

MapGuide is using geometry extent provided by King.Oracle provider to zoom on data in preview window. King.Oracle provider will read extent in two ways. One is for non-geodetic data it will read from spatial index ( which is updated dynamically) and second for geodetic coordinate systems it will read from Oracle SDO_GEOM_METADATA view. Usually in this view will be [-180, -90] [180,90] for geodetic data so MapGuide You can use Fdo2Fdo to recalculate geometry extent and find correct SRID and insert or update SDO_GEOM_METADATA view.

Spatial Context

FDO has a concept of Spatial Context and every geometry in a layer has a description to which Spatial Context it belongs. Spatial Context is description of coordinate system (WKT), number of dimensions, geometry extent and tolerance. When reading Oracle tables King.Oracle provider will create one or more Spatial Contexts which depends on number of used coordinate systems (SRID). If all geometry tables have same SRID it will be just one Spatial Context. Important thing is that geometry extent is calculated based on all layers in that Spatial Context. So you need to set correct layer extents for every layer to be correctly displayed in preview window of MapGuide.

Primary Key and Identity

King.Oracle provider will check if table has a primary key defined. If primary key is found and it is single column with NUMERIC or VARCHAR2 data type than it will be used as identity column for FDO Feature class. FDO Feature class can be used without Identity for viewing geometry data. If you want to be able to edit data in MapGuide or Map you need an Identity column. You can use Fdo2Fdo tool to create primary key and also to create primary key on Oracle Views. King.Oracle provider allows update, delete and insert of data without identity column. For update you can set any filter (geometry, attribute filter) or no filter at all.

Oracle Sequence "_FDOSEQ"

If FDO Feature class has primary key defined and if it is of type NUMERIC than King.Oracle provider will also check for Oracle Sequence to use for primary key column. Sequence has to have a name like TableName_FDOSEQ to be used by provider. For table named RAIL sequence name of sequence is RAIL_FDOSEQ. This sequence will be used by provider during inserts to populate value for identity column. If value for identity is not set during insert than provider will use next value from sequence to insert a row. When using Fdo2Fdo tool to import data to Oracle it will create primary key and sequence if source FDO Feature class has a identity of integer (int16,int32,int64) type.

Oracle Views

You can use non-materialized and materialized Oracle Views with King.Oracle provider. To use view you need to insert SDO_GEOM_METADATA in same way as you would do for table. Also you can create an primary key on non-materialized and materialized Oracle Views.