Quantcast

[gdal-dev] Primary keys are ignored in ogr2ogr when converting from Sqlite databases

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

[gdal-dev] Primary keys are ignored in ogr2ogr when converting from Sqlite databases

César Martínez
Hi, I am using ogr2ogr to export data from Spatialite to other formats
(such as PostGIS or a new Spatialite db).

The layers have an integer primary key, but this field is ignored in
the output table, as an OGC_FID field is created which uses
consecutive numbers, ignoring the real values of the primary key in
the original field.

This is quite inconvenient, as the layer is quite useless after loosing the PK.

I'll provide one example to make it clearer.

- Input data:

CREATE TABLE test
(id INTEGER NOT NULL PRIMARY KEY,
name TEXT NOT NULL);
SELECT AddGeometryColumn('test', 'the_geom', 4326, 'POINT', 'XY');

INSERT INTO 'test' (id, name, the_geom) VALUES (1, "row1",
GeomFromText('POINT(1.01 2.02)', 4326));
INSERT INTO 'test' (id, name, the_geom) VALUES (3, "row3",
GeomFromText('POINT(3.03 4.04)', 4326));

Now we export the table using ogr2ogr:
ogr2ogr -f "SQLite" db2.sqlite  db.sqlite test -dsco SPATIALITE=YES

Now compare both layers:
$ ogrinfo -q db.sqlite test

Layer name: test
OGRFeature(test):1
  name (String) = row1
  POINT (1.01 2.02)

OGRFeature(test):3
  name (String) = row3
  POINT (3.03 4.04)

$ ogrinfo -q db2.sqlite test

Layer name: test
OGRFeature(test):1
  name (String) = row1
  POINT (1.01 2.02)

OGRFeature(test):2
  name (String) = row3
  POINT (3.03 4.04)

Is this a bug or is an intended behaviour? Is there any way to workaround it?
I am using GDAL version 1.11.3. It also happens when exporting to a
different format such as PostGIS.

Greetings,

César Martinez


--
SCOLAB
http://scolab.es
_______________________________________________
gdal-dev mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/gdal-dev
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Primary keys are ignored in ogr2ogr when converting from Sqlite databases

Bo Victor Thomsen

Have you tried to use the -preserve_fid qualifier ??

ex.
ogr2ogr -preserve_fid -f "SQLite" db2.sqlite  db.sqlite test -dsco SPATIALITE=YES

Regards
Bo Victor Thomsen
AestasGIS
Den 09-12-2016 kl. 00:21 skrev César Martínez:
Hi, I am using ogr2ogr to export data from Spatialite to other formats
(such as PostGIS or a new Spatialite db).

The layers have an integer primary key, but this field is ignored in
the output table, as an OGC_FID field is created which uses
consecutive numbers, ignoring the real values of the primary key in
the original field.

This is quite inconvenient, as the layer is quite useless after loosing the PK.

I'll provide one example to make it clearer.

- Input data:

CREATE TABLE test
(id INTEGER NOT NULL PRIMARY KEY,
name TEXT NOT NULL);
SELECT AddGeometryColumn('test', 'the_geom', 4326, 'POINT', 'XY');

INSERT INTO 'test' (id, name, the_geom) VALUES (1, "row1",
GeomFromText('POINT(1.01 2.02)', 4326));
INSERT INTO 'test' (id, name, the_geom) VALUES (3, "row3",
GeomFromText('POINT(3.03 4.04)', 4326));

Now we export the table using ogr2ogr:
ogr2ogr -f "SQLite" db2.sqlite  db.sqlite test -dsco SPATIALITE=YES

Now compare both layers:
$ ogrinfo -q db.sqlite test

Layer name: test
OGRFeature(test):1
  name (String) = row1
  POINT (1.01 2.02)

OGRFeature(test):3
  name (String) = row3
  POINT (3.03 4.04)

$ ogrinfo -q db2.sqlite test

Layer name: test
OGRFeature(test):1
  name (String) = row1
  POINT (1.01 2.02)

OGRFeature(test):2
  name (String) = row3
  POINT (3.03 4.04)

