PROJ SQLite database problems using new 5.0.0 Release in Java

[HansGIS]

Hello,

first I want to congratulate and express my gratitude for the long awaited new release!

Usecase:

I have been using the SpatiaLite v4.x.x x64 libs in Java in conjunction with sqlite-jdbc v3.32.3.2 on Windows 10 for a long time now.

Now I updated everything with the new SpatiaLite v5.0.0 x64 release and first it continued working as expected. Only after I tried "ST_Transform()" a geometry. the SQL querry failed with following error message:

• [SQLITE_ERROR] SQL error or missing database (ST_Transform exception - PROJ reports "proj_create: no database context specified".)

Debugging steps:

I cheked that the 'proj.db' file provided in the 'spatialite-loadable-modules' archive is positioned in the same folder as the SpatiaLite libs loaded in Java. And then I tried debugging with the new SQL functions: "PROJ_GetDatabasePath()" and "PROJ_SetDatabasePath()"

• Before I do anything else on the new  SpatiaLite database  I run "SELECT InitSpatialMetaData(1);". It returns 1.
• The Querry "SELECT PROJ_GetDatabasePath();" returned NULL.
• Then I tried setting the absolute path to the proj.db "SELECT PROJ_SetDatabasePath('C:\Temp\proj.db');" and the querry returned the Path itself, but the transformations still failed with the same error message.

My Questions:

• How to set the PROJ-SQLite-database path for spatialite libs loaded in Java correctly?
• In the case I can get everything working I'd like to set the path relative. But I'm not quite sure where the source path for the relative path, for my Java usecase, is supposed to be. Does anybody know?

If needed I can provide more information.

[a.fu...@lqt.it]

On Fri, 28 Aug 2020 08:03:48 -0700 (PDT), HansGIS wrote:
> I have been using the _SpatiaLite _v4.x.x x64 libs in Java in
> conjunction with _sqlite-jdbc_ v3.32.3.2 on Windows 10 for a long
> time
> now.
>

> [SQLITE_ERROR] SQL error or missing database (ST_Transform
> exception - PROJ reports "proj_create: no database context

> specified".)_
>

Hi Hans,

please read my replies under each question


> I cheked that the '_proj.db_' file provided in the

> 'spatialite-loadable-modules' archive is positioned in the same
> folder
> as the SpatiaLite libs loaded in Java.
>

sorry, but this can never work.
this is what says the documentation about Windows:

"the first place where proj.db will be searched is the same folder form
where the EXE binary was initially loaded"

note that in your case the EXE binary is the Virtual Java Machine;
the folders from where your process has possibly loaded additional
dynamic modules have absolutely no influence.

accordingly to the doc, alternative "well known" locations that will
be always searched on Windows are:

C:\Users\Public\spatialite\proj\proj.db
or
C:\Users\<username>\spatialite\proj\proj.db
(in which you are abviously expected to correctly set your username)

> Before I do anything else on the new SpatiaLite database I run

> "_SELECT InitSpatialMetaData(1);_". It returns _1_.
>

note that this is now obsolete, and it still continues to be supported
just for historical reasons.
the correct way for fully initializing a 5.0.0 database is by calling:

SELECT InitSpatialMetaDataFull(1);


> The Querry "_SELECT PROJ_GetDatabasePath();_" returned _NULL_.
>

that's rather obvious, because proj.db wasn't found in none of the
"well known" locations.


> Then I tried setting the absolute path to the proj.db "_SELECT
> PROJ_SetDatabasePath('C:\Temp\proj.db');_" and the querry returned

> the
> Path itself, but the transformations still failed with the same error
> message.
>

note that placing proj.db on \Temp is not a good idea.
on Windows 10 this is considered to be a system folder
subject to very strict security restrictions.
special Admin powers are usually required in order to
access any file on \Temp

final suggestion: please carefully read the documentation;
at the very bottom of the Wiki page you'll find a detailed
explanation about installing proj.db on Windows.

https://www.gaia-gis.it/fossil/libspatialite/wiki?name=PROJ.6

bye Sandro

[HansGIS]

Thanks for the deatailed answer! With the proj.db file in the fixed path, SpatiaLite works like a charm. :-)