Spatialite, NULL when using Distance

Question

When using Spatialite, I cannot run a Distance query. I've tried investigating the data stored in the database, however it doesn't appear to be related to my database. If I run a Distance query on two coordinates, there is even a difference between the CLI and using the C library.

The correct response is given from the CLI (built with homebrew on OS X):

$ spatialite
SpatiaLite version ..: 4.2.0    Supported Extensions:
    - 'VirtualShape'    [direct Shapefile access]
    - 'VirtualDbf'      [direct DBF access]
    - 'VirtualXL'       [direct XLS access]
    - 'VirtualText'     [direct CSV/TXT access]
    - 'VirtualNetwork'  [Dijkstra shortest path]
    - 'RTree'       [Spatial Index - R*Tree]
    - 'MbrCache'        [Spatial Index - MBR cache]
    - 'VirtualSpatialIndex' [R*Tree metahandler]
    - 'VirtualXPath'    [XML Path Language - XPath]
    - 'VirtualFDO'      [FDO-OGR interoperability]
    - 'VirtualGPKG' [OGC GeoPackage interoperability]
    - 'VirtualBBox'     [BoundingBox tables]
    - 'SpatiaLite'      [Spatial SQL - OGC]
PROJ.4 version ......: Rel. 4.8.0, 6 March 2012
GEOS version ........: 3.4.2-CAPI-1.8.2 r3921
TARGET CPU ..........: x86_64-apple-darwin14.1.0
the SPATIAL_REF_SYS table already contains some row(s)
SQLite version ......: 3.8.8.2
spatialite> SELECT Distance(MakePoint(-73.995, 41.145556, 4326), MakePoint(-122.416667, 37.783333, 4326), 0);
4119935.70473556

However if I do this from my code using the C library, the response is NULL:

SpatiaLite version ..: 4.2.0    Supported Extensions:
    - 'VirtualShape'    [direct Shapefile access]
    - 'VirtualDbf'      [direct DBF access]
    - 'VirtualText'     [direct CSV/TXT access]
    - 'VirtualNetwork'  [Dijkstra shortest path]
    - 'RTree'       [Spatial Index - R*Tree]
    - 'MbrCache'        [Spatial Index - MBR cache]
    - 'VirtualSpatialIndex' [R*Tree metahandler]
    - 'VirtualFDO'      [FDO-OGR interoperability]
    - 'VirtualGPKG' [OGC GeoPackage interoperability]
    - 'VirtualBBox'     [BoundingBox tables]
    - 'SpatiaLite'      [Spatial SQL - OGC]
PROJ.4 version ......: Rel. 4.8.0, 6 March 2012
GEOS version ........: 3.4.2-CAPI-1.8.2 r0
TARGET CPU ..........: x86_64-apple-darwin14.0.0

(sqlite3_column_type(...) == SQLITE_NULL) == true

What do I have to change in order to get a valid distance calculation?

Answer 1

Sandro Furieri, the author of Spatialite, explains in this post how to initialize Spatialite correctly:

So the "new style" initialization is as follows:

1. invoke first of all spatialite_alloc_connection() this will create an independent "connection object" containing any thread specific memory allocation. (this including XML and GEOS internal caches)
2. then call sqlite3_open_v2() in order to establish a physical connection to some DB-file
3. then invoke spatialite_init_ex() so to register the SpatiaLite extension
4. ... performing any other required process (custom implementation)
5. at the end of the process call sqlite3_close() as usual.
6. then invoke spatialite_cleanup_ex() so to unregister the SpatiaLite extension.
7. and finally invoke spatialite_free_internal_cache() thus freeing any related memory allocation.

I discovered by stepping through Spatialite's code, at some point the internal "connection object" was being referenced incorrectly. In my code I passed the wrong pointer, which eventually resulted in the NULL value.