Trouble saving specificly shaped GeometryCollection to spatialite backend

[Tim Sheerman-Chase]

I have trouble saving the following shape to a spatialite backend:

		test="POLYGON ((0.004585821432403 53.39205655445362, 0.004370524051853 53.39197386424815, 0.003511999412435 53.39224718406162, 0.003334269156126 53.39230564512001, 0.003010761561655 53.39244359770373, 0.002313077441331 53.39273886079805, 0.002153264778766 53.3928494141358, 0.002098473352319 53.39289040220098, 0.001975450131408 53.39301575633081, 0.001571912175901 53.39342627983574, 0.00144845213223 53.39354238985806, 0.001054429533822 53.39382638644516, 0.000660690765872 53.39411654416967, 0.000258658810006 53.3944499832029, -0.000208743564432 53.39485788447784, -0.000230623648264 53.39490287808773, -0.000236528103555 53.39494760407232, -0.000210152629556 53.39499816331232, -0.000134789640903 53.39507021540287, 0.0000 53.39512930610134, 0.00001 53.39519086239132, 0.000320701021055 53.39528731045475, 0.000440541032034 53.39534108611749, 0.000495476965256 53.39537522708896, 0.000526128004445 53.39540340096636, 0.000552505095391 53.39545396900132, 0.00056354800768 53.39551912431292, 0.000561917344505 53.39565462410584, 0.001403023462806 53.39573616267051, 0.001776075560191 53.39578728279303, 0.002238095515083 53.39585922310508, 0.002415730663242 53.39589449667334, 0.00262892046244 53.39594829555955, 0.002759136797677 53.39599552084307, 0.003148451411336 53.39571166071373, 0.003852256735541 53.39519780287833, 0.004239121351319 53.39491876291179, 0.0048580524367 53.39447166424996, 0.00570113015205 53.39385822744637, 0.006098176439091 53.39356945364467, 0.006789195134597 53.39306853168554, 0.006903507578287 53.39298848546464, 0.006452162455299 53.39282038704182, 0.006147262062949 53.39269606225199, 0.005531717833986 53.39243518140811, 0.005205915002175 53.39230503820097, 0.004585821432403 53.39205655445362))"

		shape = wkt.loads(test)

		pos = GEOSGeometry(str(rp), srid=4326)
		rec2 = Record(currentName = placeName, 
			currentPosition = pos,
			datasetSeries = self.ds,
			externalId = externalId)
		rec2.save()

		if not shape.is_valid:
			shape = shape.buffer(0.0)
		shape2 = GeometryCollection([shape])
		shape3 = GEOSGeometry(buffer(shape2.wkb), srid=4326)

		try:
			newAnnot = RecordShapeEdit(record = rec2,
				data = shape3, 
				timestamp = self.importTime,
				user = self.importUser)
			newAnnot.save() #Problem saving object is here
		except Exception as err:
			print err
			print shape3.valid

This fails with: NOT NULL constraint failed: records_recordshapeedit.data

If I change the point 0.00001 53.39519086239132 to 0.0001 53.39519086239132 (one less zero in the first number), it works ok. I am surprised such as small change makes any difference. The shape is valid according to shapely and the GEOSGeometry object.

Code to reproduce, see test.py here : ​https://github.com/TimSC/auxgis2/tree/reproduce

Full backtrace:

Traceback (most recent call last):
  File "importScheduledMonuments.py", line 281, in <module>
    ep.ParseFile(inFi)
  File "importScheduledMonuments.py", line 269, in ParseFile
    parser.ParseFile(ha)
  File "importScheduledMonuments.py", line 240, in EndEl
    self.db.HandlePlacemark(pn, shape, self.extendedData)
  File "importScheduledMonuments.py", line 115, in HandlePlacemark
    newAnnot.save()
  File "/usr/local/lib/python2.7/dist-packages/django/db/models/base.py", line 796, in save
    force_update=force_update, update_fields=update_fields)
  File "/usr/local/lib/python2.7/dist-packages/django/db/models/base.py", line 824, in save_base
    updated = self._save_table(raw, cls, force_insert, force_update, using, update_fields)
  File "/usr/local/lib/python2.7/dist-packages/django/db/models/base.py", line 908, in _save_table
    result = self._do_insert(cls._base_manager, using, fields, update_pk, raw)
  File "/usr/local/lib/python2.7/dist-packages/django/db/models/base.py", line 947, in _do_insert
    using=using, raw=raw)
  File "/usr/local/lib/python2.7/dist-packages/django/db/models/manager.py", line 85, in manager_method
    return getattr(self.get_queryset(), name)(*args, **kwargs)
  File "/usr/local/lib/python2.7/dist-packages/django/db/models/query.py", line 1045, in _insert
    return query.get_compiler(using=using).execute_sql(return_id)
  File "/usr/local/lib/python2.7/dist-packages/django/db/models/sql/compiler.py", line 1054, in execute_sql
    cursor.execute(sql, params)
  File "/usr/local/lib/python2.7/dist-packages/django/db/backends/utils.py", line 79, in execute
    return super(CursorDebugWrapper, self).execute(sql, params)
  File "/usr/local/lib/python2.7/dist-packages/django/db/backends/utils.py", line 64, in execute
    return self.cursor.execute(sql, params)
  File "/usr/local/lib/python2.7/dist-packages/django/db/utils.py", line 94, in __exit__
    six.reraise(dj_exc_type, dj_exc_value, traceback)
  File "/usr/local/lib/python2.7/dist-packages/django/db/backends/utils.py", line 64, in execute
    return self.cursor.execute(sql, params)
  File "/usr/local/lib/python2.7/dist-packages/django/db/backends/sqlite3/base.py", line 337, in execute
    return Database.Cursor.execute(self, query, params)
