Re: [JPP-Devel] Run datastore query too tied to schema with Spatialide DS
hmmm, still there seems to be a difference as SpatialDBDS can read those GPKG geometries, that DBQuery fails with Caused by: com.vividsolutions.jts.io.ParseException: Unknown WKB type 233 at com.vividsolutions.jts.io.WKBReader.readGeometry(WKBReader.java:223) at com.vividsolutions.jts.io.WKBReader.read(WKBReader.java:152) at com.vividsolutions.jts.io.WKBReader.read(WKBReader.java:133) ..ede On 9/29/2020 21:05, Michaud Michael wrote: > Hi Jukka, > > wrt. z/m reading, the patch I had submitted to JTS has been included in > locationtech repo just before the 1.5 release. So you're right, with our 1.14 > vividsolutions version, we probably can't read both forms of WKB. Hopefully, > it > will be easier after our migration to OJ 2.0. > > Michaël > >> envoyé : 29 septembre 2020 à 17:34 >> de : "Rahkonen Jukka (MML)" >> à : OpenJump develop and use , Larry >> Reeder >> objet : Re: [JPP-Devel] Run datastore query too tied to schema with >> Spatialide DS >> >> >> Hi, >> >> Michaël must remember this better, but JTS did not understand the ISO codes >> for 3 and 4 dimensional geometries. This is probably fixed in the >> locationtech >> version. See >> https://lists.osgeo.org/pipermail/geos-devel/2013-December/006757.html >> >> So it may be that once we move to GitHub and to new JTS version, and DB Query >> plugin gets possible fixes needed for the new JTS, it just starts to work >> with >> XYZ(M) geometries. Until that those who know can cast geometries into XY >> with SQL. >> >> -Jukka- >> >> -Jukka Rahkonen- >> >> >> -----Alkuperäinen viesti----- >> Lähettäjä: edgar.sol...@web.de >> Lähetetty: tiistai 29. syyskuuta 2020 17.36 >> Vastaanottaja: jump-pilot-devel@lists.sourceforge.net; Larry Reeder >> >> Aihe: Re: [JPP-Devel] Run datastore query too tied to schema with Spatialide >> DS >> >> On 28.09.2020 21:46, Rahkonen Jukka (MML) wrote: >> >>> We have the robust DB Query that works fine (despite with XYZ geometries but >>> that's another thing). >>> >> yeah, noticed that DBQuery choked on some geometries from a gpkg test file. >> >> Jukka, would you mind providing some test files for geometry blobs that >> currently don't work with DBQuery so we can ask Larry to enhance the >> extension? i'll cc him, so Larry, if you're not interested to maintain it >> anymore please speak up :) >> >> thx.. ede >> >> >> ___ >> 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
Re: [JPP-Devel] Run datastore query too tied to schema with Spatialide DS
Hi Jukka,wrt. z/m reading, the patch I had submitted to JTS has been included in locationtech repo just before the 1.5 release. So you're right, with our 1.14 vividsolutions version, we probably can't read both forms of WKB. Hopefully, it will be easier after our migration to OJ 2.0.Michaëlenvoyé : 29 septembre 2020 à 17:34de : "Rahkonen Jukka (MML)" à : OpenJump develop and use , Larry Reeder objet : Re: [JPP-Devel] Run datastore query too tied to schema with Spatialide DSHi,Michaël must remember this better, but JTS did not understand the ISO codes for 3 and 4 dimensional geometries. This is probably fixed in the locationtech version. See https://lists.osgeo.org/pipermail/geos-devel/2013-December/006757.htmlSo it may be that once we move to GitHub and to new JTS version, and DB Query plugin gets possible fixes needed for the new JTS, it just starts to work with XYZ(M) geometries. Until that those who know can cast geometries into XY with SQL.-Jukka--Jukka Rahkonen--Alkuperäinen viesti-Lähettäjä: edgar.sol...@web.de Lähetetty: tiistai 29. syyskuuta 2020 17.36Vastaanottaja: jump-pilot-devel@lists.sourceforge.net; Larry Reeder Aihe: Re: [JPP-Devel] Run datastore query too tied to schema with Spatialide DSOn 28.09.2020 21:46, Rahkonen Jukka (MML) wrote:We have the robust DB Query that works fine (despite with XYZ geometries but that's another thing).yeah, noticed that DBQuery choked on some geometries from a gpkg test file.Jukka, would you mind providing some test files for geometry blobs that currently don't work with DBQuery so we can ask Larry to enhance the extension? i'll cc him, so Larry, if you're not interested to maintain it anymore please speak up :)thx.. ede___Jump-pilot-devel mailing listJump-pilot-devel@lists.sourceforge.nethttps://lists.sourceforge.net/lists/listinfo/jump-pilot-devel___Jump-pilot-devel mailing listJump-pilot-devel@lists.sourceforge.nethttps://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
Re: [JPP-Devel] Run datastore query too tied to schema with Spatialide DS
On 29.09.2020 19:45, Rahkonen Jukka (MML) wrote: > select AsGPB(casttoxy(castautomagic(geom))) from test limit 1; right select casttoxy(castautomagic(geom)) from random_points works for my test gpkg data set. wrt. DBQuery ..ede ___ Jump-pilot-devel mailing list Jump-pilot-devel@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/jump-pilot-devel
Re: [JPP-Devel] Run datastore query too tied to schema with Spatialide DS
Hi, Michaël must remember this better, but JTS did not understand the ISO codes for 3 and 4 dimensional geometries. This is probably fixed in the locationtech version. See https://lists.osgeo.org/pipermail/geos-devel/2013-December/006757.html So it may be that once we move to GitHub and to new JTS version, and DB Query plugin gets possible fixes needed for the new JTS, it just starts to work with XYZ(M) geometries. Until that those who know can cast geometries into XY with SQL. -Jukka- -Jukka Rahkonen- -Alkuperäinen viesti- Lähettäjä: edgar.sol...@web.de Lähetetty: tiistai 29. syyskuuta 2020 17.36 Vastaanottaja: jump-pilot-devel@lists.sourceforge.net; Larry Reeder Aihe: Re: [JPP-Devel] Run datastore query too tied to schema with Spatialide DS On 28.09.2020 21:46, Rahkonen Jukka (MML) wrote: > We have the robust DB Query that works fine (despite with XYZ geometries but > that's another thing). yeah, noticed that DBQuery choked on some geometries from a gpkg test file. Jukka, would you mind providing some test files for geometry blobs that currently don't work with DBQuery so we can ask Larry to enhance the extension? i'll cc him, so Larry, if you're not interested to maintain it anymore please speak up :) thx.. ede ___ 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
Re: [JPP-Devel] Run datastore query too tied to schema with Spatialide DS
On 28.09.2020 21:46, Rahkonen Jukka (MML) wrote: > We have the robust DB Query that works fine (despite with XYZ geometries but > that's another thing). yeah, noticed that DBQuery choked on some geometries from a gpkg test file. Jukka, would you mind providing some test files for geometry blobs that currently don't work with DBQuery so we can ask Larry to enhance the extension? i'll cc him, so Larry, if you're not interested to maintain it anymore please speak up :) thx.. ede ___ Jump-pilot-devel mailing list Jump-pilot-devel@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/jump-pilot-devel
Re: [JPP-Devel] Run datastore query too tied to schema with Spatialide DS
On 9/29/2020 14:22, Michaud Michael wrote: > Ede, > > > OJ can't support multiple geometries and it would be a difficult task to make > this change. > > >well, in theory it can. the model as is merely limits geometry to be _only > one_ of the attributes. as Geometry is a proper attribute type we can easily > add > more than one, they won't be displayed though ;) > > Actually, FeatureSchema.addAttribute check that no Geometry geometryIndex is > -1 > (and set geometryIndex to a index > -1 if non exist). not saying there are no obstacles. just saying there is nothing that prevents us from implementing it. > Object type can be used > > how about a new BLOB attribute type, signifying that the column contains plain > bytes, in case blobs are delivered by the underlying data reader? > > What would be the advantage over Object ? your mail quoting seems to be broken agn ;9 the advantage would be the explicit typed binary nature, while for an Object we do not know how the attribute backend handles it internally. actually afaics we use Object if we get something from a data source that does not match a known data type. > - to preserve original data as much as possible, but will generally not > survive > a complete roundtrip (maybe a more specific serialization format could achieve > this goal, but with format like shapefile, we will rapidly encounter the 255 > char problem). > > if it is not saveable w/o data loss the write should simply err out and give > the > user a chance to save it another way. as much as i understand the industry > standardness of the shapefile format, we shouldn't strive to make it limiting > factor for OJ data formats. > > I think that in most drivers, Object type is converted to String with > toString() > method, which is not so bad. well, it's bad in case of BLOBs > Probably a warning would be appreciated. Error would > probably force the user to remove the column before saving without offering > better alternative. or saving to another format or converting the blob into WKT and save that to CSV. the possibilities are endless :)) > - to offer plugin developpers a way to store structured data for in-memory > usage > (e.g. multiscale display as suggested by Jukka). I'm not aware of a concrete > use-case though. > > watcha mean by "structured data for in-memory usage" ?.. ede > > E.g. computing different simplified geometries for different scale > representoins > in named attributes g1, g2, g3. Could not be saved in file, but could be used > by > a plugin aware of these attributes and avoid on the fly computation. Could > also > read image from a read-only database to display it on screen... Not sure these > example are relevant, just ideas. ic. yeah let's table that thought. i'm merely interested in not destroying additional geometry columns in datasets or at least warn that they are there or ignore them with a warning, so the user dosn't get a chance to corrupt them, if that makes sense. ..ede ___ Jump-pilot-devel mailing list Jump-pilot-devel@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/jump-pilot-devel
Re: [JPP-Devel] Run datastore query too tied to schema with Spatialide DS
Ede,OJ can't support multiple geometries and it would be a difficult task to makethis change.>well, in theory it can. the model as is merely limits geometry to be _only one_ of the attributes. as Geometry is a proper attribute type we can easily add more than one, they won't be displayed though ;)Actually, FeatureSchema.addAttribute check that no Geometry geometryIndex is -1 (and set geometryIndex to a index > -1 if non exist).Object type can be usedhow about a new BLOB attribute type, signifying that the column contains plain bytes, in case blobs are delivered by the underlying data reader?What would be the advantage over Object ?- to preserve original data as much as possible, but will generally not survivea complete roundtrip (maybe a more specific serialization format could achievethis goal, but with format like shapefile, we will rapidly encounter the 255char problem).if it is not saveable w/o data loss the write should simply err out and give the user a chance to save it another way. as much as i understand the industry standardness of the shapefile format, we shouldn't strive to make it limiting factor for OJ data formats.I think that in most drivers, Object type is converted to String with toString() method, which is not so bad.Probably a warning would be appreciated. Error would probably force the user to remove the column before saving without offering better alternative.- to offer plugin developpers a way to store structured data for in-memory usage(e.g. multiscale display as suggested by Jukka). I'm not aware of a concreteuse-case though.watcha mean by "structured data for in-memory usage" ?.. edeE.g. computing different simplified geometries for different scale representoins in named attributes g1, g2, g3. Could not be saved in file, but could be used by a plugin aware of these attributes and avoid on the fly computation. Could also read image from a read-only database to display it on screen... Not sure these example are relevant, just ideas.Michaël___Jump-pilot-devel mailing listJump-pilot-devel@lists.sourceforge.nethttps://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
Re: [JPP-Devel] Run datastore query too tied to schema with Spatialide DS
On 9/29/2020 13:00, Michaud Michael wrote: > Hi, > > OJ can't support multiple geometries and it would be a difficult task to make > this change. well, in theory it can. the model as is merely limits geometry to be _only one_ of the attributes. as Geometry is a proper attribute type we can easily add more than one, they won't be displayed though ;) > Object type can be used how about a new BLOB attribute type, signifying that the column contains plain bytes, in case blobs are delivered by the underlying data reader? > - to preserve original data as much as possible, but will generally not > survive > a complete roundtrip (maybe a more specific serialization format could achieve > this goal, but with format like shapefile, we will rapidly encounter the 255 > char problem). if it is not saveable w/o data loss the write should simply err out and give the user a chance to save it another way. as much as i understand the industry standardness of the shapefile format, we shouldn't strive to make it limiting factor for OJ data formats. > - to offer plugin developpers a way to store structured data for in-memory > usage > (e.g. multiscale display as suggested by Jukka). I'm not aware of a concrete > use-case though. watcha mean by "structured data for in-memory usage" ?.. ede ___ Jump-pilot-devel mailing list Jump-pilot-devel@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/jump-pilot-devel
Re: [JPP-Devel] Run datastore query too tied to schema with Spatialide DS
Hi,OJ can't support multiple geometries and it would be a difficult task to make this change.Object type can be used- to preserve original data as much as possible, but will generally not survive a complete roundtrip (maybe a more specific serialization format could achieve this goal, but with format like shapefile, we will rapidly encounter the 255 char problem).- to offer plugin developpers a way to store structured data for in-memory usage (e.g. multiscale display as suggested by Jukka). I'm not aware of a concrete use-case though.Michaëlenvoyé : 29 septembre 2020 à 12:30de : edgar.sol...@web.deà : jump-pilot-devel@lists.sourceforge.netobjet : Re: [JPP-Devel] Run datastore query too tied to schema with Spatialide DSOn 9/29/2020 12:14, Rahkonen Jukka (MML) wrote:edgar.sol...@web.de wrote:29.9. 2020 13.01On 9/29/2020 11:57, Rahkonen Jukka (MML) wrote:>>> select geom,>>> ST_AsText(ST_Centroid(geom)) as centroid from test limit 1; >> do we currently warn/err out if someone actually selects two geometry columns? should we auto-convert to WKT additional geometry columns?The above query without ST_AsText gives me a field with datatype "Object" and it contains value [B@11031df7that's because everything without datatype is loaded as Object type, which default attrib representation is a simpy toString(), which "surprise" works well with Strings ;)Perhaps it is good so? I have no idea about what to do with such object and if it round trips nicely in save-reload cycle with our supported file formats. I guess that object could as well be a jpeg image or any other blob.yeah, more thinking of applying the blob-autodetecting here, in case we come around to it. keep in mind, we are talking merely representation in the attrib table. it'd be still backed by the blob based Object. and you are are right the question remains, how our writers/readers handle blobs, especially those that are text based (like GeoJSON). sure we could base64 encode it or such, but what i am saying is, i'm pretty sure it currently will blow up ;)..ede___Jump-pilot-devel mailing listJump-pilot-devel@lists.sourceforge.nethttps://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
Re: [JPP-Devel] Run datastore query too tied to schema with Spatialide DS
Hi,>>what about the multiple geometry column issue? what would be a use case for that and how would you suggest to present those in OJ? ..edeFor postgis, as far as I remember, we read the first geometry column as geometry and following geometry columns as String or Object (don't remember).In the RunQueryPlugin, if I need the second geometry column of a table having geom1, geom2, I write :select geom2 as geom, * from table(or the exact list of columns to avoid duplication of geom2 column)Michaëlenvoyé : 29 septembre 2020 à 10:57de : edgar.sol...@web.deà : jump-pilot-devel@lists.sourceforge.netobjet : Re: [JPP-Devel] Run datastore query too tied to schema with Spatialide DSmorning Jukka,On 9/29/2020 9:15, Rahkonen Jukka (MML) wrote:>> 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.what about the multiple geometry column issue? what would be a use case for that and how would you suggest to present those in OJ? ..ede___Jump-pilot-devel mailing listJump-pilot-devel@lists.sourceforge.nethttps://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
Re: [JPP-Devel] Run datastore query too tied to schema with Spatialide DS
On 9/29/2020 12:14, Rahkonen Jukka (MML) wrote: > edgar.sol...@web.de wrote: > 29.9. 2020 13.01 > > On 9/29/2020 11:57, Rahkonen Jukka (MML) wrote: >>> select geom, >>> ST_AsText(ST_Centroid(geom)) as centroid from test limit 1; > >> do we currently warn/err out if someone actually selects two geometry >> columns? should we auto-convert to WKT additional geometry columns? > > The above query without ST_AsText gives me a field with datatype "Object" and > it contains value [B@11031df7 that's because everything without datatype is loaded as Object type, which default attrib representation is a simpy toString(), which "surprise" works well with Strings ;) > Perhaps it is good so? I have no idea about what to do with such object and > if it round trips nicely in save-reload cycle with our supported file > formats. I guess that object could as well be a jpeg image or any other blob. yeah, more thinking of applying the blob-autodetecting here, in case we come around to it. keep in mind, we are talking merely representation in the attrib table. it'd be still backed by the blob based Object. and you are are right the question remains, how our writers/readers handle blobs, especially those that are text based (like GeoJSON). sure we could base64 encode it or such, but what i am saying is, i'm pretty sure it currently will blow up ;) ..ede ___ Jump-pilot-devel mailing list Jump-pilot-devel@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/jump-pilot-devel
Re: [JPP-Devel] Run datastore query too tied to schema with Spatialide DS
edgar.sol...@web.de wrote: 29.9. 2020 13.01 On 9/29/2020 11:57, Rahkonen Jukka (MML) wrote: >> select geom, >> ST_AsText(ST_Centroid(geom)) as centroid from test limit 1; > do we currently warn/err out if someone actually selects two geometry > columns? should we auto-convert to WKT additional geometry columns? The above query without ST_AsText gives me a field with datatype "Object" and it contains value [B@11031df7 Perhaps it is good so? I have no idea about what to do with such object and if it round trips nicely in save-reload cycle with our supported file formats. I guess that object could as well be a jpeg image or any other blob. -Jukka- ..ede ___ 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
Re: [JPP-Devel] Run datastore query too tied to schema with Spatialide DS
On 9/29/2020 11:57, Rahkonen Jukka (MML) wrote: >> morning Jukka, >> On 9/29/2020 9:15, Rahkonen Jukka (MML) wrote: 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. >> what about the multiple geometry column issue? what would be a use case for >> that and how would you suggest to present those in OJ? ..ede > Use cases that I know are like having cities as polygons and points for > rendering at different scales, or having accurate/generalized geometries for > the same purpose. The same result can be achieved by having multiple layers > though there is then unnecessary multiplication of attributes as well. I > would wait until someone presents a really cool use case and pull request > about multiple geometry fields in OpenJUMP. Until that I suppose people feel > happy if this works also in the future as it works now and saves processed > geometry as WKT into text field. Example tested with Spatialite > > select geom, > ST_AsText(ST_Centroid(geom)) as centroid > from test limit 1; do we currently warn/err out if someone actually selects two geometry columns? should we auto-convert to WKT additional geometry columns? ..ede ___ Jump-pilot-devel mailing list Jump-pilot-devel@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/jump-pilot-devel
Re: [JPP-Devel] Run datastore query too tied to schema with Spatialide DS
edgar.sol...@web.de wrote 29.9. 2020 11.57 > morning Jukka, > On 9/29/2020 9:15, Rahkonen Jukka (MML) wrote: >>> 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. > what about the multiple geometry column issue? what would be a use case for > that and how would you suggest to present those in OJ? ..ede Use cases that I know are like having cities as polygons and points for rendering at different scales, or having accurate/generalized geometries for the same purpose. The same result can be achieved by having multiple layers though there is then unnecessary multiplication of attributes as well. I would wait until someone presents a really cool use case and pull request about multiple geometry fields in OpenJUMP. Until that I suppose people feel happy if this works also in the future as it works now and saves processed geometry as WKT into text field. Example tested with Spatialite select geom, ST_AsText(ST_Centroid(geom)) as centroid from test limit 1; -Jukka- ___ Jump-pilot-devel mailing list Jump-pilot-devel@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/jump-pilot-devel
Re: [JPP-Devel] Run datastore query too tied to schema with Spatialide DS
morning Jukka, On 9/29/2020 9:15, Rahkonen Jukka (MML) wrote: >> 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. what about the multiple geometry column issue? what would be a use case for that and how would you suggest to present those in OJ? ..ede ___ Jump-pilot-devel mailing list Jump-pilot-devel@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/jump-pilot-devel
Re: [JPP-Devel] Run datastore query too tied to schema with Spatialide DS
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 > 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 obviou
Re: [JPP-Devel] Run datastore query too tied to schema with Spatialide DS
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
> 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.
Re: [JPP-Devel] Run datastore query too tied to schema with Spatialide DS
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
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.
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/SpatialiteValueConverterFactory.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$TaskWrapper.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.RunDatastoreQueryPlugIn$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
Re: [JPP-Devel] Run datastore query too tied to schema with Spatialide DS
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/SpatialiteValueConverterFactory.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$TaskWrapper.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.RunDatastoreQueryPlugIn$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
[JPP-Devel] Run datastore query too tied to schema with Spatialide DS
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$TaskWrapper.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.RunDatastoreQueryPlugIn$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
