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? > However, it may get complicated to support this in OJ datastores either for > users, or OpenJUMP developers, or both. why? > 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. > 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. > 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? > 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? >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. ..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/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 > _______________________________________________ Jump-pilot-devel mailing list Jump-pilot-devel@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/jump-pilot-devel
