The SE_PolyFromBSON() function

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

Syntax

create function SE_PolyFromBSON(bson,varchar(255))
returns ST_Polygon

Return type

ST_Polygon

Example

The city engineer wants to know the “Polygon” of a city, an array of one or more LinearRings. A LinearRing is closed LineString with 4 or more positions. The first and last positions are equivalent (they represent equivalent points). Though a LinearRing is not explicitly represented as a GeoJSON geometry type, it is referred to in the Polygon geometry type definition.

The following code inserts one row for GeoJSON geometry type Polygon.

-- 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 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.

The query selects a polygon value from the table by using SE_PolyFromBSON.

select pid,se_polyfrombson(geometry,'city') from bson_tab where type = 'Polygon' ;

This is the result.

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)) 

1 row(s) retrieved.