Ticket #1172 (closed defect: fixed)

Opened 2 years ago

Last modified 2 weeks ago

GDAL OVF OCI does not work with SrcSQL

Reported by: bartvde@xs4all.nl Assigned to: mloskot
Priority: normal Milestone: 1.5.3
Component: OGR_SF Version: unspecified
Severity: normal Keywords: oracle oci
Cc: warmerdam

Description (Last modified by warmerdam)

When using SrcSQL for an Oracle (OCI) table all coordinates are reported as 0 (using ogrinfo). When using a SrcLayer? to a database view everything works fine.

ovf file used: 

<OGRVRTDataSource>
  <OGRVRTLayer name="calamiteitenoud">
<SrcDataSource>OCI:BVDE/BART@//rws-svl012i.int.storage.nwr.local:1521/TGEOS.rws.nl:VW_CALAMITEITEN_OUD</SrcDataSource>
<SrcSQL>SELECT * FROM CALAMITEITEN WHERE ACTUEEL = 0</SrcSQL>
<GeometryType>wkbPoint</GeometryType>
<LayerSRS>EPSG:28992</LayerSRS>
<GeometryField encoding="PointFromColumns" x="CALX" y="CALY"/>
</OGRVRTLayer>
</OGRVRTDataSource>

I will attach a database script to create the tables and data.

Attachments

ogr.tgz (2.7 kB) - added by bartvde@xs4all.nl on 05/01/06 04:28:32.
tgz file with Oracle SQL scripts and shell scripts

Change History

05/01/06 04:28:32 changed by bartvde@xs4all.nl

  • attachment ogr.tgz added.

tgz file with Oracle SQL scripts and shell scripts

07/17/06 13:43:22 changed by warmerdam 

Mateusz,

Could you try reproducing this?

04/04/07 23:19:24 changed by warmerdam

  • priority changed from high to normal.
  • cc set to warmerdam.
  • description changed.
  • milestone set to 1.4.2.

04/24/07 08:40:18 changed by mloskot

  • status changed from new to assigned.

I think I reproduced the problem and there is something broken.

If I query OCI layer this way, value of coordinates columns (CALX, CALY) is 0: 

mloskot:~/dev/gdal/bugs/1172$ ogrinfo OCI:system/pantera@localhost -sql 'SELECT * FROM CALAMITEITEN WHERE CALID=1'
OCI: Userid=system, Password=pantera, Database=localhost
OCI: Prepare(SELECT TABLE_NAME, OWNER FROM ALL_SDO_GEOM_METADATA)
OGR: OGROpen(OCI:system/pantera@localhost/0x804e690) succeeded as OCI.
INFO: Open of `OCI:system/pantera@localhost'
      using driver `OCI' successful.
OCI: ExecuteSQL(SELECT * FROM CALAMITEITEN WHERE CALID=1)
OCI: Prepare(SELECT * FROM CALAMITEITEN WHERE CALID=1)

Layer name: SELECT * FROM CALAMITEITEN WHERE CALID=1
Geometry: Unknown (any)
OCI: Prepare(SELECT * FROM CALAMITEITEN WHERE CALID=1)
Feature Count: 1
OCI: Prepare(SELECT * FROM CALAMITEITEN WHERE CALID=1)
Layer SRS WKT:
(unknown)
CALID: Integer (0.0)
CALTITEL: String (255.0)
CALDATETYPE: String (20.0)
CALX: Integer (0.0)
CALY: Integer (0.0)
CALNAAMCOORD: String (255.0)
CALAARD: String (255.0)
CALCODE: String (100.0)
ACTUEEL: Integer (0.0)
OCI: Prepare(SELECT * FROM CALAMITEITEN WHERE CALID=1)
OGRFeature(SELECT * FROM CALAMITEITEN WHERE CALID=1):0
  CALID (Integer) = 1
  CALTITEL (String) =  
  CALDATETYPE (String) = X���X
  CALX (Integer) = 0
  CALY (Integer) = 0
  CALNAAMCOORD (String) = H���H
  CALAARD (String) = X���X
  CALCODE (String) = X���X
  ACTUEEL (Integer) = 0

OCI: 3 features read on layer 'SELECT * FROM CALAMITEITEN WHERE CALID=1'.
OGR: GetLayerCount() = 0

When I query for CALX and CALY explicitly, all values are returned properly: 

mloskot:~/dev/gdal/bugs/1172$ ogrinfo OCI:system/pantera@localhost -sql 'SELECT CALX, CALY FROM CALAMITEITEN WHERE CALID=1'D
OCI: Userid=system, Password=pantera, Database=localhost
OCI: Prepare(SELECT TABLE_NAME, OWNER FROM ALL_SDO_GEOM_METADATA)
OGR: OGROpen(OCI:system/pantera@localhost/0x8065a20) succeeded as OCI.
INFO: Open of `OCI:system/pantera@localhost'
      using driver `OCI' successful.
OCI: ExecuteSQL(SELECT CALX, CALY FROM CALAMITEITEN WHERE CALID=1D)
OCI: Prepare(SELECT CALX, CALY FROM CALAMITEITEN WHERE CALID=1D)

