[gdal-dev] OGR2OGR seem lost the field with primary key . ex [OGR2OGR seem last ...]

[Even Rouault]

Andrea,

Several points :
1) in the OGR data model, there is the concept of feature ID. According to 
http://gdal.org/ogr/ogr_arch.html :
"""The feature id (FID) of a feature is intended to be a unique identifier for 
the feature within the layer it is a member of. Freestanding features, or 
features not yet written to a layer may have a null (OGRNullFID) feature id. 
The feature ids are modelled in OGR as a long integer; however, this is not 
sufficiently expressive to model the natural feature ids in some formats. For 
instance, the GML feature id is a string, and the row id in Oracle is larger 
than 4 bytes."""

In the case of the PG driver, 
* when examining a OGR layer resulting from a PG table (which is the case when 
you only pass the name of the PG datasource to ogrinfo/ogr2ogr, or pass the 
name of one or several of its layers directly, i.e *without* a -sql 
statement), it will query the PG system tables to look for a primary key 
column of type int2, int4 or serial for the table. If found, this will be 
considered as the FID column. If it's of another type (like varchar), the 
column will be seen as a regular column.
* when examining a  OGRlayer resulting from the result of a -sql request, it 
will only identify a FID column if the column is named "ogc_fid".

All the above explains what you observe on the below samples :

* table1 has a integer primary key.

If you try :

ogrinfo PG:dbname=xxxx testing.table1

you'll see : FID Column = id  and the id column will not be listed as a 
regular field. The values of the FID are given at the end of each of the lines 
beginning by OGRFeature(testing.table1), like OGRFeature(testing.table1):1

If you try :

ogrinfo PG:dbname=xxxx -sql "select * from testing.table1"

you won't see a FID Column line, but the id column will be listed as a regular 
field (its name is not ogc_fid). In the case, OGR will create a fake FID, and 
you'll see OGRFeature(sql_statement):0

* table2 has a varchar primary key. In both cases (directly providing 
testing.table2 or via a -sql statement), this will be considered as a regular 
field by OGR.

When translating to shapefiles, FIDs cannot be preserved directly (the FID of a 
shapefile is a sequential number). You must transform the FID column as a 
regular column with a -sql statement.

But when translating to sqlite DBs, they can be preserved with the -
preserve_fid option of ogr2ogr.

For example :

1) Create a table with a feature whose primary key is 10

drop schema if exists testing cascade;
create schema testing;
create table testing.table1 (id integer primary key, field_2 varchar(10), 
field_3 integer, geometry geometry(Point,4326));
insert into testing.table1 (id, field_2, field_3,geometry) values(10,'aaa',3, 
ST_GeomFromText('POINT(1 1)',4326));

2) Check with ogrinfo that the FID is correctly identified :

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

Layer name: testing.table1
Geometry: Point
Feature Count: 1
Extent: (1.000000, 1.000000) - (1.000000, 1.000000)
Layer SRS WKT:
GEOGCS["WGS 84",
    DATUM["WGS_1984",
        SPHEROID["WGS 84",6378137,298.257223563,
            AUTHORITY["EPSG","7030"]],
        AUTHORITY["EPSG","6326"]],
    PRIMEM["Greenwich",0,
        AUTHORITY["EPSG","8901"]],
    UNIT["degree",0.01745329251994328,
        AUTHORITY["EPSG","9122"]],
    AUTHORITY["EPSG","4326"]]
FID Column = id
Geometry Column = geometry
field_2: String (10.0)
field_3: Integer (0.0)
OGRFeature(testing.table1):10
  field_2 (String) = aaa
  field_3 (Integer) = 3
  POINT (1 1)

3) Translate to SQLite db with -preserve_fid option :

$ ogr2ogr table1.sqlite pg:dbname=autotest-postgis2.0 testing.table1 -f sqlite 
-preserve_fid -nln table1 -dsco SPATIALITE=YES

4) Check the DB with ogrinfo :

INFO: Open of `table1.sqlite'
      using driver `SQLite' successful.

Layer name: table1
Geometry: Point
Feature Count: 1
Extent: (1.000000, 1.000000) - (1.000000, 1.000000)
Layer SRS WKT:
GEOGCS["WGS 84",
    DATUM["WGS_1984",
        SPHEROID["WGS 84",6378137,298.257223563,
            AUTHORITY["EPSG","7030"]],
        AUTHORITY["EPSG","6326"]],
    PRIMEM["Greenwich",0,
        AUTHORITY["EPSG","8901"]],
    UNIT["degree",0.0174532925199433,
        AUTHORITY["EPSG","9122"]],
    AUTHORITY["EPSG","4326"]]
FID Column = OGC_FID
Geometry Column = GEOMETRY
field_2: String (0.0)
field_3: Integer (0.0)
OGRFeature(table1):10
  field_2 (String) = aaa
  field_3 (Integer) = 3
  POINT (1 1)

5) You can retrieve it as a regular field with :
$ ogrinfo table1.sqlite -sql "select *, ogc_fid as id from table1"
INFO: Open of `table1.sqlite'
      using driver `SQLite' successful.

Layer name: SELECT
Geometry: Unknown (any)
Feature Count: 1
Extent: (1.000000, 1.000000) - (1.000000, 1.000000)
Layer SRS WKT:
(unknown)
Geometry Column = GEOMETRY
field_2: String (0.0)
field_3: Integer (0.0)
id: Integer (0.0)
OGRFeature(SELECT):0
  field_2 (String) = aaa
  field_3 (Integer) = 3
  id (Integer) = 10
  POINT (1 1)



Best regards,

Even