Proj error

[Jim]

I know this has been discussed before but I'm encountering a problem on Windows both with my code and spatialite_gui with regards to St_Transform.

I am using spatailite 5.0.1 in my code and for  spatialite_gui it is 5.0.0.

Both have proj available but it appears not to be proj6 as shown by HasProj and HasProj6. 

The 'proj.db' is in the same directory as spatailite_gui  and in  my executable directory as well.

The database was loaded using spatialite_tool from a UK shapefile and using SRID 27700 which is the British standard.

I issue the following sql in both my code and spatialite_gui:

SELECT  ST_SRID(geometry) as srid, cty22nm as name,  AsText(ST_Transform(geometry, 4326)) as geom FROM uk_counties LIMIT 1;

I get a response both in spatilaite_gui and my code.  It is:

"ST_Transform exception - PROJ reports "proj_create: no database context specified"

if I remove the ST_Transform, the query works fine, its just that the geometry output is in SRID 2770 format which is not usable for my project.

I used ST_Transform a while ago in a similar situation with no problem.

I have been all over my laptop and I can't find the issue.  I know there were lots of changes when the upgrade to 5.0 occurred.

So, any suggestion or thoughts would be most helpful.

Regards,

Jim

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

Hi Jim,

the most recent versions of PROJ have introduced many interesting
innovations,
but unfortunately they have also introduced various complications
regarding
the configuration, which can become truly deadly especially on Windows
platforms.

explained in the simplest possible way: PROJ now relies completely on
its own
SQLite DB in which are stored all parameters required for calculating
geodetic
transformations.

if at run time the PROJ library is unable to connect to its geodetic DB
nothing
will work anymore.
any call to PROJ will then simply return this error message:

ST_Transform exception - PROJ reports "proj_create: no database context
specified

All the complications arise from the fact that the rules used by PROJ
to find
its own geodetic DB are quite complex, particularly on Windows where
the risk
of something going wrong is particularly high.
However, things are much better on Linux.

you can find everything explained in detail on this page of the
SpatiaLite Wiki
(look out for the special notes for Windows at the very bottom of the
Wiki page):

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


Particularly useful SQL functions:
----------------------------------
* PROJ_GetDatabasePath()
this will return the current path where PROJ expects to find its own
geodetic DB; check if it really exists.

* PROJ_SetDatabasePath(somepath)
this will directly tell to PROJ where to find its own geodetic DB
(you are expected to call this before calling any other PROJ
function).
----------------------------------


further complications
=====================
all PROJ geodetic DBs are always named proj.db, but every single
version
of the library strictly depends on its corresponding version of proj.db
WARNING: randomly mixing the library and proj.db versions can prevent
PROJ from working !!!

For example the proj.db required by SpatiaLiteGUI 5.0.0 and
spatialite-tools
5.0.1 are different; you can easily notice it because they have very
different sizes.


final conclusions
========================================================
it's very likely that there are some old settings left in your PC
that are now preventing the PROJ from working correctly.
Check all your configuration and clean all the old settings,
then try to reinstall everything from scratch.
By working methodically (and with a lot of patience) you
should be able to solve the problem.


very last consideration
==========================================================
I just realized that there is a nasty bug on HasProj6() that
always returns ZERO :-P

bye Sandro

[Jim]

Sandro,

Well, the only polite word I can think of is "Wow".  Of course, thank you for the detailed explanation. As always happens, what I am doing is a "once and done" project so I might take strange actions just to get it done.

At 82, it may be time to put my laptop on the shelf and sit and watch TV instead.

OK, to the problem, could you send me an exact link to where I can download the latest 'spatialite_gui' for Windows?  I manage t get easily lost in the Spatialite web page.

Also, since Windows has the Linux Subsystem, I might try to do my project there.  Will "npm:" install 'spatialite_gui'?  Alternatives?

This issue for me is most likely the buildup of trash on my system over the years, but none the less, I just wish to get this project over and done.

Again, thank you Sandro.

Regards,

Jim                                                                            

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

