2

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?

2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.