Layer name: SELECT CALX, CALY FROM CALAMITEITEN WHERE CALID=1D
Geometry: Unknown (any)
OCI: Prepare(SELECT CALX, CALY FROM CALAMITEITEN WHERE CALID=1D)
Feature Count: 1
OCI: Prepare(SELECT CALX, CALY FROM CALAMITEITEN WHERE CALID=1D)
Layer SRS WKT:
(unknown)
CALX: Integer (0.0)
CALY: Integer (0.0)
OCI: Prepare(SELECT CALX, CALY FROM CALAMITEITEN WHERE CALID=1D)
OGRFeature(SELECT CALX, CALY FROM CALAMITEITEN WHERE CALID=1D):0
  CALX (Integer) = 33219
  CALY (Integer) = 669433

OCI: 3 features read on layer 'SELECT CALX, CALY FROM CALAMITEITEN WHERE CALID=1D'.
OGR: GetLayerCount() = 0

My first thought is, perhaps the zero precision in definition of Integer (0.0) is significant here, and the value is just queried as a kind of "empty" value, but not null.

06/20/07 23:07:29 changed by warmerdam

  • keywords set to oracle oci.
  • milestone changed from 1.4.2 to 1.4.3.

Lets put this off for 1.4.3, and try to do a fuller review of oracle spatial support in one big pass.

09/14/07 09:56:05 changed by warmerdam

  • milestone changed from 1.4.3 to 1.5.0.

Moving to 1.5 to lighten 1.4.3 load.

12/04/07 12:59:26 changed by warmerdam

  • milestone changed from 1.5.0 to 1.5.1.

OCI issues deferred to 1.5.1.

03/04/08 16:19:58 changed by warmerdam

  • milestone changed from 1.5.1 to 1.5.2.

07/09/08 15:53:33 changed by mloskot

  • status changed from assigned to closed.
  • resolution set to fixed.

After a long break, I got back to this issue and I tried to reproduce it. And, I've revealed that I can not reproduce it. In other words, using the same dataset from the attached ogr.tgz file and the same two ogrinfo commands, I've observed that OCI driver works, means in both cases CALX and CALY coordinates are correctly retrieved.

Using GDAL SVN + Oracle 10g Express Edition on Ubuntu 7.04, here is the proof:

  • SELECT * FROM... 
    $ ogrinfo OCI:system/pantera@localhost -sql 'SELECT * FROM CALAMITEITEN WHERE CALID=1'
OCI: Userid=system, Password=pantera, Database=localhost
OCI: Prepare(ALTER SESSION SET NLS_DATE_FORMAT='YYYY/MM/DD'         NLS_TIME_FORMAT='HH24:MI:SS' NLS_TIME_TZ_FORMAT='HH24:MI:SS TZHTZM'         NLS_TIMESTAMP_FORMAT='YYYY/MM/DD HH24:MI:SS'         NLS_TIMESTAMP_TZ_FORMAT='YYYY/MM/DD HH24:MI:SS TZHTZM')
OCI: Prepare(SELECT TABLE_NAME, OWNER FROM ALL_SDO_GEOM_METADATA)
OCI: Prepare(SELECT SRID FROM ALL_SDO_GEOM_METADATA WHERE TABLE_NAME = 'TEST_POINTS' AND COLUMN_NAME = 'ORA_GEOMETRY')
OGR: OGROpen(OCI:system/pantera@localhost/0x804d468) succeeded as OCI.
INFO: Open of `OCI:system/pantera@localhost'
      using driver `OCI' successful.
OCI: ExecuteSQL(SELECT * FROM CALAMITEITEN WHERE CALID=1)
OCI: Prepare(SELECT * FROM CALAMITEITEN WHERE CALID=1)

Layer name: SELECT * FROM CALAMITEITEN WHERE CALID=1
Geometry: Unknown (any)
OCI: Prepare(SELECT * FROM CALAMITEITEN WHERE CALID=1)
Feature Count: 1
OCI: Prepare(SELECT * FROM CALAMITEITEN WHERE CALID=1)
Layer SRS WKT:
(unknown)
CALID: Integer (0.0)
CALTITEL: String (255.0)
CALDATE: Date (0.0)
CALDATETYPE: String (20.0)
CALX: Integer (0.0)
CALY: Integer (0.0)
CALNAAMCOORD: String (255.0)
CALAARD: String (255.0)
CALCODE: String (100.0)
ACTUEEL: Integer (0.0)
OCI: Prepare(SELECT * FROM CALAMITEITEN WHERE CALID=1)
OGRFeature(SELECT * FROM CALAMITEITEN WHERE CALID=1):0
  CALID (Integer) = 1
  CALTITEL (String) =  
  CALDATE (Date) = (null)
  CALDATETYPE (String) =  
  CALX (Integer) = 33219
  CALY (Integer) = 669433
  CALNAAMCOORD (String) =  
  CALAARD (String) = 0
  CALCODE (String) =  
  ACTUEEL (Integer) = 0

OCI: 3 features read on layer 'SELECT * FROM CALAMITEITEN WHERE CALID=1'.
OGR: GetLayerCount() = 1
  • SELECT ALX, CALY FROM... 
