#1172 closed defect (fixed)
GDAL OVF OCI does not work with SrcSQL
Reported by: | Owned by: | Mateusz Łoskot | |
---|---|---|---|
Priority: | normal | Milestone: | 1.5.3 |
Component: | OGR_SF | Version: | unspecified |
Severity: | normal | Keywords: | oracle oci |
Cc: | warmerdam |
Description (last modified by )
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 (1)
Change History (10)
by , 18 years ago
comment:2 by , 17 years ago
Cc: | added |
---|---|
Description: | modified (diff) |
Milestone: | → 1.4.2 |
Priority: | high → normal |
comment:3 by , 17 years ago
Status: | new → 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.
comment:4 by , 17 years ago
Keywords: | oracle oci added |
---|---|
Milestone: | 1.4.2 → 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.
comment:7 by , 16 years ago
Milestone: | 1.5.1 → 1.5.2 |
---|
comment:8 by , 16 years ago
Resolution: | → fixed |
---|---|
Status: | assigned → closed |
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.
comment:9 by , 16 years ago
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.
tgz file with Oracle SQL scripts and shell scripts