Yes, the only robust way to deal with SpatiaLite and GeoPackage geometries would probably be to use the duck test like our DB Query Plugin does. If the result is a blob that like a SpatiaLite BLOB or like a GeoPackage BLOB, then it probably is so. However, it may get complicated to support this in OJ datastores either for users, or OpenJUMP developers, or both. The Spatialite datastore supports both SpatiaLite geometries and GeoPackage geometries but it selects which one when the connection is created. However, Spatialite functions can make casts between those blob types and the usage is not the most obvious.
If datastore driver is prepared to receive GeoPackage blob then user should know how to use some Spatialite specialities in SQL query. At least castautomagic and AsGPB at least http://www.gaia-gis.it/gaia-sins/spatialite-sql-latest.html. If datastore is recognized to be of the Spatialite type and OJ awaits Spatialite geometries then it would be more simple to use the functions because casts would not be needed. I am not sure how much we should work with this. We have the robust DB Query that works fine (despite with XYZ geometries but that's another thing). It would be nice if OpenJUMP could recognize the GeoPackage BLOB as geometry when query returns such, or similarly the Spatialite BLOB. There would be still some corner cases left (select geom as geometry1, ST_Centroid(geom) as geometry2...). -Jukka- -----Alkuperäinen viesti----- Lähettäjä: edgar.sol...@web.de <edgar.sol...@web.de> Lähetetty: maanantai 28. syyskuuta 2020 20.44 Vastaanottaja: jump-pilot-devel@lists.sourceforge.net Aihe: Re: [JPP-Devel] Run datastore query too tied to schema with Spatialide DS hey Jukka, looked a bit deeper. sqlite is not really tagging cols retrieved in the metadata apart from known col types (eg. text,blob,...) . it obviously is is totally ignorant of geometries. as a workaround we could "transport" a type information in the col name which is then used in OJ only. eg. SELECT AsWkt(GeomFromText('POINT (1 1)')) as 'geometry[type=wkt]' but it's still hackish and in no way intuitiv and needs to be documented well. how do other spatialite enabled frontends deal with that? ..ede On 28.09.2020 15:11, edgar.sol...@web.de wrote: > well, at least spatialite works again :)) yayhh. > > wrt. the issue below. > https://sourceforge.net/p/jump-pilot/code/HEAD/tree/core/trunk/src/com > /vividsolutions/jump/datastore/spatialite/SpatialiteValueConverterFact > ory.java#l47 is where the column type is "detected" and it uses the > column name to do so. so fetching it with a different column name (eg. > geometry instaed of geom) will obviously not work. not sure how this can be > solved differently or what the appropriate standards to handle this are. > > just committed r6555. now statements "reusing" the coltype of an > existing table column work for me eg. (note the single quotes) > > SELECT ST_Centroid(geom) as 'test.geom' from test; SELECT > ST_GeomFromText('POINT (1 1)') as 'test.geom' > > providing there is a table test with a SPATIALITE typed geom column. > > ..ede > > ps. any success on providing a new OGC API testbed? > > On 9/28/2020 14:06, Rahkonen Jukka (MML) wrote: >> Hi, >> >> When using Spatialite/Geopackage as data source OpenJUMP seems to >> check the schema too literally. While this works SELECT geom FROM >> test LIMIT 1; the same query with a simple alias gives an error >> SELECT geom AS geometry FROM test LIMIT 1; >> >> java.lang.Exception: java.lang.Exception: Result Set Must Have a Geometry >> Column >> at >> com.vividsolutions.jump.workbench.ui.plugin.datastore.RunDatastoreQueryPlugIn.createLayer(RunDatastoreQueryPlugIn.java:92) >> at >> com.vividsolutions.jump.workbench.ui.plugin.datastore.RunDatastoreQueryPlugIn.createLayerable(RunDatastoreQueryPlugIn.java:41) >> at >> com.vividsolutions.jump.workbench.ui.plugin.datastore.AbstractAddDatastoreLayerPlugIn.run(AbstractAddDatastoreLayerPlugIn.java:33) >> at >> com.vividsolutions.jump.workbench.ui.task.TaskMonitorManager$TaskWrap >> per.run(TaskMonitorManager.java:151) >> Caused by: java.lang.Exception: Result Set Must Have a Geometry Column >> at >> com.vividsolutions.jump.datastore.spatialite.SpatialiteDSConnection.executeAdhocQuery(SpatialiteDSConnection.java:90) >> at >> com.vividsolutions.jump.datastore.spatialdatabases.SpatialDatabasesDSConnection.execute(SpatialDatabasesDSConnection.java:56) >> at >> com.vividsolutions.jump.workbench.ui.plugin.datastore.RunDatastoreQue >> ryPlugIn$RunnableQuery.run(RunDatastoreQueryPlugIn.java:134) >> >> >> The above query works with PostGIS datastore. Renaming geometry field >> is not the real problem but I think that the same part of code makes >> it impossible to utilize the SpatiaLite functions like SELECT >> ST_Centroid(geom) from test; or just playing with on-the-fly >> generated geometries like in SELECT ST_GeomFromText('POINT (1 1)') >> >> The problem does not seem to be in the name of the geometry because SELECT >> ST_Centroid(geom) as geom fails as well. >> >> -Jukka Rahkonen- >> >> >> >> _______________________________________________ >> Jump-pilot-devel mailing list >> Jump-pilot-devel@lists.sourceforge.net >> https://lists.sourceforge.net/lists/listinfo/jump-pilot-devel >> > _______________________________________________ Jump-pilot-devel mailing list Jump-pilot-devel@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/jump-pilot-devel _______________________________________________ Jump-pilot-devel mailing list Jump-pilot-devel@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/jump-pilot-devel
