Skip to navigation
SQL ReferenceGeospatial Functions

Point Constructors

View as Markdown

OcientGeo™ POINT constructors use geospatial data to create a POINT object.

ST_CENTROID 

Returns a POINT that is the geographic center of mass of a specified geospatial object.

The geographic center of mass is calculated by taking the average of all points on a three-dimensional sphere, projecting the resultant point onto the sphere, and converting it back to latitude and longitude coordinates.

Syntax

ST_CENTROID( geo [, use_spheroid ] )
ArgumentData TypeDescription
geoPOINT, LINESTRING, or POLYGONA geospatial object that the function analyzes to compute its geometric center.
use_spheroidBOOLEANOptional.
If this argument is TRUE, the function computes the centroid using a spheroid model instead of a spherical model.
If you do not specify this value, the value defaults to FALSE.

Example

SELECT ST_CENTROID(ST_POLYGON('POLYGON((1 1, 2 1, 2 2, 1 1))'));

Output: POINT(1.66 1.33) 

ST_GEOGPOINT

Alias for ST_POLYGON(ST_LINESTRING(ARRAY(ST_POINT(lon, lat)))).

Creates and returns a POLYGON geography with a single point, defined by the longitude and latitude specified for the function.

Syntax

ST_GEOGPOINT(lon, lat)
ArgumentData TypeDescription
lonDOUBLEA longitude coordinate to be used to create a POLYGON with a single point.
latDOUBLEA latitude coordinate to be used to create a POLYGON with a single point.

Example

SELECT ST_GEOGPOINT(41.882548,-87.63922);

Output: POLYGON((41.882548 -87.63922))

ST_MAKEPOINT

Alias for ST_POINT.

ST_POINT

Creates a POINT from the specified input arguments.

ST_POINT(binary)

Alias for ST_POINTFROMWKB.

Creates a POINT from the specified BINARY value, which must represent a point in WKB format.

Syntax

ST_POINT(binary)
ArgumentData TypeDescription
binaryBINARYA binary value in WKB format that represents a POINT.

Example

SELECT ST_POINT(BINARY('0x0101000000000000000000f03f0000000000000040'));

Output: POINT(1 2)

ST_POINT(char)

Creates a POINT from a CHAR value that represents a point in WKT format.

Syntax

ST_POINT(char)
ArgumentData TypeDescription
charCHARA CHAR that represents a POINT in WKT format.

Example

SELECT ST_POINT('POINT(1 2)');

Output: POINT(1 2)

ST_POINT(lon, lat)

Creates a POINT defined with the values provided for the longitude and latitude coordinates.

Syntax

ST_POINT(lon, lat)
ArgumentData TypeDescription
lonDOUBLEA DOUBLE value that represents a longitude coordinate.
latDOUBLEA DOUBLE value that represents a latitude coordinate.

Example

SELECT ST_POINT(87.6386, 41.8843);

Output: POINT(87.6386 41.8843)

ST_POINTFROMEWKT

Alias for ST_POINT.

Creates a POINT using an EWKT-formatted CHAR as an input argument.

Syntax

ST_POINTFROMEWKT(srid_char)
ArgumentData TypeDescription
srid_charCHARA string that includes a Spatial Reference Identifier (SRID) value and an EWKT-formatted value that represents a POINT.
Separate the values in the string by a semicolon using this format:
'SRID = value; point_value'
The input string must include the SRID = value; component. However, the database ignores this component as all Ocient® geography types are SRID = 4326.

Example

SELECT ST_POINTFROMEWKT('SRID=4326;POINT(1 2)');

Output: POINT(1 2)

ST_POINTFROMGEOJSON

Creates a POINT represented by the specified GeoJSON value as an input argument. A second optional BOOLEAN argument determines if the specified GeoJSON should be converted from a planar to a geodesic representation.

If you specify a NULL value for either argument, the function returns NULL.

Syntax

ST_POINTFROMGEOJSON(geojson [, geodesic ] )
ArgumentData TypeDescription
geo_jsonSTRINGA string of a GeoJSON.
Valid GeoJSON formats follow IETF® standards, which you can generate using the ST_ASGEOJSON function.
If you specify an invalid GeoJSON, the behavior of the function is undefined. The function returns NULL if any argument is NULL.
geodesicBOOLEANOptional.
If you set this argument to TRUE, the specified GeoJSON is converted from a planar to a geodesic representation. This does not affect POINT geospatial objects.
The default value is FALSE.

Examples

In this example, the function captures a POINT from an empty GeoJSON representation.

SELECT ST_POINTFROMGEOJSON('{"type":"Point","coordinates":[]}',false);

Output: POINT EMPTY

In this example, the function captures a POINT from a GeoJSON representation.

SELECT ST_POINTFROMGEOJSON('{"type":"Point","coordinates":[1,1]}',false);

Output: POINT(1 1)

ST_POINTFROMGEOHASH

Creates a POINT represented by the specified geohash.

Syntax

ST_POINTFROMGEOHASH(geohash)
ArgumentData TypeDescription
geohashSTRINGA string of a valid geohash.
Valid geohashes contain only the characters 0123456789bcdefghjkmnpqrstuvwxyz.
If you specify an invalid geohash, the function throws an INVALID_ARGUMENT error. The function returns NULL if this argument is NULL.

Examples

SELECT ST_POINTFROMGEOHASH('c0w3hf1s70w3hf1s70w3');

Output: POINT(-126.00000000000003 48.00000000000007)

SELECT ST_POINTFROMGEOHASH('kzn21');

Output: POINT(42.60498046875 -5.60302734375)

ST_POINTFROMTEXT

Alias for ST_POINT(char).

ST_POINTFROMWKB

Alias for ST_POINT(binary).

Related Links

Geospatial Data Types

Conversion Functions