Is this a bug or is an intended behaviour? Is there any way to workaround it?
I am using GDAL version 1.11.3. It also happens when exporting to a
different format such as PostGIS.

Greetings,

César Martinez




_______________________________________________
gdal-dev mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/gdal-dev
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: [gdal-dev] Primary keys are ignored in ogr2ogr when converting from Sqlite databases

jratike80
In reply to this post by César Martínez
César Martínez wrote
Is this a bug or is an intended behaviour? Is there any way to workaround it?
I am using GDAL version 1.11.3. It also happens when exporting to a
different format such as PostGIS.
A workaround is to pick the primary key as a normal attribute and let GDAL to create its own primary key. You can add a unique constraint to ID afterwards https://www.techonthenet.com/sqlite/unique.php and then it is almost like a PK for you

ogr2ogr -f "SQLite" db2.sqlite  db.sqlite -dsco SPATIALITE=YES -sql "select id, name, the_geom from test" -nln test

Easier way for preventing duplicate values would be to create an unique index on ID.

-Jukka Rahkonen-
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Primary keys are ignored in ogr2ogr when converting from Sqlite databases

César Martínez
In reply to this post by Bo Victor Thomsen
Thank you, I was not aware of the -preserve_fid qualifier, it works as
expected when I use it.
Sorry about the noise.

César Martínez

2016-12-09 9:36 GMT+01:00 Bo Victor Thomsen <[hidden email]>:

> Have you tried to use the -preserve_fid qualifier ??
>
> ex.
>
> ogr2ogr -preserve_fid -f "SQLite" db2.sqlite  db.sqlite test -dsco
> SPATIALITE=YES
>
> Regards
> Bo Victor Thomsen
> AestasGIS
>
> Den 09-12-2016 kl. 00:21 skrev César Martínez:
>
> Hi, I am using ogr2ogr to export data from Spatialite to other formats
> (such as PostGIS or a new Spatialite db).
>
> The layers have an integer primary key, but this field is ignored in
> the output table, as an OGC_FID field is created which uses
> consecutive numbers, ignoring the real values of the primary key in
> the original field.
>
> This is quite inconvenient, as the layer is quite useless after loosing the
> PK.
>
> I'll provide one example to make it clearer.
>
> - Input data:
>
> CREATE TABLE test
> (id INTEGER NOT NULL PRIMARY KEY,
> name TEXT NOT NULL);
> SELECT AddGeometryColumn('test', 'the_geom', 4326, 'POINT', 'XY');
>
> INSERT INTO 'test' (id, name, the_geom) VALUES (1, "row1",
> GeomFromText('POINT(1.01 2.02)', 4326));
> INSERT INTO 'test' (id, name, the_geom) VALUES (3, "row3",
> GeomFromText('POINT(3.03 4.04)', 4326));
>
> Now we export the table using ogr2ogr:
> ogr2ogr -f "SQLite" db2.sqlite  db.sqlite test -dsco SPATIALITE=YES
>
> Now compare both layers:
> $ ogrinfo -q db.sqlite test
>
> Layer name: test
> OGRFeature(test):1
>   name (String) = row1
>   POINT (1.01 2.02)
>
> OGRFeature(test):3
>   name (String) = row3
>   POINT (3.03 4.04)
>
> $ ogrinfo -q db2.sqlite test
>
> Layer name: test
> OGRFeature(test):1
>   name (String) = row1
>   POINT (1.01 2.02)
>
> OGRFeature(test):2
>   name (String) = row3
>   POINT (3.03 4.04)
>
> Is this a bug or is an intended behaviour? Is there any way to workaround
> it?
> I am using GDAL version 1.11.3. It also happens when exporting to a
> different format such as PostGIS.
>
> Greetings,
>
> César Martinez
>
>
>
>
> _______________________________________________
> gdal-dev mailing list
> [hidden email]
> http://lists.osgeo.org/mailman/listinfo/gdal-dev



--
SCOLAB
http://scolab.es
_______________________________________________
gdal-dev mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/gdal-dev
Loading...