jvail / spatiasql.js

JavaScript port of SpatiaLite

spatiasql.js

This project is not maintained anymore. There is a new project with updated dependencies a new API (no sql.js anymore): https://github.com/jvail/spl.js

JavaScript (emscripten) port of SpatiaLite 5.0.0-beta (including SQLite 3.24.0, proj 5.1.0, geos 3.6.2)

Demo

A demo (WebAssembly required) is available from here: http://jvail.github.io/spatiasql.js/

(be patient, loading the js and database file takes some time).

Usage

spatiasql.js (SpatiaLite) is an extension of sql.js (SQLite) and implements the same (for node), but async API for the browser, exept:

• loading shapefiles in node:

const fs = require('fs');
const spatiasql = require('spatiasql-node');

spatiasql.then(Database => {

  const db = new Database();

  db.loadshp('my_table', 'CP1251', 4326, {
    shp: fs.readFileSync('my_file.shp'),
    shx: fs.readFileSync('my_file.shx'),
    dbf: fs.readFileSync('my_file.dbf')
  });

  let res = db.exec('SELECT * FROM my_table');
  console.log(res);

});

• loading shapefiles in the browser:

import { Database } from 'spatiasql';

const db = new Database();

const files = await Promise.all([
  fetch('my_file.shp').then(res => res.arrayBuffer()),
  fetch('my_file.shx').then(res => res.arrayBuffer()),
  fetch('my_file.dbf').then(res => res.arrayBuffer())
]);

const loaded = await db.loadshp('my_table', 'CP1251', 4326, {
    shp: files[0],
    shx: files[1],
    dbf: files[2]
  });

if (loaded) {
  db.exec('SELECT * FROM my_table')
    .then(res => console.log(res));
}

angular, vue & webpack

The worker script (& wasm) file is expected to live in a 'lib' folder and may be copied with e.g. CopyWebpackPlugin. See vue and angular examples:

• angular: https://github.com/jvail/spatialitebrowser
• vue: https://github.com/jvail/spatiasql-vue-test

spatiasql.js's Issues

How to open pre-populated sqlite database?

Great work. How to open pre-populated database with .sqlite extension? I don't find such example in readme file. Can someone, please, give me some example of opening sqlite DB?

Working example with sample data from geopackage.org

Cool project!

I tried to open some of the sample files from http://www.geopackage.org/#sampledata but failed to export any data from "the_geom" columns. Only getting null values as a result and no error message.

Would be nice if you could submit a working example or point us in the right direction.

catch geos exceptions and warnings

catch geos exceptions and make geos warnings available

make stopped at libspatialite installing phae

Hello, thank you for great working!
compiled version of spatiasql.js is working very well, even on electron(Atom shell).

But I recompile it, make stopped at installing phase of libspatialite bytecode.

Message showing on console after sanity check, is like this:

