Aw: Re: Spatialite Integration in Cayenne

Hi Andrus,
thanks for your help. I made some progress by adding an own SpatialiteProcessor. But now a new challenge is coming up.
Spatialite forces to define a SRID within a GeomFromText-Functioncall.

 
The current wrapInFunction-method is implemented like this:
 
"INSERT INTO tabname (geom) VALUES (GeomFromText (?))";
 
To put the SRID inside the WRT-String is not working since the SQL engine is unaware that we intend using a SQL function.
Consequently it will handle the corresponding argument just as a plain text string.
Next an example insert query (SRID = 4236) that should work:
 
"INSERT INTO tabname (geom) VALUES (GeomFromText (?,4236))";
"INSERT INTO tabname (geom) VALUES (GeomFromText (?, ?),4236)"

 
Is it possible to add a second numeric parameter to the prepared statement?
 
Kind regards,
Max
 

Gesendet: Sonntag, 24. Januar 2021 um 08:17 Uhr
Von: "Andrus Adamchik" <[email protected]>
An: [email protected]
Betreff: Re: Spatialite Integration in Cayenne
Hi Inayah,

As you noticed, spatial features are still new in Cayenne, and we will need to fill more than a few gaps. So thanks for your feedback. This will help us to prioritize our effort in this area.

> WKT wrapper were added to the MySQLTreeProcessor and PostgreSQLTreeProcessor. Both Processors extend a TypeAwareSQLTreeProcessor and and add the "ST_"-Convert commands as required. In contrast the current SQLiteTreeProcessor extends BaseSQLTreeProcessor.

Good point.

@Nikita: Is there a reason not to use TypeAwareSQLTreeProcessor as a superclass in all adapters? IIRC there's a minor performance hit, but looks like still worth it.

Andrus

> On Jan 23, 2021, at 11:18 PM, Inayah Max <[email protected]> wrote:
>
> Hi all,
> my issue is related to the recently added support for geospatial types in Cayenne. As I understand it, Mysql and Postgres spatial extensions are already integrated in 4.2M2.
> This is great but doesn't fit to my tech stack. My lightweighted application has to use a filebased database (spatialite) and can't rely on a server based solution.
> Spatialite is an SQlite extention that adds spatial functionality to SQLite in the same way like Postgis is doing for Postgres.
> My current progress is that Cayenne can use connect to a spatialite database via JDBC (using jdbcUrl: jdbc:sqlite:file.db?enable_load_extension=true and SQLSelect.dataRowQuery("SELECT load_extension('mod_spatialite');").select(context);). But all queries fail since the ST-Functions are not implemented yet.
>
> When looking at Cayenne spatial implementation, WKT wrapper were added to the MySQLTreeProcessor and PostgreSQLTreeProcessor.
> Both Processors extend a TypeAwareSQLTreeProcessor and and add the "ST_"-Convert commands as required.
> In contrast the current SQLiteTreeProcessor extends BaseSQLTreeProcessor. Having no registerColumnProcess, the WKT convert can't take place in the same way like before.
> Let me know if someone has an idea how to overcome this problem.
> Thanks.