GeoJSONFromGeom Function Examples | Teradata Vantage - GeoJSONFromGeom Examples - Advanced SQL Engine - Teradata Database

JSON Data Type

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Published
July 2021
Language
English (United States)
Last Update
2021-07-27
dita:mapPath
uwa1591040057999.ditamap
dita:ditavalPath
uwa1591040057999.ditaval
dita:id
B035-1150
lifecycle
previous
Product Category
Teradata Vantageā„¢
These examples show the following conversions of ST_Geometry objects to JSON documents that conform to GeoJSON standards:
  • Conversion of Point ST_Geometry objects to JSON documents that have a data type of VARCHAR, CLOB, or JSON.
  • Conversion of various ST_Geometry objects to JSON documents that have a data type of VARCHAR(2000) CHARACTER SET LATIN.

Example: Conversion to a JSON Document with a VARCHAR Type

SELECT (GeoJSONFromGeom(new ST_Geometry('Point(45.12345 85.67891)'))
RETURNS VARCHAR(2000) CHARACTER SET LATIN);

Result:

GeoJSONFromGeom( NEW ST_GEOMETRY('Point(45.12345 85.67891)'))
-------------------------------------------------------------
{ "type": "Point", "coordinates": [ 45.12345, 85.67891 ] }

Example: Conversion to a JSON Document with a CLOB Type

SELECT (GeoJSONFromGeom(new ST_Geometry('Point(45.12345 85.67891)'))
RETURNS CLOB CHARACTER SET UNICODE);

Result:

GeoJSONFromGeom( NEW ST_GEOMETRY('Point(45.12345 85.67891)'))
-------------------------------------------------------------
{ "type": "Point", "coordinates": [ 45.12345, 85.67891 ] }

Example: Conversion to a JSON Document with a JSON Type

SELECT (GeoJSONFromGeom(new ST_Geometry('Point(45.12345 85.67891)'))
RETURNS JSON(2000) CHARACTER SET LATIN);

Result:

GeoJSONFromGeom( NEW ST_GEOMETRY('Point(45.12345 85.67891)'))
-------------------------------------------------------------
{ "type": "Point", "coordinates": [ 45.12345, 85.67891 ] }

Example: Convert LineString ST_Geometry Object

SELECT (GeoJSONFromGeom (new ST_Geometry('LineString(10 20, 50 80, 200 50)') )
RETURNS VARCHAR(2000) CHARACTER SET LATIN);

Result:

GeoJSONFromGeom( NEW ST_GEOMETRY('LineString(10 20, 50 80, 200 50)'))
---------------------------------------------------------------------
{ "type": "LineString", "coordinates": [ [ 10.0, 20.0 ], [ 50.0, 80.0 ],
[ 200.0, 50.0 ] ] }

Example: Convert Polygon ST_Geometry Object

SELECT (GeoJSONFromGeom (new ST_Geometry('Polygon((0 0, 0 10, 10 10, 10 0, 0 0))',
4326) )
RETURNS VARCHAR(2000) CHARACTER SET LATIN);

Result:

GeoJSONFromGeom( NEW ST_GEOMETRY('Polygon((0 0, 0 10, 10 10, 10 0, 0 0))', 4326))
---------------------------------------------------------------------------------
{ "type": "Polygon", "coordinates": [ [ [ 0.0, 0.0 ], [ 0.0, 10.0 ], [ 10.0, 10.0 ],
[ 10.0, 0.0 ], [ 0.0, 0.0 ] ] ] }

Example: Convert MultiPoint ST_Geometry Object

SELECT (GeoJSONFromGeom (new ST_Geometry('MultiPoint(10 20, 50 80, 200 50)',
4326) )
RETURNS VARCHAR(2000) CHARACTER SET LATIN);

Result:

GeoJSONFromGeom( NEW ST_GEOMETRY('MultiPoint(10 20, 50 80, 200 50)', 4326))
---------------------------------------------------------------------------
{ "type": "MultiPoint", "coordinates": [ [ 10.0, 20.0 ], [ 50.0, 80.0 ],
[ 200.0, 50.0 ] ] }

Example: Convert MultiLineString ST_Geometry Object

SELECT (GeoJSONFromGeom (new ST_Geometry(
'MultiLineString((10 20, 50 80, 200 50), (0 100, 10 220, 20 240))', 4326) )
RETURNS VARCHAR(2000) CHARACTER SET LATIN);

Result:

GeoJSONFromGeom( NEW ST_GEOMETRY('MultiLineString((10 20, 50 80, 200 50),
(0 100, 10 220, 20 240))', 4326))
-------------------------------------------------------------------------
{ "type": "MultiLineString", "coordinates": [ [ [ 10.0, 20.0 ], [ 50.0, 80.0 ], 
[ 200.0, 50.0 ] ], [ [ 0.0, 100.0 ], [ 10.0, 220.0 ], [ 20.0, 240.0 ] ] ] }

Example: Convert MultiPolygon ST_Geometry Object

SELECT (GeoJSONFromGeom (new ST_Geometry(
'MultiPolygon(((0 0, 0 10, 10 10, 10 0, 0 0)), ((0 50, 0 100, 100 100, 100 50, 0 50)))',
4326) )
RETURNS VARCHAR(2000) CHARACTER SET LATIN);

Result:

GeoJSONFromGeom( NEW ST_GEOMETRY('MultiPolygon(((0 0, 0 10, 10 10, 10 0, 0 0)),
((0 50, 0 100, 100 100, 100 50, 0 50)))', 4326))
-------------------------------------------------------------------------------
{ "type": "MultiPolygon", "coordinates": [ [ [ [ 0.0, 0.0 ], [ 0.0, 10.0 ], 
[ 10.0, 10.0 ], [ 10.0, 0.0 ], [ 0.0, 0.0 ] ] ], [ [ [ 0.0, 50.0 ], [ 0.0, 100.0 ],
[ 100.0, 100.0 ], [ 100.0, 50.0 ], [ 0.0, 50.0 ] ] ] ] }

Example: Convert GeometryCollection ST_Geometry Object

SELECT (GeoJSONFromGeom (new ST_Geometry(
'GeometryCollection(point(10 20), linestring(50 80, 200 50))', 4326) )
RETURNS VARCHAR(2000) CHARACTER SET LATIN);

Result:

GeoJSONFromGeom( NEW ST_GEOMETRY('GeometryCollection(point(10 20),
linestring(50 80, 200 50))', 4326))
------------------------------------------------------------------
{ "type": "GeometryCollection", "geometries": [ { "type": "Point", "coordinates":
[ 10.0, 20.0 ] }, { "type": "LineString", "coordinates": [ [ 50.0, 80.0 ],
[ 200.0, 50.0 ] ] } ] }