INFO     root: (Emscripten: Running sanity checks)
DEBUG    root: compiling to bitcode
DEBUG    root: emcc step "parse arguments and setup" took 0.02 seconds
DEBUG    root: using bitcode file: /Users/kokogiko/Projects/spatiasql.js/src/zlib/adler32.o
DEBUG    root: using bitcode file: /Users/kokogiko/Projects/spatiasql.js/src/zlib/compress.o
DEBUG    root: using bitcode file: /Users/kokogiko/Projects/spatiasql.js/src/zlib/crc32.o
DEBUG    root: using bitcode file: /Users/kokogiko/Projects/spatiasql.js/src/zlib/deflate.o
DEBUG    root: using bitcode file: /Users/kokogiko/Projects/spatiasql.js/src/zlib/gzclose.o
DEBUG    root: using bitcode file: /Users/kokogiko/Projects/spatiasql.js/src/zlib/gzlib.o
DEBUG    root: using bitcode file: /Users/kokogiko/Projects/spatiasql.js/src/zlib/gzread.o
DEBUG    root: using bitcode file: /Users/kokogiko/Projects/spatiasql.js/src/zlib/gzwrite.o
DEBUG    root: using bitcode file: /Users/kokogiko/Projects/spatiasql.js/src/zlib/infback.o
DEBUG    root: using bitcode file: /Users/kokogiko/Projects/spatiasql.js/src/zlib/inffast.o
DEBUG    root: using bitcode file: /Users/kokogiko/Projects/spatiasql.js/src/zlib/inflate.o
DEBUG    root: using bitcode file: /Users/kokogiko/Projects/spatiasql.js/src/zlib/inftrees.o
DEBUG    root: using bitcode file: /Users/kokogiko/Projects/spatiasql.js/src/zlib/trees.o
DEBUG    root: using bitcode file: /Users/kokogiko/Projects/spatiasql.js/src/zlib/uncompr.o
DEBUG    root: using bitcode file: /Users/kokogiko/Projects/spatiasql.js/src/zlib/zutil.o
DEBUG    root: emcc step "bitcodeize inputs" took 0.00 seconds
DEBUG    root: link: ['/Users/kokogiko/Projects/spatiasql.js/src/zlib/adler32.o', '/Users/kokogiko/Projects/spatiasql.js/src/zlib/compress.o', '/Users/kokogiko/Projects/spatiasql.js/src/zlib/crc32.o', '/Users/kokogiko/Projects/spatiasql.js/src/zlib/deflate.o', '/Users/kokogiko/Projects/spatiasql.js/src/zlib/gzclose.o', '/Users/kokogiko/Projects/spatiasql.js/src/zlib/gzlib.o', '/Users/kokogiko/Projects/spatiasql.js/src/zlib/gzread.o', '/Users/kokogiko/Projects/spatiasql.js/src/zlib/gzwrite.o', '/Users/kokogiko/Projects/spatiasql.js/src/zlib/infback.o', '/Users/kokogiko/Projects/spatiasql.js/src/zlib/inffast.o', '/Users/kokogiko/Projects/spatiasql.js/src/zlib/inflate.o', '/Users/kokogiko/Projects/spatiasql.js/src/zlib/inftrees.o', '/Users/kokogiko/Projects/spatiasql.js/src/zlib/trees.o', '/Users/kokogiko/Projects/spatiasql.js/src/zlib/uncompr.o', '/Users/kokogiko/Projects/spatiasql.js/src/zlib/zutil.o']/Users/kokogiko/Projects/spatiasql.js/src/install_bc/lib/zlib.bc
DEBUG    root: emcc: llvm-linking: ['/Users/kokogiko/Projects/spatiasql.js/src/zlib/adler32.o', '/Users/kokogiko/Projects/spatiasql.js/src/zlib/compress.o', '/Users/kokogiko/Projects/spatiasql.js/src/zlib/crc32.o', '/Users/kokogiko/Projects/spatiasql.js/src/zlib/deflate.o', '/Users/kokogiko/Projects/spatiasql.js/src/zlib/gzclose.o', '/Users/kokogiko/Projects/spatiasql.js/src/zlib/gzlib.o', '/Users/kokogiko/Projects/spatiasql.js/src/zlib/gzread.o', '/Users/kokogiko/Projects/spatiasql.js/src/zlib/gzwrite.o', '/Users/kokogiko/Projects/spatiasql.js/src/zlib/infback.o', '/Users/kokogiko/Projects/spatiasql.js/src/zlib/inffast.o', '/Users/kokogiko/Projects/spatiasql.js/src/zlib/inflate.o', '/Users/kokogiko/Projects/spatiasql.js/src/zlib/inftrees.o', '/Users/kokogiko/Projects/spatiasql.js/src/zlib/trees.o', '/Users/kokogiko/Projects/spatiasql.js/src/zlib/uncompr.o', '/Users/kokogiko/Projects/spatiasql.js/src/zlib/zutil.o'] to /Users/kokogiko/Projects/spatiasql.js/src/install_bc/lib/zlib.bc
DEBUG    root: stopping at bitcode
EMDEBUG=1 emcc -O2 -DSQLITE_OMIT_LOAD_EXTENSION -DSQLITE_ENABLE_RTREE -DSQLITE_DISABLE_LFS -DLONGDOUBLE_TYPE=double -DSQLITE_INT64_TYPE="long long int" -DSQLITE_THREADSAFE=0 \
    /Users/kokogiko/Projects/spatiasql.js/src/sqlite-amalgamation/sqlite3.c \
    -o /Users/kokogiko/Projects/spatiasql.js/src/install_bc/lib/sqlite.bc
