View Ticket
Not logged in
Ticket Hash: 72b73df2a33739874bedb654955b71b6f3af9181
Title: spatialite produces NULL instead of empty geometries
Status: Closed Type: Feature_Request
Severity: Minor Priority: Low
Subsystem: Resolution: Works_As_Designed
Last Modified: 2016-11-26 10:32:06
Version Found In: 4.3.0a
User Comments:
anonymous added on 2016-06-19 18:44:39:

sqlite> SELECT ST_GeomFromText('POINT EMPTY') IS NULL;
1
sqlite> SELECT ST_Intersection(ST_Point(0, 0), ST_Point(1, 1)) IS NULL;
1


anonymous added on 2016-06-23 07:43:45:

This example shows that handling of empty geometries is important:

sqlite> SELECT ST_Union(ST_Intersection(ST_Point(0, 0), ST_Point(1, 1)), ST_Point(2, 2)) IS NULL;
1
though expected geometry is 'POINT (2 2)'.


mj10777 added on 2016-06-24 04:53:50:

1) this is a GEOS function.
2) the expected result of a Union, where one geometry is NULL is NULL

SELECT
 ST_Intersection(ST_Point(0, 0), ST_Point(1, 1)) AS geom_null,
 ST_Point(2, 2) AS geom_valid,
 ST_Union(ST_Intersection(ST_Point(0, 0), ST_Point(1, 1)), ST_Point(2, 2)) AS geom_union,
 ST_Union(ST_Intersection(ST_Point(0, 0), ST_Point(1, 1)), ST_Point(2, 2)) IS NULL AS is_null,
 ST_Union(ST_Intersection(ST_Point(0, 0), ST_Point(1, 1)), ST_Point(2, 2)) IS NOT NULL AS is_not_null
;
returns:
 geom_null NULL
 geom_valid SRID=0;POINT(2 2)
 geom_union NULL
 is_null 1=true
 is_not_null 0=false
Since a union of geometries can only be created from valid geometries
    the addition of an invalid geometry, will always create an invalid geometry

If, as desired, invalid geometries where excluded/skipped
    there would be no way to check for errors
      only if all of the geometries where invalid, would NULL be returned

The purpose of IS NULL or IS NOT NULL
    is to check for errors
      which here is done here correctly

ISO/IEC 13249-3:201x(E) 5.1.33 ST_Union Method:
Return an ST_Geometry value that represents the point set union of two ST_Geometry values, ignoring z
and m coordinate values in the calculations and not including them in the resultant geometry.
Since NULL is not a Geometry, it cannot be included in the final result.
    therefore a Union is not possible

PostGIS:ST_Union states:
    ... If any are NULL, then NULL is returned.

For all of the above reasons, the results returned be GEOS are correct
    therefore should not be considered an error / Code Defect

Other than adding a similar statement to the spatialite documentation of ST_UNION, nothing else should be done - in my opnion.


anonymous added on 2016-07-18 04:18:46:

1) 'POINT EMPTY' is valid WKT for geometry, but

SELECT ST_GeomFromText('POINT EMPTY') IS NULL;

returns:

1

2) the result of ST_Intersection(ST_Point(0, 0), ST_Point(1, 1)) is valid geometry, but

SELECT ST_Intersection(ST_Point(0, 0), ST_Point(1, 1)) IS NULL;

returns:

1

3) on PostgreSQL

SELECT
 ST_Intersection(ST_Point(0, 0), ST_Point(1, 1)) AS geom_null,
 ST_Point(2, 2) AS geom_valid,
 ST_Union(ST_Intersection(ST_Point(0, 0), ST_Point(1, 1)), ST_Point(2, 2)) AS geom_union,
 ST_Union(ST_Intersection(ST_Point(0, 0), ST_Point(1, 1)), ST_Point(2, 2)) IS NULL AS is_null,
 ST_Union(ST_Intersection(ST_Point(0, 0), ST_Point(1, 1)), ST_Point(2, 2)) IS NOT NULL AS is_not_null
;

returns:

     geom_null      |                 geom_valid                 |                 geom_union                 | is_null | is_not_null 
--------------------+--------------------------------------------+--------------------------------------------+---------+-------------
 010700000000000000 | 010100000000000000000000400000000000000040 | 010100000000000000000000400000000000000040 | f       | t


anonymous added on 2016-07-27 23:19:45:
I consider handling empty geometries as a marginal case. Do you really think that severity=critical and priority=immediate is right classification? Try to think about all Spatialite users, not just yourself.

sandro added on 2016-07-30 10:08:37:
1. strict standard conformance undoubtedly dictates to distinguish between NULL and EMPTY geometries.
2. unhappily SpatiaLite started since the beginning of its lifecycle by ignoring such distinction; as a matter of fact all versions of SpatiaLite consider any EMPTY geometries to be NULL
3. introducing EMPTY geometries will surely have a very deep impact on the existing codebase and will require an almost complete rewrite of the main core.
4. all this considered, it's not a bug; it's more a design peculiarity (yet another between the meanies where SpatiaLite departs more or less radically from the standard specifications).
5. happily enough, the current implementation seems to be reputed substantially valid by the vast majority of the users.

conclusion: will be possibly fixed in some future release; but resolving this issue currently has a very low priority.