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?