cd /Users/kokogiko/Projects/spatiasql.js/src/libspatialite; \
    cp -n configure configure.backup; \
    # patch spatialite configure \
    sed -i \
     -e 's/^return pj_init_plus ();/\/\/return pj_init_plus ();/' \
     -e 's/^return inflateInit_ ();/\/\/return inflateInit_ ();/' \
     -e 's/^return sqlite3_rtree_geometry_callback ();/\/\/return sqlite3_rtree_geometry_callback ();/' \
     -e 's/^return sqlite3_prepare_v2 ();/\/\/return sqlite3_prepare_v2 ();/' \
     -e 's/^return pj_init_plus ();/\/\/return pj_init_plus ();/' \
     -e 's/^return pj_init_ctx ();/\/\/return pj_init_ctx ();/' \
     -e 's/^return iconv ();/\/\/return iconv ();/' \
     -e 's/^return libiconv ();/\/\/return libiconv ();/' \
     -e 's/^return locale_charset ();/\/\/return locale_charset ();/' \
     -e 's/^return nl_langinfo ();/\/\/return nl_langinfo ();/' \
     -e 's/^return freexl_open ();/\/\/return freexl_open ();/' \
     -e 's/^return GEOSCoveredBy ();/\/\/return GEOSCoveredBy ();/' \
     -e 's/^return GEOSDelaunayTriangulation ();/\/\/return GEOSDelaunayTriangulation ();/' \
     -e 's/^return lwgeom_set_handlers ();/\/\/return lwgeom_set_handlers ();/' configure ; \
    EMCONFIGURE_JS=1 emconfigure ./configure --prefix=/Users/kokogiko/Projects/spatiasql.js/src/install_bc --host=none-none-none \
    CFLAGS="-ULOADABLE_EXTENSION" \
    CPPFLAGS="-I/Users/kokogiko/Projects/spatiasql.js/src/install_bc/include/ -I/Users/kokogiko/Projects/spatiasql.js/src/sqlite-amalgamation/" \
    LDFLAGS="-L/Users/kokogiko/Projects/spatiasql.js/src/install_bc/lib/" \
    --disable-mathsql \
    --disable-geocallbacks \
    --disable-freexl \
    --disable-epsg \
    --disable-geosadvanced \
    --disable-lwgeom \
    --disable-libxml2 \
    --disable-gcov \
    --disable-examples ; \
    EMDEBUG=1 emmake make install EMCC_CFLAGS="-O2"
make: *** [spatialite] Error 1

Just showing "Error 1".
I can't find the way to solve it.
Do you have any information about this?

I don't know there are any "debug.log" or so, then I couldn't show them, but if you tell me where they are, I'll take and upload them here.

Environment is,
Mac OS X Yosemite 10.10.3
Xcode 6.3.2
node.js v0.12.5
emscripten (emcc) 1.30.0
javac 1.8.0_45
Python 2.7.9

Add error cb for spatialite errors

Can this work with IDFS?

Took me a while of digging to realize that this only likely worked with in-memory data due to sql.js. Is this accurate?

If so, is there any possibility of using IDFS, or some other backend for using large files in the browser? I've been investigating for a while and suspect the answer is no, but thought I'd ask to be sure.

It might be worth adding a "this only works with in-memory data" blurb in the README.md. I'm not super familiar with the Emscripten ecosystem yet, and was very excited about this until I realized that using my 2 GB databases was likely a no-go. :)

Thanks.

Expose queue size

Refactor nested array hell in results

Refactor Database class to iife

Remove unnecessary async

Create scripts to run all tests in tests folder

Add runScript methode

Make proj failure '/bin/sh: ../src/nad2bin: Permission denied'

Hi -- cool project! I'm attempting to re-compile because I'd like to enable the geocallbacks flag so that I can test improvements when spatial indices are applied to queries.

OS: MacOS 10.12.5

Tooling versions:

• xcode 8.3.2
• emscripten 1.37.10
• cmake 3.8.1

I started running each of the make targets in order after initial failure with just make.

After fixing make getsrc via changes in #4, I get the following error when running make proj:

