Enhancement of Row Count Indicator in Status Bar

[Totò Fiandaca]

Current Issue Description

In the current version of SpatiaLite GUI, when executing a query that returns tabular results, the status bar at the bottom displays information in the format:

current block: 1/500 [500 rows]

The value within square brackets [500 rows] currently always indicates the number of rows in the current displayed block (typically 500), regardless of the total number of records returned by the query.

Requested Feature

I request modifying the indicator to display the total count of rows returned by the query, rather than the number of rows in the current block.

Proposed format:

• current block: 1/500 [total rows: 2,847]
• or more simply: current block: 1/500 [2,847 rows total]

Technical Rationale

1. Improved usability: Users can immediately understand the overall dataset size without having to scroll through all blocks
2. Analysis efficiency: Facilitates evaluation of data completeness and planning of subsequent operations
3. Consistency with other DBMS: Most modern database clients provide this basic information
4. Decision support: Allows users to evaluate whether results need additional filters or query optimizations

Suggested Implementation

The modification should be compatible with the following scenarios:

• Queries with results smaller than block size
• Queries with very large results (millions of records)
• Queries with approximate counts to optimize performance on huge datasets

Additional Considerations

For particularly complex queries or very large tables, one might consider:

• An approximate count with indication [~2,847 rows]
• An option to disable total counting if it significantly impacts performance
• A loading indicator during total calculation

Priority

This feature would significantly improve user experience without requiring substantial changes to the existing interface.

  Thank you for the excellent work on SpatiaLite and for your attention to community feedback.  

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

On Thu, 14 Aug 2025 02:18:18 -0700 (PDT), Totò Fiandaca wrote:
> Current Issue Description
>
> In the current version of SpatiaLite GUI, when executing a query that
> returns tabular results, the status bar at the bottom displays
> information in the format:
>
> current block: 1/500 [500 rows]
>
> The value within square brackets [500 rows] currently always
> indicates
> the number of rows in the current displayed block (typically 500),
> regardless of the total number of records returned by the query.
>

Note: this is not an issue, is absolutely intentional.


> I request modifying the indicator to display the TOTAL COUNT OF ROWS

> returned by the query, rather than the number of rows in the current
> block.
>

A bit of history: the very first versions of the GUI took exactly this
approach, which was then quickly abandoned when several users began
to complain because this significantly slowed down the display of SQL
query results involving large tables.

We finally came up with the current criterion which at least does
not require unnecessary time-wasting.
It's a compromise, but it's a very reasonable compromise:

1) Most users prefer to have fast response times.
2) When needed, it's rather trivial to find out how many rows there
are in a resultset.
Simply press the button that requests positioning on the last rows
of the current resultset (see attached figure).

bye Sandro

[Jukka Rahkonen]

Hi,

A relatively good workaround is to press the "Go to the last row" button. Even better, if you want to know the number of rows then query it directly. If your query was SELECT "fid", "geom" FROM "my_table" WHERE... then change it into SELECT count("fid") FROM "my_table" WHERE...

-Jukka Rahkonen-

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

On Thu, 14 Aug 2025 04:53:11 -0700 (PDT), Jukka Rahkonen wrote:
> Hi,
>
> A relatively good workaround is to press the "Go to the last row"
> button. Even better, if you want to know the number of rows then
> query
> it directly. If your query was SELECT "fid", "geom" FROM "my_table"
> WHERE... then change it into SELECT count("fid") FROM "my_table"
> WHERE...
>

Hi Jukka,
Your answer is absolutely correct, but it is not generalizable.

The question "how many rows are there" is ambiguous and should
be clarified better:

A) how many rows are in a table ?
B) or how many rows are in a resultset ?

Obviously they are two different things, and are interchangeable
only in the most basic cases when a single table is queried.

Clearly, we are talking about the problem "how many rows in a
resultset",
which only in very particular cases overlaps with the other problem
"how
many rows in a table".

So, strictly speaking, the SELECT Count(*) FROM my_table; solution is
preferable when you want to find out how many rows there are in a
table.

Directly jumping to the end of the resultset is a more general
approach,
that is perfectly suited to very complex queries involve many tables,
and perhaps requiring the evaluation of a complex WHERE clause.

One is not better than the other; it all depends on what you want to
achieve.

bye Sandro

