Can't get the correct FID from SQLITE and Spatialite DB

[MCA4213]

I tested OGR with an SPatialite DB with :

-my DB has 250 feature;

-my SQL statement is : "select * from Feature where ogc_fid>240".

But the OGR_F_GetFID is returning an FID that start from 0 to 9 and not my real FID, this cause me a problem in osgEarth selection (this work for me with PostGis);

I find also that :

-OGR_L_GetFIDColumn(layer) is returning an empty name, so OGR is not able to get my primary key even with no SQL expression.

-OGR is overwriting my ogc_fid in the results layer, I tried with other name (for the pk) but the returned FID is still wrong.

for my configuration, I download my GDAL 1.11 and SQLITE 3.7 and SPATILITE 4.1.1 from OSGeo4W (I work with visual C++ 10). Any help please?

[Jukka Rahkonen]

Hi,

If you are not sure that you have found a real bug it would be better to write mail to gdal-dev mailing list instead of creating tickets right away. There are more audience and thinking about your problem will help other GDAL users as well.

I made a test database as

ogr2ogr -f sqlite -dsco spatialite=yes test.sqlite ne_10m_populated_places.shp

OGC_FID has some special meaning for GDAL so you must select it by using an alias:

ogrinfo test.sqlite -sql "select ogc_fid as id from ne_10m_populated_places where ogc_fid>100"
INFO: Open of `test.sqlite'
      using driver `SQLite' successful.

Layer name: SELECT
Geometry: None
Feature Count: 7243
Layer SRS WKT:
(unknown)
id: Integer (0.0)
OGRFeature(SELECT):0
  id (Integer) = 101

OGRFeature(SELECT):1
  id (Integer) = 102

OGRFeature(SELECT):2
  id (Integer) = 103

[MCA4213]

Hi, first thank you for the answer;

I am sure that is a bug because like I said the osgEarth use the OGR_F_GetFID and it works for PostGis and not for SPatialite.

What you did in your example is to read a Field and not get an FID, reading a field is working for me and to get my ogc_fid I used like you said an alias for that.

The problem is getting the FICCOLUMN and the FID. I tested with the code below:

OGRRegisterAll();
OGRDataSourceH           dsHandle;
OGRDataSourceH poDS;
OGRSFDriverH drv=OGRGetDriverByName( "SQLITE");
poDS=OGR_Dr_Open(drv,"semi.sqlite",false);
if( poDS == NULL )
{
   printf( "Open failed.\n" );
   exit( 1 );
}
OGRLayerH in = OGR_DS_GetLayerByName(poDS, "Feature" );
cout<<"fid name "<<OGR_L_GetFIDColumn(in)<<endl;
string from = OGR_FD_GetName( OGR_L_GetLayerDefn( in )); 
string delim = "\"";
from = delim + from + delim;  
poLayer=OGR_DS_ExecuteSQL( poDS,("select ogc_fid AS id  from "+from+" where ogc_fid>240").c_str(), 0L, "SQLITE" );
OGR_L_ResetReading(poLayer);
while(OGRFeatureH handle = OGR_L_GetNextFeature( poLayer ) )
     cout<<" fid= "<<OGR_F_GetFID(handle)<<endl;

but the results is : empty FidColumn fid= 0 to 9

So OGR is not able to get the FID.

[Even Rouault]

Could likely be improved. Would require a code enhancement and the request to explicetely list _rowid_ as a field, like "select _rowid_, * from Feature where ogc_fid>240"

[MCA4213]

Hi, I added _rowid_ in my SQL statement but nothing is changed, I still have an FID from O to 9;

I notice that OGR_L_GetFIDColumn on the poLayer is returning "OGC_FID" that is created by OGR, but not on my source Data base (I am sure that I have a primary key).

I think that if OGC can't get my FIDColumn name from my source data base so it will be impossible to get an FID.

Am I right???

[Even Rouault]

I wrote "Would require a code enhancement AND the request to explicetely list _rowid_ as a field"...

