jvail / spl.js Goto Github PK

README:

.save([dest: string]) -> DB | ArrayBuffer
Export the current database. This is using SQLite's backup API. If dest is undefined or empty an ArrayBuffer is returned.

compiled save code:

        this.save = ()=>(q.push({
            id: f,
            fn: "db.save",
            args: []
        }),

Salut @jvail mon ami,

First, a big thank for helping me play with gis sql on the phone in my hand.
there are two bug manifestations of sqlite in general, that i haven't encountered with other versions, on either linux or wasm.
As the following queries do work, even in spatialite, but give (similar) error messages,
probably, compiling against newer sqlite (I'm using 3.41 on wasm and 3.43 on linux) would remove the error messages.

So the queries:

• create temp table t2 as
  WITH t1(x,y,n,color) AS (VALUES
  (100,100,3,'red'),
  (200,100,4,'orange'),
  (300,100,5,'green'),
  (400,100,6,'blue'),
  (500,100,7,'purple'),
  (100,200,8,'red'),
  (200,200,10,'orange'),
  (300,200,12,'green'),
  (400,200,16,'blue'),
  (500,200,20,'purple')
  )
  SELECT * FROM t1;
  -- table t2 already exists

• ATTACH DATABASE '/proj/proj.db' AS proj;
  -- database proj is already in use

again, a big thank
alex

Hi,
First thanks a lot for creating this library.

I'm trying to execute this script:

const script =`
BEGIN TRANSACTION;
SELECT InitSpatialMetaData();
CREATE TABLE test (
id TEXT NOT NULL
  PRIMARY KEY);
SELECT AddGeometryColumn('test', 'geom', 4326, 'POLYGON', 'XY');

INSERT OR REPLACE INTO test VALUES ('osm998307425',GeomFromGeoJSON('{"type":"Polygon","coordinates":[[[35.172522345781324,31.807637007387367],[35.1730225777626,31.807379406789376],[35.17296088695526,31.807292779878278],[35.17246065497398,31.807550380717725],[35.172522345781324,31.807637007387367]]]}'));
SELECT CreateSpatialIndex('test', 'geom');
COMMIT;`
await db.read(script);

But getting this error:

test.geom violates Geometry constraint [geom-type or SRID not allowed]

However when executing only the following expression, everything works correctly:

db.exec("select GeomFromGeoJSON('{\"type\":\"Polygon\",\"coordinates\":[[[35.172522345781324,31.807637007387367],[35.1730225777626,31.807379406789376],[35.17296088695526,31.807292779878278],[35.17246065497398,31.807550380717725],[35.172522345781324,31.807637007387367]]]}');" );

How should I do this?

Thanks a lot!

Reading through the seminal Hosting SQLite databases on Github Pages blogpost, I see that the author did the following:

implemented a virtual file system that fetches chunks of the database with HTTP Range requests when SQLite tries to read from the filesystem

The output of this is captured in sql.js-httpvfs.

What is the feasibility of adding such a feature to this codebase? I'd like to be able to query a large (300MB+) Spatialite database without requiring the end-user to load the file in its entirety.

I am opening to the idea of contributing to this feature but am not well versed in WASM so this would be a bit of a learning exercise on my part.

Hi @jvail , catch block is not working, I can see error in console but it does not propagates to catch callback, same is the case if I use try catch block.
https://codepen.io/spaciandd/pen/QWavjwv

import SPL from 'spl.js';

async function run() {
    const db = await SPL().then(spl => spl.db());

    console.assert(await db.exec('select spatialite_version()').get.first === '5.0.1');

    db.exec('select r as hello', ['spatialite']).get.objs
        .then(res => {
            console.log('ujsakhdsa')
        }
        )
        .catch(err => {
            console.log("err.message")
        }
        );
}
run()

Hello again,
I'm trying to add a few thousands of records to my db (in browser) and getting some errors:

const db = <...>;
const srid = 4326;
 
let script = `
BEGIN TRANSACTION;
SELECT InitSpatialMetaData();
CREATE TABLE ${tableName} (
    id TEXT NOT NULL
      PRIMARY KEY);
SELECT AddGeometryColumn('${tableName}', '${GEOM_FIELD_NAME}', ${srid}, 'GEOMETRY', 'XY');
`;
const geoJson = '{"type":"Polygon","coordinates":[[[35.172522345781324,31.807637007387367],[35.1730225777626,31.807379406789376],[35.17296088695526,31.807292779878278],[35.17246065497398,31.807550380717725],[35.172522345781324,31.807637007387367]]]}';
let geometryAddition = `SetSRID(GeomFromGeoJSON('${geoJson}'), 4326)`;
for (let i = 0; i < 5000; ++i) {
   script += `INSERT OR REPLACE INTO ${tableName} VALUES ('a${i}',${geometryAddition});`;
}
script += "COMMIT;"
await db.read(script);

With 5000 rows I got:

table index is out of bounds

And with 10000 rows:

memory access out of bounds

This seems related to this chromium issue, but I have an up-to-date version (Version 103.0.5060.134).

Can you confirm this bug?
If it's only on my desktop machine I'm OK with this but if this is a common issue perhaps this chromium bug should be reopened.

Hello @jvail,
This is a feature request.
I have a use case where it would be useful to access a db from a different iframe/window.

Is it possible to make spl.js running from a SharedWorker rather than a dedicated worker?

Thank you very much!

Hi there,
I am currently researching the possibility of implementing API with Geospatial search capabilities on the Edge, using Cloudflare Workers, Fly.io, or similar. I came across this promising project, but I am having difficulty understanding it after reading the README.

I wanted to ask how this is supposed to be used with Node / or other server-side JS runtimes? Would it be possible to hook it up with Edge SQLite database provider such as Turso, Cloudflare D1 , or Fly.io SQLite or LiteFS? I would really appreciate some clarification.

Thanks a lot

I need to load stats extension. Is there any way ?

Hi again @jvail ,

Possibly I'm doing something wrong again but running the following code gives me this error:

database disk image is malformed

I was able to reproduce this most of the times, though sometimes the problem doesn't happen.
Executing other operations after this error, sometime gives me errors of memory access out of bound.
The code:

const spl = await SPL(undefined, {
    autoGeoJSON: {
      precision: 8,
      options: 0
    }
  });
// In this line I load the entire proj.db (instead of fetching it in parts).
// the 'projDbStr' var is made using your 'stringify' script and imported as a module
const projdbBinary = pako.inflate(Uint8Array.from(atob(projDbStr), c => c.charCodeAt(0))).buffer;
const db = await spl.mount(
        'proj', [{ name: 'proj.db', data: projdbBinary }]).db(undefined);  

// UPDATE: the following line added later
await db.exec("SELECT InitSpatialMetadata(1);");

const srid = 32636;
const tableName = "test";
const script = `
                SELECT InitSpatialMetadata(1);
                CREATE TABLE ${tableName} (id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, field INTEGER, src_id TEXT);
                SELECT AddGeometryColumn('${tableName}', 'geometry', ${srid}, 'GEOMETRY', 'XY');
            `;
await db.read(script);
const batchSize = 10000;
const geom = '{"type": "Polygon", "coordinates": [[[35.172522345781324,31.807637007387367],[35.1730225777626,31.807379406789376],[35.17296088695526,31.807292779878278],[35.17246065497398,31.807550380717725],[35.172522345781324,31.807637007387367]]]}';
    
const parameters = [];
for (let i = 0; i < batchSize; ++i) {
    parameters.push({
      "@field": i,
      "@src_id": i.toString(),
      "@geometry": geom
    });
}
const statement = `INSERT OR REPLACE INTO ${tableName} (field, src_id, geometry) VALUES (@field, @src_id, ST_Transform(SetSRID(GeomFromGeoJSON(@geometry), 4326), ${srid}));`;
await db.exec(statement, parameters);

Changing batchSize to 10 works fine (even if repeated 1000 times to overall number of 10,000 rows).
Also, when using read method, with 10,000 insert statements, everything works fine.

Hope this is not another mistake of mine.

Thank you very much!

Hi @jvail

Thanks for this beautiful work. I am trying to integrate spl.js in a typescript frontend. But I did not manage to import
the async version.

When I try a simple example:

import SPL from 'spl.js';

export async function splPlayground() {
    const db = await SPL().then(spl => spl.db(undefined));
    console.log(await db.exec('select spatialite_version()').get.first);
}

I got these errors:

ERROR in src/splPlayground.ts:4:28

TS2339: Property 'then' does not exist on type 'ISPLSync'.
    2 |
    3 | export async function splPlayground() {
  > 4 |     const db = await SPL().then(spl => spl.db(undefined));
      |                            ^^^^
    5 |

Code can be found in this repository: https://github.com/remipassmoilesel/spl.js-issue-2021-12-27
See splPlayground.js: https://github.com/remipassmoilesel/spl.js-issue-2021-12-27/blob/master/src/splPlayground.ts

In my opinion it is due to spl.js package.json structure:

    ...
    "main": "dist/spl.js",
    "browser": "dist/index.js",   <- Typescript does not seem to use that property
    ...

If I modify it like this, all works fine:

    ...
    "main": "dist/index.js",
    ...

Questions

• Is there something that I am doing wrong ?
• Could we only use index.ts without default export ? Since the types are named differently, it would then suffice to
  choose according to the target (node or browser)

    import {IAsyncSpl as Spl} from 'spl.js'
    import {ISyncSpl as Spl} from 'spl.js'

Is it available in electron?

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

Hello !
I'm trying to expand my proposed series on spl.js, and I came across an issue -

When trying to use ST_Transform(), I get Error: ST_Transform exception - PROJ reports "proj_create: no database context specified".

The test SQL: SELECT ST_Buffer(ST_Transform(MakePoint(5.7245, 45.1885,4326),3857), 200);

Is this from the lib, or my src DB, or the sqlite version at Observable, or something else?

https://observablehq.com/d/b250322cbcc02b2f

Hi Jan,

First off, thank you for pulling together this project as I've been excited about having up-to-date versions of PROJ and Spatialite in WASM (and the browser) for some time.

One problem I have is that the current GPL-3 licence makes it impossible to use WASM files from this in my own non-GPL projects, especially if I want to customise the source (eg. to generate isolated WASM builds for PROJ). While I have no problem with releasing any customisations under an open licence, I wouldn't want to infect the rest of my codebases with GPL-3 simply by using the generated WASM files from this project.

Would you be willing to consider moving to a less infectious licence? Perhaps LGPL, or MIT like your previous iteration of the project?

I understand if not, though it would be great if there was some way to tweak this as I know a lot of people would love to use and share the work that you've done here so long as it doesn't interfere with their existing codebases.

Either way, keep up the great work here with the WASM builds.

Hi again,
Probably I'm doing something wrong again but I can't figure this out.
I'm using the following statement to add geometries of type GeometryCollection to spl.js:

CREATE TABLE test (id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, src_id TEXT);
SELECT AddGeometryColumn('test', 'geometry', 4326, 'GEOMETRY', 'XY');

Then:

INSERT OR REPLACE INTO test (src_id, geometry)
 VALUES ('1', 
          SetSRID(
            GeomFromGeoJSON(
              '{"type":"GeometryCollection","geometries":[{"coordinates":[[35.17246065497398,31.807637007387367],[35.1730225777626,31.807637007387367],[35.1730225777626,31.807292779878278],[35.17246065497398,31.807292779878278],[35.17246065497398,31.807637007387367]],"type":"LineString"},{"coordinates":[[[36.17246065497398,32.80763700738737],[36.1730225777626,32.80763700738737],[36.1730225777626,32.80729277987828],[36.17246065497398,32.80729277987828],[36.17246065497398,32.80763700738737]]],"type":"Polygon"},{"coordinates":[37.17246065497398,33.80763700738737],"type":"Point"}]}'), 
          4326));

However when I query this table I get null for field geometry.

It worth mentioning that the following query returns a good geometry:

select SetSRID(
   GeomFromGeoJSON('{"type":"GeometryCollection","geometries":[{"type":"LineString", "coordinates":[[35.17246065497398,31.807637007387367],[35.1730225777626,31.807637007387367],[35.1730225777626,31.807292779878278],[35.17246065497398,31.807292779878278],[35.17246065497398,31.807637007387367]]},{"type":"Polygon", "coordinates":[[[36.17246065497398,32.80763700738737],[36.1730225777626,32.80763700738737],[36.1730225777626,32.80729277987828],[36.17246065497398,32.80729277987828],[36.17246065497398,32.80763700738737]]]},{"type":"Point", "coordinates":[37.17246065497398,33.80763700738737]}]}'),
  4326);

Also any other geometry type (other than 'GeometryCollection') works fine.

What am I doing wrong?

Thank you very much!