Hi Michaël,
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.
-Jukka Rahkonen-
Lähettäjä: Michaud Michael <m.michael.mich...@orange.fr>
Lähetetty: sunnuntai 25. huhtikuuta 2021 20.52
Vastaanottaja: OpenJump develop and use <jump-pilot-devel@lists.sourceforge.net>
Aihe: [JPP-Devel] OpenJUMP and spatialite/geopackage
Hi Jumpers,
I think we have 3 options to read spatialite : OpenJUMP wizard, Larry's dbquery and spatialite plugin. I think we should put our work on one of them, but as I'm not familiar with spatialite/geopackage, I'd like to know which important capabilities dbquery and spatialite have to offer which are not present in OpenJUMP core.
Thank you,
Michaël