[Jukka Rahkonen]

Hi Sandro,

Why not it is not generalizable? I wrote "WHERE..." to mean that that the original, perhaps complex WHERE would be there unaltered (as well as the tables in FROM, possible joins etc.. But maybe count("fid") is not good if all features in the resultset do not contain "fid". However, count(*) should work always.

-Jukka Rahkonen-

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

On Thu, 14 Aug 2025 02:18:18 -0700 (PDT), Totò Fiandaca wrote:

> * CONSISTENCY WITH OTHER DBMS: Most modern database clients provide
> this basic information
>

Just for the sake of completeness, it is important to clarify
any misleading consideration.

I assume that when you talk about other DBMS you mean PostgreSQL;
but SQLite and PostgreSQL are radically different, and apart from the
SQL language they have absolutely nothing in common.

To better understand, let's quickly see how a query works on
PostgreSQL:
1. the client sends its request to the server, which begins processing
it.
2. the client waits until the server notifies that the resultset is
available.
Note that this can be a long wait.
3- At this point the client checks how many rows there are in the
resultset
and starts fetching them one at a time.

It's important to note that server and client work phases are strictly
separate.
While the server is busy building the resultset, the client is
completely idle,
and wakes up only when the query has been fully executed on the server
side.

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

Let's see how things work on SQLite:
1. there is no server and client; everything happens within a single
process.
2. we start by preparing a SQL query that will be executed by SQLite,
but still
absolutely nothing happens.
3. at this point we need to implement a loop.
4. at each iteration the program will instruct SQLite to extract the
next row
from the resultset, which will be made available as soon as
possible.
Note that it's the program that must take care of making the best
use of
the row it has just received, otherwise it will be lost forever.
5. When SQLite signals that there are no more rows to extract, the loop
will
finally stop.

Let's jump straight to the conclusions.
On PostgreSQL, a resultset becomes accessible to the client only when
it is
complete, so it's rather trivial to find out how many rows it contains.

On SQLite, however, a resultset is something that grows dynamically one
row
at a time, in a sort of very close ping-pong game between the main
program
and SQLite.
The only possible way to find out how many rows are in the resultset is
to
query it from beginning to end. There are no shortcuts possible.

PROS AND CONS:

- SQLite's strategy makes data available immediately as retrieved.
This is precisely why SQLite often seems much more responsive than
PostgreSQL.
- Obviously, this is a relative advantage that diminishes when it
becomes
necessary to query the entire resultset.

Final conclusion: this explains why saying "other DBMSs do it" is often
misleading.
SQLite is absolutely special and there is little point in comparing it
to other DBMS
of a different class: SQLite focuses on simplicity, lightness and
speed, even at the
cost of sacrificing something in terms of completeness and ease of
use.

The important thing to understand is that SQLite is like a dirt bike:
simple,
indestructible, and easy to handle.
There's little point in complaining because it doesn't offer the
comfort and
richness of accessories typical of a luxury sedan car.

bye Sandro

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

On Thu, 14 Aug 2025 06:42:41 -0700 (PDT), Jukka Rahkonen wrote:
> Hi Sandro,
>
> Why not it is not generalizable? I wrote "WHERE..." to mean that that
> the original, perhaps complex WHERE would be there unaltered (as well
> as the tables in FROM, possible joins etc.. But maybe count("fid") is
> not good if all features in the resultset do not contain "fid".
> However, count(*) should work always.
>

Hi Jukka,

of course you are absolutely right.

My answer was aimed more at Toto, since he seems to dislike using SQL
expressions and much prefers the ease of use of GUI-based widgets.

Your Count(*) approach is perfect and surely general, but in many cases
it may require writing some non-trivial SQL code
... I'm not sure it's to Toto's taste.

bye Sandro

[Totò Fiandaca]

Thank you for the detailed response and for sharing the historical context of this design choice. I greatly appreciate that you already experimented with this approach in the initial versions.

My Perspective on the Current Compromise

I understand the logic of the speed/information compromise, however I'd like to propose an alternative reflection from a GUI user experience perspective.

GUI vs Command Line Usage Philosophy

When I use a graphical interface like SpatiaLite GUI, my mental approach differs from when I work with command line or automated scripts:

• In GUI: I expect complete and immediate information, even at the cost of a few extra seconds
• In CLI/Script: I prioritize speed and manually control what I want to display

The Problem with Partial Information

The point I'd like to emphasize is that showing "500 rows" when there are actually 50,000 doesn't provide meaningful information, but rather misleading information. It's like having a speedometer that stops at 100 km/h even when you're going faster.

If my goal is speed, I prefer adopting explicit strategies:

• Adding LIMIT 100 to the query
• Using WHERE with more restrictive filters
• Sampling data with ORDER BY RANDOM() LIMIT 1000

Compromise Proposal

Rather than completely abandoning the idea, one could consider:

1. Configurable option: A checkbox in preferences "Show total row count (may slow queries on large datasets)"
2. Smart counting:
   • If result is < 5,000 rows → automatic counting
   • If > 5,000 → show "500+ rows" with button to calculate total
3. Asynchronous counting: Show data first, then update count in background

Practical Use Case

Often my queries are exploratory in nature:

SELECT * FROM customer_table WHERE region = 'Lombardy'

I don't know if I should expect 100 or 10,000 results. This information drastically influences my subsequent decisions (export, refine query, change approach).

The button to go to the last row is useful, but requires an additional action and still needs to calculate the total anyway.

Conclusion

I fully respect the design choice and understand that not all users share this need. However, I believe a configurable option could satisfy both usage philosophies without compromising the experience for speed-oriented users.

Thank you again for your attention and for the continued development of this excellent tool.

totò

--
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 visit https://groups.google.com/d/msgid/spatialite-users/4f115e03a9af6680260e470acbfbdf3c%40lqt.it.

--

Ing. Salvatore Fiandaca
mobile.:+39 327.493.8955 
m: pigrecoin...@gmail.com

C.F.: FNDSVT71E29Z103G

P.IVA: 06597870820

membro QGIS Italia - http://qgis.it/
socio GFOSS.it - http://gfoss.it/

member OSGeo - https://www.osgeo.org/member/fiandaca/

blog: https://pigrecoinfinito.com/ 
FB: Co-admin - https://www.facebook.com/qgis.it/  

TW: https://twitter.com/totofiandaca

37°35'11.4"N 14°10'25.1"E - EPSG:4326

“Se la conoscenza deve essere aperta a tutti,

perchè mai limitarne l’accesso?” 

R. Stallman

Questo documento, allegati inclusi, contiene informazioni di proprietà di FIANDACA SALVATORE e deve essere utilizzato esclusivamente dal destinatario in relazione alle finalità per le quali è stato ricevuto. E' vietata qualsiasi forma di riproduzione o divulgazione senza l'esplicito consenso di FIANDACA SALVATORE. Qualora fosse stato ricevuto per errore si prega di informare tempestivamente il mittente e distruggere la copia in proprio possesso.

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

Hi Toto',

Please don't overuse capital letters and bold in your emails, as it
sounds
unpleasantly aggressive... like someone yelling at you.

In general, I understand and largely agree with your considerations on
the
role of GUIs, but I find one flaw: they express a decidedly one-sided
point
of view.

The SpatiaLite user community is very large and diverse, and good
solutions
are those that please everyone (or, if you prefer, those that displease
everyone
but to a tolerable degree).
Bad solutions are those that please some but are indigestible to many
others.
As always, the best path is a well-balanced compromise.

Here's a quick rundown of the SpatiaLite users I've come into contact
with over
the years (all real cases):

- Doctors, veterinarians and biologists working in the field for the
WHO in
the Amazon, Africa or Southeast Asia trying to identify the next
viral
pandemics. At first they used Excel to collect their data, then some
of
them discovered that using a lightweight Spatial DBMS like SpatiaLite
was even better, and it was something simple enough that they could
use even in the most primitive and remote situations.

- African insurance companies facing countless fraudulent claims for
damages
suffered from non-existent, purely fictional plantations.
A brilliant and imaginative French programmer solved these problems
by
unleashing many "inspectors" into the countryside and forests,
equipped
with an iPhone (obviously used and purchased for next to nothing).
These "inspectors" were in fact simply unemployed young men with no
training whatsoever, who were simply tasked with taking some
GPS-tagged photos.
Everything then ended up in a SpatiaLite DB, and on this basis of
objective evidence it became possible to reject the fraud attempts.

- A group of Florentine traffic engineers uses SpatiaLite extensively
to power their traffic models needed to regulate road and rail
circulation
in large cities and on the european railway network.
(of course, this also requires a fair amount of complex algorithms
developed in-house in Python).

- Another British engineering firm uses SpatiaLite in the energy
sector for the design of district heating networks.
Here the biggest problem is identifying the optimal route for
laying the pipes, trying to avoid hard rocky terrain with a
clear preference for clayey or sandy terrain which is much
easier (and cheaper) to excavate.
Always taking into account the constraint that it is necessary
to avoid excessively long paths because they imply the dissipation
of too much heat.
In this case too, SpatiaLite simply is the source of the essential
data to feed sophisticated mathematical models.

- I could go on with many more examples, but it would get boring.
So I'll close with the two use cases I'm most fond of:
* An Iranian elementary school teacher in a small village who
taught herself Python programming to create a sort of dynamic
atlas that she then used to teach the basics of Geography to
her pupils.
* A sprightly American computer scientist in his 80s who lives
in the Rocky Mountains. In the 1970s, he worked at IBM research
labs: now retired, he enjoys spending his time building his own
homemade satellite navigation systems (obviously based on
SpatiaLite).

- Just to finish, let me tell you my personal story.
SpatiaLite was created many years ago because I needed a simple,
lightweight tool for my professional work in the public
transportation
sector.
Over the course of my career, I've learned that a database can't be
called
"large" unless it takes up less than several GB, and that a "large
table"
is worthy of the name only when it exceeds many millions of rows.
But I've also learned that when you have to work with large volumes
of
data, speed is everything... ease of use comes later, much later.

If you've grasped the gist of these examples, you'll have noticed that
although they are very different from each other, they all have
something
in common.
These are all use cases that require good spatial data analysis
capabilities
(and maybe even a solid computer foundation) but where traditional GIS
tools never comes into play.

I speak for myself. but what I'm about to say also applies to other
cases.
In my entire professional life, I've never used ESRI software, and even
QGIS
I've only used it a couple of times, just because I needed to print
some maps.
The last time I installed QGIS on one of my PCs was back in the Windows
XP days;
almost 10 years have passed since then, and I've never had another
opportunity
to use it, yet in all that time I've worked on nothing other than
Tuscan public
transportation mapping.

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

To conclude: it seems to me that your point of view is fairly
widespread among
QGIS users or, more generally, among GIS experts.
It's an important point of view that has always been taken into
consideration
whenever possible; but it's certainly not the only possible one, and
perhaps
it's not even the most widespread.

Fortunately, we still live in a plural world where many alternative
approaches
can coexist.
I've never appreciated one-track thinking, and I appreciate it even
less in this
final phase of my life.
As long as I have the physical and intellectual strength to continue
pursuing
SpatiaLite, I'll do everything I can to preserve its respect for the
most
varied diversity, where everyone is free to explore in the most
unexpected,
possibly unconventional, directions. ;-)

I hope I've managed to explain why I don't think it's appropriate to
accept
your requests; they're certainly good for a certain context, but
they're
detrimental to many others.

bye Sandro

[Totò Fiandaca]

Dear Alessandro,
I sincerely apologize if my emails seemed aggressive with the excessive use of capitals and bold text - it was absolutely not my intention to appear rude or insistent in any way.
After reading your detailed response and the rich and varied examples of the SpatiaLite user community, I completely understand your position and fully respect your decision not to implement this feature.
The use cases you shared - from doctors in the Amazon to Florentine engineers, from the lively computer scientist in the Rocky Mountains to the Iranian teacher - paint an extraordinarily rich picture of how SpatiaLite serves such a diverse community with such varied needs. It's clear that every design choice must necessarily take into account this incredible diversity.
Your philosophy of preserving a plural approach that respects different work methodologies is admirable and demonstrates wisdom gained through years of experience in the field.
Thank you so much for taking the time to explain the context and philosophy behind SpatiaLite, and especially thank you for creating and continuing to develop this extraordinary tool that has clearly had such a positive impact on so many people in such diverse contexts.
I will continue to use SpatiaLite with great appreciation for all the work you have put into it.
Best regards and best wishes for your work,

Totò

--
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 visit https://groups.google.com/d/msgid/spatialite-users/05f115c4554f2d13b26bea99b6bb91ee%40lqt.it.