ST_GeomFromGeoJSON

Converts the geometry portion of a GeoJSON record in the standard format into a GEOMETRY object.

Converts the geometry portion of a GeoJSON record in the standard format into a GEOMETRY object. This function returns an error when you provide a GeoJSON Feature or FeatureCollection object.

Behavior type

Immutable

Syntax

ST_GeomFromGeoJSON( geojson [, srid] [ USING PARAMETERS param=value[,...] ] );

Arguments

geojson

String containing a GeoJSON GEOMETRY object, type LONG VARCHAR.

Vertica accepts the following GeoJSON key values:

type
coordinates
geometries

Other key values are ignored.

srid

Spatial reference system identifier (SRID) of the GEOMETRY object, type INTEGER.

The SRID is stored in the GEOMETRY object, but does not influence the results of spatial computations.

This argument is optional when not performing operations.

Parameters

ignore_3d

(Optional) Boolean, whether to silently remove 3D and higher-dimensional data from the returned GEOMETRY object or return an error, based on the following values:

true: Removes 3D and higher-dimensional data from the returned GEOMETRY object.
false (default): Returns an error when the GeoJSON contains 3D or higher-dimensional data.

ignore_errors

(Optional) Boolean, whether to ignore errors on invalid GeoJSON objects or return an error, based on the following values:

true: Ignores errors during GeoJSON parsing and returns NULL.
false (default): Returns an error if GeoJSON parsing fails.

Note

The ignore_errors setting takes precedence over the ignore_3d setting. For example, if ignore_errors is set to true and ignore_3d is set to false, the function returns NULL if a GeoJSON object contains 3D and higher-dimensional data.

Returns

GEOMETRY

Supported data types

Point
Multipoint
Linestring
Multilinestring
Polygon
Multipolygon
GeometryCollection

Examples

The following example shows how to use ST_GeomFromGeoJSON.

Validating a single record

The following example validates a ST_GeomFromGeoJSON statement with ST_IsValid. The statement includes the SRID 4326 to indicate that the point data type represents latitude and longitude coordinates, and sets ignore_3d to true to ignore the last value that represents the altitude:

=> SELECT ST_IsValid(ST_GeomFromGeoJSON('{"type":"Point","coordinates":[35.3606, 138.7274, 29032]}', 4326 USING PARAMETERS ignore_3d=true));
 ST_IsValid
------------
 t
(1 row)

Loading data into a table

The following example processes GeoJSON types from STDIN and stores them in a GEOMETRY data type table column:

Create a table named polygons that stores GEOMETRY spatial types:
```
=> CREATE TABLE polygons(geom GEOMETRY(1000));
CREATE TABLE
```

Use COPY to read supported GEOMETRY data types from STDIN and store them in an object named geom:

=> COPY polygons(geojson filler VARCHAR(1000), geom as ST_GeomFromGeoJSON(geojson)) FROM STDIN;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> { "type": "Polygon", "coordinates": [ [ [100.0, 0.0], [101.0, 0.0], [101.0, 1.0], [100.0, 1.0], [100.0, 0.0] ] ] }
>> { "type": "Point", "coordinates": [1, 2] }
>> { "type": "Polygon", "coordinates": [ [ [1, 3], [3, 2], [1, 1], [3, 0], [1, 0], [1, 3] ] ] }
>> \.

Query the polygons table. The following example uses ST_AsText to return the geom object in its Well-known text (WKT) representation, and uses ST_IsValid to validate each object:

=> SELECT ST_AsText(geom), ST_IsValid(geom) FROM polygons;
                   ST_AsText                   | ST_IsValid
-----------------------------------------------+------------
 POINT (1 2)                                   | t
 POLYGON ((1 3, 3 2, 1 1, 3 0, 1 0, 1 3))      | f
 POLYGON ((100 0, 101 0, 101 1, 100 1, 100 0)) | t
(3 rows)