.mode column results in strange output with spatialite

[A A]

Hello, new user here.

I have already posted my question on StackExchange, but this might be a better place for it:

I’m currently experimenting with Spatialite because I plan to eventually use it to programmatically generate a geodatabase from an API I have subscribed to. I already have sqlite3 available as a command in my terminal on Powershell. To be able to use Spatialite I opted for simply downloading the binaries from here and extracting them to a folder which I then added to the PATH environment variable. It seems to me that both spatialite and spatialite_tools work fine from my terminal now.

I did something very simple, first of all I created a brand new database as follows:

$ sqlite3 test.db

Then I created a basic table and inserted some values into it:

sqlite> CREATE TABLE points(id integer primary key autoincrement, name text not null, Xcoord real not null, Ycoord real not null); sqlite> insert into points (name, Xcoord, Ycoord) values ("A", 1, 5); sqlite> insert into points (name, Xcoord, Ycoord) values ("B", 3, 5); sqlite> insert into points (name, Xcoord, Ycoord) values ("C", 0, -2); sqlite> insert into points (name, Xcoord, Ycoord) values ("D", 3, -1);

With .headers on and .mode column, select * from points gives me properly formatted output:

sqlite> .headers on sqlite> .mode column sqlite> select * from points; id name Xcoord Ycoord -- ---- ------ ------ 1 A 1.0 5.0 2 B 3.0 5.0 3 C 0.0 -2.0 4 D 3.0 -1.0

However, when I .exit out of sqlite3 and open the same database with spatialite, the output looks weird:

$ spatialite test.db SpatiaLite version ..: 5.1.0 Supported Extensions: - 'VirtualShape' [direct Shapefile access] - 'VirtualDbf' [direct DBF access] - 'VirtualText' [direct CSV/TXT access] - 'VirtualGeoJSON' [direct GeoJSON access] - 'VirtualXL' [direct XLS access] - 'VirtualNetwork' [Dijkstra shortest path - obsolete] - 'RTree' [Spatial Index - R*Tree] - 'MbrCache' [Spatial Index - MBR cache] - 'VirtualFDO' [FDO-OGR interoperability] - 'VirtualBBox' [BoundingBox tables] - 'VirtualSpatialIndex' [R*Tree metahandler] - 'VirtualElementary' [ElemGeoms metahandler] - 'VirtualRouting' [Dijkstra shortest path - advanced] - 'VirtualKNN2' [K-Nearest Neighbors metahandler] - 'VirtualGPKG' [OGC GeoPackage interoperability] - 'VirtualXPath' [XML Path Language - XPath] - 'SpatiaLite' [Spatial SQL - OGC] PROJ version ........: Rel. 9.2.1, June 1st, 2023 GEOS version ........: 3.12.0-CAPI-1.18.0 RTTOPO version ......: 1.1.0 TARGET CPU ..........: i686-w64-mingw32 SQLite version ......: 3.42.0 Enter ".help" for instructions SQLite version 3.42.0 2023-05-16 12:36:15 Enter ".help" for instructions Enter SQL statements terminated with a ";" spatialite> .mode column spatialite> .headers on spatialite> select * from points; id name Xcoord Ycoord ---------- ---------- ---------- ---------- 1 1 A A 1.0 1.0 5.0 5.0 2 2 B B 3.0 3.0 5.0 5.0 3 3 C C 0.0 0.0 -2.0 -2.0 4 4 D D 3.0 3.0 -1.0

The table looks fine without .mode column, so why does .mode column look different in Spatialite than it does on sqlite3?

​

[a.fu...@lqt.it]

On Tue, 2 Jul 2024 13:35:13 -0700 (PDT), A A wrote:
> The table looks fine without .mode column, so why does .mode column
> look different in Spatialite than it does on sqlite3?
> ​

for three excellent reasons:

1. SpatiaLite CLI is not SQLite3, it's a different software and nothing
guarantees that it behaves in the same way.

2. Spatialite CLI is intentionnaly out of maintenance.
it still continues to be distributed only to maintain long term
backwards compatibility.

3. for any new development the use of SpatiaLite CLI is now deprecated.
the correct solution is to always use SQLite3 by loading SpatiaLite
as a dynamic extension.
it only requires you to run a single SQL command:

SELECT load_extension('mod_spatialite');

--------------------

for any further reading please see:

https://wiki.openstreetmap.org/wiki/Map_features

best regards,
Sandro

[A A]

Dear Sandro,

Thank you very much for your response. While Spatialite is different from Sqlite, my understanding is that spatialite is built on top of Spatialite, so I would expect basic things like STDOUT when running a `select * from table_name` statement to be the same. 

That being said, would you mind sharing any resources that explain how to install and load Spatialite as a dynamic extension? Do I have to build and generate the DLLs myself or are the released binaries available somewhere?

Kind Regards,

Amine

--
You received this message because you are subscribed to the Google Groups "SpatiaLite Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to spatialite-use...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/spatialite-users/248dc65aa7cf943bc150c9e5504c0e57%40lqt.it.

[A A]

Nevermind, I have successfully loaded the extension by downloading the binaries from The Gaia-SINS federated project home-page (gaia-gis.it), extracting the DLLs and adding them to my path environment variable.

Regards,

Amine