activating spatialite when calling sqlite3 on a gpkg from power query

Question

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:

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?

https://gis.stackexchange.com/questions/387914/no-st-spatial-functions-are-available-in-sqlite3 ? — horseyride, Jun 02 '22 at 19:28
st_* works in the cli. can't manage to pass two expressions to power query, that's the main problem — ido klein, Jun 02 '22 at 19:41

score 0 · Answer 1 · answered Jun 05 '22 at 20:40

0

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

answered Jun 05 '22 at 20:40

Davide Bacci

16,647
3
10
36

didn't work. got "DataSource.Error: ODBC: ERROR [HY000] no such function: st_centroid (1)", as described in the question – ido klein Jun 06 '22 at 05:45
Having looked into this a bit more, I think this is a restriction of the ODBC driver you are using. Do you know which driver it is? Some drivers have options to submit multiple statements which you need to provide in your connection string. e.g. MySQL: https://stackoverflow.com/questions/4144961/multiple-insert-statements-in-single-odbc-executenonquery-c and Snowflake: https://docs.snowflake.com/en/developer-guide/sql-api/submitting-multiple-statements.html – Davide Bacci Jun 06 '22 at 08:19
The only other avenue you could try is Value.NativeQuery as shown inthe link. https://blog.crossjoin.co.uk/2021/02/21/query-folding-on-sql-queries-in-power-query-using-value-nativequery-and-enablefoldingtrue/ – Davide Bacci Jun 06 '22 at 08:22
I'm using SQLite ODBC – ido klein Jun 06 '22 at 08:28
Yes but where was the driver downloaded from? Did it come with SLQLite? Do you have a documentation link for the driver? – Davide Bacci Jun 06 '22 at 08:32
http://www.ch-werner.de/sqliteodbc/ – ido klein Jun 06 '22 at 08:34
1

You're out of luck then as that driver doesn't support multiple statements from what I can see. The only option you have is rather than loading spatial_lite dynamically using a select statement, you actually preload this by overwriting the sqlite dlls as described in the answer here: https://stackoverflow.com/questions/8590599/sqlite-load-extension-fail-for-spatialite-in-python – Davide Bacci Jun 06 '22 at 08:54

activating spatialite when calling sqlite3 on a gpkg from power query

1 Answers1