I'm confused by your latest comment. Could you show the full CREATE TABLE statement you've used if you didn't create the table through OGR CreateLayer() interface ?

And what is the output of 'ogrinfo your.sqlite -sql "pragma table_info(the_table_name)" ' ?

[MCA4213]

OK : for my table, I create it using Spatialite-gui interface:

CREATE TABLE Feature  (
   OGC_FID INTEGER PRIMARY KEY ,
   name  TEXT  ,
   path  TEXT   ,
   active  INTEGER DEFAULT (1)  ,
   driver  TEXT   ,
   coordinate_systeme  TEXT   ,
   image_size_x  Real DEFAULT (-1)  ,
   image_size_y  Real DEFAULT (-1)  ,
   pixel_size_x  Real DEFAULT (-1)  ,
   pixel_size_y  Real DEFAULT (-1)  ,
   metadata  TEXT   ,
   image_metadata  TEXT   ,
   rpc_metadata  TEXT   ,
   gcp_projection  TEXT   ,
   subdatasets  TEXT   ,
   geolocation  TEXT   ,
   proj4  TEXT   ,
   thumbnail  Bytea   ,
   satellite  text,
   date_image  timestamp    ,
   cloud_stat Real DEFAULT (-2)  ,
   cloud_pct  Real DEFAULT (-1)  
  , "Geometry" POLYGON)

and ogrinfo is returning:

C:\Users\MCA>ogrinfo D:\Travail\Testes\testeOGR_PG\testeOGR_PG\semi.sqlite -sql
"pragma table_info(Feature)"
INFO: Open of `D:\Travail\Testes\testeOGR_PG\testeOGR_PG\semi.sqlite'
      using driver `SQLite' successful.

Layer name: SELECT
Geometry: None
Feature Count: 23
Layer SRS WKT:
(unknown)
cid: Integer (0.0)
name: String (0.0)
type: String (0.0)
notnull: Integer (0.0)
dflt_value: String (0.0)
pk: Integer (0.0)
OGRFeature(SELECT):0
  cid (Integer) = 0
  name (String) = OGC_FID
  type (String) = INTEGER
  notnull (Integer) = 0
  dflt_value (String) = (null)
  pk (Integer) = 1

OGRFeature(SELECT):1
  cid (Integer) = 1
  name (String) = name
  type (String) = TEXT
  notnull (Integer) = 0
  dflt_value (String) = (null)
  pk (Integer) = 0

OGRFeature(SELECT):2
  cid (Integer) = 2
  name (String) = path
  type (String) = TEXT
  notnull (Integer) = 0
  dflt_value (String) = (null)
  pk (Integer) = 0

OGRFeature(SELECT):3
  cid (Integer) = 3
  name (String) = active
  type (String) = INTEGER
  notnull (Integer) = 0
  dflt_value (String) = 1
  pk (Integer) = 0

OGRFeature(SELECT):4
  cid (Integer) = 4
  name (String) = driver
  type (String) = TEXT
  notnull (Integer) = 0
  dflt_value (String) = (null)
  pk (Integer) = 0

OGRFeature(SELECT):5
  cid (Integer) = 5
  name (String) = coordinate_systeme
  type (String) = TEXT
  notnull (Integer) = 0
  dflt_value (String) = (null)
  pk (Integer) = 0

OGRFeature(SELECT):6
  cid (Integer) = 6
  name (String) = image_size_x
  type (String) = Real
  notnull (Integer) = 0
  dflt_value (String) = -1
  pk (Integer) = 0

OGRFeature(SELECT):7
  cid (Integer) = 7
  name (String) = image_size_y
  type (String) = Real
  notnull (Integer) = 0
  dflt_value (String) = -1
  pk (Integer) = 0

OGRFeature(SELECT):8
  cid (Integer) = 8
  name (String) = pixel_size_x
  type (String) = Real
  notnull (Integer) = 0
  dflt_value (String) = -1
  pk (Integer) = 0

