Reading an OGR VRT with geometry from an Oracle OCI data source

Question

I am having trouble creating/reading OGR VRT files from an Oracle OCI data source.

I am running GDAL 2.2.1 on Windows 7 Professional SP1.

My data source is an Oracle 11g (11.2.0.4.0) 64bit database running ArcSDE (10.2.2 Patch 290200).

I am trying to read spatial and non spatial ArcSDE tables and views. I can read non spatial data without a problem. I can even read the non spatial columns of spatial tables by isolating them using the "SrcSQL" tag. Problems occur only when a spatial column is present.

Below are some simple examples that I have come up with to illustrate/isolate the problem.

This works (non spatial data):

<OGRVRTDataSource>
  <OGRVRTLayer name="TEST_tab">
    <SrcDataSource>OCI:user/pword@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=host)(PORT=1521))(CONNECT_DATA=(SID=sid)))</SrcDataSource>
    <SrcSQL>SELECT cast('This is text' AS VARCHAR(255)) AS col_text FROM dual</SrcSQL>
  </OGRVRTLayer>
</OGRVRTDataSource>

This doesn't work (spatial data - WKB linestring):

<OGRVRTDataSource>
  <OGRVRTLayer name="TEST_tab">
    <SrcDataSource>OCI:user/pword@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=host)(PORT=1521))(CONNECT_DATA=(SID=sid)))</SrcDataSource>
    <SrcSQL>SELECT cast('This is text' AS VARCHAR(255)) AS col_text, sde.ST_GeomFromText('LINESTRING(-71.160281 42.258729,-71.160837 42.259113,-71.161144 42.25932)',4269) AS col_geom FROM dual</SrcSQL>
    <GeometryField encoding="WKB" field="COL_GEOM"></GeometryField>
  </OGRVRTLayer>
</OGRVRTDataSource>

Ogrinfo returns the following error - "Unable to identify source field 'COL_GEOM' for geometry."

I have tried specifying "wkbLineString" in a "GeometryType" tag, but the extra tag/specification returns the same error.

---

After @user30184 @Albert Godfrind for the sdo_geometry cast suggestion, here is a working VRT using the sdo_geometry solution :

<OGRVRTDataSource>
  <OGRVRTLayer name="test">
    <SrcDataSource>OCI:user/pword@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=host)(PORT=1521))(CONNECT_DATA=(SID=sid)))</SrcDataSource>
    <SrcSQL>SELECT cast('This is text' AS VARCHAR(255)) AS col_text, sdo_geometry(sde.st_asbinary(sde.ST_GeomFromText('LINESTRING(-71.160281 42.258729,-71.160837 42.259113,-71.161144 42.25932)',4269))) AS col_geom FROM dual</SrcSQL>
    <LayerSRS>EPSG:4326</LayerSRS>
  </OGRVRTLayer>
</OGRVRTDataSource>

Note the absence of the "GeometryField" tag. When the tag was included, ogrinfo would return the same "Unable to identify source field 'COL_GEOM' for geometry." error. I am assuming that the error is returned because there is no "SDO_GEOMETRY" encoding option - only "WKT", "WKB" or "PointFromColumns.

The VRT works in QGIS as well!

Answer 1

The OCI driver only understands SDO_GEOMETRY types. One possibility is to turn your st_geometry to wktext:

select ... sdo_geometry(sde.st_astext(shape)...) as ora_geom from ...

See no†e ST_GEOMETRY to SDO_GEOMETRY (mentioned in one comment)

You could define a view on the ESRI table to make the transformation a bit more transparent. I suspect you need some more setup (such as oracle spatial metadata).

Not sure this would work anyway: if you use the VRT from a GIS tool then that will eventually lead to some spatial filter queries being generated as the user pans and zooms. That query will find its way eventually to the OCI driver who will then turn this into an Oracle Spatial filter query (... where sdo_filter (...) ...) which will fail since the ESRI implementation will not understand that.

If all you want is to use ogr2ogr to copy the data out, then the above will not be an issue.