spl.js
spl.js copied to clipboard
jvail
json - geojson interoperativity
hello cher ami,
j'espere que tu vas bien.
i'm very happy to experience gis in the privacy of my computer.
there is a challenge for you, in my following query.
as geojson is, in fact, json, it seems counter intuitive to need to be casted to json i order to be used in json_object.
the query will run as is, but it needs the following cacophony, which you may solve: json(asgeojson(makepolygon(makeline(point)))).
my kind regards, alex
WITH t1 AS (
WITH t1 AS (
WITH t1 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)
SELECT
cast (n as text) as name,
json(asgeojson(makepolygon(makeline(point)))) as feature,
json_object(
'fill-color', color,
'fill-opacity', 0.05,
'stroke-color', color,
'stroke-width', 2,
'text-value', cast (n as text)
) as flatstyle,
json_object(
'fillColor', color,
'fillOpacity', 0.05,
'color', color,
'opacity', 1,
'weight', 2
) as style
from (
select
makepoint(
x + 40 * sin((value % n) * 2 * pi() / n),
y + 40 * cos((value % n) * 2 * pi() / n),
3857
) as point,
n,
color
FROM t1
inner join generate_series
on generate_series.start=0 and generate_series.stop=t1.n and generate_series.step=1
)
group by n, color)
SELECT
json_object(
'type', 'Feature',
'geometry', json(Feature),
'properties',
json_object(
'name', name,
'style', json(style),
'flatstyle', json(flatstyle)
)
) as feature
FROM t1)
SELECT
json_object(
'type', 'FeatureCollection',
'crs',
json('{ "type": "name", "properties": { "name": "EPSG:3857" } }'),
'features',
json_group_array(
json(feature)
)
) as feature
FROM t1
I hope you do not expect me to go through your query :). If I understand correctly then this may explain it:
An argument with SQL type TEXT it is normally converted into a quoted JSON string even if the input text is well-formed JSON. However, if the argument is the direct result from another JSON function or the -> operator (but not the ->> operator), then it is treated as JSON and all of its JSON type information and substructure is preserved. This allows calls to json_object() and json_array() to be nested. The json() function can also be used to force strings to be recognized as JSON.
https://sqlite.org/json1.html#jobj
If so, then it may be a topic for spatialite/sqlite to solve/explain.
Bonne chance, Jan
I appologize for seeing your answer that late.
It's all about an advantage in your get api endpoint, that autoconverts json text to js objects: asgeojson() returns text as you say, but your get api endpoint is autoconverting it to js object. json() also returns text, autoconverted by your endpoint. So get api is converting json text to objects, and knows when to do it.
Could the same autodetection happen in other spl functions too? If yes, it would be possible to directly return an object from asgeojson. And, this object won't need parsing in the endpoint, as well as in bare sqlite json routines.
n.b. I wouldn't dare ask you to find a misterious problem in such a long query. It is working correctly, and in my sqlitexplorer it also shows colored features on the map. All I felt uneasy about was that line where I had to convert geojson to json.
All my best wishes, mon cher ami.
It's all about an advantage in your get api endpoint, that autoconverts json text to js objects: asgeojson() returns text as you say, but your get api endpoint is autoconverting it to js object. json() also returns text, autoconverted by your endpoint. So get api is converting json text to objects, and knows when to do it.
Yes, but when you nest asgeojson() somewhere in your SQL then I can not do much about it. Maybe it would be a good idea if spatialite would internally wrap asgeojson in a json function call. Probably the SQLite json function was not available in SQLite (it used to be an optional add-on) when asgeojson was implemented in spatialite. Just guessing ...
I see we enter uncharted land.
It could be simpler to patch the json* functions in the spl.js distribution.
This could be a start point to my next wish. More and more RDBMS take pride of their support of Javascript in stored procedures. I have personally overridden json() with the barebones sqlite WASM with my own JavaScript by create_function. Will it be possible to do it in spl.js too?
n.b. I took the challenge of defining a stored procedure with the tools in spl.js, but I still think real language can be better.
Patching SQLite or SpatiaLite sources is not something I'd like to do. But a db.create_function is certainly missing. If you'd like to file a PR we can give it a try. You should maybe take a look at sql.js sources, how they implemented this feature (especially in the browser/worker setup).
I will have some time these days, and would happily try. As my first time to make a wasm target, I'd kindly ask you for some initiation. My computer is an arm64 Chromebook.
I'd kindly ask you for some initiation
The only thing you need is emsdk: https://emscripten.org/docs/getting_started/downloads.html For building please take a look at the README. You may need to activate/install the specific emsdk version mentioned there if you get emscripten errors. I am not sure if it build with the most recent emsdk version.
I started the process based on sqlite wasm instructions and it appeared to get built, but older debian stable emscripten failed.
spl.js make naturally took more time, while downloading libraries for spatialite, and failed when cmake was missing. As it was late, I stopped the build and will resume today.
Please document the need of cmake and the emscripten sdk you mentioned before. I will first try with the latest installed as per the sqlite wasm instructions.
alex
On Wed, May 1, 2024, 08:47 Jan Vaillant @.***> wrote:
I'd kindly ask you for some initiation
The only thing you need is emsdk: https://emscripten.org/docs/getting_started/downloads.html For building please take a look at the README. You may need to activate/install the specific emsdk version mentioned there if you get emscripten errors. I am not sure if it build with the most recent emsdk version.
— Reply to this email directly, view it on GitHub https://github.com/jvail/spl.js/issues/28#issuecomment-2088014559, or unsubscribe https://github.com/notifications/unsubscribe-auth/ABER7ANWKFLVOMURJURYIW3ZAB6WHAVCNFSM6AAAAABFVR5SDGVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDAOBYGAYTINJVHE . You are receiving this because you authored the thread.Message ID: @.***>
the build eventually broke in libxml2 configure:
Checking zlib
./configure: line 13932: syntax error near unexpected token Z,zlib,' ./configure: line 13932: PKG_CHECK_MODULES(Z,zlib,'
emconfigure: error: './configure
--prefix=/home/alex/wasm/spl.js/src/build/bc --host=none-none-none
--without-python --disable-shared --with-zlib CPPFLAGS=-DDEBUG_HTTP
"Z_LIBS=-L/home/alex/wasm/spl.js/src/build/bc/lib -lz"
Z_CFLAGS=-I/home/alex/wasm/spl.js/src/build/bc/include' failed (returned 2)
make: *** [Makefile:138: xml2-conf] Error 1
have you encountered this?
On Wed, May 1, 2024 at 8:47 AM Jan Vaillant @.***> wrote:
I'd kindly ask you for some initiation
The only thing you need is emsdk: https://emscripten.org/docs/getting_started/downloads.html For building please take a look at the README. You may need to activate/install the specific emsdk version mentioned there if you get emscripten errors. I am not sure if it build with the most recent emsdk version.
— Reply to this email directly, view it on GitHub https://github.com/jvail/spl.js/issues/28#issuecomment-2088014559, or unsubscribe https://github.com/notifications/unsubscribe-auth/ABER7ANWKFLVOMURJURYIW3ZAB6WHAVCNFSM6AAAAABFVR5SDGVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDAOBYGAYTINJVHE . You are receiving this because you authored the thread.Message ID: @.***>
--
alex
I have no idea about Chromebook but a quick search for your error hints to missing autotools library. Maybe you can install the equivalent of build-essentials and autotools on your OS?
https://packages.debian.org/bookworm/autotools-dev https://packages.debian.org/bookworm/build-essential
sorry for the late reply. i do (and did) have these packages installed.
alex
On Wed, May 1, 2024, 15:13 Jan Vaillant @.***> wrote:
I have no idea about Chromebook but a quick search for your error hints to missing autotools library. Maybe you can install the equivalent of build-essentials and autotools on your OS?
https://packages.debian.org/bookworm/autotools-dev https://packages.debian.org/bookworm/build-essential
— Reply to this email directly, view it on GitHub https://github.com/jvail/spl.js/issues/28#issuecomment-2088382008, or unsubscribe https://github.com/notifications/unsubscribe-auth/ABER7AK7WEMPS4U4FTRHPXLZADMABAVCNFSM6AAAAABFVR5SDGVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDAOBYGM4DEMBQHA . You are receiving this because you authored the thread.Message ID: @.***>
You could try to comment out the xml2 build in the Makefile. It is not required for spl.js and I use it only for tests. However it would be good to find the problem. I have never had such an issue.
hello jan,
i pushed the build just a little bit forward, and i'll push my patches and PR.
however, i'm stuck again at "configure: error: 'libsqlite3' is required but it doesn't seem to be installed on this system."
it happens despite i have libsqlite3-dev installed, and also sqlite got built in the process.
if you have a solution, i'll be glad to try it!
bon chance mon ami, alex
On Thu, May 2, 2024 at 7:33 PM Jan Vaillant @.***> wrote:
You could try to comment out the xml2 build in the Makefile. It is not required for spl.js and I use it only for tests. However it would be good to find the problem. I have never had such an issue.
— Reply to this email directly, view it on GitHub https://github.com/jvail/spl.js/issues/28#issuecomment-2090997983, or unsubscribe https://github.com/notifications/unsubscribe-auth/ABER7AIIHFUB5J3AYM2ZAMTZAJTDXAVCNFSM6AAAAABFVR5SDGVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDAOJQHE4TOOJYGM . You are receiving this because you authored the thread.Message ID: @.***>
--
alex
however, i'm stuck again at "configure: error: 'libsqlite3' is required but it doesn't seem to be installed on this system."
Salut, I can only guess that it is looking for the library inside the path I supply for the emsdk builds and can not find it there - not on your system but here: src/build/bc