Opened 17 years ago

Closed 17 years ago

#1321 closed defect (fixed)

[OGR-ODBC] : DECIMAL field not recognised

Reported by: assefa@… Owned by: Mateusz Łoskot
Priority: normal Milestone: 1.4.2
Component: OGR_SF Version: unspecified
Severity: normal Keywords:
Cc: warmerdam

Description (last modified by warmerdam)

e-mail conversation

orifinal question :

I am trying to access an ODBC database using the ogr virtual layer access (using shp2img). The layer seems to be defined properly as far as I know. It looks something like :

...
CONNECTION "<OGRVRTDataSource>
  <OGRVRTLayer name='AL05AK0001'>
  <SrcDataSource>ODBC:@sos_test</SrcDataSource>
  <SrcSQL>SELECT * FROM t_ResEau_Station_Data WHERE Site_Number = 'AL05AK0001'</SrcSQL>
  <GeometryType>wkbPoint</GeometryType>
  <GeometryType>wkbPoint</GeometryType>
  <LayerSRS>WGS84</LayerSRS>
  <GeometryField encoding='PointFromColumns' x='Longitude' y='Latitude'/>
  </OGRVRTLayer>
  </OGRVRTDataSource>"

The error message is the following :

msOGRFileNextShape(): OGR error. Attempt to use STRING field `Longitude' with numeric comparison `>'. <br>

If I just use the SrcLayer instead of the SrcSQL, It seems to work ok :

<SrcDataSource>ODBC:@sos_test</SrcDataSource>
 <SrcLayer>t_ResEau_Station_Data</SrcLayer>
  <GeometryType>wkbPoint</GeometryType>
  <LayerSRS>WGS84</LayerSRS>
  <GeometryField encoding='PointFromColumns' x='Longitude' y='Latitude'/>

Frank's last e-mail

if I do an ogrinfo the Latitude/Longitude is reported as being String

  • if I do access the db using php and echo the field types, I get Latitude DECIMAL and Longitude DECIMAL

Assefa,

Ah. I suspect that the ODBC driver is not recognising DECIMAL as a numeric field type, and so it falls back to treating it as a String. And now that I think about it, the extra spatial constraint is being interpreted within the OGR code for SrcSQL while for a conventional table they are passed through to the actual ODBC driver which understands DECIMAL properly.

The proper solution likely is fixing the OGR ODBC driver to understand the decimal field type. If you could file a bug against OGR listing the database you are using and the SQL declaration for the field in that database I can try and get this fixed sooner or later.

Change History (6)

comment:2 by warmerdam, 17 years ago

Cc: warmerdam added
Description: modified (diff)
Milestone: 1.4.1
Owner: changed from warmerdam to Mateusz Łoskot
Priority: highnormal

Mateusz,

Could you look into this, and if there is a fix try to get it into 1.4 branch?

comment:3 by Mateusz Łoskot, 17 years ago

Status: newassigned

According to my understanding of the description, the problem with reading DECIMAL value occurs only when using SQL-based layer. But when a table layer is queried, DECIMAL values are mapped correctly:

(...)while for a conventional table they are passed through to
the actual ODBC driver which understands DECIMAL properly.

I tried to reproduce this issue using ogrinfo on Windows with PostgreSQL database accessed through ODBC, but unfortunately I can not reproduce the problem (please, check my tests below):

Here is definition of layer I used to read NUMERIC/DECIMAL values:

CREATE TABLE point3d
(
  ogc_fid serial NOT NULL,
  wkb_geometry geometry,
  latitude numeric(18,10),
  longitude numeric(18,10),
  altitude numeric(15,3)
}

1. Reading table layer

D:\dev\gdal\_svn\trunk\gdal>ogrinfo ODBC:pgsql_test point3d|more
INFO: Open of `ODBC:pgsql_test'
      using driver `ODBC' successful.

Layer name: point3d
Geometry: Unknown (any)
Feature Count: 6085
Layer SRS WKT:
(unknown)
ogc_fid: Integer (10.0)
wkb_geometry: String (254.0)
latitude: Real (18.10)
longitude: Real (18.10)
altitude: Real (15.3)
OGRFeature(point3d):1
  ogc_fid (Integer) = 1
  wkb_geometry (String) = 01010000A0FF7F00008883715D1E581F40CB845FEAE7CF49403333333333236140
  latitude (Real) =      51.6242650000
  longitude (Real) =       7.8360533333
  altitude (Real) =         137.100

2. Reading SQL layer - with fields specification

D:\dev\gdal\_svn\trunk\gdal>ogrinfo ODBC:pgsql_test point3d -sql "SELECT ogc_fid,latitude,longitude,altitude FROM point3d WHERE ogc_fid=1
INFO: Open of `ODBC:pgsql_test'
      using driver `ODBC' successful.
layer names ignored in combination with -sql.

Layer name: SELECT
Geometry: Unknown (any)
Feature Count: 1
Layer SRS WKT:
(unknown)
ogc_fid: Integer (10.0)
latitude: Real (18.10)
longitude: Real (18.10)
altitude: Real (15.3)
OGRFeature(SELECT):0
  ogc_fid (Integer) = 1
  latitude (Real) =      51.6242650000
  longitude (Real) =       7.8360533333
  altitude (Real) =         137.100

3. Reading SQL layer - fields not specified

D:\dev\gdal\_svn\trunk\gdal>ogrinfo ODBC:pgsql_test point3d -sql "SELECT * FROM point3d WHERE ogc_fid=1"
INFO: Open of `ODBC:pgsql_test'
      using driver `ODBC' successful.
layer names ignored in combination with -sql.

Layer name: SELECT
Geometry: Unknown (any)
Feature Count: 1
Layer SRS WKT:
(unknown)
ogc_fid: Integer (10.0)
wkb_geometry: String (254.0)
latitude: Real (18.10)
longitude: Real (18.10)
altitude: Real (15.3)
OGRFeature(SELECT):0
  ogc_fid (Integer) = 1
  wkb_geometry (String) = 01010000A0FF7F00008883715D1E581F40CB845FEAE7CF49403333333333236140
  latitude (Real) =      51.6242650000
  longitude (Real) =       7.8360533333
  altitude (Real) =         137.100

Please, correct me if I've missed anything in my tests.

If my tests make sense and are equivalent to steps Assefa did take, then may be I use incorrect data, with different schema definition?

In the latter case, Assefa, would it be possible to get subset of your data?

comment:4 by warmerdam, 17 years ago

Milestone: 1.4.11.4.2

Punting this issue to 1.4.2 due to inability to reproduce a problem. If we don't get any supporting information within a week we might as well close it.

comment:5 by Mateusz Łoskot, 17 years ago

I've contacted Assefa directly. I'm waiting for his response if he is still interested in following up on it.

comment:6 by assefa, 17 years ago

Sorry I have not followed up this. I have tried to reproduce this problem right now but could not. It uses an MS Access table accessed through ODBC. I still have the same map file/data from a year ago and following the bug description, I am not getting any error. I guess the only difference that I see is that I am currently using gdal 1.4.0 and last year I think I was using gdal 1.3.2 (or 1.3.1). I guess we can close the bug. If you want I can make the data available (off the bug).

comment:7 by Mateusz Łoskot, 17 years ago

Resolution: fixed
Status: assignedclosed

Assefa,

Thanks for the comment.

I believe it's OK to close this ticket as fixed.

Note: See TracTickets for help on using tickets.