Improve core database driver and get rid of db extensions

[mukoki]

Do we really need to maintain extensions like dbquery or spatialite driver now that the core db driver support databases like oracle or spatialite. Here after is a complete answer from Jukka. Proposition is to add the capability to read non spatial tables from the core driver by adding an empty geometry column. One difficulty maybe that OpenJUMP wizard starts from geometry column metadata so that non spatial tables are even not visible.

[mukoki]

Here is Jukka's statement taken from the mailing list

Larry’s DB Query is the most robust SQL client for all databases that it support because it is so simple. It just executes the SQL query and builds a layer from the result. It does not read and resolve the metadata tables (like geometry_columns, spatial_ref_sys). Therefore it does not matter if the metadata is wrong or missing. Valid gpkg and spatialite databases should have metadata in place but sometimes it is convenient to create temporary tables simply with “create table as select something from somewhere”. That DB Query deals also with queries which do not return geometry by creating an empty geometry on-the-fly. The DB Query plugin supports also Oracle Spatial. That is a need that is year by year less important, based on the number of questions in the gis.stackexchange and the mailing lists that I follow. But my view is probably restricted based on https://blogs.oracle.com/database/oracle-databases-top-db-engines-ranking. The decrease in popularity seems to be still real https://db-engines.com/en/ranking_trend/system/Oracle.

With Geopackage and Spatialite the Run datastore query does almost the same than DB Query. The biggest difference is that the result of the query must contain geometry. It might not be very difficult to add an empty geometry like DB Query does. When is comes to user friendliness, in DB Query the user must write the path to the gpkg/sqlite file by hand. Both can read the geopackage and spatialite geometries natively, without spatialite binaries. They are naturally needed for getting access to the spatialite functions. DB Query has problems with some geometry types, I think it cannot read XYZ geometries. The OpenJUMP DB allows to save the connection with Connection Manager. That means a few additional clicks if user wants to check some geopackage/spatialite db just once, but saves some keyclicks when the same gpkg/sqlite file is used again.

The Spatialite plugin is something different. It offers a graphical user interface for the data in the spatialite database, but also for the spatialite functions. See some images and examples in http://ojwiki.soldin.de/index.php?title=OpenJUMP_with_SpatialLite#Using_SpatiaLite_with_Spatialite_Reader_Plugin. It has been a pretty nice tool for workshops about spatial SQL for beginners because it saves a lot of writing and helps with the syntax of the functions by showing the tooltip hints and by writing the template of the SQL by double clicking the function. The down side is that it requires the Spatialite binaries and it is in a need of quite heavy update for adding all the new Spatialite functions into the GUI. Last time when I did that it took about a week to add and test. It may even be hard to make the plugin to run at the moment. I have saved an old complete zip with OJ, the plugin, the spatialite binaries, and java for the workshops.

I would say that the biggest problem with Spatialite reader plugin is that it is written for a wrong database. It should offer the menu access to PostGIS functions with all the helper features. Oracle would be a nice pair with its own spatial functions with peculiar syntax.

The OpenJUMP wizard reads spatial databases as they are supposed to be read: by starting from the metadata. Only tables which are registered into geometry_columns are available and the use of SQL is limited to filtering with “where”. Fortunately the same connections are available automatically for Run datastore query if users just know that.

My conclusion is that the Spatialite reader plugins has certainly very few users, it is difficult to install, and it would require a face lift. So perhaps it may go. DB Query might be good to keep because of Oracle support but for gpkg/spatialite it is not necessary. And it would be nice if the native OpenJUMP datastore could be made to handle the queries that does not return geometry.

[jratike80]

One difficulty maybe that OpenJUMP wizard starts from geometry column metadata so that non spatial tables are even not visible.

First step could be to add the geometry column and empty geometries to the results of Run Datastore Query if needed. That tool does not read any metadata. Now query select ST_GeomFromText('GEOMETRYCOLLECTION EMPTY') as geometry, 'foo' as attr; works fine but select 'foo' as attr; gives an error java.lang.Exception: java.lang.Exception: Result Set Must Have a Geometry Column

[edeso]

what is the status of this?

i could imagine to add a 'show tables without geometry' checkbox, that would enable listing and loading (with empty geometry).