Re: [gdal-dev] sqlite -> spatialite (rowid)

2017-03-07 Thread Even Rouault
> > So wondering if that makes sense in the SQLite driver to switch to > > AUTOINCREMENT FYI, I've just switched to declaring as AUTOINCREMENT (no option) -- Spatialys - Geospatial professional services http://www.spatialys.com ___ gdal-dev mailing

Re: [gdal-dev] sqlite -> spatialite (rowid)

2017-03-07 Thread a . furieri
On Tue, 07 Mar 2017 20:41:26 +0100, Even Rouault wrote: I see that in the SQLite/Spatialite driver the primary key column is created just as INTEGER PRIMARY KEY, whereas in GeoPackage the standard requires that is INTEGER PRIMARY KEY AUTOINCREMENT. Hi Even, spatialite_gui and

Re: [gdal-dev] sqlite -> spatialite (rowid)

2017-03-07 Thread Even Rouault
; > Lähetetty: 7. maaliskuuta 2017 18:01 > Vastaanottaja: Rahkonen Jukka (MML) > Kopio: Even Rouault; gdal-dev@lists.osgeo.org > Aihe: Re: [gdal-dev] sqlite -> spatialite (rowid) > > On Tue, 7 Mar 2017 15:07:04 +, Rahkonen Jukka (MML) wrote: > > Hi, > > > > I have a

Re: [gdal-dev] sqlite -> spatialite (rowid)

2017-03-07 Thread Rahkonen Jukka (MML)
kuuta 2017 18:01 Vastaanottaja: Rahkonen Jukka (MML) Kopio: Even Rouault; gdal-dev@lists.osgeo.org Aihe: Re: [gdal-dev] sqlite -> spatialite (rowid) On Tue, 7 Mar 2017 15:07:04 +, Rahkonen Jukka (MML) wrote: > Hi, > > I have a slight feeling that there may be some trouble if attribute

Re: [gdal-dev] sqlite -> spatialite (rowid)

2017-03-07 Thread a . furieri
On Tue, 7 Mar 2017 15:07:04 +, Rahkonen Jukka (MML) wrote: Hi, I have a slight feeling that there may be some trouble if attribute "ROWID" is INTEGER PRIMARY KEY but it does not AUTOINCREMENT and users do lot of deletes and inserts. Maybe not if the triggers in r-tree tables are clever

Re: [gdal-dev] sqlite -> spatialite (rowid)

2017-03-07 Thread a . furieri
On Tue, 07 Mar 2017 13:41:48 +0100, Even Rouault wrote: I think that Spatialite's validateRowid() fuction should be patched to check if the rowid column is not of type INTEGER PRIMARY KEY, if so that's acceptable, instead of just checking for rowid. Hi Even, very good point. SQLite

Re: [gdal-dev] sqlite -> spatialite (rowid)

2017-03-07 Thread a . furieri
On Tue, 7 Mar 2017 07:21:55 -0700 (MST), jratike80 wrote: Let's see what Sandro says but I do not believe it is acceptable. The r-tree spatial index is returning rowids to be used as keys to real data and if the main table has an ordinary attribute named as ROWID you can guess what will

Re: [gdal-dev] sqlite -> spatialite (rowid)

2017-03-07 Thread Martin Landa
Hi Even, 2017-03-07 13:41 GMT+01:00 Even Rouault : [...] > FID Column = rowid <-- see right, this exactly my case. > Workaround : > $ ogr2ogr test2.db test.db -dsco spatialite=yes -f sqlite -lco fid=my_rowid Thanks, it works. Ma -- Martin Landa

Re: [gdal-dev] sqlite -> spatialite (rowid)

2017-03-07 Thread Rahkonen Jukka (MML)
e: Re: [gdal-dev] sqlite -> spatialite (rowid) Arg, it seems my email has been truncated in text version. Retrying... > Now I delete feature with rowid=3: > Let's see the state: $ ogrinfo test.db -al INFO: Open of `test.db' using driver `SQLite' successful. Layer name: tes

Re: [gdal-dev] sqlite -> spatialite (rowid)

2017-03-07 Thread Even Rouault
Arg, it seems my email has been truncated in text version. Retrying... > Now I delete feature with rowid=3: > Let's see the state: $ ogrinfo test.db -al INFO: Open of `test.db' using driver `SQLite' successful. Layer name: test Geometry: Unknown (any) Feature Count: 2 Extent: (1.00,

Re: [gdal-dev] sqlite -> spatialite (rowid)

2017-03-07 Thread Even Rouault
On mardi 7 mars 2017 07:21:55 CET jratike80 wrote: > Even Rouault-2 wrote > > > I think that Spatialite's validateRowid() fuction should be patched to > > check if the rowid > > column is not of type INTEGER PRIMARY KEY, if so that's acceptable, > > instead of just checking > > for rowid. CC'ing

Re: [gdal-dev] sqlite -> spatialite (rowid)

2017-03-07 Thread jratike80
s. I remember I had quite a headache because of that some years ago https://groups.google.com/forum/#!searchin/spatialite-users/rowid%7Csort:relevance/spatialite-users/QwRdCiWoVKw/sXqCNZ5wn34J. -Jukka Rahkonen- -- View this message in context: http://osgeo-org.1560.x6.nabble.com/gdal-dev-sq

Re: [gdal-dev] sqlite -> spatialite (rowid)

2017-03-07 Thread Even Rouault
Martin, This error is emitted by spatialite. See https://www.gaia-gis.it/fossil/libspatialite/wiki?name=Shadowed+ROWID+issues Did you check that the FID column name of your input table wasn't rowid ? I could reproduce by forging first such a DB $ ogrinfo test.db -al -so INFO: Open of

[gdal-dev] sqlite -> spatialite (rowid)

2017-03-07 Thread Martin Landa
Hi, recently I discovered strange behaviour when converting SQLite database to Spatialite: ogr2ogr -f SQLite -dsco SPATIALITE=YES Export_vse.sdb Export_vse.db ... CreateSpatialIndex() error: a physical column named ROWID shadows the real ROWID The output table contains rowid column even input