[gdal-dev] sqlite -> spatialite (rowid)
Even Rouault
even.rouault at spatialys.com
Tue Mar 7 06:45:21 PST 2017
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 Sandro.
>
> 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
I agree. But my point is about an explicit column called ROWID and of type INTEGER
PRIMARY KEY. In the case, I think it's OK since both the implicit rowid and explicit rowid
columns happen to match.
I've led a small experiment by patching spatialite to accept creating a spatiali index even if a
table has a rowid column.
Initial state:
$ ogrinfo test.db -sql "select * from sqlite_master where name = 'test'"
INFO: Open of `test.db'
using driver `SQLite' successful.
Layer name: SELECT
Geometry: None
Feature Count: 1
Layer SRS WKT:
(unknown)
type: String (0.0)
name: String (0.0)
tbl_name: String (0.0)
rootpage: Integer (0.0)
sql: String (0.0)
OGRFeature(SELECT):0
type (String) = table
name (String) = test
tbl_name (String) = test
rootpage (Integer) = 5673
sql (String) = CREATE TABLE 'test' ( rowid INTEGER PRIMARY KEY, "GEOMETRY" GEOMETRY)
$ ogrinfo test.db -al
INFO: Open of `test.db'
using driver `SQLite' successful.
Layer name: test
Geometry: Unknown (any)
Feature Count: 3
Extent: (1.000000, 2.000000) - (5.000000, 6.000000)
Layer SRS WKT:
(unknown)
FID Column = rowid
Geometry Column = GEOMETRY
OGRFeature(test):1
POINT (1 2)
OGRFeature(test):3
POINT (3 4)
OGRFeature(test):5
POINT (5 6)
==> 3 records of feature id / row id 1, 3 and 5
$ ogrinfo test.db -sql "select * from idx_test_GEOMETRY"
INFO: Open of `test.db'
using driver `SQLite' successful.
Layer name: SELECT
Geometry: None
Feature Count: 3
Layer SRS WKT:
(unknown)
pkid: Integer (0.0)
xmin: Real (0.0)
xmax: Real (0.0)
ymin: Real (0.0)
ymax: Real (0.0)
OGRFeature(SELECT):0
pkid (Integer) = 1
xmin (Real) = 1
xmax (Real) = 1
ymin (Real) = 2
ymax (Real) = 2
OGRFeature(SELECT):1
pkid (Integer) = 3
xmin (Real) = 3
xmax (Real) = 3
ymin (Real) = 4
ymax (Real) = 4
OGRFeature(SELECT):2
pkid (Integer) = 5
xmin (Real) = 5
xmax (Real) = 5
ymin (Real) = 6
ymax (Real) = 6
Now I delete feature with rowid=3:
$ ogrinfo test.db -sql "delete from test where rowid = 3"
INFO: Open of `test.db'
using driver `SQLite' successful.
Let's the state:
$ ogrinfo test.db -al
INFO: Open of `test.db'
using driver `SQLite' successful.
Layer name: test
Geometry: Unknown (any)
Feature Count: 2
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/gdal-dev/attachments/20170307/54106c64/attachment-0001.html>
More information about the gdal-dev
mailing list