Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

json - geojson interoperativity #28

Open
alexbodn opened this issue Apr 3, 2024 · 14 comments
Open

json - geojson interoperativity #28

alexbodn opened this issue Apr 3, 2024 · 14 comments

Comments

@alexbodn
Copy link

alexbodn commented Apr 3, 2024 •
edited
Loading

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
@jvail
Copy link
Owner

jvail commented Apr 3, 2024

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

@alexbodn
Copy link
Author

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.

@jvail
Copy link
Owner

jvail commented Apr 21, 2024

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 ...

@alexbodn
Copy link
Author

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.

@jvail
Copy link
Owner

jvail commented Apr 21, 2024

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).

@alexbodn
Copy link
Author

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.

@jvail
Copy link
Owner

jvail commented May 1, 2024

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.

@alexbodn
Copy link
Author

alexbodn commented May 1, 2024 via email

@alexbodn
Copy link
Author

alexbodn commented May 1, 2024 via email

@jvail
Copy link
Owner

jvail commented May 1, 2024

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

@alexbodn
Copy link
Author

alexbodn commented May 2, 2024 via email

@jvail
Copy link
Owner

jvail commented May 2, 2024

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.

@alexbodn
Copy link
Author

alexbodn commented May 13, 2024 via email

@jvail
Copy link
Owner

jvail commented May 14, 2024

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

@jvail jvail mentioned this issue May 31, 2024
Open
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
2 participants
@alexbodn @jvail