More

Spatialite, NULL when using Distance

Spatialite, NULL when using Distance


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 aDistancequery 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?


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 allspatialite_alloc_connection()this will create an independent "connection object" containing any thread specific memory allocation. (this including XML and GEOS internal caches)
  2. then callsqlite3_open_v2()in order to establish a physical connection to some DB-file
  3. then invokespatialite_init_ex()so to register the SpatiaLite extension
  4. … performing any other required process (custom implementation)
  5. at the end of the process callsqlite3_close()as usual.
  6. then invokespatialite_cleanup_ex()so to unregister the SpatiaLite extension.
  7. and finally invokespatialite_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.


Watch the video: Spatialite GUI - SQLite - Download and Run Spatialite Graphical User Interface Software - Part I