/usr/local/Cellar/emscripten/1.37.10/libexec/emcc -DHAVE_CONFIG_H -I. -DPROJ_LIB=\"/Users/andrew/src/spatiasql.js/bc/share/proj\" -DMUTEX_stub    -Wall -Wdeclaration-after-statement  -MT cs2cs.o -MD -MP -MF .deps/cs2cs.Tpo -c -o cs2cs.o cs2cs.c
cs2cs.c:56:23: warning: unused variable 'facs' [-Wunused-variable]
static struct FACTORS facs;
                      ^
1 warning generated.
mv -f .deps/cs2cs.Tpo .deps/cs2cs.Po
/bin/sh ../libtool  --tag=CC   --mode=link /usr/local/Cellar/emscripten/1.37.10/libexec/emcc -Wall -Wdeclaration-after-statement    -o cs2cs cs2cs.o gen_cheb.o p_series.o libproj.la -lm
libtool: link: /usr/local/Cellar/emscripten/1.37.10/libexec/emcc -Wall -Wdeclaration-after-statement -o cs2cs cs2cs.o gen_cheb.o p_series.o  ./.libs/libproj.a -lm
 .././install-sh -c -d '/Users/andrew/src/spatiasql.js/bc/lib'
 /bin/sh ../libtool   --mode=install /usr/bin/install -c   libproj.la '/Users/andrew/src/spatiasql.js/bc/lib'
libtool: install: /usr/bin/install -c .libs/libproj.lai /Users/andrew/src/spatiasql.js/bc/lib/libproj.la
libtool: install: /usr/bin/install -c .libs/libproj.a /Users/andrew/src/spatiasql.js/bc/lib/libproj.a
libtool: install: chmod 644 /Users/andrew/src/spatiasql.js/bc/lib/libproj.a
libtool: install: /usr/local/Cellar/emscripten/1.37.10/libexec/emranlib /Users/andrew/src/spatiasql.js/bc/lib/libproj.a
 .././install-sh -c -d '/Users/andrew/src/spatiasql.js/bc/bin'
  /bin/sh ../libtool   --mode=install /usr/bin/install -c proj nad2bin geod cs2cs '/Users/andrew/src/spatiasql.js/bc/bin'
libtool: install: /usr/bin/install -c proj /Users/andrew/src/spatiasql.js/bc/bin/proj
libtool: install: /usr/bin/install -c nad2bin /Users/andrew/src/spatiasql.js/bc/bin/nad2bin
libtool: install: /usr/bin/install -c geod /Users/andrew/src/spatiasql.js/bc/bin/geod
libtool: install: /usr/bin/install -c cs2cs /Users/andrew/src/spatiasql.js/bc/bin/cs2cs
rm -f /Users/andrew/src/spatiasql.js/bc/bin/invproj
(cd /Users/andrew/src/spatiasql.js/bc/bin; ln -s proj invproj)
rm -f /Users/andrew/src/spatiasql.js/bc/bin/invgeod
(cd /Users/andrew/src/spatiasql.js/bc/bin; ln -s geod invgeod)
 .././install-sh -c -d '/Users/andrew/src/spatiasql.js/bc/include'
 /usr/bin/install -c -m 644 proj_api.h projects.h geodesic.h org_proj4_Projections.h org_proj4_PJ.h '/Users/andrew/src/spatiasql.js/bc/include'
Making install in man
Making install in man1
make[4]: Nothing to be done for `install-exec-am'.
 ../.././install-sh -c -d '/Users/andrew/src/spatiasql.js/bc/share/man/man1'
 /usr/bin/install -c -m 644 geod.1 proj.1 cs2cs.1 '/Users/andrew/src/spatiasql.js/bc/share/man/man1'
Making install in man3
make[4]: Nothing to be done for `install-exec-am'.
 ../.././install-sh -c -d '/Users/andrew/src/spatiasql.js/bc/share/man/man3'
 /usr/bin/install -c -m 644 pj_init.3 geodesic.3 '/Users/andrew/src/spatiasql.js/bc/share/man/man3'
make[4]: Nothing to be done for `install-exec-am'.
make[4]: Nothing to be done for `install-data-am'.
Making install in nad
make[3]: Nothing to be done for `install-exec-am'.
../src/nad2bin null < ../nad/null.lla
/bin/sh: ../src/nad2bin: Permission denied
make[3]: *** [process-nad2bin] Error 126
make[2]: *** [install-am] Error 2
make[1]: *** [install-recursive] Error 1

Adopt correct semantic versioning

Implement better system table initialization options