H2 - H2GIS geojson

[ebocher]

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]

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]

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]

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"}}]}