json - geojson interoperativity
Opened this issue · 14 comments
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 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
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.
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