Hi, Even
thx for you full explanation . :)
It is really useful for me.
Andrea.
Il 21/01/2012 12:28, Even Rouault ha scritto:
> 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
>