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.

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

H2 - H2GIS geojson #1198

Open
ebocher opened this issue Mar 9, 2021 · 3 comments
Open

H2 - H2GIS geojson #1198

ebocher opened this issue Mar 9, 2021 · 3 comments

Comments

@ebocher
Copy link
Member

ebocher commented Mar 9, 2021

It'd be nice to support this kind of query in the famous H2 db with H2GIS functions

select json_build_object(
    'type', 'FeatureCollection',
    'features', json_agg(ST_AsGeoJSON(t.*)::json)
    )
from ( values (1, 'one', 'POINT(1 1)'::geometry),
              (2, 'two', 'POINT(2 2)'),
              (3, 'three', 'POINT(3 3)')
     ) as t(id, name, geom);

return

{"type" : "FeatureCollection", "features" : [{"type": "Feature", "geometry": {"type":"Point","coordinates":[1,1]}, "properties": {"id": 1, "name": "one"}}, {"type": "Feature", "geometry": {"type":"Point","coordinates":[2,2]}, "properties": {"id": 2, "name": "two"}}, {"type": "Feature", "geometry": {"type":"Point","coordinates":[3,3]}, "properties": {"id": 3, "name": "three"}}]}

Source : http://postgis.net/docs/ST_AsGeoJSON.html

@katzyn
Copy link

katzyn commented Mar 9, 2021

I don't know what t.* means here, but it doesn't look like valid SQL for almost all DBMS.

There is no JSON_BUILD_OBJECT in H2, but H2 has standard-compliant JSON_OBJECT function.

There is no JSON_AGG in H2, but H2 has standard-compliant JSON_ARRAYAGG.

Personally I don't think that H2 should support PostgreSQL-specific functions, because people who use them typically also use obscure PostgreSQL-specific operators in combination with them, these operators aren't supported by H2 and most likely aren't going to be supported.

H2 actually can write GeoJSON by itself, but if you need features, you need to construct them with JSON functions, subqueries, etc.

SELECT CAST(GEOMETRY 'POINT(1 1)' AS JSON);
> {"type":"Point","coordinates":[1,1]}

@ebocher
Copy link
Member Author

ebocher commented Mar 9, 2021

I don't want to support PostgreSQL-specific functions but find a way to return

{"type" : "FeatureCollection", "features" : [{"type": "Feature", "geometry": {"type":"Point","coordinates":[1,1]}, "properties": {"id": 1, "name": "one"}}, {"type": "Feature", "geometry": {"type":"Point","coordinates":[2,2]}, "properties": {"id": 2, "name": "two"}}, {"type": "Feature", "geometry": {"type":"Point","coordinates":[3,3]}, "properties": {"id": 3, "name": "three"}}]}

with H2 functions will be good.

Something like :

SELECT JSON_OBJECT("type" : "FeatureCollection", "features": JSON_ARRAY())   FROM mygeotable

@katzyn
Copy link

katzyn commented Mar 9, 2021

You can use something like

SELECT JSON_OBJECT(
'type': 'FeatureCollection',
'features': (
SELECT JSON_ARRAYAGG(
JSON_OBJECT('type': 'feature', 'geometry': GEOM, 'properties': JSON_OBJECT('id': ID, 'name': NAME)))
FROM (VALUES
(1, 'one', GEOMETRY 'POINT(1 1)'),
(2, 'two', GEOMETRY 'POINT(2 2)'),
(3, 'three', GEOMETRY 'POINT(3 3)')
) T(ID, NAME, GEOM)));

It returns

{"type":"FeatureCollection","features":[{"type":"feature","geometry":{"type":"Point","coordinates":[1,1]},"properties":{"id":1,"name":"one"}},{"type":"feature","geometry":{"type":"Point","coordinates":[2,2]},"properties":{"id":2,"name":"two"}},{"type":"feature","geometry":{"type":"Point","coordinates":[3,3]},"properties":{"id":3,"name":"three"}}]}

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

2 participants