OGRFeature(SELECT):9
  cid (Integer) = 9
  name (String) = pixel_size_y
  type (String) = Real
  notnull (Integer) = 0
  dflt_value (String) = -1
  pk (Integer) = 0

OGRFeature(SELECT):10
  cid (Integer) = 10
  name (String) = metadata
  type (String) = TEXT
  notnull (Integer) = 0
  dflt_value (String) = (null)
  pk (Integer) = 0

OGRFeature(SELECT):11
  cid (Integer) = 11
  name (String) = image_metadata
  type (String) = TEXT
  notnull (Integer) = 0
  dflt_value (String) = (null)
  pk (Integer) = 0

OGRFeature(SELECT):12
  cid (Integer) = 12
  name (String) = rpc_metadata
  type (String) = TEXT
  notnull (Integer) = 0
  dflt_value (String) = (null)
  pk (Integer) = 0

OGRFeature(SELECT):13
  cid (Integer) = 13
  name (String) = gcp_projection
  type (String) = TEXT
  notnull (Integer) = 0
  dflt_value (String) = (null)
  pk (Integer) = 0

OGRFeature(SELECT):14
  cid (Integer) = 14
  name (String) = subdatasets
  type (String) = TEXT
  notnull (Integer) = 0
  dflt_value (String) = (null)
  pk (Integer) = 0

OGRFeature(SELECT):15
  cid (Integer) = 15
  name (String) = geolocation
  type (String) = TEXT
  notnull (Integer) = 0
  dflt_value (String) = (null)
  pk (Integer) = 0

OGRFeature(SELECT):16
  cid (Integer) = 16
  name (String) = proj4
  type (String) = TEXT
  notnull (Integer) = 0
  dflt_value (String) = (null)
  pk (Integer) = 0

OGRFeature(SELECT):17
  cid (Integer) = 17
  name (String) = thumbnail
  type (String) = Bytea
  notnull (Integer) = 0
  dflt_value (String) = (null)
  pk (Integer) = 0

OGRFeature(SELECT):18
  cid (Integer) = 18
  name (String) = satellite
  type (String) = text
  notnull (Integer) = 0
  dflt_value (String) = (null)
  pk (Integer) = 0

OGRFeature(SELECT):19
  cid (Integer) = 19
  name (String) = date_image
  type (String) = timestamp
  notnull (Integer) = 0
  dflt_value (String) = (null)
  pk (Integer) = 0

OGRFeature(SELECT):20
  cid (Integer) = 20
  name (String) = cloud_stat
  type (String) = Real
  notnull (Integer) = 0
  dflt_value (String) = -2
  pk (Integer) = 0

OGRFeature(SELECT):21
  cid (Integer) = 21
  name (String) = cloud_pct
  type (String) = Real
  notnull (Integer) = 0
  dflt_value (String) = -1
  pk (Integer) = 0

OGRFeature(SELECT):22
  cid (Integer) = 22
  name (String) = Geometry
  type (String) = POLYGON
  notnull (Integer) = 0
  dflt_value (String) = (null)
  pk (Integer) = 0

[MCA4213]

HI, I am still testing the OGR_L_GetFIDColumn and OGR_F_GetFID with SPATIALITE and POSTGIS:

1) without SQL statement:
-POSTGIS : OGR_L_GetFIDColumn is returning the name of "ogc_fid"=my pk;
-SPATIALITE: OGR_L_GetFIDColumn is returning an empty string;

BUT when I call OGR_L_GetFeatureCount before OGR_L_GetFIDColumn, than the OGR_L_GetFIDColumn function is returning the name of my PK. it is very strange.?????

2) with SQL statement:
-POSTGIS : OGR_L_GetFIDColumn is returning the name of "ogc_fid"=my pk , and the returned FID is correct;
-SPATIALITE: OGR_L_GetFIDColumn is returning an empty string, and the returned FID start always from 0;

I can say that the resulting layer from the SQL statement can not get the FIDColumn, perhaps because you should call OGR_L_GetFeatureCount before you try to get the FidColumn, or to correct the OGR_L_GetFIDColumn function with SPATIALITE to works directly.