On Mon, 8 Apr 2024 06:01:41 -0700 (PDT), Jim wrote:
> OK, to the problem, could you send me an exact link to where I can
> download the latest 'spatialite_gui' for Windows?  I manage t get
> easily lost in the Spatialite web page.
>

you can download the latest precompiled binaries for Windows
from here:

WIN32
-------
https://www.gaia-gis.it/gaia-sins/windows-bin-x86/mod_spatialite-5.1.0-win-x86.7z
https://www.gaia-gis.it/gaia-sins/windows-bin-x86/spatialite-tools-5.1.0a-win-x86.7z
https://www.gaia-gis.it/gaia-sins/windows-bin-x86-latest/spatialite_gui-NG-5.0.0-win-x86.7z


WIN64
-------
https://www.gaia-gis.it/gaia-sins/windows-bin-amd64/mod_spatialite-5.1.0-win-amd64.7z
https://www.gaia-gis.it/gaia-sins/windows-bin-amd64/spatialite-tools-5.1.0a-win-amd64.7z
https://www.gaia-gis.it/gaia-sins/windows-bin-amd64-latest/spatialite_gui-NG-5.0.0-win-amd64.7z

> Also, since Windows has the Linux Subsystem, I might try to do my
> project there.  Will "npm:" install 'spatialite_gui'? 
>

sorry, no

> Alternatives?
>

building binaries for Windows requires using the MinGW + MSYS
toolchain, that are a platform-specific porting of the standard
Linux gcc compiler and related tools ("vanilla" make and alike).

pros:
Windows then simply becomes a sligthly eretic variant of Linux;
there is no need to adapt the code accordingly to specific
platform requirementes.
the executable code is highly optimized and runs faster than
the one generated by MSVC.

cons:
you have to install an exotic development system.
not at all too difficult, but it requires some extra
attentions and skills.

here you can find a detailed (and regularly updated) HowTo:

WIN32
----------
https://www.gaia-gis.it/gaia-sins/mingw32_how_to.html

WIN64
----------
https://www.gaia-gis.it/gaia-sins/mingw64_how_to.html

note: starting from zero it will require a couple of days
to complete the whole installation process.

bye Sandro

[Jim]

Sando, thank you but 'no joy'.

I installed the spatialite_gui link you sent into a new directory and deleted the old one completely.

The results:

* The "about" shows Proj 7.1.0  and Spatialite 5.0.0
* On the new spatialite_gui the following "SELECT spatialite_version() as version , HasProj() as p6;" returns 5.0.0 and 1
* " SELECT  PROJ_GetDatabasePath()" returns NULL
* " SELECT  PROJ_SetDatabasePath('C:/new_spatialite_gui/')"  returns NULL  (note: this is were proj.db resides)
* " SELECT   PROJ_GetDatabasePath()" still returns NULL
* "SELECT  ST_SRID(geometry) as srid, cty22nm as name, AsText(ST_Transform(geometry, 4326)) as geom          FROM uk_counties LIMIT 1"  
   still gets the same error about database context

I have checked and there is only one proj.db on my system and
it is in the directory with the new spatialite_gui install and the proj.db size is 6,758,400

The only issue I have is with ST_Transform and I have 2  databases that need the geometry transformed from SRID 27700 to SRID 4326 and I only need to do it once.

Apparently on Windows, Proj6 can't find it's database and even trying to set the value doesn't work unless I have that wrong.

To say that I am lost and a bit frustrated, is a wee bit of an understatement.

Regards,

Jim

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

On Mon, 8 Apr 2024 09:46:02 -0700 (PDT), Jim wrote:
> * " SELECT  PROJ_SetDatabasePath('C:/new_spatialite_gui/')"  returns
> NULL  (note: this is were proj.db resides)
>

Jim,

this is wrong; you should pass the full path to proj.db, as in

SELECT PROJ_SetDatabasePath('C:/new_spatialite_gui/proj.db');

bye Sandro

[Jim]

There is a Greek word I can't spell, so let 'WHOOPEE' suffice!!!

All is well now, it was the path and the geometry is transformed.

Sandro, thank you  sincerely for your patience!!

Regards,

Jim