Loading SDO_GEOMETRY data from Oracle in TimeXtender

Contents

• Info about SDO_GEOMETRY
• How to load SDO_GEOMETRY data - WKB and SRID - in Oracle
• How to load SDO_GEOMETRY data from Oracle in TimeXtender
  • Open the Data Source Settings dialog
  • Add a Query Table
  • Read objects from Data Source
  • Add a custom field of type Geometry
  • Add a custom transformation
  • Mark fields as read-only
  • Deploy, execute, and preview

Info about SDO_GEOMETRY

The SDO_GEOMETRY data type in Oracle contains spatial data, e.g. geographic locations and shapes, that can be used for spatial presentations calculations, and transformations. An equivalent data type in SQL Server for this purpose is GEOMETRY. Both databases store the same data in proprietary formats which are incompatible with one another. Both databases, however, can convert the data to a standard format named well-known binary (WKB) or well-known text (WKT), defined by the Open Geospatial Consortium and the International Organization for Standardization (ISO). ISO/IEC 13249-3:2016, Information technology - Database language - SQL multimedia and application packages Part 3: Spatial]

The proprietary formats in both databases consist not only of the data defined by the WKB/WKT format but also contain a special number defining the coordinate system associated with all points in the spatial data. This number is named spatial reference identifier (SRID) and is largely interchangeable. Both databases understand SRID numbers defined by the Environmental Systems Research Institute (Esri) and the European Petroleum Survey Group (EPSG). The databases support also their own custom SRID numbers, which are not compatible between both databases. Most of the numbers, however, including the ellipsoidal World Geodetic System (WGS84) used by GPS and Google Maps, spheroid to flat zones in the Universal Transverse Mercator system (UTM), and many others, are identical in both databases and can be interchanged.

How to load SDO_GEOMETRY data – WKB and SRID – in Oracle

To load both WKB and SRID data from an SDO_GEOMETRY column in Oracle, we need to SELECT the data in two separate columns.

In our example, we have a table named GEOTABLE with two columns: “ID” int – which is our primary key, and “GEO” SDO_GEOMETRY.

To select the ID as well as the WKB and SRID data from the GEO column, we can type the following query in Oracle:

Please notice the table alias “t”. In order to access the SDO_SRID property, we need to have a table alias specified in Oracle.

Here is the result of the query:

The first row uses the SRID 4326, which is the World Geodetic System (WGS84), and the second row uses the SRID 4283, which is the Geocentric Datum of Australia (GDA94). Many other values are possible, depending on the geographical location, e.g. SRID 25832 means UTM zone 32N of the European Terrestrial Reference System (ETRS89) widely used in maps of Denmark and Germany.

How to load SDO_GEOMETRY data from Oracle in TimeXtender

To load SDO_GEOMETRY data from Oracle, we need to perform the following steps in Discovery Hub.

1. Right-click your Oracle data source, and open Data Source Settings :

2. In the “Query tables” tab, add the previous query from Oracle selecting both the WKB data and SRID number:

   SELECT ID as ID
        ,SDO_UTIL.TO_WKBGEOMETRY(GEO) as GEO_WKB 
        ,t.GEO.SDO_SRID as GEO_SRID 
   FROM GEOTABLE t  

   

   Remember to enter the name of the query and schema.
   The general formula to select the WKB data is:

   SDO_UTIL.TO_WKBGEOMETRY(column) as column_WKB

   and to select the SRID number the formula is:

   table_alias.column.SDO_SRID as column_SRID 

3. Synchronize the Oracle data source, and select the new query table from the Data Selection:

   

   

4. Add a new custom field of Geometry type to the table in your business unit:

   

   

5. Add a custom transformation to the newly added field, combining the WKB and SRID values into a SQL Server GEOMETRY value:

   

   The formula is following:

   geometry::STGeomFromWKB([column_WKB], [column_SRID])

   Note: If the SRID value from Oracle is unknown in SQL Server or no value was set in Oracle, you can type 0 instead of [column_SRID] or any predefined value, e.g. 4326 – which indicates WGS84.

6. Mark the _WKB and _SRID fields as raw-only, so that they would not show up in the results:

   

7. Deploy, execute and preview the valid data:

   

This way you have loaded SDO_GEOMETRY values from Oracle into GEOMETRY values in SQL Server.