django.db.utils.IntegrityError: NOT NULL constraint failed: records_recordshapeedit.data

UPDATE: I translated the shape away from zero and it works fine. I suspect that numbers are being represented in scientific notation in sql, which is perhaps not supported by some component of the back end... perhaps this is an upstream issue: ​https://www.gaia-gis.it/fossil/libspatialite/info/4d07ddcdff

[Tim Sheerman-Chase]

When I get the wkt from the GEOSGeometry object, it has one number with scientific notation (1e-05):

GEOMETRYCOLLECTION (POLYGON ((0.004585821432403 53.39205655445362, 0.004370524051853 53.39197386424815, 0.003511999412435 53.39224718406162, 0.003334269156126 53.39230564512001, 0.003010761561655 53.39244359770373, 0.002313077441331 53.39273886079805, 0.002153264778766 53.3928494141358, 0.002098473352319 53.39289040220098, 0.001975450131408 53.39301575633081, 0.001571912175901 53.39342627983574, 0.00144845213223 53.39354238985806, 0.001054429533822 53.39382638644516, 0.000660690765872 53.39411654416967, 0.000258658810006 53.3944499832029, -0.000208743564432 53.39485788447784, -0.000230623648264 53.39490287808773, -0.000236528103555 53.39494760407232, -0.000210152629556 53.39499816331232, -0.000134789640903 53.39507021540287, 0 53.39512930610134, 1e-05 53.39519086239132, 0.000320701021055 53.39528731045475, 0.000440541032034 53.39534108611749, 0.000495476965256 53.39537522708896, 0.000526128004445 53.39540340096636, 0.000552505095391 53.39545396900132, 0.00056354800768 53.39551912431292, 0.000561917344505 53.39565462410584, 0.001403023462806 53.39573616267051, 0.001776075560191 53.39578728279303, 0.002238095515083 53.39585922310508, 0.002415730663242 53.39589449667334, 0.00262892046244 53.39594829555955, 0.002759136797677 53.39599552084307, 0.003148451411336 53.39571166071373, 0.003852256735541 53.39519780287833, 0.004239121351319 53.39491876291179, 0.0048580524367 53.39447166424996, 0.00570113015205 53.39385822744637, 0.006098176439091 53.39356945364467, 0.006789195134597 53.39306853168554, 0.006903507578287 53.39298848546464, 0.006452162455299 53.39282038704182, 0.006147262062949 53.39269606225199, 0.005531717833986 53.39243518140811, 0.005205915002175 53.39230503820097, 0.004585821432403 53.39205655445362)))

From my understanding of the WKT 2 spec, scientific notation should not be used (as far as I can tell). ​http://docs.opengeospatial.org/is/12-063r5/12-063r5.html Also, AFAIK the WKT 1 spec leaves this detail unspecified. I can't find where the numbers are being encoded in the source. Any ideas?

[Claude Paroz]

WKT formatting is done by GEOS (Django's calling it in the GEOSGeometry.wkt property).
It looks like this is also commanded by the trim property (see also #25951).

>>> from django.contrib.gis.geos import Point
>>> p1 = Point(0.0000000001, 2.34598712386)
>>> p1.wkt
'POINT (1e-10 2.34598712386)'

>>> from django.contrib.gis.geos.prototypes.io import WKTWriter
>>> writer = WKTWriter(trim=False)
>>> writer.write(p1)
'POINT (0.0000000001000000 2.3459871238600001)'

However, if GEOS permits it, I'd say the backend should also support it. So I would classify this as "not Django" bug considering that the Spatialite commit you mention should fix this.

[Tim Graham]

Please reopen if further investigation concludes that Django is at fault.