Importing an Oracle Data Source
The minimum requirements for OpenCities Map to extract (import) spatial data from tables in Oracle Spatial or Locator are:
- Tables can have only one geometry column (type: SDO_GEOMETRY).
- Geometry data in a table must be restricted to only one geometry type (SDO_GTYPE).
To determine if a table contains more than one geometry type, use the following query:
SELECT t.<geometry column>.SDO_GTYPE, COUNT(*) FROM <TABLE NAME>T GROUP BY T<geometry column>.SDO_GTYPE;
If the query returns only one column, the table meets the one geometry type requirement for OpenCities Map.
- Geometry type support is restricted to point, multi point, curve, multi curve, polygon and multi polygon types.
- The geometry column needs to have a spatial index, with the geometry type explicitly defined.
The following is an example of a CREATE TABLE statement:
CREATE TABLE "PARCELS" (Owner VARCHAR2(20), Market_Value VARCHAR2(10), Parcel_ID NUMBER PRIMARY KEY, Geometry MDSYS.SDO_GEOMETRY);
The following is an example of a CREATE INDEX statement with an explicit geometry type:
CREATE INDEX PARCELS_SIDX ON PARCELS(Geometry) INDEXTYPE IS MDSYS.SPATIAL_INDEX_V2 PARAMETERS ('layer_gtype=POINT');
To be able to create a spatial index on a table it needs to be registered in the appropriate Oracle metadata view (typically USER_SDO_GEOM_METADATA).
The following is an example of the entry used to be able to successfully execute the CREATE INDEX statement listed above:
INSERT INTO USER_SDO_GEOM_METADATA (TABLE_NAME, COLUMN_NAME, DIMINFO, SRID) VALUES ('PARCELS', 'Geometry', SDO_DIM_ARRAY (SDO_DIM_ELEMENT('LONG', -180.0, 180.0, 0.5), SDO_DIM_ELEMENT('LAT', -90.0, 90.0, 0.5)), 8307);
An alternate example would be:
INSERT INTO USER_SDO_GEOM_METADATA (TABLE_NAME, COLUMN_NAME, DIMINFO, SRID) VALUES ('PARCLES', 'Geometry', MDSYS.SDO_DIM_ARRAY (MDSYS.SDO_DIM_ELEMENT('X', 2197290.78478466, 2401264.08333616, 0.000000050), MDSYS.SDO_DIM_ELEMENT('Y', 703310.077261334, 911592.401111043, 0.000000050)), NULL);
Any table with spatial data in Oracle that abides to the requirements listed in this section, is available for import into OpenCities Map using the Add Oracle (read-only) option in the Map Imports/Exports dialog.