geo_json ​

Description ​

One GeoJSON FeatureCollection. Each SQL row becomes a Feature. The output detects its native geometry column. Supported shapes are Point, MultiPoint, LineString, MultiLineString, Polygon and MultiPolygon, using native coordinate types. GeometryCollection is unsupported; geo_json_unsupported_geometry='null' can replace it with NULL on input.

Input and output. Select it with FORMAT geo_json. Names ignore ASCII case; use the snake_case name shown here.

Output detects exactly one column containing a native geometry alias; there is no geometry-column setting. For an all-NULL geometry column, name it geometry. A column named id becomes the feature identifier; other columns become properties. A single object-valued column named properties can supply that object directly.

Input binds the fixed id, geometry and properties members. Coordinates must be finite. Geometry validation additionally checks line/ring sizes, ring closure and nonempty collections. GeometryCollection is not supported; the configured null policy can validate and replace it on input.

SELECT expressions FORMAT geo_json;
INSERT INTO table [(columns)] [SETTINGS ...] FORMAT geo_json
<data>

Example usage ​

SELECT CAST('[1,2]' AS Point) AS geometry, 'Ada' AS name FORMAT geo_json;

{"type":"FeatureCollection","features":[{"type":"Feature","geometry":{"type":"Point","coordinates":[1,2]},"properties":{"name":"Ada"}}]}

Output has no trailing newline.

Detecting the geometry by its value type ​

The geometry column can have an application-specific name:

SELECT CAST('[1,2]' AS Point) AS location, 'Depot' AS name
FORMAT geo_json;

{"type":"FeatureCollection","features":[{"type":"Feature","geometry":{"type":"Point","coordinates":[1,2]},"properties":{"name":"Depot"}}]}

Output has no trailing newline.

Format settings ​

Use a statement SETTINGS clause; these options are not persisted by SET.