I hope that it will help to find the problem.

[Even Rouault]

trunk r28177, branches/1.11 r28178 "SQLite: make GetFIDColumn() work when run as first method call (#5781)"

For the rest, I'm not sure this is really a good objective to track (and my previous suggestion about adding _rowid_ for sqlite was probably a nonsense). Especially when doing joins. I've tested with PostgreSQL and the results are really weird.

Consider:

$ ogrinfo pg:dbname=autotest table1
INFO: Open of `pg:dbname=autotest'
      using driver `PostgreSQL' successful.

Layer name: table1
Geometry: None
Feature Count: 2
Layer SRS WKT:
(unknown)
FID Column = ogc_fid
id1: String (0.0)
id2: String (0.0)
OGRFeature(table1):1
  id1 (String) = 1
  id2 (String) = 1

OGRFeature(table1):2
  id1 (String) = 2
  id2 (String) = 2

$ ogrinfo pg:dbname=autotest table2
INFO: Open of `pg:dbname=autotest'
      using driver `PostgreSQL' successful.

Layer name: table2
Geometry: None
Feature Count: 4
Layer SRS WKT:
(unknown)
FID Column = ogc_fid
id2: String (0.0)
id3: String (0.0)
OGRFeature(table2):1
  id2 (String) = 1
  id3 (String) = 1

OGRFeature(table2):2
  id2 (String) = 1
  id3 (String) = 2

OGRFeature(table2):3
  id2 (String) = 2
  id3 (String) = 1

OGRFeature(table2):4
  id2 (String) = 2
  id3 (String) = 2

$ ogrinfo pg:dbname=autotest  -sql "select * from table1 t1 join table2 t2 on t1.id2 = t2.id2"
INFO: Open of `pg:dbname=autotest'
      using driver `PostgreSQL' successful.
Warning 1: More than one ogc_fid column was found in the result of the SQL request. Only last one will be used

Layer name: sql_statement
Geometry: None
Feature Count: 4
Layer SRS WKT:
(unknown)
FID Column = ogc_fid
id1: String (0.0)
id2: String (0.0)
id2: String (0.0)
id3: String (0.0)
OGRFeature(sql_statement):2
  id1 (String) = 1
  id2 (String) = 1
  id2 (String) = (null)
  id3 (String) = 2

OGRFeature(sql_statement):1
  id1 (String) = 1
  id2 (String) = 1
  id2 (String) = (null)
  id3 (String) = 1

OGRFeature(sql_statement):4
  id1 (String) = 2
  id2 (String) = 2
  id2 (String) = (null)
  id3 (String) = 2

OGRFeature(sql_statement):3
  id1 (String) = 2
  id2 (String) = 2
  id2 (String) = (null)
  id3 (String) = 1

$ ogrinfo pg:dbname=autotest  -sql "select t1.ogc_fid, t1.id1, t1.id2, t2.id3 from table1 t1 join table2 t2 on t1.id2 = t2.id2"
INFO: Open of `pg:dbname=autotest'
      using driver `PostgreSQL' successful.

Layer name: sql_statement
Geometry: None
Feature Count: 4
Layer SRS WKT:
(unknown)
FID Column = ogc_fid
id1: String (0.0)
id2: String (0.0)
id3: String (0.0)
OGRFeature(sql_statement):1
  id1 (String) = 1
  id2 (String) = 1
  id3 (String) = 2

OGRFeature(sql_statement):1
  id1 (String) = 1
  id2 (String) = 1
  id3 (String) = 1

OGRFeature(sql_statement):2
  id1 (String) = 2
  id2 (String) = 2
  id3 (String) = 2

OGRFeature(sql_statement):2
  id1 (String) = 2
  id2 (String) = 2
  id3 (String) = 1

That doesn't make much sense to me to have a concept of FID for SQL requests. It really only makes sense on "real" tables.

So improving this would require analyzing the SQL request to see that it just fetches from a single table. Not high in my own priority list.

[Even Rouault]

Unlikely that further action will happen. Closing