Spatial tables with null geometries

[Jukka Rahkonen]

Hi,

I wonder if it could be possible be a little bit more liberal with the demand that spatial tables must contain only one sort of geometries so that NULL geometries would also be accepted. For example geometry functions can fail for some reason and the resulting data set may contain null geometries. Now it is not possible to create spatial index for such tables. Deleting rows with null geometries is one alternative and updating the  null geometries with a fake geometry is another and usually better possibility. 

My use case is that the source data has null values but the data is still correct. Data contains parcels which are created into the database but they do not have geometries because they have not been digitized yet. Well, I could have parcel attributes in one table and parcels geometries in another and use relations. However, the data is maintained in Oracle and there the parcel attributes and geometries are in one table, possible with null geometries.

How much damage would be done if the spatial index constraints would accept also NULL geometry types? I know that clients like QGIS could have troubles when meeting records without geometries because they can't internally handle such. On the other hand, there are clients who do not care, for example OpenJUMP is converting null geometries into GEOMETRY COLLECTION EMPTY and feature attributes can be edited and a new real geometries can be created.

-Jukka Rahkonen-

[a.fu...@lqt.it]

Hi Jukka,

tested right now on v4.0.0-RC1:

CREATE TABLE jukka (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL);

SELECT AddGeometryColumn('jukka', 'geom', 4326, 'POINT', 'XY');
SELECT CreateSpatialIndex('jukka', 'geom');

INSERT INTO jukka (id, name, geom)
VALUES(NULL, 'alpha', MakePoint(1, 1, 4326));
INSERT INTO jukka (id, name, geom)
VALUES(NULL, 'beta', MakePoint(2, 2, 4326));
INSERT INTO jukka (id, name, geom)
VALUES(NULL, 'gamma', NULL);
INSERT INTO jukka (id, name, geom)
VALUES(NULL, 'delta', NULL);
INSERT INTO jukka (id, name, geom)
VALUES(NULL, 'epsilon', MakePoint(5, 5, 4326));

UPDATE jukka SET geom = MakePoint(3, 3, 4326)
WHERE id = 3;
UPDATE jukka SET geom = NULL
WHERE id = 2;

anything works as expected, this including the Spatial Index;
there is absolutely nothing forbidding to store NULL geometries
and e.g. Points at the same time.

---
Just for my curiosity, which version are you using ?
As far as I can remember, the latest version unable to correctly
handle NULL geometries with Spatial Index was 2.3.1 (or 2.4.0 ?);
but it dates back to two/three years ago.

bye Sandro


--
Il messaggio e' stato analizzato alla ricerca di virus o
contenuti pericolosi da MailScanner, ed e'
risultato non infetto.

[Jukka Rahkonen]

Hi Sandro,

Thank you for the SQL. I discovered that the problem in in another place. What happens is that if I have the table with POINT and NULL geometries first and I try to register the table into geometry_columns afterwords then it fails.

DELETE FROM geometry_columns WHERE f_table_name='jukka'

SELECT AddGeometryColumn('jukka', 'geom', 4326, 'POINT', 'XY');

Tested with Spatialite-gui 1.5.0 stable on Windows (SpatiaLite 3.0.1).

-Jukka-

[a.fu...@lqt.it]

Hi Jukka,

> Thank you for the SQL. I discovered that the problem in in another
> place. What happens is that if I have the table with POINT and NULL
> geometries first and I try to register the table into
> geometry_columns
> afterwords then it fails.
>
> DELETE FROM geometry_columns WHERE f_table_name='jukka'
> SELECT AddGeometryColumn('jukka', 'geom', 4326, 'POINT', 'XY');
>

the above SQL will surely fail; the clean way to do such a thing is
as shown below:

SELECT DiscardGeometryColumn('jukka', 'geom');
SELECT RecoverGeometryColumn('jukka', 'geom', 4326, 'POINT', 'XY');

a) performing a direct "DELETE FROM geometry_columns" isn't a good
idea;
it will surely remove the row in this table, but will not drop the
related triggers supporting Geometry.
may well be a minor and harmless issue. anyway using
DiscardGeometryColumn()
is what is expected in this case.

b) AddGeometryColumn() will surely fail if the "geom" column already
exists.
an implicit "ALTER TABLE ADD COLUMN geom" will be silently executed,
and will
obviously fail.
AddGeometryColumn() is intended to create from scratch a brand new
column.
in order to register an already defined (and may be, already
populated column)
you are expected to use RecoverGeometryColumn()

[Jukka Rahkonen]

Hi,

Sorry, it was a poor trial to do with SQL the same that Spatialite-gui Recover Geometry Column is doing from the menu.
So what I really did was to remove spatial index and geometry triggers with Spatialite-gui, delete the corresponding row fror geometry_columns and then try to do "Recover Geometry Column". It leads to ROLLBACK because of constraint violation.

In real life I have experienced that if I create a table as
Create table as ST_OffsetCurve... and the result has empty geometries, then I have not been able to run Recover Geometry Column before deleting all the rows with null geometries.

-Jukka-

[a.fu...@lqt.it]

> Sorry, it was a poor trial to do with SQL the same that
> Spatialite-gui
> Recover Geometry Column is doing from the menu.
> So what I really did was to remove spatial index and geometry
> triggers
> with Spatialite-gui, delete the corresponding row fror
> geometry_columns and then try to do "Recover Geometry Column". It
> leads to ROLLBACK because of constraint violation.
>

Jukka,

I can finally confirm; yes, spatialite_gui 1.5.0 (3.0.1) is surely
unable to succesfully recover any Geometry column containing NULLs

anyway the latest spatialite_gui 1.6.0 (4.0.0-RC1) works as expected;
so it looks like a problem affecting older versions but now resolved.

bye sandro