go程序运行Spaitalite踩坑记录

Spatialite参考资料：8.1. 开源地理空间数据库 — Python与开源GIS

Ubuntu安装SpaitaLite：

apt-get install libspatialite7 libsqlite3-mod-spatialite
apt-get install spatialite-bin

命令行打开数据库：spatialite xxx.db
执行一个空间函数：

SELECT ST_Distance(
GeomFromText('POINT(113.324521 23.1064289)'),
GeomFromText('POINT(113.307392 23.387375)')
);

windows安装，下载地址：Index of /gaia-sins/windows-bin-amd64
解压后目录配置到环境变量path中即可
(注意spatialite-tools和mod_spatialite两个都需要，spatialite-tools包含spatialite命令行工具，mod_spatialite里面dll是运行go、python程序所需要的)

go操作spatialite驱动：GitHub - briansorahan/spatialite: database/sql driver for libspatialite
添加依赖：go get -u github.com/briansorahan/spatialite

gorm操作spatialite示例代码：

package mainimport ("fmt""gorm.io/driver/sqlite""gorm.io/gorm"
)import (_ "github.com/briansorahan/spatialite"
)func main() {sqliteDialector := sqlite.Dialector{DriverName: "spatialite",DSN:        "./spatialite.db",}db, err := gorm.Open(sqliteDialector, &gorm.Config{})if err != nil {fmt.Println(fmt.Sprintf("open database error: %v", err))return}var ff float64rs := db.Raw("SELECT ST_Distance(GeomFromText('POINT(113.324521 23.1064289)'),GeomFromText('POINT(113.307392 23.387375)'))").Scan(&ff)if rs.Error != nil {fmt.Println(fmt.Sprintf("SELECT ST_Distance error: %v", rs.Error))return}fmt.Println(fmt.Sprintf("ST_Distance: %v", ff))
}

这里使用spatialite驱动连接sqlite数据库，并执行一个空间函数

报错解决：

报错一：
/root/go/pkg/mod/github.com/briansorahan/spatialite@v0.0.0-20200320201955-49400a1f7714/driver.go:6:11: fatal error: sqlite3.h: No such file or directory 6 | // #include <sqlite3.h>
报错二：
/root/go/pkg/mod/github.com/briansorahan/spatialite@v0.0.0-20200320201955-49400a1f7714/driver.go:7:11: fatal error: spatialite.h: No such file or directory 7 | // #include <spatialite.h>这里报错是由于驱动依赖本地C库，所以需要安装好对应的库linux本地库安装：
sudo apt install sqlite3 libsqlite3-dev
sudo apt install libspatialite-devwindows：下载相应头文件，并配置CGO_CFLAGS环境变量
我这里直接使用QGIS中带的头文件，将需要的头文件提取出来放到单独的spatialite-include目录(对应资源放到附件中)
然后配置环境变量CGO_CFLAGS=-ID:\spatialite-include (我这里是spatialite-include直接放D盘，记得换成自己的目录)

这里驱动存在一个bug，第二次开始运行会有个报错：InitSpatiaMetaData() error:"table spatial_ref_sys already exists" 
这是由于驱动中代码，每次打开数据库连接时都会执行SELECT InitSpatialMetaData(1)导致
不过这个报错并不影响程序正常运行，可以暂时先无视，后续有空再解决

---

踩坑记录：

前面不小心下载错mod_spatialite，下载成了32位版本的，导致每次运行都会报: not an error，根本看不出啥问题
然后一路跟踪报错，定位到报错代码在sqlite3_load_extension.go中调用C代码处：C.sqlite3_load_extension

这里C.sqlite3_load_extension的方法在sqlite3.h中定义，打开sqlite3.h文件，找到sqlite3_load_extension方法：

/*
** CAPI3REF: Load An Extension
** METHOD: sqlite3
**
** ^This interface loads an SQLite extension library from the named file.
**
** ^The sqlite3_load_extension() interface attempts to load an
** [SQLite extension] library contained in the file zFile.  If
** the file cannot be loaded directly, attempts are made to load
** with various operating-system specific extensions added.
** So for example, if "samplelib" cannot be loaded, then names like
** "samplelib.so" or "samplelib.dylib" or "samplelib.dll" might
** be tried also.
**
** ^The entry point is zProc.
** ^(zProc may be 0, in which case SQLite will try to come up with an
** entry point name on its own.  It first tries "sqlite3_extension_init".
** If that does not work, it constructs a name "sqlite3_X_init" where the
** X is consists of the lower-case equivalent of all ASCII alphabetic
** characters in the filename from the last "/" to the first following
** "." and omitting any initial "lib".)^
** ^The sqlite3_load_extension() interface returns
** [SQLITE_OK] on success and [SQLITE_ERROR] if something goes wrong.
** ^If an error occurs and pzErrMsg is not 0, then the
** [sqlite3_load_extension()] interface shall attempt to
** fill *pzErrMsg with error message text stored in memory
** obtained from [sqlite3_malloc()]. The calling function
** should free this memory by calling [sqlite3_free()].
**
** ^Extension loading must be enabled using
** [sqlite3_enable_load_extension()] or
** [sqlite3_db_config](db,[SQLITE_DBCONFIG_ENABLE_LOAD_EXTENSION],1,NULL)
** prior to calling this API,
** otherwise an error will be returned.
**
** <b>Security warning:</b> It is recommended that the
** [SQLITE_DBCONFIG_ENABLE_LOAD_EXTENSION] method be used to enable only this
** interface.  The use of the [sqlite3_enable_load_extension()] interface
** should be avoided.  This will keep the SQL function [load_extension()]
** disabled and prevent SQL injections from giving attackers
** access to extension loading capabilities.
**
** See also the [load_extension() SQL function].
*/
SQLITE_API int sqlite3_load_extension(sqlite3 *db,          /* Load the extension into this database connection */const char *zFile,    /* Name of the shared library containing extension */const char *zProc,    /* Entry point.  Derived from zFile if 0 */char **pzErrMsg       /* Put error message here if not 0 */
);

这里注释明确说明应该使用pzErrMsg来获取错误信息，但是go代码中却使用sqlite3_errmsg来获取，所以导致返回：not an error

对这里代码做简单的改造：

var errMsg *C.char
defer C.sqlite3_free(unsafe.Pointer(errMsg)) // 确保释放错误信息的内存fmt.Println("Loading extension: %s", C.GoString(cext))
rv = C.sqlite3_load_extension(c.db, cext, nil, &errMsg)
if rv != C.SQLITE_OK {if errMsg != nil {fmt.Println("sqlite3_load_extension error: ", C.GoString(errMsg))}C.sqlite3_enable_load_extension(c.db, 0)return errors.New(C.GoString(C.sqlite3_errmsg(c.db)))
}

再重新运行，获取到真正的报错：

至此才终于定位到是下载的dll文件问题，重新下载64位版本的终于解决