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
_______________________________________________
gdal-dev mailing list
gdal-dev@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/gdal-dev
