activating spatialite when calling sqlite3 on a gpkg from power query

user_5308972 0
Viewed 100

I'm trying to make a spatial query on a table with a geometry column, stored in a gpkg, through excel's power query. when trying to run the query, as such:

= Odbc.Query("database=path/to/gpkg;dsn=SQLite3 Datasource", "select *,st_centroid(geom) as cent from some_layer")

it returns the following error:

DataSource.Error: ODBC: ERROR [HY000] no such function: st_centroid (1)

The following happens also when running from sqlite3 cli: enter image description here

however, if I run

select load_extension('mod_spatialite')

beforehand, it works.

My problem is that when I try to run two commands in power query, like so:

= Odbc.Query("database=path/to/gpkg;dsn=SQLite3 Datasource", "select load_extension('mod_spatialite');#(lf)select * from some_layer")

I get the following error:

DataSource.Error: ODBC: ERROR [HY000] only one SQL statement allowed

so my question is - how can one setup a call to sqlite3 where the spatialite extension is already loaded?

spatialite powerquery odbc excel sqlite
1 Answers

Could you try running both queries in succession? As they're part of the same session, it might give you what you need. I don't have an environment to test but I'm thinking along the lines of:

let
    Source = Table.Combine({a, b}),
    a = Odbc.Query("database=path/to/gpkg;dsn=SQLite3 Datasource", "select load_extension('mod_spatialite')"),
    b = Odbc.Query("database=path/to/gpkg;dsn=SQLite3 Datasource", "select *,st_centroid(geom) as cent from some_layer")
in
    Source
user_183450370
Related