The SE_GeomFromBSON() function

The SE_GeomFromBson() function takes a bson value with spatial data following the GeoJSON specification and a JSON path in a VARCHAR string, and returns an ST_Geometry object.

Syntax

create function SE_GeomFromBSON(bson,varchar(255))
returns ST_Geometry

Return type

ST_Geometry

Example

The city engineer wants to know all the geometry values of the cities.

The following code inserts six rows, one for each GeoJSON geometry type: Point, LineString, Polygon, MultiPoint, MultiLineString, MultiPolygon.

-- create a table of bson value containing GeoJson spatial values under the city tag
create table bson_tab (pid serial, geometry bson, type varchar(16));
Table created.

-- insert values for Point, LineString, Polygon, MultiPoint, MultiLineString, MultiPolygon
insert into bson_tab values(0, ('{
		"city":{
			"type":"Point",
			"coordinates" : [3, 2]
		}
	 }'::json)::bson,
	'Point');
1 row(s) inserted.

insert into bson_tab values(0, ('{
		"city":{
			"type":"LineString",
			"coordinates" : [[1, 0], [3, 2], [4, 5]]
		}
	 }'::json)::bson,
	'LineString');
1 row(s) inserted.

insert into bson_tab values(0, ('{
		"city":{
			"type":"Polygon",
			"coordinates" : [[[0, 0], [10, 0], [10, 10], [0, 10], [0, 0]], [[2, 2], [8, 2], [8, 8], [2, 8], [2, 2]]]
		}
	 }'::json)::bson,
	'Polygon');
1 row(s) inserted.

insert into bson_tab values(0, ('{
		"city":{
			"type":"MultiPoint",
			"coordinates" : [[1, 0], [3, 2], [3, 2], [4, 5]]
		}
	 }'::json)::bson,
	'MultiPoint');
1 row(s) inserted.

insert into bson_tab values(0, ('{
		"city":{
			"type":"MultiLineString",
			"coordinates" : [[[1, 0], [3, 2], [4, 5]], [[11, 10], [13, 12]], [[111, 11], [13, 21]], [[11, 10], [13, 12], [131, 12], [132, 13], [134, 15], [135, 16]]]
		}
	 }'::json)::bson,
	'MultiLineString');
1 row(s) inserted.

insert into bson_tab values(0, ('{
		"city":{
			"type":"MultiPolygon",
			"coordinates" : [[[[0,  0], [10,  0], [10, 10], [0, 10], [0, 0]], [[2, 2], [8, 2], [8, 8], [2, 8], [2, 2]]], [[[22, 22],[[28, 28],[22, 28],[22, 22]]]]
		}
	 }'::json)::bson,
	'MultiPolygon');
1 row(s) inserted.

The query selects geometry values of ALL types from the table by using SE_GeomFromBSON.

select pid,se_geomfrombson(geometry,'city') from bson_tab;

This is the result.

pid           1
(expression)  4326 POINT (3 2) 

pid           2
(expression)  4326 LINESTRING (1 0, 3 2, 4 5) 

pid           3
(expression)  4326 POLYGON ((0 0, 10 0, 10 10, 0 10, 0 0),(2 2, 2 8, 8 8, 8 2, 2 2)) 

pid           4
(expression)  4326 MULTIPOINT (1 0, 3 2, 3 2, 4 5) 

pid           5
(expression)  4326 MULTILINESTRING ((1 0, 3 2, 4 5),(11 10, 13 12),(111 11, 13 21),(11 10, 13 12, 131 12, 132 13, 133 14, 134 15, 135 16)) 

pid           6
(expression)  4326 MULTIPOLYGON (((0 0, 10 0, 10 10, 0 10, 0 0),(2 2, 2 8, 8 8, 8 2, 2 2)),((22 22, 28 22, 28 28, 22 28, 22 22))) 

6 row(s) retrieved.