jvail/spl.js

crs not found

Closed this issue · 7 comments

alexbodn commented

hello friend,

and a big thank for letting me take a spatial sql in my pocket with room to show my data in the browser.

my first query has unfortunately failed (what should i expect?).

select aswkt (
st_transform(
--gpkg
MakePoint (
-22562.401432422717, 6730934.887787993, 3857)
, 27700)
);

ST_Transform exception - PROJ reports "proj_create: crs not found".

proj4js does this transformation with either proj4 or newer definition.

i have (maybe not enough) initialized the database with initspatialmetadata(); enablegpkgamphibiousmode(); autogpkgstart(), and both the projections are in both spatial_ref_sys and gpkg_spatial_ref_sys.

hoping for a solution,
alex

alexbodn commented

vanilla spatialite 5.0.1 on a debian machine gave a good looking answer:
POINT(524536.3379876725 192293.2136773111)

i noticed that spatialite on this debian had proj 7.

alexbodn commented

without initspatialmetadata()
the error is:
ST_Transform exception - unable to find the origin SRID.

alexbodn commented

thank you @jvail for your reply.

your example works as installed,
but i couldn't manage to run it locally.
my aim is to build and run real software
on the android in my pocket,
thus I don't have a full blown web server.
as such, i couldn't mount (while i could fetch)
proj.db.

I'd kindly ask your instruction to mount a db file
downloaded in a buffer.
otherwise, i might try to strip proj.db
of rarely used projections,
then dump and read it in the current running db.

could you please advice, especially on mounting
an arraybuffer in spl? this might prove useful with other db files, too.

thanks in advance,
alex

jvail commented

Salut @alexbodn,

the most important thing is that you supply a path to proj.db to spatialite if you'd like to use your own, a full proj.db - the db shipped within the browers lib does only contain a very small subset of projections. From the README:

A minimal proj.db including EPSG codes for lon/lat to "Web Mercator" and UTM is embeded. Other PROJ files and the complete proj.db are available from the dist/proj folder. If you like to use the full proj.db instead you need to mount it to /proj/proj.db and set the path with spatialite's PROJ_SetDatabasePath.

Here is an example for mounting an arraybuffer instead of an URL (string): https://github.com/jvail/spl.js/blob/main/test/browser.js#L114-L136

Let me know if that works for you.

  • 🎉1
  • ❤️1
alexbodn commented

Salut mon ami Jan,

Your solution worked very well for me.
Also, thank you for teaching me to mount the fetched file. It will definitely help with other sqlite based projects.
With your example init code from openlayers.html, I could run your query there:
select transform(geom, 27700)
from london_boroughs;
To run my example:
select
aswkt (
st_transform(
gpkgMakePoint (-22562.401432422717, 6730934.887787993, 3857)
, 27700)
);
with either gpkgMakePoint or just MakePoint, I had to change:
select enablegpkgmode();
to
select EnableGpkgAmphibiousMode();
in the initialization code.

jvail commented

Very good, thanks for the feedback!