edgar.sol...@web.de wrote: 28.9. 2020 23.19 > On 28.09.2020 21:46, Rahkonen Jukka (MML) wrote: >> 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.
> have to check what DbQuery does. you mean it checks if it is a blob and if so > parse it to find what kind of? Exactly. There are excellent comments with links to specifications in the code. We can forget FDO, it is so rare. https://sourceforge.net/p/jumpdbqplugin/code/ci/default/tree/src/main/java/org/freevoice/jumpdbqueryextension/spatialite/JumpSpatialiteDbQuery.java >> However, it may get complicated to support this in OJ datastores either for >> users, or OpenJUMP developers, or both. > why? Spatialite is older than geopackage and Spatialite functions are dealing internally with spatialite BLOBs. Functions work as spatialite in - spatialite out. If the native BLOB is also of spatialite type then there is nothing special. But if native BLOB is a geopackage BLOB then the query must contain casts in and out SELECT AsGPB(ST_SomeFunction(GeomFromGPB(gpkg_geometry)))... There is also "CastAutomagic" that is a convenience function that accepts both spatialite and geopackage blobs as input and outputs spatialite. I suppose that most software and users who use geopackage to not know that all the Spatialite functions could be available for them with relatively small pain. >> The Spatialite datastore supports both SpatiaLite geometries and GeoPackage >> geometries but it selects which one when the connection is created. > is this spec or just the way it is? anyway. we talk about columns not in the > table hence not marked via feature schema as geometry of some type. so in > case the col is not in the table metadata and response metadata says it's a > blob it makes sense to detect it's type. It is just so that Spatialite and geopackage are both SQLite databases and they have much in common. It was possible to enhance the existing SQLite/Spatialite drivers in OJ datastores and DB Query plugin to handle also geopackage geometries. What is nice is that OpenJUMP can extract standard well known binary blobs from geopackage blob with native code without external libraries. >> However, Spatialite functions can make casts between those blob types and >> the usage is not the most obvious. > sure but if this is not "selected as" to a col name of a matching type that > is currently unreadable. Right, and in this situation the DB Query plugin is checking if the blobs happen to contain some recognized geometry encodings with "private Geometry getNativeGeometryFromBlob(byte[] blobAsBytes)". >> 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. > what do you mean here? As I wrote before, when we deal with geopackage blobs the query to return a centroid is SELECT AsGPB(ST_Centroid(GeomFromGPB(gpkg_geometry)))... while in spatialite case the query is simply SELECT ST_Centroid(geom)... This may not be obvious for OpenJUMP users because the datastore connection to geopackage and Spatialite appear similar. >> 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. > so you're in favor of autodetecting as described above? Yes. >>There would be still some corner cases left (select geom as geometry1, >>ST_Centroid(geom) as geometry2... > in theory our features may hold multiple geometries. just one can be shown > though and the second would be like some object attribute. not sure what'd be > a use case for that. -Jukka- ..ede > > -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/co >> m >> /vividsolutions/jump/datastore/spatialite/SpatialiteValueConverterFac >> t >> 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$TaskWra >>> p >>> 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.RunDatastoreQu >>> e >>> 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 > _______________________________________________ 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
