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
_______________________________________________
gdal-dev mailing list
gdal-dev@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/gdal-dev