r$ ogrinfo OCI:system/pantera@localhost -sql 'SELECT CALX, CALY FROM CALAMITEITEN WHERE CALID=1' 
OCI: Userid=system, Password=pantera, Database=localhost
OCI: Prepare(ALTER SESSION SET NLS_DATE_FORMAT='YYYY/MM/DD'         NLS_TIME_FORMAT='HH24:MI:SS' NLS_TIME_TZ_FORMAT='HH24:MI:SS TZHTZM'         NLS_TIMESTAMP_FORMAT='YYYY/MM/DD HH24:MI:SS'         NLS_TIMESTAMP_TZ_FORMAT='YYYY/MM/DD HH24:MI:SS TZHTZM')
OCI: Prepare(SELECT TABLE_NAME, OWNER FROM ALL_SDO_GEOM_METADATA)
OCI: Prepare(SELECT SRID FROM ALL_SDO_GEOM_METADATA WHERE TABLE_NAME = 'TEST_POINTS' AND COLUMN_NAME = 'ORA_GEOMETRY')
OGR: OGROpen(OCI:system/pantera@localhost/0x804d468) succeeded as OCI.
INFO: Open of `OCI:system/pantera@localhost'
      using driver `OCI' successful.
OCI: ExecuteSQL(SELECT CALX, CALY FROM CALAMITEITEN WHERE CALID=1)
OCI: Prepare(SELECT CALX, CALY FROM CALAMITEITEN WHERE CALID=1)

Layer name: SELECT CALX, CALY FROM CALAMITEITEN WHERE CALID=1
Geometry: Unknown (any)
OCI: Prepare(SELECT CALX, CALY FROM CALAMITEITEN WHERE CALID=1)
Feature Count: 1
OCI: Prepare(SELECT CALX, CALY FROM CALAMITEITEN WHERE CALID=1)
Layer SRS WKT:
(unknown)
CALX: Integer (0.0)
CALY: Integer (0.0)
OCI: Prepare(SELECT CALX, CALY FROM CALAMITEITEN WHERE CALID=1)
OGRFeature(SELECT CALX, CALY FROM CALAMITEITEN WHERE CALID=1):0
  CALX (Integer) = 33219
  CALY (Integer) = 669433

OCI: 3 features read on layer 'SELECT CALX, CALY FROM CALAMITEITEN WHERE CALID=1'.
OGR: GetLayerCount() = 1

I may be wrong, but probably it's got fixed with recent submissions to the OCI driver made by Tamas.

Bart, I'm closing this ticket as fixed. But, if it's still not working for you, please reopen with additional comments.

07/10/08 11:19:55 changed by mloskot

I've also tested OCI driver (GDAL SVN) and confirmed it works well remotely connecting from Windows XP (a client with OracleXEClient.exe software installed) to Oracle 10g installed on Ubuntu 7.04:

  • SELECT * FROM 
    D:\dev\gdal\_svn\trunk\gdal>apps\ogrinfo OCI:system/pantera@10.211.55.7 -sql "SELECT * FROM CALAMITEITEN WHERE CALID=1"
INFO: Open of `OCI:system/pantera@10.211.55.7'
      using driver `OCI' successful.

Layer name: SELECT * FROM CALAMITEITEN WHERE CALID=1
Geometry: Unknown (any)
Feature Count: 1
Layer SRS WKT:
(unknown)
CALID: Integer (0.0)
CALTITEL: String (255.0)
CALDATE: Date (0.0)
CALDATETYPE: String (20.0)
CALX: Integer (0.0)
CALY: Integer (0.0)
CALNAAMCOORD: String (255.0)
CALAARD: String (255.0)
CALCODE: String (100.0)
ACTUEEL: Integer (0.0)
OGRFeature(SELECT * FROM CALAMITEITEN WHERE CALID=1):0
  CALID (Integer) = 1
  CALTITEL (String) =
  CALDATE (Date) = (null)
  CALDATETYPE (String) =
  CALX (Integer) = 33219
  CALY (Integer) = 669433
  CALNAAMCOORD (String) =
  CALAARD (String) = 0
  CALCODE (String) =
  ACTUEEL (Integer) = 0
  • SELECT CALX, CALY FROM 
    D:\dev\gdal\_svn\trunk\gdal>apps\ogrinfo OCI:system/pantera@10.211.55.7 -sql "SELECT CALX,CALY FROM CALAMITEITEN WHERE CALID=1"
INFO: Open of `OCI:system/pantera@10.211.55.7'
      using driver `OCI' successful.

Layer name: SELECT CALX,CALY FROM CALAMITEITEN WHERE CALID=1
Geometry: Unknown (any)
Feature Count: 1
Layer SRS WKT:
(unknown)
CALX: Integer (0.0)
CALY: Integer (0.0)
OGRFeature(SELECT CALX,CALY FROM CALAMITEITEN WHERE CALID=1):0
  CALX (Integer) = 33219
  CALY (Integer) = 669433

Unfortunately, I'm unable to test OCI with Oracle 9 and 8.