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

[a.furieri at lqt.it]

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
> happen. System works fine if you do not utilize spatial index. If you 
> use it
> you use you will get crazy results. 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.
>

Hi Jukka,

the SpatiaLite's spatial index is fully based on SQLite's
R*Tree; and an R*Tree isn't at all "an index", it simply
is a distinct VirtualTable.
SQLite's itself has absolutely no idea about the strict
relationship joining the Spatial Index and the indexed
table; and symmetrically SpatiaLite itself is completely
unaware of the actions performed by SQLite.

so we must walk on a very narrow and slippery path in
order to keep properly aligned both the SpatialIndex
and the indexed table:

1. all INSERT, UPDATE and DELETE operations on behalf
    of the indexed table _MUST_ be intercepted by
    appropriate Triggers so to properly synchronize
    the companion R*Tree table.

2. corresponding rows on both tables _MUST_
    declare a common key value allowing to perform
    relational JOINs in the safest way.

the R*Tree exclusively supports an INTEGER (64 bit)
key value uniquely identifying each row.

this fits very well with indexed tables explicitly
declaring a Primary Key of the INTEGER data-type,
but we must carefully consider that several different
alternatives could legitimately exist:

a. the indexed table could completely lack any PK
b. it could eventually declare a PK based on
    a single column of some data-type different from
    INTEGER (e.g. TEXT).
c. and finally it could possibly declare a
    multi-column PK.

none of the above three cases can ever match the
INTEGER value declared by the R*Tree row.
happily enough, SQLite supports a special INTEGER
key  uniquely identifying each single row: ROWID
- as the most general rule, any row has its
   own ROWID corresponding to the positional
   index of that row within its table.
- an exception exists: if the table declares s
   single-column PK and the column is declared
   to be of the INTEGER data-type then the ROWID
   simply is an alias name containing exactly
   the same value stored into the PK column.

short conclusion: using ROWIDs as the common
relational key joining both the R*Tree and
the indexed table is the unique solution
applicable to any table independently by
the exact nature of its Primary Key; and
it works well even in the absence of any PK.

this design choice poses a very strict constraint:
no ordinary column named ROWID should be present
into the indexed table, otherwise a big chaotic
mess will devastate the Spatial Index.
it could be sometimes an unpleasant restriction,
but it is usually well acceptable in the vast
majority of cases.

bye Sandro