Geospatial functions

• 1: ST_Area
• 2: ST_AsBinary
• 3: ST_AsText
• 4: ST_Boundary
• 5: ST_Buffer
• 6: ST_Centroid
• 7: ST_Contains
• 8: ST_ConvexHull
• 9: ST_Crosses
• 10: ST_Difference
• 11: ST_Disjoint
• 12: ST_Distance
• 13: ST_Envelope
• 14: ST_Equals
• 15: ST_GeographyFromText
• 16: ST_GeographyFromWKB
• 17: ST_GeoHash
• 18: ST_GeometryN
• 19: ST_GeometryType
• 20: ST_GeomFromGeoHash
• 21: ST_GeomFromGeoJSON
• 22: ST_GeomFromText
• 23: ST_GeomFromWKB
• 24: ST_Intersection
• 25: ST_Intersects
• 26: ST_IsEmpty
• 27: ST_IsSimple
• 28: ST_IsValid
• 29: ST_Length
• 30: ST_NumGeometries
• 31: ST_NumPoints
• 32: ST_Overlaps
• 33: ST_PointFromGeoHash
• 34: ST_PointN
• 35: ST_Relate
• 36: ST_SRID
• 37: ST_SymDifference
• 38: ST_Touches
• 39: ST_Transform
• 40: ST_Union
• 41: ST_Within
• 42: ST_X
• 43: ST_XMax
• 44: ST_XMin
• 45: ST_Y
• 46: ST_YMax
• 47: ST_YMin
• 48: STV_AsGeoJSON
• 49: STV_Create_Index
• 50: STV_Describe_Index
• 51: STV_Drop_Index
• 52: STV_DWithin
• 53: STV_Export2Shapefile
• 54: STV_Extent
• 55: STV_ForceLHR
• 56: STV_Geography
• 57: STV_GeographyPoint
• 58: STV_Geometry
• 59: STV_GeometryPoint
• 60: STV_GetExportShapefileDirectory
• 61: STV_Intersect scalar function
• 62: STV_Intersect transform function
• 63: STV_IsValidReason
• 64: STV_LineStringPoint
• 65: STV_MemSize
• 66: STV_NN
• 67: STV_PolygonPoint
• 68: STV_Refresh_Index
• 69: STV_Rename_Index
• 70: STV_Reverse
• 71: STV_SetExportShapefileDirectory
• 72: STV_ShpCreateTable
• 73: STV_ShpSource and STV_ShpParser

1 - ST_Area

Calculates the area of a spatial object.

The units are:

• GEOMETRY objects: spatial reference system identifier (SRID) units

• GEOGRAPHY objects: square meters

Behavior type

Syntax

ST_Area( g )

Arguments

g

Spatial object for which you want to calculate the area, type GEOMETRY or GEOGRAPHY

Returns

FLOAT

Supported data types

Examples

The following examples show how to use ST_Area.

Calculate the area of a polygon:

=> SELECT ST_Area(ST_GeomFromText('POLYGON((0 0,1 0,1 1,0 1,0 0))'));
 ST_Area
---------
       1
(1 row)

Calculate the area of a multipolygon:

=> SELECT ST_Area(ST_GeomFromText('MultiPolygon(((0 0,1 0,1 1,0 1,0 0)),
   ((2 2,2 3,4 6,3 3,2 2)))'));
 ST_Area
---------
     3
(1 row)

Suppose the polygon has a hole, as in the following figure.

Calculate the area, excluding the area of the hole:

=> SELECT ST_Area(ST_GeomFromText('POLYGON((2 2,5 5,8 2,2 2),
   (4 3,5 4,6 3,4 3))'));
 ST_Area
---------
       8
(1 row)

Calculate the area of a geometry collection:

=> SELECT ST_Area(ST_GeomFromText('GEOMETRYCOLLECTION(POLYGON((20.5 20.45,
   20.51 20.52,20.69 20.32,20.5 20.45)),POLYGON((10 20,30 40,25 50,10 20)))'));
 ST_Area
----------
 150.0073
(1 row)

Calculate the area of a geography object:

=> SELECT ST_Area(ST_GeographyFromText('POLYGON((20.5 20.45,20.51 20.52,
   20.69 20.32,20.5 20.45))'));
     ST_Area
------------------
 84627437.116037
(1 row)

2 - ST_AsBinary

Creates the Well-Known Binary (WKB) representation of a spatial object. Use this function when you need to convert an object to binary form for porting spatial data to or from other applications.

The Open Geospatial Consortium (OGC) defines the format of a WKB representation in the Simple Feature Access Part 1 - Common Architecture specification.

Behavior type

Syntax

ST_AsBinary( g )

Arguments

g

Spatial object for which you want the WKB, type GEOMETRY or GEOGRAPHY

Returns

LONG VARBINARY

Supported data types

Examples

The following example shows how to use ST_AsBinary.

Retrieve WKB and WKT representations:

=> CREATE TABLE locations (id INTEGER, name VARCHAR(100), geom1 GEOMETRY(800), geom2 GEOGRAPHY);
CREATE TABLE
=> COPY locations
    (id, geom1x FILLER LONG VARCHAR(800), geom1 AS ST_GeomFromText(geom1x), geom2x FILLER LONG VARCHAR (800),
     geom2 AS ST_GeographyFromText(geom2x))
   FROM stdin;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> 1|POINT(2 3)|
>> 2|LINESTRING(2 4,1 5)|
>> 3||POLYGON((-70.96 43.27,-70.67 42.95,-66.90 44.74,-67.81 46.08,-67.81 47.20,-69.22 47.43,-71.09 45.25,-70.96 43.27))
>> \.
=> SELECT id, ST_AsText(geom1),ST_AsText(geom2) FROM locations ORDER BY id ASC;
 id |       ST_AsText       |                 ST_AsText
----+-----------------------+---------------------------------------------
  1 | POINT (2 3)           |
  2 | LINESTRING (2 4, 1 5) |
  3 |                       | POLYGON ((-70.96 43.27, -70.67 42.95, -66.9 44.74, -67.81 46.08, -67.81 47.2, -69.22 47.43, -71.09 45.25, -70.96 43.27))
=> SELECT id, ST_AsBinary(geom1),ST_AsBinary(geom2) FROM locations ORDER BY id ASC;
.
.
.
(3 rows)

Calculate the length of a WKB using the Vertica SQL function LENGTH:

=> SELECT LENGTH(ST_AsBinary(St_GeomFromText('POLYGON ((-1 2, 0 3, 1 2,
                                                        0 1, -1 2))')));
 LENGTH
--------
     93
(1 row)

See also

3 - ST_AsText

Creates the Well-Known Text (WKT) representation of a spatial object. Use this function when you need to specify a spatial object in ASCII form.

The Open Geospatial Consortium (OGC) defines the format of a WKT string in the Simple Feature Access Part 1 - Common Architecture specification.

Behavior type

Syntax

ST_AsText( g )

Arguments

g

Spatial object for which you want the WKT string, type GEOMETRY or GEOGRAPHY

Returns

LONG VARCHAR

Supported data types

Examples

The following example shows how to use ST_AsText.

Retrieve WKB and WKT representations:

=> CREATE TABLE locations (id INTEGER, name VARCHAR(100), geom1 GEOMETRY(800),
   geom2 GEOGRAPHY);
CREATE TABLE
=> COPY locations
    (id, geom1x FILLER LONG VARCHAR(800), geom1 AS ST_GeomFromText(geom1x), geom2x FILLER LONG VARCHAR (800),
     geom2 AS ST_GeographyFromText(geom2x))
    FROM stdin;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> 1|POINT(2 3)|
>> 2|LINESTRING(2 4,1 5)|
>> 3||POLYGON((-70.96 43.27,-70.67 42.95,-66.90 44.74,-67.81 46.08,-67.81 47.20,-69.22 47.43,-71.09 45.25,-70.96 43.27))
>> \.
=> SELECT id, ST_AsText(geom1),ST_AsText(geom2) FROM locations ORDER BY id ASC;
 id |       ST_AsText       |                 ST_AsText
----+-----------------------+---------------------------------------------
  1 | POINT (2 3)           |
  2 | LINESTRING (2 4, 1 5) |
  3 |                       | POLYGON ((-70.96 43.27, -70.67 42.95, -66.9 44.74, -67.81 46.08, -67.81 47.2, -69.22 47.43, -71.09 45.25, -70.96 43.27))
(3 rows)

Calculate the length of a WKT using the Vertica SQL function LENGTH:

=> SELECT LENGTH(ST_AsText(St_GeomFromText('POLYGON ((-1 2, 0 3, 1 2,
                                                      0 1, -1 2))')));
 LENGTH
--------
     37
(1 row)

See also

• ST_AsBinary

4 - ST_Boundary

Calculates the boundary of the specified GEOMETRY object. An object's boundary is the set of points that define the limit of the object.

For a linestring, the boundary is the start and end points. For a polygon, the boundary is a linestring that begins and ends at the same point.

Behavior type

Syntax

ST_Boundary( g )

Arguments

g

Spatial object for which you want the boundary, type GEOMETRY

Returns

GEOMETRY

Supported data types

Examples

The following examples show how to use ST_Boundary.

Returns a linestring that represents the boundary:

=> SELECT ST_AsText(ST_Boundary(ST_GeomFromText('POLYGON((-1 -1,2 2,
   0 1,-1 -1))')));
 ST_AsText
--------------
LINESTRING(-1 -1, 2 2, 0 1, -1 -1)
(1 row)

Returns a multilinestring that contains the boundaries of both polygons:

=> SELECT ST_AsText(ST_Boundary(ST_GeomFromText('POLYGON((2 2,5 5,8 2,2 2),
   (4 3,5 4,6 3,4 3))')));
                            ST_AsText
------------------------------------------------------------------
 MULTILINESTRING ((2 2, 5 5, 8 2, 2 2), (4 3, 5 4, 6 3, 4 3))
(1 row)

The boundary of a linestring is its start and end points:

=> SELECT ST_AsText(ST_Boundary(ST_GeomFromText(
   'LINESTRING(1 1,2 2,3 3,4 4)')));
       ST_AsText
-----------------------
 MULTIPOINT (1 1, 4 4)
(1 row)

A closed linestring has no boundary because it has no start and end points:

=> SELECT ST_AsText(ST_Boundary(ST_GeomFromText(
   'LINESTRING(1 1,2 2,3 3,4 4,1 1)')));
    ST_AsText
------------------
 MULTIPOINT EMPTY
(1 row)

5 - ST_Buffer

Creates a GEOMETRY object greater than or equal to a specified distance from the boundary of a spatial object. The distance is measured in Cartesian coordinate units. ST_Buffer does not accept a distance size greater than +1e15 or less than –1e15.

Behavior type

Syntax

ST_Buffer( g, d )

Arguments

g

Spatial object for which you want to calculate the buffer, type GEOMETRY

d

Distance from the object in Cartesian coordinate units, type FLOAT

Returns

GEOMETRY

Supported data types

Usage tips

• If you specify a positive distance, ST_Buffer returns a polygon that represents the points within or equal to the distance outside the object. If you specify a negative distance, ST_Buffer returns a polygon that represents the points within or equal to the distance inside the object.

• For points, multipoints, linestrings, and multilinestrings, if you specify a negative distance, ST_Buffer returns an empty polygon.

• The Vertica Place version of ST_Buffer returns the buffer as a polygon, so the buffer object has corners at its vertices. It does not contain rounded corners.

Examples

The following example shows how to use ST_Buffer.

Returns a GEOMETRY object:

=> SELECT ST_AsText(ST_Buffer(ST_GeomFromText('POLYGON((0 1,1 4,4 3,0 1))'),1));
                                  ST_AsText
------------------------------------------------------------------------------
POLYGON ((-0.188847498856 -0.159920845081, -1.12155598386 0.649012935089, 0.290814745534 4.76344136152,
 0.814758063466 5.02541302048, 4.95372324225 3.68665254814, 5.04124517538 2.45512549204, -0.188847498856 -0.159920845081))
(1 row)

6 - ST_Centroid

Calculates the geometric center—the centroid—of a spatial object. If points or linestrings or both are present in a geometry with polygons, only the polygons contribute to the calculation of the centroid. Similarly, if points are present with linestrings, the points do not contribute to the calculation of the centroid.

To calculate the centroid of a GEOGRAPHY object, see the examples for STV_Geometry and STV_Geography.

Behavior type

Syntax

ST_Centroid( g )

Arguments

g

Spatial object for which you want to calculate the centroid, type GEOMETRY

Returns

GEOMETRY (POINT only)

Supported data types

Examples

The following examples show how to use ST_Centroid.

Calculate the centroid for a polygon:

=> SELECT ST_AsText(ST_Centroid(ST_GeomFromText('POLYGON((-1 -1,2 2,-1 2,
   -1 -1))')));
  ST_AsText
------------
 POINT (-0 1)
(1 row)

Calculate the centroid for a multipolygon:

=> SELECT ST_AsText(ST_Centroid(ST_GeomFromText('MULTIPOLYGON(((1 0,2 1,2 0,
   1 0)),((-1 -1,2 2,-1 2,-1 -1)))')));
               ST_AsText
--------------------------------------
 POINT (0.166666666667 0.933333333333)
(1 row)

This figure shows the centroid for the multipolygon.

7 - ST_Contains

Determines if a spatial object is entirely inside another spatial object without existing only on its boundary. Both arguments must be the same spatial data type. Either specify two GEOMETRY objects or two GEOGRAPHY objects.

If an object such as a point or linestring only exists along a spatial object's boundary, then ST_Contains returns false. The interior of a linestring is all the points on the linestring except the start and end points.

ST_Contains(g1, g2) is functionally equivalent to ST_Within(g2, g1).

GEOGRAPHY Polygons with a vertex or border on the International Date Line (IDL) or the North or South pole are not supported.

Behavior type

Syntax

ST_Contains( g1, g2
                        [USING PARAMETERS spheroid={true | false}] )

Arguments

g1

Spatial object, type GEOMETRY or GEOGRAPHY

g2

Spatial object, type GEOMETRY or GEOGRAPHY

Parameters

spheroid = {true | false}

(Optional) BOOLEAN that specifies whether to use a perfect sphere or WGS84.

Default: False

Returns

BOOLEAN

Supported data types

Compatible GEOGRAPHY pairs:

Examples

The following examples show how to use ST_Contains.

The first polygon does not completely contain the second polygon:

=> SELECT ST_Contains(ST_GeomFromText('POLYGON((0 2,1 1,0 -1,0 2))'),
   ST_GeomFromText('POLYGON((-1 3,2 1,0 -3,-1 3))'));
 ST_Contains
-------------
 f
(1 row)

If a point is on a linestring, but not on an end point:

=> SELECT ST_Contains(ST_GeomFromText('LINESTRING(20 20,30 30)'),
   ST_GeomFromText('POINT(25 25)'));
 ST_Contains
--------------
 t
(1 row)

If a point is on the boundary of a polygon:

=> SELECT ST_Contains(ST_GeographyFromText('POLYGON((20 20,30 30,30 25,20 20))'),
   ST_GeographyFromText('POINT(20 20)'));
 ST_Contains
--------------
 f
(1 row)

Two spatially equivalent polygons:

=> SELECT ST_Contains (ST_GeomFromText('POLYGON((-1 2, 0 3, 0 1, -1 2))'),
   ST_GeomFromText('POLYGON((0 3, -1 2, 0 1, 0 3))'));
 ST_Contains
--------------
 t
(1 row)

See also

• ST_Overlaps
• ST_Within

8 - ST_ConvexHull

Calculates the smallest convex GEOMETRY object that contains a GEOMETRY object.

Behavior type

Syntax

ST_ConvexHull( g )

Arguments

g

Spatial object for which you want the convex hull, type GEOMETRY

Returns

GEOMETRY

Supported data types

Examples

The following examples show how to use ST_ConvexHull.

For a pair of points in a geometry collection:

=> SELECT ST_AsText(ST_ConvexHull(ST_GeomFromText('GEOMETRYCOLLECTION(
   POINT(1 1),POINT(0 0))')));
       ST_AsText
-----------------------
 LINESTRING (1 1, 0 0)
(1 row)

For a geometry collection:

=> SELECT ST_AsText(ST_ConvexHull(ST_GeomFromText('GEOMETRYCOLLECTION(
   LINESTRING(2.5 3,-2 1.5), POLYGON((0 1,1 3,1 -2,0 1)))')));
                  ST_AsText
---------------------------------------------
 POLYGON ((1 -2, -2 1.5, 1 3, 2.5 3, 1 -2))
(1 row)

The solid lines represent the original geometry collection and the dashed lines represent the convex hull.

9 - ST_Crosses

Determines if one GEOMETRY object spatially crosses another GEOMETRY object. If two objects touch only at a border, ST_Crosses returns FALSE.

Two objects spatially cross when both of the following are true:

• The two objects have some, but not all, interior points in common.

• The dimension of the result of their intersection is less than the maximum dimension of the two objects.

Behavior type

Syntax

ST_Crosses( g1, g2 )

Arguments

g1

Spatial object, type GEOMETRY

g2

Spatial object, type GEOMETRY

Returns

BOOLEAN

Supported data types

Examples

The following examples show how to use ST_Crosses.

=> SELECT ST_Crosses(ST_GeomFromText('LINESTRING(-1 3,1 4)'),
   ST_GeomFromText('LINESTRING(-1 4,1 3)'));
 ST_Crosses
------------
 t
(1 row)

=> SELECT ST_Crosses(ST_GeomFromText('LINESTRING(-1 1,1 2)'),
   ST_GeomFromText('POLYGON((1 1,0 -1,3 -1,2 1,1 1))'));
 ST_Crosses
------------
 f
(1 row)

=> SELECT ST_Crosses(ST_GeomFromText('POINT(-1 4)'),
   ST_GeomFromText('LINESTRING(-1 4,1 3)'));
ST_ Crosses
------------
 f
(1 row)

10 - ST_Difference

Calculates the part of a spatial object that does not intersect with another spatial object.

Behavior type

Syntax

ST_Difference( g1, g2 )

Arguments

g1

Spatial object, type GEOMETRY

g2

Spatial object, type GEOMETRY

Returns

GEOMETRY

Supported data types

Examples

The following examples show how to use ST_Difference.

Two overlapping linestrings:

=> SELECT ST_AsText(ST_Difference(ST_GeomFromText('LINESTRING(0 0,0 2)'),
   ST_GeomFromText('LINESTRING(0 1,0 2)')));
       ST_AsText
-----------------------
 LINESTRING (0 0, 0 1)
(1 row)
=> SELECT ST_AsText(ST_Difference(ST_GeomFromText('LINESTRING(0 0,0 3)'),
   ST_GeomFromText('LINESTRING(0 1,0 2)')));
                ST_AsText
------------------------------------------
 MULTILINESTRING ((0 0, 0 1), (0 2, 0 3))
(1 row)

Two overlapping polygons:

=> SELECT ST_AsText(ST_Difference(ST_GeomFromText('POLYGON((0 1,0 3,2 3,2 1,0 1))'),
   ST_GeomFromText('POLYGON((0 0,0 2,2 2,2 0,0 0))')));
            ST_AsText
-------------------------------------
 POLYGON ((0 2, 0 3, 2 3, 2 2, 0 2))
(1 row)

Two non-intersecting polygons:

=> SELECT ST_AsText(ST_Difference(ST_GeomFromText('POLYGON((1 1,1 3,3 3,3 1,
   1 1))'),ST_GeomFromText('POLYGON((1 5,1 7,-1 7,-1 5,1 5))')));
              ST_AsText
-------------------------------------
 POLYGON ((1 1, 1 3, 3 3, 3 1, 1 1))
(1 row)

11 - ST_Disjoint

Determines if two GEOMETRY objects do not intersect or touch.

If ST_Disjoint returns TRUE for a pair of GEOMETRY objects, ST_Intersects returns FALSE for the same two objects.

GEOGRAPHY Polygons with a vertex or border on the International Date Line (IDL) or the North or South pole are not supported.

Behavior type

Syntax

ST_Disjoint( g1, g2
                         [USING PARAMETERS spheroid={true | false}] )

Arguments

g1

Spatial object, type GEOMETRY

g2

Spatial object, type GEOMETRY

Parameters

spheroid = {true | false}

(Optional) BOOLEAN that specifies whether to use a perfect sphere or WGS84.

Default: False

Returns

BOOLEAN

Supported data types

Compatible GEOGRAPHY pairs:

Data Type

GEOGRAPHY (WGS84)

Point-Point

No

Linestring-Point

No

Polygon-Point

Yes

Multipolygon-Point

No

Examples

The following examples show how to use ST_Disjoint.

Two non-intersecting or touching polygons:

=> SELECT ST_Disjoint (ST_GeomFromText('POLYGON((-1 2,0 3,0 1,-1 2))'),
   ST_GeomFromText('POLYGON((1 0, 1 1, 2 2, 1 0))'));
 ST_Disjoint
-------------
 t
(1 row)

Two intersecting linestrings:

=> SELECT ST_Disjoint(ST_GeomFromText('LINESTRING(-1 2,0 3)'),
   ST_GeomFromText('LINESTRING(0 2,-1 3)'));
 ST_Disjoint
-------------
 f
(1 row)

Two polygons touching at a single point:

=> SELECT ST_Disjoint (ST_GeomFromText('POLYGON((-1 2, 0 3, 0 1, -1 2))'),
   ST_GeomFromText('POLYGON((0 2, 1 1, 1 2, 0 2))'));
 ST_Disjoint
--------------
 f
(1 row)

See also

• ST_Intersects

12 - ST_Distance

Calculates the shortest distance between two spatial objects. For GEOMETRY objects, the distance is measured in Cartesian coordinate units. For GEOGRAPHY objects, the distance is measured in meters.

Parameters g1 and g2 must be both GEOMETRY objects or both GEOGRAPHY objects.

Behavior type

Syntax

ST_Distance( g1, g2
        [USING PARAMETERS spheroid={ true | false } ] )

Arguments

g1

Spatial object, type GEOMETRY or GEOGRAPHY

g2

Spatial object, type GEOMETRY or GEOGRAPHY

Parameters

spheroid = { true | false }

(Optional) BOOLEAN that specifies whether to use a perfect sphere or WGS84.

Default: False

Returns

FLOAT

Supported data types

Compatible GEOGRAPHY pairs:

Recommendations

Vertica recommends pruning invalid data before using ST_Distance. Invalid geography values could return non-guaranteed results.

Examples

The following examples show how to use ST_Distance.

Distance between two polygons:

=> SELECT ST_Distance(ST_GeomFromText('POLYGON((-1 -1,2 2,0 1,-1 -1))'),
                      ST_GeomFromText('POLYGON((5 2,7 4,5 5,5 2))'));
 ST_Distance
-------------
           3
(1 row)

Distance between a point and a linestring in meters:

=> SELECT ST_Distance(ST_GeographyFromText('POINT(31.75 31.25)'),
    ST_GeographyFromText('LINESTRING(32 32,32 35,40.5 35,32 35,32 32)'));
   ST_Distance
------------------
 86690.3950562969
(1 row)

13 - ST_Envelope

Calculates the minimum bounding rectangle that contains the specified GEOMETRY object.

Behavior type

Syntax

ST_Envelope( g )

Arguments

g

Spatial object for which you want to find the minimum bounding rectangle, type GEOMETRY

Returns

GEOMETRY

Supported data types

Examples

The following example shows how to use ST_Envelope.

Returns the minimum bounding rectangle:

=> SELECT ST_AsText(ST_Envelope(ST_GeomFromText('POLYGON((0 0,1 1,1 2,2 2,
   2 1,3 0,1.5 -1.5,0 0))')));
                 ST_AsText
-------------------------------------------
 POLYGON ((0 -1.5, 3 -1.5, 3 2, 0 2, 0 -1.5))
(1 row)

14 - ST_Equals

Determines if two spatial objects are spatially equivalent. The coordinates of the two objects and their WKT/WKB representations must match exactly for ST_Equals to return TRUE.

The order of the points do not matter in determining spatial equivalence:

• LINESTRING(1 2, 4 3) equals LINESTRING(4 3, 1 2).

• POLYGON ((0 0, 1 1, 1 2, 2 2, 2 1, 3 0, 1.5 -1.5, 0 0)) equals POLYGON((1 1 , 1 2, 2 2, 2 1, 3 0, 1.5 -1.5, 0 0, 1 1)).

• MULTILINESTRING((1 2, 4 3),(0 0, -1 -4)) equals MULTILINESTRING((0 0, -1 -4),(1 2, 4 3)).

Coordinates are stored as FLOAT types. Thus, rounding errors are expected when importing Well-Known Text (WKT) values because the limitations of floating-point number representation.

g1 and g2 must both be GEOMETRY objects or both be GEOGRAPHY objects. Also, g1 and g2 cannot both be of type GeometryCollection.

Behavior type

Syntax

ST_Equals( g1, g2 )

Arguments

g1

Spatial object to compare to g2, type GEOMETRY or GEOGRAPHY

g2

Spatial object to compare to g1, type GEOMETRY or GEOGRAPHY

Returns

BOOLEAN

Supported data types

Examples

The following examples show how to use ST_Equals.

Two linestrings:

=> SELECT ST_Equals (ST_GeomFromText('LINESTRING(-1 2, 0 3)'),
   ST_GeomFromText('LINESTRING(0 3, -1 2)'));
 ST_Equals
--------------
 t
(1 row)

Two polygons:

=> SELECT ST_Equals (ST_GeographyFromText('POLYGON((43.22 42.21,40.3 39.88,
   42.1 50.03,43.22 42.21))'),ST_GeographyFromText('POLYGON((43.22 42.21,
   40.3 39.88,42.1 50.31,43.22 42.21))'));
 ST_Equals
--------------
 f
(1 row)

15 - ST_GeographyFromText

Converts a Well-Known Text (WKT) string into its corresponding GEOGRAPHY object. Use this function to convert a WKT string into the format expected by the Vertica Place functions.

A GEOGRAPHY object is a spatial object with coordinates (longitude, latitude) defined on the surface of the earth. Coordinates are expressed in degrees (longitude, latitude) from reference planes dividing the earth.

The maximum size of a GEOGRAPHY object is 10 MB. If you pass a WKT to ST_GeographyFromText, the result is a spatial object whose size is greater than 10 MB, ST_GeographyFromText returns an error.

The Open Geospatial Consortium (OGC) defines the format of a WKT string in Section 7 in the Simple Feature Access Part 1 - Common Architecture specification.

Behavior type

Syntax

ST_GeographyFromText( wkt [ USING PARAMETERS ignore_errors={'y'|'n'} ] )

Arguments

wkt

Well-Known Text (WKT) string of a GEOGRAPHY object, type LONG VARCHAR

ignore_errors

(Optional) ST_GeographyFromText returns the following, based on the parameters supplied:

• NULL—If wkt is invalid and ignore_errors='y'.

• Error—If wkt is invalid and ignore_errors='n' or is unspecified.

Returns

GEOGRAPHY

Supported data types

Examples

The following example shows how to use ST_GeographyFromText.

Convert WKT into a GEOGRAPHY object:

=> CREATE TABLE wkt_ex (g GEOGRAPHY);
 CREATE TABLE
=> INSERT INTO wkt_ex VALUES(ST_GeographyFromText('POLYGON((1 2,3 4,2 3,1 2))'));
 OUTPUT
--------
      1
(1 row)

16 - ST_GeographyFromWKB

Converts a Well-Known Binary (WKB) value into its corresponding GEOGRAPHY object. Use this function to convert a WKB into the format expected by Vertica Place functions.

A GEOGRAPHY object is a spatial object defined on the surface of the earth. Coordinates are expressed in degrees (longitude, latitude) from reference planes dividing the earth. All calculations are in meters.

The maximum size of a GEOGRAPHY object is 10 MB. If you pass a WKB to ST_GeographyFromWKB that results in a spatial object whose size is greater than 10 MB, ST_GeographyFromWKB returns an error.

The Open Geospatial Consortium (OGC) defines the format of a WKB representation in Section 8 in the Simple Feature Access Part 1 - Common Architecture specification.

Behavior type

Syntax

ST_GeographyFromWKB( wkb [ USING PARAMETERS ignore_errors={'y'|'n'} ] )

Arguments

wkb

Well-Known Binary (WKB) value of a GEOGRAPHY object, type LONG VARBINARY

ignore_errors

(Optional) ST_GeographyFromWKB returns the following, based on the parameters supplied:

• NULL—If wkb is invalid and ignore_errors='y'.

• Error—If wkb is invalid and ignore_errors='n' or is unspecified.

Returns

GEOGRAPHY

Supported data types

Examples

The following example shows how to use ST_GeographyFromWKB.

Convert WKB into a GEOGRAPHY object:

=> CREATE TABLE wkb_ex (g GEOGRAPHY);
CREATE TABLE
=> INSERT INTO wkb_ex VALUES(ST_GeographyFromWKB(X'0103000000010000000 ... );
 OUTPUT
--------
      1
(1 row)

17 - ST_GeoHash

Returns a GeoHash in the shape of the specified geometry.

Behavior type

Syntax

ST_GeoHash( SpatialObject [ USING PARAMETERS numchars=n] )

Arguments

Spatial object

A GEOMETRY or GEOGRAPHY spatial object. Inputs must be in polar coordinates (-180 <= x <= 180 and -90 <= y <= 90) for all points inside the given geometry.

n

Specifies the length, in characters, of the returned GeoHash.

Returns

GEOHASH

Supported data types

Examples

The following examples show how to use ST_PointFromGeoHash.

Generate a full precision GeoHash for the specified geometry:

=> SELECT ST_GeoHash(ST_GeographyFromText('POINT(3.14 -1.34)'));
ST_GeoHash
----------------------
kpf0rkn3zmcswks75010
(1 row)

Generate a GeoHash based on the first five characters of the specified geometry:

=>  select ST_GeoHash(ST_GeographyFromText('POINT(3.14 -1.34)')USING PARAMETERS numchars=5);
ST_GeoHash
------------
kpf0r
(1 row)

18 - ST_GeometryN

Returns the n^th geometry within a geometry object.

If n is out of range of the index, then NULL is returned.

Behavior type

Syntax

ST_GeometryN( g , n )

Arguments

g

Spatial object of type GEOMETRY.

n

The geometry's index number, 1-based.

Returns

GEOMETRY

Supported data types

Examples

The following examples show how to use ST_GeometryN.

Return the second geometry in a multipolygon:

=> CREATE TABLE multipolygon_geom (gid int, geom GEOMETRY(1000));
CREATE TABLE
=> COPY multipolygon_geom(gid, gx FILLER LONG VARCHAR, geom AS ST_GeomFromText(gx)) FROM stdin delimiter '|';
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>>9|MULTIPOLYGON(((2 6, 2 9, 6 9, 7 7, 4 6, 2 6)),((0 0, 0 5, 1 0, 0 0)),((0 2, 2 5, 4 5, 0 2)))
>>\.
=> SELECT gid, ST_AsText(ST_GeometryN(geom, 2)) FROM multipolygon_geom;
 gid |           ST_AsText
-----+--------------------------------
   9 | POLYGON ((0 0, 0 5, 1 0, 0 0))
(1 row)

Return all the geometries within a multipolygon:

=> CREATE TABLE multipolygon_geom (gid int, geom GEOMETRY(1000));
CREATE TABLE
=> COPY multipolygon_geom(gid, gx FILLER LONG VARCHAR, geom AS ST_GeomFromText(gx)) FROM stdin delimiter '|';
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>>9|MULTIPOLYGON(((2 6, 2 9, 6 9, 7 7, 4 6, 2 6)),((0 0, 0 5, 1 0, 0 0)),((0 2, 2 5, 4 5, 0 2)))
>>\.
=> CREATE TABLE series_numbers (numbs int);
CREATE TABLE
=> COPY series_numbers FROM STDIN;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> 1
>> 2
>> 3
>> 4
>> 5
>> \.
=> SELECT numbs, ST_AsText(ST_GeometryN(geom, numbs))
   FROM multipolygon_geom, series_numbers
   WHERE ST_AsText(ST_GeometryN(geom, numbs)) IS NOT NULL
   ORDER BY numbs ASC;
 numbs |                ST_AsText
-------+------------------------------------------
     1 | POLYGON ((2 6, 2 9, 6 9, 7 7, 4 6, 2 6))
     2 | POLYGON ((0 0, 0 5, 1 0, 0 0))
     3 | POLYGON ((0 2, 2 5, 4 5, 0 2))
(3 rows)

See also

19 - ST_GeometryType

Determines the class of a spatial object.

Behavior type

Syntax

ST_GeometryType( g )

Arguments

g

Spatial object for which you want the class, type GEOMETRY or GEOGRAPHY

Returns

VARCHAR

Supported data types

Examples

The following example shows how to use ST_GeometryType.

Returns spatial class:

=> SELECT ST_GeometryType(ST_GeomFromText('GEOMETRYCOLLECTION(LINESTRING(1 1,
   2 2), POLYGON((1 3,4 5,2 2,1 3)))'));
    ST_GeometryType
-----------------------
 ST_GeometryCollection
(1 row)

20 - ST_GeomFromGeoHash

Returns a polygon in the shape of the specified GeoHash.

Behavior type

Syntax

ST_GeomFromGeoHash(GeoHash)

Arguments

GeoHash

A valid GeoHash string of arbitrary length.

Returns

GEOGRAPHY

Examples

The following examples show how to use ST_GeomFromGeoHash.

Converts a GeoHash string to a Geography object and back to a GeoHash

=>  SELECT ST_GeoHash(ST_GeomFromGeoHash(‘vert1c9’));
ST_GeoHash
--------------------
vert1c9
(1 row)

Returns a polygon of the specified GeoHash and uses ST_AsText to convert the polygon, rectangle map tile, into Well-Known Text:

=>  SELECT ST_AsText(ST_GeomFromGeoHash('drt3jj9n4dpcbcdef'));
ST_AsText
------------------------------------------------------------------------------------------------------------------------------------------------------------------
POLYGON ((-71.1459699298 42.3945346513, -71.1459699297 42.3945346513, -71.1459699297 42.3945346513, -71.1459699298 42.3945346513, -71.1459699298 42.3945346513))
(1 row)

Returns multiple polygons and their areas for the specified GeoHashes. The polygon for the high level GeoHash (1234) has a significant area, while the low level GeoHash (1234567890bcdefhjkmn) has an area of zero.

=>  SELECT ST_Area(short) short_area, ST_AsText(short) short_WKT, ST_Area(long) long_area, ST_AsText(long) long_WKT from (SELECT ST_GeomFromGeoHash('1234') short, ST_GeomFromGeoHash('1234567890bcdefhjkmn') long) as foo;
-[ RECORD 1 ]---------------------------------------------------------------------------------------------------------------------------------------------------------------------
short_area | 24609762.8991076
short_WKT  | POLYGON ((-122.34375 -88.2421875, -121.9921875 -88.2421875, -121.9921875 -88.06640625, -122.34375 -88.06640625, -122.34375 -88.2421875))
long_area  | 0
long_WKT   | POLYGON ((-122.196077187 -88.2297377551, -122.196077187 -88.2297377551, -122.196077187 -88.2297377551, -122.196077187 -88.2297377551, -122.196077187 -88.2297377551))

21 - ST_GeomFromGeoJSON

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

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:

1. Create a table named polygons that stores GEOMETRY spatial types:

   => CREATE TABLE polygons(geom GEOMETRY(1000));
   CREATE TABLE
   

2. 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] ] ] }
   >> \.
   

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)
   

22 - ST_GeomFromText

Converts a Well-Known Text (WKT) string into its corresponding GEOMETRY object. Use this function to convert a WKT string into the format expected by the Vertica Place functions.

A GEOMETRY object is a spatial object defined by the coordinates of a plane. Coordinates are expressed as points on a Cartesian plane (x,y). SRID values of 0 to 2^32-1 are valid. SRID values outside of this range will generate an error.

The maximum size of a GEOMETRY object is 10 MB. If you pass a WKT to ST_GeomFromText and the result is a spatial object whose size is greater than 10 MB, ST_GeomFromText returns an error.

The Open Geospatial Consortium (OGC) defines the format of a WKT representation. See section 7 in the Simple Feature Access Part 1 - Common Architecture specification.

Behavior type

Syntax

ST_GeomFromText( wkt [, srid] [ USING PARAMETERS ignore_errors={'y'|'n'} ])

Arguments

wkt

Well-Known Text (WKT) string of a GEOMETRY object, type LONG VARCHAR.

srid

(Optional when not performing operations)

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.

ignore_errors

(Optional) ST_GeomFromText returns the following, based on parameters supplied:

• NULL—If wkt is invalid and ignore_errors='y'.

• Error—If wkt is invalid and ignore_errors='n' or is unspecified.

Returns

GEOMETRY

Supported data types

Examples

The following example shows how to use ST_GeomFromText.

Convert WKT into a GEOMETRY object:

=> SELECT ST_Area(ST_GeomFromText('POLYGON((1 1,2 3,3 5,0 5,1 -2,0 0,1 1))'));
 ST_Area
---------
       6
(1 row)

23 - ST_GeomFromWKB

Converts the Well-Known Binary (WKB) value to its corresponding GEOMETRY object. Use this function to convert a WKB into the format expected by many of the Vertica Place functions.

A GEOMETRY object is a spatial object with coordinates (x,y) defined in the Cartesian plane.

The maximum size of a GEOMETRY object is 10 MB. If you pass a WKB to ST_GeomFromWKB and the result is a spatial object whose size is greater than 10 MB, ST_GeomFromWKB returns an error.

The Open Geospatial Consortium (OGC) defines the format of a WKB representation in section 8 in the Simple Feature Access Part 1 - Common Architecture specification.

Behavior type

Syntax

ST_GeomFromWKB( wkb[, srid] [ USING PARAMETERS ignore_errors={'y'|'n'} ])

Arguments

wkb

Well-Known Binary (WKB) value of a GEOMETRY object, type LONG VARBINARY

srid

(Optional) 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.

ignore_errors

(Optional) ST_GeomFromWKB returns the following, based on the parameters supplied:

• NULL—If wkb is invalid and ignore_errors='y'.

• Error—If wkb is invalid and ignore_errors='n' or is unspecified.

Returns

GEOMETRY

Supported data types

Examples

The following example shows how to use ST_GeomFromWKB.

Convert GEOMETRY into WKT:

=> CREATE TABLE t(g GEOMETRY);
CREATE TABLE
=> INSERT INTO t VALUES(
ST_GeomFromWKB(X'0103000000010000000400000000000000000000000000000000000000000000000000f
               03f0000000000000000f64ae1c7022db544000000000000f03f00000000000000000000000000000000'));
 OUTPUT
--------
      1
(1 row)
=> SELECT ST_AsText(g) from t;
             ST_AsText
------------------------------------
 POLYGON ((0 0, 1 0, 1e+23 1, 0 0))
(1 row)

24 - ST_Intersection

Calculates the set of points shared by two GEOMETRY objects.

Behavior type

Syntax

ST_Intersection( g1, g2 )

Arguments

g1

Spatial object, type GEOMETRY

g2

Spatial object, type GEOMETRY

Returns

GEOMETRY

Supported data types

Examples

The following examples show how to use ST_Intersection.

Two polygons intersect at a single point:

=> SELECT ST_AsText(ST_Intersection(ST_GeomFromText('POLYGON((0 2,1 1,0 -1,
   0 2))'),ST_GeomFromText('POLYGON((-1 2,0 0,-2 0,-1 2))')));
    ST_AsText
-----------------
 POINT(0 0)
(1 row)

Two polygons:

=> SELECT ST_AsText(ST_Intersection(ST_GeomFromText('POLYGON((1 2,1 5,4 5,
   4 2,1 2))'), ST_GeomFromText('POLYGON((3 1,3 3,5 3,5 1,3 1))')));
 ST_AsText
------------------
POLYGON ((4 3, 4 2, 3 2, 3 3, 4 3))
(1 row)

Two non-intersecting linestrings:

=> SELECT ST_AsText(ST_Intersection(ST_GeomFromText('LINESTRING(1 1,1 3,3 3)'),
   ST_GeomFromText('LINESTRING(1 5,1 7,-1 7)')));
        ST_AsText
--------------------------
 GEOMETRYCOLLECTION EMPTY
(1 row)

25 - ST_Intersects

Determines if two GEOMETRY or GEOGRAPHY objects intersect or touch at a single point. If ST_Disjoint returns TRUE, ST_Intersects returns FALSE for the same GEOMETRY or GEOGRAPHY objects.

GEOGRAPHY Polygons with a vertex or border on the International Date Line (IDL) or the North or South pole are not supported.

Behavior type

Syntax

ST_Intersects( g1, g2
                  [USING PARAMETERS bbox={true | false}, spheroid={true | false}])

Arguments

g1

Spatial object, type GEOMETRY

g2

Spatial object, type GEOMETRY

Parameters

bbox = {true | false}

Boolean. Intersects the bounding box of g1 and g2.

Default: False

spheroid = {true | false}

(Optional) BOOLEAN that specifies whether to use a perfect sphere or WGS84.

Default: False

Returns

BOOLEAN

Supported data types

Compatible GEOGRAPHY pairs:

Examples

The following examples show how to use ST_Intersects.

Two polygons do not intersect or touch:

=> SELECT ST_Intersects (ST_GeomFromText('POLYGON((-1 2,0 3,0 1,-1 2))'),
   ST_GeomFromText('POLYGON((1 0,1 1,2 2,1 0))'));
 ST_Intersects
--------------
 f
(1 row)

Two polygons touch at a single point:

=> SELECT ST_Intersects (ST_GeomFromText('POLYGON((-1 2,0 3,0 1,-1 2))'),
   ST_GeomFromText('POLYGON((1 0,1 1,0 1,1 0))'));
 ST_Intersects
--------------
 t
(1 row)

Two polygons intersect:

=> SELECT ST_Intersects (ST_GeomFromText('POLYGON((-1 2, 0 3, 0 1, -1 2))'),
   ST_GeomFromText('POLYGON((0 2, -1 3, -2 0, 0 2))'));
 ST_Intersects
--------------
 t
(1 row)

See also

26 - ST_IsEmpty

Determines if a spatial object represents the empty set. An empty object has no dimension.

Behavior type

Syntax

ST_IsEmpty( g )

Arguments

g

Spatial object, type GEOMETRY or GEOGRAPHY

Returns

BOOLEAN

Supported data types

Examples

The following example shows how to use ST_IsEmpty.

An empty polygon:

=> SELECT ST_IsEmpty(ST_GeomFromText('GeometryCollection EMPTY'));
 ST_IsEmpty
------------
 t
(1 row)

27 - ST_IsSimple

Determines if a spatial object does not intersect itself or touch its own boundary at any point.

Behavior type

Syntax

ST_IsSimple( g )

Arguments

g

Spatial object, type GEOMETRY or GEOGRAPHY

Returns

BOOLEAN

Supported data types

Examples

The following examples show how to use ST_IsSimple.

Polygon does not intersect itself:

=> SELECT ST_IsSimple(ST_GeomFromText('POLYGON((-1 2,0 3,1 2,1 -2,-1 2))'));
 ST_IsSimple
--------------
 t
(1 row)

Linestring intersects itself.:

=> SELECT ST_IsSimple(ST_GeographyFromText('LINESTRING(10 10,25 25,26 34.5,
   10 30,10 20,20 10)'));
 St_IsSimple
-------------
 f
(1 row)

Linestring touches its interior at one or more locations:

=> SELECT ST_IsSimple(ST_GeomFromText('LINESTRING(0 0,0 1,1 0,2 1,2 0,0 0)'));
 ST_IsSimple
-------------
 f
(1 row)

28 - ST_IsValid

Determines if a spatial object is well formed or valid. If the object is valid, ST_IsValid returns TRUE; otherwise, it returns FALSE. Use STV_IsValidReason to identify the location of the invalidity.

Spatial validity applies only to polygons and multipolygons. A polygon or multipolygon is valid if all of the following are true:

• The polygon is closed; its start point is the same as its end point.

• Its boundary is a set of linestrings.

• The boundary does not touch or cross itself.

• Any polygons in the interior do not touch the boundary of the exterior polygon except at a vertex.

The Open Geospatial Consortium (OGC) defines the validity of a polygon in section 6.1.11.1 of the Simple Feature Access Part 1 - Common Architecture specification.

If you are not sure if a polygon is valid, run ST_IsValid first. If you pass an invalid spatial object to a Vertica Place function, the function fails or returns incorrect results.

Behavior type

Syntax

ST_IsValid( g )

Arguments

g

Geospatial object to test for validity, value of type GEOMETRY or GEOGRAPHY (WGS84).

Returns

BOOLEAN

Supported data types

Examples

The following examples show how to use ST_IsValid.

Valid polygon:

=> SELECT ST_IsValid(ST_GeomFromText('POLYGON((1 1,1 3,3 3,3 1,1 1))'));
 ST_IsValid
------------
 t
(1 row)

Invalid polygon:

=> SELECT ST_IsValid(ST_GeomFromText('POLYGON((1 3,3 2,1 1,3 0,1 0,1 3))'));
 ST_IsValid
------------
 f
(1 row)

Invalid polygon:

=> SELECT ST_IsValid(ST_GeomFromText('POLYGON((0 0,2 2,0 2,2 0,0 0))'));
 ST_IsValid
------------
 f
(1 row)

Invalid multipolygon:.

=> SELECT ST_IsValid(ST_GeomFromText('MULTIPOLYGON(((0 0, 0 1, 1 1, 0 0)),
   ((0.5 0.5, 0.7 0.5, 0.7 0.7, 0.5 0.7, 0.5 0.5)))'));
 ST_IsValid
------------
 f
(1 row)

Valid polygon with hole:

=> SELECT ST_IsValid(ST_GeomFromText('POLYGON((1 1,3 3,6 -1,0.5 -1,1 1),
   (1 1,3 1,2 0,1 1))'));
 ST_IsValid
------------
 t
(1 row)

Invalid polygon with hole:

=> SELECT ST_IsValid(ST_GeomFromText('POLYGON((1 1,3 3,6 -1,0.5 -1,1 1),
   (1 1,4.5 1,2 0,1 1))'));
 ST_IsValid
------------
 f
(1 row)

29 - ST_Length

Calculates the length of a spatial object. For GEOMETRY objects, the length is measured in Cartesian coordinate units. For GEOGRAPHY objects, the length is measured in meters.

Calculates the length as follows:

• The length of a point or multipoint object is 0.

• The length of a linestring is the sum of the lengths of each line segment The length of a line segment is the distance from the start point to the end point.

• The length of a polygon is the sum of the lengths of the exterior boundary and any interior boundaries.

• The length of a multilinestring, multipolygon, or geometrycollection is the sum of the lengths of all the objects it contains.

Behavior type

Syntax

ST_Length( g )

Arguments

g

Spatial object for which you want to calculate the length, type GEOMETRY or GEOGRAPHY

Returns

FLOAT

Supported data types

Examples

The following examples show how to use ST_Length.

Returns length in Cartesian coordinate units:

=> SELECT ST_Length(ST_GeomFromText('LINESTRING(-1 -1,2 2,4 5,6 7)'));
    ST_Length
------------------
 10.6766190873295
(1 row)

Returns length in meters:

=> SELECT ST_Length(ST_GeographyFromText('LINESTRING(-56.12 38.26,-57.51 39.78,
   -56.37 45.24)'));
    ST_Length
------------------
 821580.025733461
(1 row)

30 - ST_NumGeometries

Returns the number of geometries contained within a spatial object. Single GEOMETRY or GEOGRAPHY objects return 1 and empty objects return NULL.

Behavior type

Syntax

ST_NumGeometries( g )

Arguments

g

Spatial object of type GEOMETRY or GEOGRAPHY

Returns

INTEGER

Supported data types

Examples

The following example shows how to use ST_NumGeometries.

Return the number of geometries:

=> SELECT ST_NumGeometries(ST_GeomFromText('MULTILINESTRING ((1 5, 2 4, 5 3, 6 6), (3 5, 3 7))'));
 ST_NumGeometries
------------------
                2
(1 row)

See also

31 - ST_NumPoints

Calculates the number of vertices of a spatial object, empty objects return NULL.

The first and last vertex of polygons and multipolygons are counted separately.

Behavior type

Syntax

ST_NumPoints( g )

Arguments

g

Spatial object for which you want to count the vertices, type GEOMETRY or GEOGRAPHY

Returns

INTEGER

Supported data types

Examples

The following examples show how to use ST_NumPoints.

Returns the number of vertices in a linestring:

=> SELECT ST_NumPoints(ST_GeomFromText('LINESTRING(1.33 1.56,2.31 3.4,2.78 5.82,
   3.76 3.9,4.11 3.27,5.85 4.34,6.9 4.231,7.61 5.77)'));
 ST_NumPoints
--------------
            8
(1 row)

Use ST_Boundary and ST_NumPoints to return the number of vertices of a polygon:

=> SELECT ST_NumPoints(ST_Boundary(ST_GeomFromText('POLYGON((1 2,1 4,
   2 5,3 6,4 6,5 5,4 4,3 3,1 2))')));
 ST_NumPoints
--------------
            9
(1 row)

32 - ST_Overlaps

Determines if a GEOMETRY object shares space with another GEOMETRY object, but is not completely contained within that object. They must overlap at their interiors. If two objects touch at a single point or intersect only along a boundary, they do not overlap. Both parameters must have the same dimension; otherwise, ST_Overlaps returns FALSE.

Behavior type

Syntax

ST_Overlaps ( g1, g2 )

Arguments

g1

Spatial object, type GEOMETRY

g2

Spatial object, type GEOMETRY

Returns

BOOLEAN

Supported data types

Data Type

GEOMETRY

Point

Yes

Multipoint

Yes

Linestring

Yes

Multilinestring

Yes

Polygon

Yes

Multipolygon

Yes

GeometryCollection

Yes

Examples

The following examples show how to use ST_Overlaps.

Polygon_1 overlaps but does not completely contain Polygon_2:

=> SELECT ST_Overlaps(ST_GeomFromText('POLYGON((0 0, 0 1, 1 1, 0 0))'),
   ST_GeomFromText('POLYGON((0.5 0.5, 0.7 0.5, 0.7 0.7, 0.5 0.7, 0.5 0.5))'));
 ST_Overlaps
-------------
 t
(1 row)

Two objects with different dimensions:

=> SELECT ST_Overlaps(ST_GeomFromText('LINESTRING(2 2,4 4)'),
   ST_GeomFromText('POINT(3 3)'));
 ST_Overlaps
-------------
 f
(1 row)

33 - ST_PointFromGeoHash

Returns the center point of the specified GeoHash.

Behavior type

Syntax

ST_PointFromGeoHash(GeoHash)

Arguments

GeoHash

A valid GeoHash string of arbitrary length.

Returns

GEOGRAPHY POINT

Examples

The following examples show how to use ST_PointFromGeoHash.

Returns the geography point of a high-level GeoHash and uses ST_AsText to convert that point into Well-Known Text:

=>  SELECT ST_AsText(ST_PointFromGeoHash('dr'));
ST_AsText
-------------------------
POINT (-73.125 42.1875)
(1 row)

Returns the geography point of a detailed GeoHash and uses ST_AsText to convert that point into Well-Known Text:

=>  SELECT ST_AsText(ST_PointFromGeoHash('1234567890bcdefhjkmn'));
ST_AsText
---------------------------------------
POINT (-122.196077187 -88.2297377551)
(1 row)

34 - ST_PointN

Finds the n^th point of a spatial object. If you pass a negative number, zero, or a number larger than the total number of points on the linestring, ST_PointN returns NULL.

The vertex order is based on the Well-Known Text (WKT) representation of the spatial object.

Behavior type

Syntax

ST_PointN( g, n )

Arguments

g

Spatial object to search, type GEOMETRY or GEOGRAPHY

n

Point in the spatial object to be returned. The index is one-based, type INTEGER

Returns

GEOMETRY or GEOGRAPHY

Supported data types

Examples

The following examples show how to use ST_PointN.

Returns the fifth point:

=> SELECT ST_AsText(ST_PointN(ST_GeomFromText('
          POLYGON(( 2 6, 2 9, 6 9, 7 7, 4 6, 2 6))'), 5));
  ST_AsText
-------------
 POINT (4 6)
(1 row)

Returns the second point:

=> SELECT ST_AsText(ST_PointN(ST_GeographyFromText('
          LINESTRING(23.41 24.93,34.2 32.98,40.7 41.19)'), 2));
     ST_AsText
--------------------
 POINT (34.2 32.98)
(1 row)

35 - ST_Relate

Determines if a given GEOMETRY object is spatially related to another GEOMETRY object, based on the specified DE-9IM pattern matrix string.

The DE-9IM standard identifies how two objects are spatially related to each other.

Behavior type

Syntax

ST_Relate( g1, g2, matrix )

Arguments

g1

Spatial object, type GEOMETRY

g2

Spatial object, type GEOMETRY

matrix

DE-9IM pattern matrix string, type CHAR(9). This string represents a 3 x 3 matrix of restrictions on the dimensions of the respective intersections of the interior, boundary, and exterior of the two geometries. Must contain exactly 9 of the following characters:

• T

• F

• 0

• 1

• 2

• *

Returns

BOOLEAN

Supported data types

Examples

The following examples show how to use ST_Relate.

The DE-9IM pattern for "equals" is 'T*F**FFF2':

=> SELECT ST_Relate(ST_GeomFromText('LINESTRING(0 1,2 2)'),
   ST_GeomFromText('LINESTRING(2 2,0 1)'), 'T*F**FFF2');
 ST_Relate
--------------
 t
(1 row)

The DE-9IM pattern for "overlaps" is 'T*T***T**':

=> SELECT ST_Relate(ST_GeomFromText('POLYGON((-1 -1,0 1,2 2,-1 -1))'),
   ST_GeomFromText('POLYGON((0 1,1 -1,1 1,0 1))'), 'T*T***T**');
 ST_Relate
-----------
 t
(1 row)

36 - ST_SRID

Identifies the spatial reference system identifier (SRID) stored with a spatial object.

The SRID of a GEOMETRY object can only be determined when passing an SRID to either ST_GeomFromText or ST_GeomFromWKB. ST_SRID returns this stored value. SRID values of 0 to 2^32-1 are valid.

Behavior type

Syntax

ST_SRID( g )

Arguments

g

Spatial object for which you want the SRID, type GEOMETRY or GEOGRAPHY

Returns

INTEGER

Supported data types

Examples

The following examples show how to use ST_SRID.

The default SRID of a GEOMETRY object is 0:

=> SELECT ST_SRID(ST_GeomFromText(
      'POLYGON((-1 -1,2 2,0 1,-1 -1))'));
 ST_SRID
---------
 0
(1 row)

The default SRID of a GEOGRAPHY object is 4326:

=> SELECT ST_SRID(ST_GeographyFromText(
      'POLYGON((22 35,24 35,26 32,22 35))'));
 ST_SRID
---------
 4326
(1 row)

37 - ST_SymDifference

Calculates all the points in two GEOMETRY objects except for the points they have in common, but including the boundaries of both objects.

This result is called the symmetric difference and is represented mathematically as: Closure (g1 – g2) È Closure (g2 – g1)

Behavior type

Syntax

ST_SymDifference( g1, g2 )

Arguments

g1

Spatial object, type GEOMETRY

g2

Spatial object, type GEOMETRY

Returns

GEOMETRY

Supported data types

Examples

The following examples show how to use ST_SymDifference.

Returns the two linestrings:

=> SELECT ST_AsText(ST_SymDifference(ST_GeomFromText('LINESTRING(30 40,
   30 55)'),ST_GeomFromText('LINESTRING(30 32.5,30 47.5)')));
    ST_AsText
-----------------
MULTILINESTRING ((30 47.5, 30 55),(30 32.5,30 40))
(1 row)

Returns four squares:

=> SELECT ST_AsText(ST_SymDifference(ST_GeomFromText('POLYGON((2 1,2 4,3 4,
   3 1,2 1))'),ST_GeomFromText('POLYGON((1 2,1 3,4 3,4 2,1 2))')));
                                ST_AsText
-------------------------------------------------------------------------
 MULTIPOLYGON (((2 1, 2 2, 3 2, 3 1, 2 1)), ((1 2, 1 3, 2 3, 2 2, 1 2)),
 ((2 3, 2 4, 3 4, 3 3, 2 3)), ((3 2, 3 3, 4 3, 4 2, 3 2)))
(1 row)

38 - ST_Touches

Determines if two GEOMETRY objects touch at a single point or along a boundary, but do not have interiors that intersect.

GEOGRAPHY Polygons with a vertex or border on the International Date Line (IDL) or the North or South pole are not supported.

Behavior type

Syntax

ST_Touches( g1, g2
                        [USING PARAMETERS spheroid={true | false}] )

Arguments

g1

Spatial object, value of type GEOMETRY

g2

Spatial object, value of type GEOMETRY

Parameters

spheroid = {true | false}

(Optional) BOOLEAN that specifies whether to use a perfect sphere or WGS84.

Default: False

Returns

BOOLEAN

Supported data types

Compatible GEOGRAPHY pairs:

Examples

The following examples show how to use ST_Touches.

Two polygons touch at a single point:

=> SELECT ST_Touches(ST_GeomFromText('POLYGON((-1 2,0 3,0 1,-1 2))'),
   ST_GeomFromText('POLYGON((1 3,0 3,1 2,1 3))'));
 ST_Touches
------------
 t
(1 row)

Two polygons touch only along part of the boundary:

=> SELECT ST_Touches(ST_GeomFromText('POLYGON((-1 2,0 3,0 1,-1 2))'),
   ST_GeomFromText('POLYGON((1 2,0 3,0 1,1 2))'));
 ST_Touches
------------
 t
(1 row)

Two polygons do not touch at any point:

=> SELECT ST_Touches(ST_GeomFromText('POLYGON((-1 2,0 3,0 1,-1 2))'),
   ST_GeomFromText('POLYGON((0 2,-1 3,-2 0,0 2))'));
 ST_Touches
------------
 f
(1 row)

39 - ST_Transform

Returns a new GEOMETRY with its coordinates converted to the spatial reference system identifier (SRID) used by the srid argument.

This function supports the following transformations:

• EPSG 4326 (WGS84) to EPSG 3857 (Web Mercator)

• EPSG 3857 (Web Mercator) to EPSG 4326 (WGS84)

For EPSG 4326 (WGS84), unless the coordinates fall within the following ranges, conversion results in failure:

• Longitude limits: -572 to +572
• Latitude limits: -89.9999999 to +89.9999999

Behavior type

Syntax

ST_Transform( g1, srid )

Arguments

g1

Spatial object of type GEOMETRY.

srid

Spatial reference system identifier (SRID) to which you want to convert your spatial object, of type INTEGER.

Returns

GEOMETRY

Supported data types

Examples

The following example shows how you can transform data from Web Mercator (3857) to WGS84 (4326):

=> SELECT ST_AsText(ST_Transform(STV_GeometryPoint(7910240.56433, 5215074.23966, 3857), 4326));
        ST_AsText
-------------------------
 POINT (71.0589 42.3601)
(1 row)

The following example shows how you can transform linestring data in a table from WGS84 (4326) to Web Mercator (3857):

=> CREATE TABLE transform_line_example (g GEOMETRY);
CREATE TABLE
=> COPY transform_line_example (gx FILLER LONG VARCHAR, g AS ST_GeomFromText(gx, 4326)) FROM STDIN;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> LINESTRING(0 0, 1 1, 2 2, 3 4)
>> \.
=> SELECT ST_AsText(ST_Transform(g, 3857)) FROM transform_line_example;
                                                        ST_AsText
-------------------------------------------------------------------------------------------------------------------------
 LINESTRING (0 -7.08115455161e-10, 111319.490793 111325.142866, 222638.981587 222684.208506, 333958.47238 445640.109656)
(1 row)

The following example shows how you can transform point data in a table from WGS84 (4326) to Web Mercator (3857):

=> CREATE TABLE transform_example (x FLOAT, y FLOAT, srid INT);
CREATE TABLE
=> COPY transform_example FROM STDIN;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> 42.3601|71.0589|4326
>> 122.4194|37.7749|4326
>> 94.5786|39.0997|4326
>> \.
=> SELECT ST_AsText(ST_Transform(STV_GeometryPoint(x, y, srid), 3857)) FROM transform_example;
              ST_AsText
-------------------------------------
 POINT (4715504.76195 11422441.5961)
 POINT (13627665.2712 4547675.35434)
 POINT (10528441.5919 4735962.8206)
(3 rows)

40 - ST_Union

Calculates the union of all points in two spatial objects.

This result is represented mathematically by: g1 È g2

Behavior type

Syntax

ST_Union( g1, g2 )

Arguments

g1

Spatial object, type GEOMETRY

g2

Spatial object, type GEOMETRY

Returns

GEOMETRY

Supported data types

Examples

The following example shows how to use ST_Union.

Returns a polygon that represents all the points contained in these two polygons:

=> SELECT ST_AsText(ST_Union(ST_GeomFromText('POLYGON((0 2,1 1,0 -1,-1 1,0 2))'),
   ST_GeomFromText('POLYGON((-1 2, 0 0, -2 0, -1 2))')));
                                     ST_AsText
------------------------------------------------------------------------------
 POLYGON ((0 2, 1 1, 0 -1, -0.5 0, -2 0, -1 2, -0.666666666667 1.33333333333, 0 2))
(1 row)

41 - ST_Within

If spatial object g1 is completely inside of spatial object g2, then ST_Within returns true. Both parameters must be the same spatial data type. Either specify two GEOMETRY objects or two GEOGRAPHY objects.

If an object such as a point or linestring only exists along a polygon's boundary, then ST_Within returns false. The interior of a linestring is all the points along the linestring except the start and end points.

ST_Within(g``g is functionally equivalent to ST_Contains(g``g.

GEOGRAPHY Polygons with a vertex or border on the International Date Line (IDL) or the North or South pole are not supported.

Behavior type

Syntax

ST_Within( g1, g2
                      [USING PARAMETERS spheroid={true | false}] )

Arguments

g1

Spatial object, type GEOMETRY or GEOGRAPHY

g2

Spatial object, type GEOMETRY or GEOGRAPHY

Parameters

spheroid = {true | false}

(Optional) BOOLEAN that specifies whether to use a perfect sphere or WGS84.

Default: False

Returns

BOOLEAN

Supported data types

Compatible GEOGRAPHY pairs:

Examples

The following examples show how to use ST_Within.

The first polygon is completely contained within the second polygon:

=> SELECT ST_Within(ST_GeomFromText('POLYGON((0 2,1 1,0 -1,0 2))'),
   ST_GeomFromText('POLYGON((-1 3,2 1,0 -3,-1 3))'));
 ST_Within
-----------
 t
(1 row)

The point is on a vertex of the polygon, but not in its interior:

=> SELECT ST_Within (ST_GeographyFromText('POINT(30 25)'),
   ST_GeographyFromText('POLYGON((25 25,25 35,32.2 35,30 25,25 25))'));
 ST_Within
-----------
 f
(1 row)

Two polygons are spatially equivalent:

=> SELECT ST_Within (ST_GeomFromText('POLYGON((-1 2, 0 3, 0 1, -1 2))'),
   ST_GeomFromText('POLYGON((0 3, -1 2, 0 1, 0 3))'));
 ST_Within
-----------
 t
(1 row)

See also

• ST_Contains
• ST_Overlaps

42 - ST_X

Determines the x- coordinate for a GEOMETRY point or the longitude value for a GEOGRAPHY point.

Behavior type

Syntax

ST_X( g )

Arguments

g

Point of type GEOMETRY or GEOGRAPHY

Returns

FLOAT

Supported data types

Examples

The following examples show how to use ST_X.

Returns the x-coordinate:

=> SELECT ST_X(ST_GeomFromText('POINT(3.4 1.25)'));
 ST_X
-----
 3.4
(1 row)

Returns the longitude value:

=> SELECT ST_X(ST_GeographyFromText('POINT(25.34 45.67)'));
 ST_X
-------
 25.34
(1 row)

43 - ST_XMax

Returns the maximum x-coordinate of the minimum bounding rectangle of the GEOMETRY or GEOGRAPHY object.

For GEOGRAPHY types, Vertica Place computes maximum coordinates by calculating the maximum longitude of the great circle arc from (MAX(longitude), ST_YMin(GEOGRAPHY)) to (MAX(longitude), ST_YMax(GEOGRAPHY)). In this case, MAX(longitude) is the maximum longitude value of the geography object.

If either latitude or longitude is out of range, ST_XMax returns the maximum plain value of the geography object.

Behavior type

Syntax

ST_XMax( g )

Arguments

g

Spatial object for which you want to find the maximum x-coordinate, type GEOMETRY or GEOGRAPHY.

Returns

FLOAT

Supported data types

Examples

The following examples show how to use ST_XMax.

Returns the maximum x-coordinate within a rectangle:

=> SELECT ST_XMax(ST_GeomFromText('POLYGON((0 1,0 2,1 2,1 1,0 1))'));
  ST_XMax
-----------
     1
(1 row)

Returns the maximum longitude value within a rectangle:

=> SELECT ST_XMax(ST_GeographyFromText(
    'POLYGON((-71.50 42.35, -71.00 42.35, -71.00 42.38, -71.50 42.38, -71.50 42.35))'));
  ST_XMax
---------
     -71
(1 row)

44 - ST_XMin

Returns the minimum x-coordinate of the minimum bounding rectangle of the GEOMETRY or GEOGRAPHY object.

For GEOGRAPHY types, Vertica Place computes minimum coordinates by calculating the minimum longitude of the great circle arc from (MIN(longitude), ST_YMin(GEOGRAPHY)) to (MIN(longitude), ST_YMax(GEOGRAPHY)). In this case, MIN(latitude) represents the minimum longitude value of the geography object

If either latitude or longitude is out of range, ST_XMin returns the minimum plain value of the geography object.

Behavior type

Syntax

ST_XMin( g )

Arguments

g

Spatial object for which you want to find the minimum x-coordinate, type GEOMETRY or GEOGRAPHY.

Returns

FLOAT

Supported data types

Examples

The following examples show how to use ST_XMin.

Returns the minimum x-coordinate within a rectangle:

=> SELECT ST_XMin(ST_GeomFromText('POLYGON((0 1,0 2,1 2,1 1,0 1))'));
  ST_XMin
----------
       0
(1 row)

Returns the minimum longitude value within a rectangle:

=> SELECT ST_XMin(ST_GeographyFromText(
    'POLYGON((-71.50 42.35, -71.00 42.35, -71.00 42.38, -71.50 42.38, -71.50 42.35))'));
  ST_XMin
----------
    -71.5
(1 row)

45 - ST_Y

Determines the y-coordinate for a GEOMETRY point or the latitude value for a GEOGRAPHY point.

Behavior type

Syntax

ST_Y( g )

Arguments

g

Point of type GEOMETRY or GEOGRAPHY

Returns

FLOAT

Supported data types

Examples

The following examples show how to use ST_Y.

Returns the y-coordinate:

=> SELECT ST_Y(ST_GeomFromText('POINT(3 5.25)'));
 ST_Y
------
 5.25
(1 row)

Returns the latitude value:

=> SELECT ST_Y(ST_GeographyFromText('POINT(35.44 51.04)'));
 ST_Y
-------
 51.04
(1 row)

46 - ST_YMax

Returns the maximum y-coordinate of the minimum bounding rectangle of the GEOMETRY or GEOGRAPHY object.

For GEOGRAPHY types, Vertica Place computes maximum coordinates by calculating the maximum latitude of the great circle arc from (ST_XMin(GEOGRAPHY), MAX(latitude)) to (ST_XMax(GEOGRAPHY), MAX(latitude)). In this case, MAX(latitude) is the maximum latitude value of the geography object.

If either latitude or longitude is out of range, ST_YMax returns the maximum plain value of the geography object.

Behavior type

Syntax

ST_YMax( g )

Arguments

g

Spatial object for which you want to find the maximum y-coordinate, type GEOMETRY or GEOGRAPHY.

Returns

FLOAT

Supported data types

Examples

The following examples show how to use ST_YMax.

Returns the maximum y-coordinate within a rectangle:

=> SELECT ST_YMax(ST_GeomFromText('POLYGON((0 1,0 4,1 4,1 1,0 1))'));
  ST_YMax
-----------
     4
(1 row)

Returns the maximum latitude value within a rectangle:

=> SELECT ST_YMax(ST_GeographyFromText(
    'POLYGON((-71.50 42.35, -71.00 42.35, -71.00 42.38, -71.50 42.38, -71.50 42.35))'));
   ST_YMax
------------------
 42.3802715689979
(1 row)

47 - ST_YMin

Returns the minimum y-coordinate of the minimum bounding rectangle of the GEOMETRY or GEOGRAPHY object.

For GEOGRAPHY types, Vertica Place computes minimum coordinates by calculating the minimum latitude of the great circle arc from (ST_XMin(GEOGRAPHY), MIN(latitude)) to (ST_XMax(GEOGRAPHY), MIN(latitude)). In this case, MIN(latitude) represents the minimum latitude value of the geography object.

If either latitude or longitude is out of range, ST_YMin returns the minimum plain value of the geography object.

Behavior type

Syntax

ST_YMin( g )

Arguments

g

Spatial object for which you want to find the minimum y-coordinate, type GEOMETRY or GEOGRAPHY.

Returns

FLOAT

Supported data types

Examples

The following examples show how to use ST_YMin.

Returns the minimum y-coordinate within a rectangle:

=> SELECT ST_YMin(ST_GeomFromText('POLYGON((0 1,0 4,1 4,1 1,0 1))'));
  ST_YMin
-----------
     1
(1 row)

Returns the minimum latitude value within a rectangle:

=> SELECT ST_YMin(ST_GeographyFromText(
    'POLYGON((-71.50 42.35, -71.00 42.35, -71.00 42.38, -71.50 42.38, -71.50 42.35))'));
  ST_YMin
------------------
 42.35
(1 row)

48 - STV_AsGeoJSON

Returns the geometry or geography argument as a Geometry Javascript Object Notation (GeoJSON) object.

Behavior type

Syntax

STV_AsGeoJSON( g, [USING PARAMETERS maxdecimals=[dec_value]])

Arguments

g

Spatial object of type GEOMETRY or GEOGRAPHY

maxdecimals = dec_value

(Optional) Integer value. Determines the maximum number of digits to output after the decimal of floating point coordinates.

Valid values**:** Between 0 and 15.

Default** value****:** 6

Returns

LONG VARCHAR

Supported data types

Examples

The following examples show how you can use STV_AsGeoJSON.

Convert a geometry polygon to GeoJSON:

=> SELECT STV_AsGeoJSON(ST_GeomFromText('POLYGON((3 2, 4 3, 5 1, 3 2), (3.5 2, 4 2.5, 4.5 1.5, 3.5 2))'));
                                            STV_AsGeoJSON
--------------------------------------------------------------------------------------------------
 {"type":"Polygon","coordinates":[[[3,2],[4,3],[5,1],[3,2]],[[3.5,2],[4,2.5],[4.5,1.5],[3.5,2]]]}
(1 row)

Convert a geography point to GeoJSON:

=> SELECT STV_AsGeoJSON(ST_GeographyFromText('POINT(42.36011 71.05899)') USING PARAMETERS maxdecimals=4);
                   STV_AsGeoJSON
-------------------------------------------------
 {"type":"Point","coordinates":[42.3601,71.059]}
(1 row)

49 - STV_Create_Index

Creates a spatial index on a set of polygons to speed up spatial intersection with a set of points.

A spatial index is created from an input polygon set, which can be the result of a query. Spatial indexes are created in a global name space. Vertica uses a distributed plan whenever the input table or projection is segmented across nodes of the cluster.

The OVER() clause must be empty.

Behavior type

Immutable

Syntax

STV_Create_Index( gid, g
                   USING PARAMETERS index='index_name'
                                    [, overwrite={ true | false } ]
                                    [, max_mem_mb=maxmem_value]
                     [, skip_nonindexable_polygons={true | false } ] )
                 OVER()
                    [ AS (polygons, srid, min_x, min_y, max_x, max_y, info) ]

Arguments

gid

Name of an integer column that uniquely identifies the polygon. The gid cannot be NULL.

g

Name of a geometry or geography (WGS84) column or expression that contains polygons and multipolygons. Only polygon and multipolygon can be indexed. Other shape types are excluded from the index.

Parameters

index = 'index_name'

Name of the index, type VARCHAR. Index names cannot exceed 110 characters. The slash, backslash, and tab characters are not allowed in index names.

overwrite = [ true | false ]

Boolean, whether to overwrite the index, if an index exists. This parameter cannot be NULL.

Default: False

max_mem_mb = maxmem_value

A positive integer that assigns a limit to the amount of memory in megabytes that STV_Create_Index can allocate during index construction. On a multi-node database this is the memory limit per node. The default value is 256. Do not assign a value higher than the amount of memory in the GENERAL resource pool. For more information about this pool, see Monitoring resource pools.

Setting a value for max_mem_mb that is at or near the maximum memory available on the node can negatively affect your system's performance. For example, it could cause other queries to time out waiting for memory resources during index construction.

skip_nonindexable_polygons = [ true | false ]

(Optional) BOOLEAN

In rare cases, intricate polygons (for instance, with too high resolution or anomalous spikes) cannot be indexed. These polygons are considered non-indexable. When set to False, non-indexable polygons cause the index creation to fail. When set to True, index creation can succeed by excluding non-indexable polygons from the index.

To review the polygons that were not able to be indexed, use STV_Describe_Index with the parameter list_polygon.

Default: False

Returns

polygons

Number of polygons indexed.

SRID

Spatial reference system identifier.

min_x, min_y, max_x, max_y

Coordinates of the minimum bounding rectangle (MBR) of the indexed geometries. (min_x, min_y) are the south-west coordinates, and (max_x, max_y) are the north-east coordinates.

info

Lists the number of excluded spatial objects as well as their type that were excluded from the index.

Supported data types

Privileges

Any user with access to the STV_*_Index functions can describe, rename, or drop indexes created by any other user.

Recommendations

• Segment large polygon tables across multiple nodes. Table segmentation causes index creation to run in parallel, leveraging the Massively Parallel Processing (MPP) architecture in Vertica. This significantly reduces execution time on large tables.

  Vertica recommends that you segment the table from which you are building the index when the total number of polygons is large.

• STV_Create_Index can consume large amounts of processing time and memory.

  Vertica recommends that when indexing new data for the first time, you monitor memory usage to be sure it stays within safe limits. Memory usage depends on number of polygons, number of vertices, and the amount of overlap among polygons.

• STV_Create_Index tries to allocate memory before it starts creating the index. If it cannot allocate enough memory, the function fails. If not enough memory is available, try the following:

  • Create the index at a time of less load on the system.

  • Avoid concurrent index creation.

  • Try segmenting the input table across the nodes of the cluster.

• Ensure that all of the polygons you plan to index are valid polygons. STV_Create_Index and STV_Refresh_Index do not check polygon validity when building an index.
  
  For more information, see Ensuring polygon validity before creating or refreshing an index.

Limitations

• Any indexes created prior to 23.3.x need to re-created.

• Index creation fails if there are WGS84 polygons with vertices on the International Date Line (IDL) or the North and South Poles.

• The backslash or tab characters are not allowed in index names.

• Indexes cannot have names greater than 110 characters.

• The following geometries are excluded from the index:

  • Non-polygons

  • Geometries with NULL identifiers

  • NULL (multi) polygon

  • EMPTY (multi) polygon

  • Invalid (multi) polygon

• The following geographies are excluded from the index:

  • Polygons with holes
  • Polygons crossing the International Date Line
  • Polygons covering the north or south pole
  • Antipodal polygons

Usage tips

• To cancel an STV_Create_Index run, use Ctrl + C.

• If there are no valid polygons in the geom column, STV_Create_Index reports an error in vertica.log and stops index creation.

• If index creation uses a large amount of memory, consider segmenting your data to utilize parallel index creation.

Examples

The following examples show how to use STV_Create_Index.

Create an index with a single literal argument:

=> SELECT STV_Create_Index(1, ST_GeomFromText('POLYGON((0 0,0 15.2,3.9 15.2,3.9 0,0 0))')
     USING PARAMETERS index='my_polygon') OVER();
polygons  | SRID | min_x | min_y | max_x | max_y | info
----------+------+-------+-------+-------+-------+------
        1 |    0 |     0 |     0 |   3.9 |  15.2 |
(1 row)

Create an index from a table:

=> CREATE TABLE pols (gid INT, geom GEOMETRY(1000));
CREATE TABLE
=> COPY pols(gid, gx filler LONG VARCHAR, geom AS ST_GeomFromText(gx)) FROM stdin delimiter '|';
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> 1|POLYGON((-31 74,8 70,8 50,-36 53,-31 74))
>> 2|POLYGON((-38 50,4 13,11 45,0 65,-38 50))
>> 3|POLYGON((10 20,15 60,20 45,46 15,10 20))
>> 4|POLYGON((5 20,9 30,20 45,36 35,5 20))
>> 5|POLYGON((12 23,9 30,20 45,36 35,37 67,45 80,50 20,12 23))
>> \.
=> SELECT STV_Create_Index(gid, geom USING PARAMETERS index='my_polygons_1', overwrite=true,
    max_mem_mb=256) OVER() FROM pols;
 polygons | SRID | min_x | min_y | max_x | max_y | info
----------+------+-------+-------+-------+-------+------
        5 |    0 |   -38 |    13 |    50 |    80 |
(1 row)

Create an index in parallel from a partitioned table:

=> CREATE TABLE pols (p INT, gid INT, geom GEOMETRY(1000)) SEGMENTED BY HASH(p) ALL NODES;
CREATE TABLE
=> COPY pols (p, gid, gx filler LONG VARCHAR, geom AS ST_GeomFromText(gx)) FROM stdin delimiter '|';
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> 1|10|POLYGON((-31 74,8 70,8 50,-36 53,-31 74))
>> 1|11|POLYGON((-38 50,4 13,11 45,0 65,-38 50))
>> 3|12|POLYGON((-12 42,-12 42,27 48,14 26,-12 42))
>> \.
=> SELECT STV_Create_Index(gid, geom USING PARAMETERS index='my_polygons', overwrite=true,
    max_mem_mb=256) OVER() FROM pols;
 polygons | SRID | min_x | min_y | max_x | max_y | info
----------+------+-------+-------+-------+-------+------
        3 |    0 |   -38 |    13 |    27 |    74 |
(1 row)

See also

• Spatial joins with ST_Intersects and STV_Intersect
• STV_Intersect scalar function
• STV_Intersect transform function
• STV_Describe_Index
• STV_Drop_Index
• STV_Rename_Index
• Ensuring polygon validity before creating or refreshing an index

50 - STV_Describe_Index

Retrieves information about an index that contains a set of polygons. If you do not pass any parameters, STV_Describe_Index returns all of the defined indexes.

The OVER() clause must be empty.

Behavior type

Syntax

STV_Describe_Index ( [ USING PARAMETERS [index='index_name']
                                        [, list_polygons={true | false } ]] ) OVER ()

Arguments

index = 'index_name'

Name of the index, type VARCHAR. Index names cannot exceed 110 characters. The slash, backslash, and tab characters are not allowed in index names.

list_polygon

(Optional) BOOLEAN that specifies whether to list the polygons in the index. The index argument must be used with this argument.

Returns

polygons

Number of polygons indexed.

SRID

Spatial reference system identifier.

min_x, min_y, max_x, max_y

Coordinates of the minimum bounding rectangle (MBR) of the indexed geometries. (min_x, min_y) are the south-west coordinates, and (max_x, max_y) are the north-east coordinates.

name

The name of the spatial index(es).

gid

Name of an integer column that uniquely identifies the polygon. The gid cannot be NULL.

state

The spatial object's state in the index. Possible values are:

• INDEXED - The spatial object was successfully indexed.

• SELF_INTERSECT - (WGS84 Only) The spatial object was not indexed because one of its edges intersects with another of its edges.

• EDGE_CROSS_IDL - (WGS84 Only) The spatial object was not indexed because one of its edges crosses the International Date Line.

• EDGE_HALF_CIRCLE - (WGS84 Only) The spatial object was not indexed because it contains two adjacent vertices that are antipodal.

• NON_INDEXABLE - The spatial object was not able to be indexed.

geography

The Well-Known Binary (WKB) representation of the spatial object.

geometry

The Well-Known Binary (WKB) representation of the spatial object.

Privileges

Any user with access to the STV_*_Index functions can describe, rename, or drop indexes created by any other user.

Limitations

Some functionality will require the index to be rebuilt if the index was created with 23.3.x or earlier.

Examples

The following examples show how to use STV_Describe_Index.

Retrieve information about the index:

=> SELECT STV_Describe_Index (USING PARAMETERS index='my_polygons') OVER ();
   type   | polygons | SRID | min_x | min_y | max_x | max_y
----------+----------+------+-------+-------+-------+-------
 GEOMETRY |        4 |    0 |    -1 |    -1 |    12 |    12

(1 row)

Return the names of all the defined indexes:

=> SELECT STV_Describe_Index() OVER ();
       name
------------------
MA_counties_index
my_polygons
NY_counties_index
US_States_Index
(4 rows)

Return the polygons included in an index:

=> SELECT STV_Describe_Index(USING PARAMETERS index='my_polygons', list_polygons=TRUE) OVER ();
 gid |     state     |            geometry
-----+---------------+----------------------------------
  12 | INDEXED       | \260\000\000\000\000\000\000\ ...
  14 | INDEXED       | \200\000\000\000\000\000\000\ ...
  10 | NON_INDEXABLE | \274\000\000\000\000\000\000\ ...
  11 | INDEXED       | \260\000\000\000\000\000\000\ ...
(4 rows)

See also

• Spatial joins with ST_Intersects and STV_Intersect
• STV_Intersect scalar function
• STV_Intersect transform function
• STV_Drop_Index
• STV_Rename_Index

51 - STV_Drop_Index

Deletes a spatial index. If STV_Drop_Index cannot find the specified spatial index, it returns an error.

The OVER clause must be empty.

Behavior type

Syntax

STV_Drop_Index( USING PARAMETERS index = 'index_name' ) OVER ()

Arguments

index = 'index_name'

Name of the index, type VARCHAR. Index names cannot exceed 110 characters. The slash, backslash, and tab characters are not allowed in index names.

Examples

The following example shows how to use STV_Drop_Index.

Drop an index:

=> SELECT STV_Drop_Index(USING PARAMETERS index ='my_polygons') OVER ();
 drop_index
------------
 Index dropped
(1 row)

See also

• Spatial joins with ST_Intersects and STV_Intersect
• STV_Create_Index
• STV_Describe_Index
• STV_Rename_Index
• STV_Intersect scalar function
• STV_Intersect transform function

52 - STV_DWithin

Determines if the shortest distance from the boundary of one spatial object to the boundary of another object is within a specified distance.

Parameters g1 and g2 must be both GEOMETRY objects or both GEOGRAPHY objects.

Behavior type

Syntax

STV_DWithin( g1, g2, d )

Arguments

g1

Spatial object of type GEOMETRY or GEOGRAPHY

g2

Spatial object of type GEOMETRY or GEOGRAPHY

d

Value of type FLOAT indicating a distance. For GEOMETRY objects, the distance is measured in Cartesian coordinate units. For GEOGRAPHY objects, the distance is measured in meters.

Returns

BOOLEAN

Supported data types

Compatible GEOGRAPHY pairs:

Data Type

GEOGRAPHY (Perfect Sphere)

Point-Point

Yes

Point-Linestring

Yes

Point-Polygon

Yes

Point-Multilinestring

Yes

Point-Multipolygon

Yes

Examples

The following examples show how to use STV_DWithin.

Two geometries are one Cartesian coordinate unit from each other at their closest points:

=> SELECT STV_DWithin(ST_GeomFromText('POLYGON((-1 -1,2 2,0 1,-1 -1))'),
   ST_GeomFromText('POLYGON((4 3,2 3,4 5,4 3))'),1);
 STV_DWithin
-------------
 t
(1 row)

If you reduce the distance to 0.99 units:

=> SELECT STV_DWithin(ST_GeomFromText('POLYGON((-1 -1,2 2,0 1,-1 -1))'),
   ST_GeomFromText('POLYGON((4 3,2 3,4 5,4 3))'),0.99);
 STV_DWithin
-------------
 f
(1 row)

The first polygon touches the second polygon:

=> SELECT STV_DWithin(ST_GeomFromText('POLYGON((-1 -1,2 2,0 1,-1 -1))'),
   ST_GeomFromText('POLYGON((1 1,2 3,4 5,1 1))'),0.00001);
 STV_DWithin
-------------
 t
(1 row)

The first polygon is not within 1000 meters from the second polygon:

=> SELECT STV_DWithin(ST_GeomFromText('POLYGON((45.2 40,50.65 51.29,
   55.67 47.6,50 47.6,45.2 40))'),ST_GeomFromText('POLYGON((25 25,25 30,
   30 30,30 25,25 25))'), 1000);
 STV_DWithin
--------------
 t
(1 row)

53 - STV_Export2Shapefile

Exports GEOGRAPHY or GEOMETRY data from a database table or a subquery to a shapefile. Output is written to the directory set with STV_SetExportShapefileDirectory.

Behavior type

Syntax

STV_Export2Shapefile( columns USING PARAMETERS shapefile = 'filename'
   [, overwrite = boolean ]
   [, shape = 'spatial-class'] )
OVER()

Arguments

columns

The columns to export to the shapefile.

A value of asterisk (*) is the equivalent to listing all columns of the FROM clause.

Parameters

shapefile

Prefix of the component names of the shapefile. The following requirements apply:

• Must end with the file extension .shp.

• Limited to 128 octets in length—for example, city-data.shp.

To save the shapefile to a subdirectory, concatenate the subdirectory to shapefile-name—for example, visualizations/city-data.shp. The subdirectory must exist; this function does not create it.

overwrite

Boolean, whether to overwrite the index, if an index exists. This parameter cannot be NULL.

Default: False

shape

One of the following spatial classes:

• Point

• Polygon

• Linestring

• Multipoint

• Multipolygon

• Multilinestring

Polygons and multipolygons always have a clockwise orientation.

Default: Polygon

Returns

Three files in the shapefile export directory with the extensions .shp, .shx, and .dbf.

Limitations

• If a multipolygon, multilinestring, or multipoint contains only one element, then it is written as a polygon, line, or point, respectively.

• Column names longer than 10 characters are truncated.

• Empty POINTS cannot be exported.

• All rows with NULL geometry or geography data are skipped.

• Unsupported or invalid dates are replaced with NULLs.

• Numeric values may lose precision when they are exported. This loss occurs because the target field in the .dbf file is a 64-bit FLOAT column, which can only represent about 15 significant digits.

• Shapefiles cannot exceed 4GB in size. If your shapefile is too large, try splitting the data and exporting to multiple shapefiles.

Examples

The following example shows how you can use STV_Export2Shapefile to export all columns from the table geo_data to a shapefile named city-data.shp:

=> SELECT STV_Export2Shapefile(*
            USING PARAMETERS shapefile = 'visualizations/city-data.shp',
                             overwrite = true, shape = 'Point')
            OVER()
            FROM geo_data
            WHERE REVENUE > 25000;
 Rows Exported |                          File Path
---------------+--------------------------------------------------------------
       6442892 | v_geo-db_node0001: /home/geo/temp/visualizations/city-data.shp
(1 row)

54 - STV_Extent

Returns a bounding box containing all of the input data.

Use STV_Extent inside of a nested query for best results. The OVER clause must be empty.

Behavior type

Syntax

STV_Extent( g )

Arguments

g

Spatial object, type GEOMETRY.

Returns

GEOMETRY

Supported data types

Examples

The following examples show how you can use STV_Extent.

Return the bounding box of a linestring, and verify that it is a valid polygon:

=> SELECT ST_AsText(geom) AS bounding_box, ST_IsValid(geom)
   FROM (SELECT STV_Extent(ST_GeomFromText('LineString(0 0, 1 1)')) OVER() AS geom) AS g;
            bounding_box             | ST_IsValid
-------------------------------------+------------
 POLYGON ((0 0, 1 0, 1 1, 0 1, 0 0)) | t
(1 row)

Return the bounding box of spatial objects in a table:

=> CREATE TABLE misc_geo_shapes (id IDENTITY, geom GEOMETRY);
CREATE TABLE
=> COPY misc_geo_shapes (gx FILLER LONG VARCHAR, geom AS ST_GeomFromText(gx)) FROM STDIN;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> POINT(-71.03 42.37)
>> LINESTRING(-71.058849 42.367501, -71.062240 42.371276, -71.067938 42.371246)
>> POLYGON((-71.066030 42.380617, -71.055827 42.376734, -71.060811 42.376011, -71.066030 42.380617))
>> \.
=> SELECT ST_AsText(geom_col) AS bounding_box
   FROM (SELECT STV_Extent(geom) OVER() AS geom_col FROM misc_geo_shapes) AS g;
                                                   bounding_box
------------------------------------------------------------------------------------------------------------------
 POLYGON ((-71.067938 42.367501, -71.03 42.367501, -71.03 42.380617, -71.067938 42.380617, -71.067938 42.367501))
(1 row)

55 - STV_ForceLHR

Alters the order of the vertices of a spatial object to follow the left-hand-rule.

Behavior type

Syntax

STV_ForceLHR( g, [USING PARAMETERS skip_nonreorientable_polygons={true | false} ])

Arguments

g

Spatial object, type GEOGRAPHY.

skip_nonreorientable_polygons = { true | false }

(Optional) Boolean

When set to False, non-orientable polygons generate an error. For example, if you use STV_ForceLHR or STV_Reverse with skip_nonorientable_polygons set to False, a geography polygon containing a hole generates an error. When set to True, the result returned is the polygon, as passed to the API, without alteration.

This argument can help you when you are creating an index from a table containing polygons that cannot be re-oriented.

Vertica Place considers these polygons non-orientable:

• Polygons with a hole

• Multipolygons

• Multipolygons with a hole

Default value: False

Returns

GEOGRAPHY

Supported data types

Examples

The following example shows how you can use STV_ForceLHR.

Re-orient a geography polygon to left-hand orientation:

=> SELECT ST_AsText(STV_ForceLHR(ST_GeographyFromText('Polygon((1 1, 3 1, 2 2, 1 1))')));
            ST_AsText
--------------------------------
 POLYGON ((1 1, 3 1, 2 2, 1 1))
(1 row)

Reverse the orientation of a geography polygon by forcing left-hand orientation:

=> SELECT ST_AsText(STV_ForceLHR(ST_GeographyFromText('Polygon((1 1, 2 2, 3 1, 1 1))')));
           ST_AsText
--------------------------------
 POLYGON ((1 1, 3 1, 2 2, 1 1))
(1 row)

See also

56 - STV_Geography

Casts a GEOMETRY object into a GEOGRAPHY object. The SRID value does not affect the results of Vertica Place queries.

When STV_Geography converts a GEOMETRY object to a GEOGRAPHY object, it sets its SRID to 4326.

Behavior type

Syntax

STV_Geography( geom )

Arguments

geom

Spatial object that you want to cast into a GEOGRAPHY object, type GEOMETRY

Returns

GEOGRAPHY

Supported data types

Examples

The following example shows how to use STV_Geography.

To calculate the centroid of the GEOGRAPHY object, convert it to a GEOMETRY object, then convert it back to a GEOGRAPHY object:

=> CREATE TABLE geogs(g GEOGRAPHY);
CREATE TABLE
=> COPY geogs(gx filler LONG VARCHAR, geog AS ST_GeographyFromText(gx)) FROM stdin delimiter '|';
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> MULTIPOINT(-108.619726 45.000284,-107.866813 45.00107,-106.363711 44.994223,-70.847746 41.205814)
>> \.
=> SELECT ST_AsText(STV_Geography(ST_Centroid(STV_Geometry(g)))) FROM geogs;
           ST_AsText
--------------------------------
 POINT (-98.424499 44.05034775)
(1 row)

57 - STV_GeographyPoint

Returns a GEOGRAPHY point based on the input values.

This is the optimal way to convert raw coordinates to GEOGRAPHY points.

Behavior type

Syntax

STV_GeographyPoint( x, y )

Arguments

x

x-coordinate or longitude, FLOAT.

y

y-coordinate or latitude, FLOAT.

Returns

GEOGRAPHY

Examples

The following examples show how to use STV_GeographyPoint.

Return a GEOGRAPHY point:

=> SELECT ST_AsText(STV_GeographyPoint(-114.101588, 47.909677));
           ST_AsText
-------------------------------
 POINT (-114.101588 47.909677)
(1 row)

Return GEOGRAPHY points using two columns:

=> CREATE TABLE geog_data (id IDENTITY, x FLOAT, y FLOAT);
CREATE TABLE
=> COPY geog_data FROM STDIN;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> -114.101588|47.909677
>> -111.532377|46.430753
>> \.
=> SELECT id, ST_AsText(STV_GeographyPoint(x, y)) FROM geog_data;
 id |           ST_AsText
----+-------------------------------
  1 | POINT (-114.101588 47.909677)
  2 | POINT (-111.532377 46.430753)
(2 rows)

Create GEOGRAPHY points by manipulating data source columns during load:

=> CREATE TABLE geog_data_load (id IDENTITY, geog GEOGRAPHY);
CREATE TABLE
=> COPY geog_data_load (lon FILLER FLOAT,
                        lat FILLER FLOAT,
                        geog AS STV_GeographyPoint(lon, lat))
   FROM 'test_coords.csv' DELIMITER ',';
 Rows Loaded
-------------
           2
(1 row)
=> SELECT id, ST_AsText(geog) FROM geog_data_load;
 id |             ST_AsText
----+------------------------------------
  1 | POINT (-75.101654451 43.363830536)
  2 | POINT (-75.106444487 43.367093798)
(2 rows)

See also

58 - STV_Geometry

Casts a GEOGRAPHY object into a GEOMETRY object.

The SRID value does not affect the results of Vertica Place queries.

Behavior type

Syntax

STV_Geometry( geog )

Arguments

geog

Spatial object that you want to cast into a GEOMETRY object, type GEOGRAPHY

Returns

GEOMETRY

Supported data types

Examples

The following example shows how to use STV_Geometry.

Convert the GEOGRAPHY values to GEOMETRY values, then convert the result back to a GEOGRAPHY type:

=> CREATE TABLE geogs(g GEOGRAPHY);
CREATE TABLE
=> COPY geogs(gx filler LONG VARCHAR, geog AS ST_GeographyFromText(gx)) FROM stdin delimiter '|';
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> MULTIPOINT(-108.619726 45.000284,-107.866813 45.00107,-106.363711 44.994223,-70.847746 41.205814)
>> \.
=> SELECT ST_AsText(STV_Geography(ST_Centroid(STV_Geometry(g)))) FROM geogs;
           ST_AsText
--------------------------------
 POINT (-98.424499 44.05034775)

59 - STV_GeometryPoint

Returns a GEOMETRY point, based on the input values.

This approach is the most-optimal way to convert raw coordinates to GEOMETRY points.

Behavior type

Syntax

STV_GeometryPoint( x, y [, srid] )

Arguments

x

x-coordinate or longitude, FLOAT.

y

y-coordinate or latitude, FLOAT.

srid

(Optional) Spatial Reference Identifier (SRID) assigned to the point, INTEGER.

Returns

GEOMETRY

Examples

The following examples show how to use STV_GeometryPoint.

Return a GEOMETRY point with an SRID:

=> SELECT ST_AsText(STV_GeometryPoint(71.148562, 42.989374, 4326));
          ST_AsText
-----------------------------
 POINT (-71.148562 42.989374)
(1 row)

Return GEOMETRY points using two columns:

=> CREATE TABLE geom_data (id IDENTITY, x FLOAT, y FLOAT, SRID int);
CREATE TABLE
=> COPY geom_data FROM STDIN;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> 42.36383053600048|-71.10165445099966|4326
>> 42.3670937980005|-71.10644448699964|4326
>> \.
=> SELECT id, ST_AsText(STV_GeometryPoint(x, y, SRID)) FROM geom_data;
 id |             ST_AsText
----+------------------------------------
  1 | POINT (-71.101654451 42.363830536)
  2 | POINT (-71.106444487 42.367093798)
(2 rows)

Create GEOMETRY points by manipulating data source columns during load:

=> CREATE TABLE geom_data_load (id IDENTITY, geom GEOMETRY);
CREATE TABLE
=> COPY geom_data_load (lon FILLER FLOAT,
                        lat FILLER FLOAT,
                        geom AS STV_GeometryPoint(lon, lat))
   FROM 'test_coords.csv' DELIMITER ',';
 Rows Loaded
-------------
           2
(1 row)
=> SELECT id, ST_AsText(geom) FROM geom_data_load;
 id |             ST_AsText
----+------------------------------------
  1 | POINT (-75.101654451 43.363830536)
  2 | POINT (-75.106444487 43.367093798)
(2 rows)

See also

60 - STV_GetExportShapefileDirectory

Returns the path of the export directory.

Behavior type

Syntax

STV_GetExportShapefileDirectory( )

Returns

The path of the shapefile export directory.

Examples

The following example shows how you can use STV_GetExportShapefileDirectory to query the path of the shapefile export directory:

=> SELECT STV_GetExportShapefileDirectory();
        STV_GetExportShapefileDirectory
-----------------------------------------------
 Shapefile export directory: [/home/user/temp]
(1 row)

61 - STV_Intersect scalar function

Spatially intersects a point or points with a set of polygons. The STV_Intersect scalar function returns the identifier associated with an intersecting polygon.

Behavior type

Syntax

STV_Intersect(  { g | x , y }
                  USING PARAMETERS index= 'index_name')

Arguments

g

A geometry or geography (WGS84) column that contains points. The g column can contain only point geometries or geographies. If the column contains a different geometry or geography type, STV_Intersect terminates with an error.

x

x-coordinate or longitude, FLOAT.

y

y-coordinate or latitude, FLOAT.

Parameters

index = 'index_name'

Name of the spatial index, of type VARCHAR.

Returns

The identifier of a matching polygon. If the point does not intersect any of the index's polygons, then the STV_Intersect scalar function returns NULL.

Examples

The following examples show how you can use STV_Intersect scalar.

Using two floats, return the gid of a matching polygon or NULL:

=> CREATE TABLE pols (gid INT, geom GEOMETRY(1000));
CREATE TABLE
=> COPY pols(gid, gx filler LONG VARCHAR, geom AS ST_GeomFromText(gx)) FROM STDIN;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> 1|POLYGON((31 74,8 70,8 50,36 53,31 74))
>> \.
=> SELECT STV_Create_Index(gid, geom USING PARAMETERS index='my_polygons_1', overwrite=true,
                            max_mem_mb=256) OVER() FROM pols;
   type   | polygons | SRID | min_x | min_y | max_x | max_y | info
----------+----------+------+-------+-------+-------+-------+------
 GEOMETRY |        1 |    0 |     8 |    50 |    36 |    74 |
(1 row)

=> SELECT STV_Intersect(12.5683, 55.6761 USING PARAMETERS index = 'my_polygons_1');
 STV_Intersect
---------------
            1
(1 row)

Using a GEOMETRY column, return the gid of a matching polygon or NULL:

=> CREATE TABLE polygons (gid INT, geom GEOMETRY(700));
CREATE TABLE
=> COPY polygons (gid, gx filler LONG VARCHAR, geom AS ST_GeomFromText(gx)) FROM stdin delimiter '|';
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> 1|POLYGON((-31 74,8 70,8 50,-36 53,-31 74))
>> 2|POLYGON((-38 50,4 13,11 45,0 65,-38 50))
>> 3|POLYGON((-18 42,-10 65,27 48,14 26,-18 42))
>> \.
=> SELECT STV_Create_Index(gid, geom USING PARAMETERS index='my_polygons', overwrite=true,
     max_mem_mb=256) OVER() FROM polygons;
   type   | polygons | SRID | min_x | min_y | max_x | max_y | info
----------+----------+------+-------+-------+-------+-------+------
 GEOMETRY |        3 |    0 |   -38 |    13 |    27 |    74 |
(1 row)

=> CREATE TABLE points (gid INT, geom GEOMETRY(700));
CREATE TABLE
=> COPY points (gid, gx filler LONG VARCHAR, geom AS ST_GeomFromText(gx)) FROM stdin delimiter '|';
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> 100|POINT(-1 52)
>> 101|POINT(-20 0)
>> 102|POINT(-8 25)
>> 103|POINT(0 0)
>> 104|POINT(1 5)
>> 105|POINT(20 45)
>> 106|POINT(-20 5)
>> 107|POINT(-20 1)
>> \.
=> SELECT gid AS pt_gid, STV_Intersect(geom USING PARAMETERS index='my_polygons') AS pol_gid
       FROM points ORDER BY pt_gid;
 pt_gid | pol_gid
--------+---------
    100 |       1
    101 |
    102 |       2
    103 |
    104 |
    105 |       3
    106 |
    107 |
(8 rows)

See also

• Best practices for spatial joins
• STV_Intersect: scalar function vs. transform function
• STV_Intersect transform function
• STV_Create_Index

62 - STV_Intersect transform function

Spatially intersects points and polygons. The STV_Intersect transform function returns a tuple with matching point/polygon pairs. For every point, Vertica returns either one or many matching polygons.

You can improve performance when you parallelize the computation of the STV_Intersect transform function over multiple nodes. To parallelize the computation, use an OVER(PARTITION BEST) clause.

Behavior type

Syntax

STV_Intersect ( { gid | i }, { g | x , y }
         USING PARAMETERS index='index_name')
         OVER() AS (pt_gid, pol_gid)

Arguments

gid | i

An integer column or integer that uniquely identifies the spatial object(s) of g or x and y.

g

A geometry or geography (WGS84) column that contains points. The g column can contain only point geometries or geographies. If the column contains a different geometry or geography type, STV_Intersect terminates with an error.

x

x-coordinate or longitude, FLOAT.

y

y-coordinate or latitude, FLOAT.

Parameters

index = 'index_name'

Name of the spatial index, of type VARCHAR.

Returns

pt_gid

Unique identifier of the point geometry or geography, of type INTEGER.

pol_gid

Unique identifier of the polygon geometry or geography, of type INTEGER.

Examples

The following examples show how you can use STV_Intersect transform.

Using two floats, return the matching point-polygon pairs.

=> CREATE TABLE pols (gid INT, geom GEOMETRY(1000));
CREATE TABLE
=> COPY pols(gid, gx filler LONG VARCHAR, geom AS ST_GeomFromText(gx)) FROM STDIN;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> 1|POLYGON((31 74,8 70,8 50,36 53,31 74))
>> \.
=> SELECT STV_Create_Index(gid, geom USING PARAMETERS index='my_polygons_1', overwrite=true,
                            max_mem_mb=256) OVER() FROM pols;
   type   | polygons | SRID | min_x | min_y | max_x | max_y | info
----------+----------+------+-------+-------+-------+-------+------
 GEOMETRY |        1 |    0 |     8 |    50 |    36 |    74 |
(1 row)

=> SELECT STV_Intersect(56, 12.5683, 55.6761 USING PARAMETERS index = 'my_polygons_1') OVER();
 pt_gid | pol_gid
--------+---------
     56 |       1
(1 row)

Using a GEOMETRY column, return the matching point-polygon pairs.

=> CREATE TABLE polygons (gid int, geom GEOMETRY(700));
CREATE TABLE
=> COPY polygons (gid, gx filler LONG VARCHAR, geom AS ST_GeomFromText(gx)) FROM stdin;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> 10|POLYGON((5 5, 5 10, 10 10, 10 5, 5 5))
>> 11|POLYGON((0 0, 0 2, 2 2, 2 0, 0 0))
>> 12|POLYGON((1 1, 1 3, 3 3, 3 1, 1 1))
>> 14|POLYGON((-1 -1, -1 12, 12 12, 12 -1, -1 -1))
>> \.
=> SELECT STV_Create_Index(gid, geom USING PARAMETERS index='my_polygons', overwrite=true, max_mem_mb=256)
      OVER() FROM polygons;
   type   | polygons | SRID | min_x | min_y | max_x | max_y | info
----------+----------+------+-------+-------+-------+-------+------
 GEOMETRY |        4 |    0 |    -1 |    -1 |    12 |    12 |
(1 row)

=> CREATE TABLE points (gid INT, geom GEOMETRY(700));
CREATE TABLE
=> COPY points (gid, gx filler LONG VARCHAR, geom AS ST_GeomFromText(gx)) FROM stdin delimiter '|';
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> 1|POINT(9 9)
>> 2|POINT(0 1)
>> 3|POINT(2.5 2.5)
>> 4|POINT(0 0)
>> 5|POINT(1 5)
>> 6|POINT(1.5 1.5)
>> \.
=> SELECT STV_Intersect(gid, geom USING PARAMETERS index='my_polygons') OVER (PARTITION BEST)
     AS (point_id, polygon_gid)
        FROM points;
 point_id | polygon_gid
----------+-------------
        5 |          14
        1 |          14
        1 |          10
        4 |          14
        4 |          11
        6 |          12
        6 |          14
        6 |          11
        2 |          14
        2 |          11
        3 |          12
        3 |          14
(12 rows)

You can improve query performance by using the STV_Intersect transform function in a WHERE clause. Performance improves because this syntax eliminates all points that do not intersect polygons in the index.

Return the count of points that intersect with the polygon, where gid = 14:

=> SELECT COUNT(pt_id) FROM
    (SELECT STV_Intersect(gid, geom USING PARAMETERS index='my_polygons')
     OVER (PARTITION BEST) AS (pt_id, pol_id) FROM points)
        AS T WHERE pol_id = 14;
 COUNT
-------
     6
(1 row)

See also

• Best practices for spatial joins
• STV_Intersect: scalar function vs. transform function
• STV_Create_Index
• STV_Intersect scalar function

63 - STV_IsValidReason

Determines if a spatial object is well formed or valid. If the object is not valid, STV_IsValidReason returns a string that explains where the invalidity occurs.

A polygon or multipolygon is valid if all of the following are true:

• The polygon is closed; its start point is the same as its end point.

• Its boundary is a set of linestrings.

• The boundary does not touch or cross itself.

• Any polygons in the interior that do not have more than one point touching the boundary of the exterior polygon.

If you pass an invalid object to a Vertica Place function, the function fails or returns incorrect results. To determine if a polygon is valid, first run ST_IsValid. ST_IsValid returns TRUE if the polygon is valid, FALSE otherwise.

Behavior type

Syntax

STV_IsValidReason( g )

Arguments

g

Geospatial object to test for validity, value of type GEOMETRY or GEOGRAPHY (WGS84).

Returns

LONG VARCHAR

Supported data types

Examples

The following example shows how to use STV_IsValidReason.

Returns a string describing where the polygon is invalid:

=> SELECT STV_IsValidReason(ST_GeomFromText('POLYGON((1 3,3 2,1 1,
   3 0,1 0,1 3))'));
               STV_IsValidReason
-----------------------------------------------
 Ring Self-intersection at or near POINT (1 1)
(1 row)

See also

64 - STV_LineStringPoint

Retrieves the vertices of a linestring or multilinestring. The values returned are points of either GEOMETRY or GEOGRAPHY type depending on the input object's type. GEOMETRY points inherit the SRID of the input object.

STV_LineStringPoint is an analytic function. For more information, see Analytic functions.

Behavior type

Syntax

STV_LineStringPoint( g )
    OVER( [PARTITION NODES] ) AS

Arguments

g

Linestring or multilinestring, value of type GEOMETRY or GEOGRAPHY

Returns

GEOMETRY or GEOGRAPHY

Supported data types

Examples

The following examples show how to use STV_LineStringPoint.

Returns the vertices of the geometry linestring and their SRID:

=> SELECT ST_AsText(Point), ST_SRID(Point)
     FROM (SELECT STV_LineStringPoint(
           ST_GeomFromText('MULTILINESTRING((1 2, 2 3, 3 1, 4 2),
                  (10 20, 20 30, 30 10, 40 20))', 4269)) OVER () AS Point) AS foo;
    ST_AsText   | ST_SRID
 ---------------+---------
  POINT (1 2)   |    4269
  POINT (2 3)   |    4269
  POINT (3 1)   |    4269
  POINT (4 2)   |    4269
  POINT (10 20) |    4269
  POINT (20 30) |    4269
  POINT (30 10) |    4269
  POINT (40 20) |    4269
 (8 rows)

Returns the vertices of the geography linestring:

=> SELECT ST_AsText(g)
     FROM (SELECT STV_LineStringPoint(
       ST_GeographyFromText('MULTILINESTRING ((42.1 71.0, 41.4 70.0, 41.3 72.9),
           (42.99 71.46, 44.47 73.21)', 4269)) OVER () AS g) AS line_geog_points;
      ST_AsText
---------------------
 POINT (42.1 71.0)
 POINT (41.4 70.0)
 POINT (41.3 72.9)
 POINT (42.99 71.46)
 POINT (44.47 73.21)
(5 rows)

See also

65 - STV_MemSize

Returns the length of the spatial object in bytes as an INTEGER.

Use this function to determine the optimal column width for your spatial data.

Behavior type

Syntax

STV_MemSize( g )

Arguments

g

Spatial object, value of type GEOMETRY or GEOGRAPHY

Returns

INTEGER

Examples

The following example shows how you can optimize your table by sizing the GEOMETRY or GEOGRAPHY column to the maximum value returned by STV_MemSize:

=> CREATE TABLE mem_size_table (id int, geom geometry(800));
CREATE TABLE
=> COPY mem_size_table (id, gx filler LONG VARCHAR, geom as ST_GeomFromText(gx)) FROM STDIN DELIMITER '|';
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>>1|POINT(3 5)
>>2|MULTILINESTRING((1 5, 2 4, 5 3, 6 6),(3 5, 3 7))
>>3|MULTIPOLYGON(((2 6, 2 9, 6 9, 7 7, 4 6, 2 6)),((0 0, 0 5, 1 0, 0 0)),((0 2, 2 5, 4 5, 0 2)))
>>\.
=> SELECT max(STV_MemSize(geom)) FROM mem_size_table;
 max
-----
 336
(1 row)

=> CREATE TABLE production_table(id int, geom geometry(336));
CREATE TABLE
=> INSERT INTO production_table SELECT * FROM mem_size_table;
 OUTPUT
--------
      3
(1 row)
=> DROP mem_size_table;
DROP TABLE

66 - STV_NN

Calculates the distance of spatial objects from a reference object and returns (object, distance) pairs in ascending order by distance from the reference object.

Parameters g1 and g2 must be both GEOMETRY objects or both GEOGRAPHY objects.

STV_NN is an analytic function. For more information, see Analytic functions.

Behavior type

Syntax

STV_NN( g, ref_obj, k ) OVER()

Arguments

g

Spatial object, value of type GEOMETRY or GEOGRAPHY

ref_obj

Reference object, type GEOMETRY or GEOGRAPHY

k

Number of rows to return, type INTEGER

Returns

(Object, distance) pairs, in ascending order by distance. If a parameter is EMPTY or NULL, then 0 rows are returned.

Supported data types

Examples

The following example shows how to use STV_NN.

Create a table and insert nine GEOGRAPHY points:

=> CREATE TABLE points (g geography);
CREATE TABLE
=> COPY points (gx filler LONG VARCHAR, g AS ST_GeographyFromText(gx)) FROM stdin delimiter '|';
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> POINT (21.5 18.4)
>> POINT (21.5 19.2)
>> POINT (21.5 20.7)
>> POINT (22.5 16.4)
>> POINT (22.5 17.15)
>> POINT (22.5 18.33)
>> POINT (23.5 13.68)
>> POINT (23.5 15.9)
>> POINT (23.5 18.4)
>> \.

Calculate the distances (in meters) of objects in table points from the GEOGRAPHY point (23.5, 20).

Returns the five objects that are closest to that point:

=> SELECT ST_AsText(nn), dist FROM (SELECT STV_NN(g,
   ST_GeographyFromText('POINT(23.5 20)'),5) OVER() AS (nn,dist) FROM points) AS example;
     ST_AsText      |       dist
--------------------+------------------
 POINT (23.5 18.4)  |  177912.12757541
 POINT (22.5 18.33) | 213339.210738322
 POINT (21.5 20.7)  |  222561.43679943
 POINT (21.5 19.2)  | 227604.371833335
 POINT (21.5 18.4)  | 275239.416790128
(5 rows)

67 - STV_PolygonPoint

Retrieves the vertices of a polygon as individual points. The values returned are points of either GEOMETRY or GEOGRAPHY type depending on the input object's type. GEOMETRY points inherit the SRID of the input object.

STV_PolygonPoint is an analytic function. For more information, see Analytic functions.

Behavior type

Syntax

STV_PolygonPoint( g )
    OVER( [PARTITION NODES] ) AS

Arguments

g

Polygon, value of type GEOMETRY or GEOGRAPHY

Returns

GEOMETRY or GEOGRAPHY

Supported data types

Examples

The following examples show how to use STV_PolygonPoint.

Returns the vertices of the geometry polygon:

=> SELECT ST_AsText(g) FROM (SELECT STV_PolygonPoint(ST_GeomFromText('POLYGON((1 2, 2 3, 3 1, 1 2))'))
     OVER (PARTITION NODES) AS g) AS poly_points;
  ST_AsText
-------------
 POINT (1 2)
 POINT (2 3)
 POINT (3 1)
 POINT (1 2)
(4 rows)

Returns the vertices of the geography polygon:

=> SELECT ST_AsText(g) FROM (SELECT STV_PolygonPoint(ST_GeographyFromText('
              POLYGON((25.5 28.76, 28.83 29.13, 27.2 30.99, 25.5 28.76))'))
   OVER (PARTITION NODES) AS g) AS poly_points;
      ST_AsText
---------------------
 POINT (25.5 28.76)
 POINT (28.83 29.13)
 POINT (27.2 30.99)
 POINT (25.5 28.76)
(4 rows)

See also

68 - STV_Refresh_Index

Appends newly added or updated polygons and removes deleted polygons from an existing spatial index.

The OVER() clause must be empty.

Behavior type

Mutable

Syntax

STV_Refresh_Index( gid, g
                   USING PARAMETERS index='index_name'
                    [, skip_nonindexable_polygons={ true | false } ] )

                 OVER()
                    [ AS (type, polygons, srid, min_x, min_y, max_x, max_y, info,
               indexed, appended, updated, deleted) ]

Arguments

gid

Name of an integer column that uniquely identifies the polygon. The gid cannot be NULL.

g

Name of a geometry or geography (WGS84) column or expression that contains polygons and multipolygons. Only polygon and multipolygon can be indexed. Other shape types are excluded from the index.

Parameters

index = 'index_name'

Name of the index, type VARCHAR. Index names cannot exceed 110 characters. The slash, backslash, and tab characters are not allowed in index names.

skip_nonindexable_polygons = { true | false }

(Optional) BOOLEAN

In rare cases, intricate polygons (for instance, with too high resolution or anomalous spikes) cannot be indexed. These polygons are considered non-indexable. When set to False, non-indexable polygons cause the index creation to fail. When set to True, index creation can succeed by excluding non-indexable polygons from the index.

To review the polygons that were not able to be indexed, use STV_Describe_Index with the parameter list_polygon.

Default: False

Returns

type

Spatial object type of the index.

polygons

Number of polygons indexed.

SRID

Spatial reference system identifier.

min_x, min_y, max_x, max_y

Coordinates of the minimum bounding rectangle (MBR) of the indexed geometries. (min_x, min_y) are the south-west coordinates, and (max_x, max_y) are the north-east coordinates.

info

Lists the number of excluded spatial objects as well as their type that were excluded from the index.

indexed

Number of polygons indexed during the operation.

appended

Number of appended polygons.

updated

Number of updated polygons.

deleted

Number of deleted polygons.

Supported data types

Privileges

Any user with access to the STV_*_Index functions can describe, rename, or drop indexes created by any other user.

Limitations

• In rare cases, intricate polygons (such as those with too-high a resolution or anomalous spikes) cannot be indexed. See the parameter skip_nonindexable_polygons.

• If you replace a valid polygon in the source table with an invalid polygon, STV_Refresh_Index ignores the invalid polygon. As a result, the polygon originally indexed persists in the index.

• The following geometries cannot be indexed:

  • Non-polygons

  • NULL gid

  • NULL (multi) polygon

  • EMPTY (multi) polygon

  • Invalid (multi) polygon

• The following geographies are excluded from the index:

  • Polygons with holes
  • Polygons crossing the International Date Line
  • Polygons covering the north or south pole
  • Antipodal polygons

Usage tips

• To cancel an STV_Refresh_Index run, use Ctrl + C.

• If you use source data not previously associated with the index, then the index will be overwritten.

• If STV_Refresh_Index has insufficient memory to process the query, then rebuild the index using STV_Create_Index.

• If there are no valid polygons in the geom column, STV_Refresh_Index reports an error in vertica.log and stops the index refresh.

• Ensure that all of the polygons you plan to index are valid polygons. STV_Create_Index and STV_Refresh_Index do not check polygon validity when building an index.
  
  For more information, see Ensuring polygon validity before creating or refreshing an index.

Examples

The following examples show how to use STV_Refresh_Index.

Refresh an index with a single literal argument:

=> SELECT STV_Create_Index(1, ST_GeomFromText('POLYGON((0 0,0 15.2,3.9 15.2,3.9 0,0 0))')
     USING PARAMETERS index='my_polygon') OVER();
   type   | polygons | SRID | min_x | min_y | max_x | max_y | info
----------+----------+------+-------+-------+-------+-------+------
 GEOMETRY |        1 |    0 |     0 |     0 |   3.9 |  15.2 |
(1 row)

=> SELECT STV_Refresh_Index(2, ST_GeomFromText('POLYGON((0 0,0 13.2,3.9 18.2,3.9 0,0 0))')
     USING PARAMETERS index='my_polygon') OVER();
   type   | polygons | SRID | min_x | min_y | max_x | max_y | info | indexed | appended | updated | deleted
----------+----------+------+-------+-------+-------+-------+------+---------+----------+---------+---------
 GEOMETRY |        1 |    0 |     0 |     0 |   3.9 |  18.2 |      |       1 |        1 |       0 |       1
(1 row)

Refresh an index from a table:

=> CREATE TABLE pols (gid INT, geom GEOMETRY);
CREATE TABLE
=> COPY pols(gid, gx filler LONG VARCHAR, geom AS ST_GeomFromText(gx)) FROM stdin delimiter '|';
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> 1|POLYGON((-31 74,8 70,8 50,-36 53,-31 74))
>> 2|POLYGON((5 20,9 30,20 45,36 35,5 20))
>> 3|POLYGON((12 23,9 30,20 45,36 35,37 67,45 80,50 20,12 23))
>> \.
=> SELECT STV_Create_Index(gid, geom USING PARAMETERS index='my_polygons_1', overwrite=true)
     OVER() FROM pols;
   type   | polygons | SRID | min_x | min_y | max_x | max_y | info
----------+----------+------+-------+-------+-------+-------+------
 GEOMETRY |        3 |    0 |   -36 |    20 |    50 |    80 |
(1 row)

=> COPY pols(gid, gx filler LONG VARCHAR, geom AS ST_GeomFromText(gx)) FROM stdin delimiter '|';
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> 6|POLYGON((-32 74,8 70,8 50,-36 53,-32 74))
>> \.
=> SELECT STV_Refresh_Index(gid, geom USING PARAMETERS index='my_polygons_1') OVER() FROM pols;
   type   | polygons | SRID | min_x | min_y | max_x | max_y | info | indexed | appended | updated | deleted
----------+----------+------+-------+-------+-------+-------+------+---------+----------+---------+---------
 GEOMETRY |        4 |    0 |   -36 |    20 |    50 |    80 |      |       1 |        1 |       0 |       0
(1 row)

See also

• STV_Create_Index
• STV_Describe_Index
• STV_Drop_Index
• STV_Rename_Index
• Ensuring polygon validity before creating or refreshing an index

69 - STV_Rename_Index

Renames a spatial index. If the index format is out of date, you cannot rename the index.

A spatial index is created from an input polygon set, which can be the result of a query. Spatial indexes are created in a global name space. Vertica uses a distributed plan whenever the input table or projection is segmented across nodes of the cluster.

The OVER() clause must be empty.

Behavior type

Syntax

STV_Rename_Index( USING PARAMETERS
                  source = 'old_index_name',
                  dest = 'new_index_name',
                  overwrite = [ 'true' | 'false' ]
                )
                 OVER ()

Arguments

source = 'old_index_name'

Current name of the spatial index, type VARCHAR.

dest = 'new_index_name'

New name of the spatial index, type VARCHAR.

overwrite = [ 'true' | 'false' ]

Boolean, whether to overwrite the index, if an index exists. This parameter cannot be NULL.

Default: False

Privileges

Any user with access to the STV_*_Index functions can describe, rename, or drop indexes created by any other user.

Limitations

• Index names cannot exceed 110 characters.

• The backslash or tab characters are not allowed in index names.

Examples

The following example shows how to use STV_Rename_Index.

Rename an index:

=> SELECT STV_Rename_Index (
       USING PARAMETERS
       source = 'my_polygons',
       dest = 'US_states',
       overwrite = 'false'
       )
       OVER ();
 rename_index
---------------
 Index renamed
(1 Row)

70 - STV_Reverse

Reverses the order of the vertices of a spatial object.

Behavior type

Syntax

STV_Reverse( g, [USING PARAMETERS skip_nonreorientable_polygons={true | false} ])

Arguments

g

Spatial object, type GEOGRAPHY.

skip_nonreorientable_polygons = { true | false }

(Optional) Boolean

When set to False, non-orientable polygons generate an error. For example, if you use STV_ForceLHR or STV_Reverse with skip_nonorientable_polygons set to False, a geography polygon containing a hole generates an error. When set to True, the result returned is the polygon, as passed to the API, without alteration.

This argument can help you when you are creating an index from a table containing polygons that cannot be re-oriented.

Vertica Place considers these polygons non-orientable:

• Polygons with a hole

• Multipolygons

• Multipolygons with a hole

Default value: False

Returns

GEOGRAPHY

Supported data types

Examples

The following examples show how you can use STV_Reverse.

Reverse vertices of a geography polygon:

=> SELECT ST_AsText(STV_Reverse(ST_GeographyFromText('Polygon((1 1, 3 1, 2 2, 1 1))')));
            ST_AsText
--------------------------------
 POLYGON ((1 1, 2 2, 3 1, 1 1))
(1 row)

Force the polygon to reverse orientation:

=> SELECT ST_AsText(STV_Reverse(ST_GeographyFromText('Polygon((1 1, 2 2, 3 1, 1 1))')));
ST_AsText
--------------------------------
 POLYGON ((1 1, 3 1, 2 2, 1 1))
(1 row)

See also

71 - STV_SetExportShapefileDirectory

Specifies the directory to export GEOMETRY or GEOGRAPHY data to a shapefile. The validity of the path is not checked, and the path cannot be empty.

Behavior type

Syntax

STV_SetExportShapefileDirectory( USING PARAMETERS path='path' )

Parameters

path

Destination path for the exported shapefile. The path can be on any shared file system or object store.

Returns

The path of the shapefile export directory.

Privileges

Only a superuser can use this function.

Examples

The following example shows how you can use STV_SetExportShapefileDirectory to set the shapefile export directory to /home/user/temp:

=> SELECT STV_SetExportShapefileDirectory(USING PARAMETERS path = '/home/user/temp');
              STV_SetExportShapefileDirectory
------------------------------------------------------------
 SUCCESS. Set shapefile export directory: [/home/user/temp]
(1 row)

72 - STV_ShpCreateTable

Returns a CREATE TABLE statement with the columns and types of the attributes found in the specified shapefile.

The column types are sized according to the shapefile metadata. The size of the column is based on the largest geometry found in the shapefile. The first column in the table is named gid, which is an IDENTITY primary key column. The cache value is set to 64 by default. The last column is a GEOMETRY data type for storing the actual geometry data.

Behavior type

Syntax

STV_ShpCreateTable (USING PARAMETERS file='filename') OVER()

Parameters

file

Fully qualified path of the .dbf, .shp, or .shx file. The path can be on any shared file system or object store.

Returns

CREATE TABLE statement that matches the specified shapefile

Usage tips

• STV_ShpCreateTable returns a CREATE TABLE statement; but it does not create the table. Modify the CREATE TABLE statement as needed, and then create the table before loading the shapefile into the table.

• To create a table with characters other than alphanumeric and underscore (_) characters, you must specify the table name enclosed in double quotes, such as "counties%NY".

• The name of the table is the same as the name of the shapefile, without the directory name or extension.

• The shapefile must be accessible from the initiator node.

• If the .shp and .shx files are corrupt, STV_ShpCreateTable returns an error. If the .shp and .shx files are valid, but the .dbf file is corrupt, STV_ShpCreateTable ignores the .dbf file and does not create columns for that data.

• All the mandatory files (.dbf, .shp, .shx) must be in the same directory. If not, STV_ShpCreateTable returns an error.

• If the .dbf component of a shapefile contains a Numeric attribute, this field's values may lose precision when the Vertica shapefile loader loads it into a table. The target field is a 64-bit FLOAT column, which can only represent about 15 significant digits. In a .dbf file, numeric fields can be up to 30 digits.

  Vertica records all instances of shapefile values that are too long in the vertica.log file.

Examples

The following example shows how to use STV_ShpCreateTable.

Returns a CREATE TABLE statement:

=> SELECT STV_ShpCreateTable
      (USING PARAMETERS file='/shapefiles/tl_2010_us_state10.shp')
      OVER() as create_table_states;
      create_table_states
 ----------------------------------
CREATE TABLE tl_2010_us_state10(
   gid IDENTITY(64) PRIMARY KEY,
   REGION10 VARCHAR(2),
   DIVISION10 VARCHAR(2),
   STATEFP10 VARCHAR(2),
   STATENS10 VARCHAR(8),
   GEOID10 VARCHAR(2),
   STUSPS10 VARCHAR(2),
   NAME10 VARCHAR(100),
   LSAD10 VARCHAR(2),
   MTFCC10 VARCHAR(5),
   FUNCSTAT10 VARCHAR(1),
   ALAND10 INT8,
   AWATER10 INT8,
   INTPTLAT10 VARCHAR(11),
   INTPTLON10 VARCHAR(12),
   geom GEOMETRY(940845)
);
(18 rows)

See also

• STV_ShpSource and STV_ShpParser

73 - STV_ShpSource and STV_ShpParser

These two functions work with COPY to parse and load geometries and attributes from a shapefile into a Vertica table, and convert them to the appropriate GEOMETRY data type. You must use these two functions together.

The following restrictions apply:

• An empty multipoint or an invalid multipolygon can not be loaded from a shapefile.

• If the .dbf component of a shapefile contains a numeric attribute, this field's values might lose precision when the Vertica Place shapefile loader loads it into a table. The target field is a 64-bit FLOAT column, which can only represent about 15 significant digits; in a .dbf file, Numeric fields can be up to 30 digits.

Rejected records are saved to CopyErrorLogs subdirectory, under the Vertica catalog directory.

Behavior type

Syntax

COPY table( columnslist )
     WITH SOURCE STV_ShpSource
          ( file = 'path'[[, SRID=spatial-reference-identifier] [, flatten_2d={true | false }] ] )
     PARSER STV_ShpParser()

Arguments

table

Name of the table in which to load the geometry data.

columnslist

Comma-delimited list of column names in the table that match fields in the external file. Run the CREATE TABLE command that STV_ShpCreateTable creates. When you do so, these columns correspond to the second through the second-to-last columns.

Parameters

file

Fully qualified path of a .dbf, .shp, or .shx file. The path can be on any shared file system or object store.

SRID

Specifies an integer spatial reference identifier (SRID) associated with the shape file.

flatten_2d

Specifies a BOOLEAN argument that excludes 3D or 4D coordinates during COPY commands:

• true: Excludes geometries with 3D or 4D coordinates before a COPY command.

• false: Causes the load to fail if a geometry with 3D or 4D coordinate is found.

Default: false

Privileges

• Source shapefile: Read

• Shapefile directory: Execute

COPY errors

The COPY command fails under one of the following conditions:

• The shapefile cannot be located or opened.

• The number of columns or the data types of the columns that STV_ShpParser creates do not match the columns in the destination table. Use STV_ShpCreateTable to generate the appropriate CREATE TABLE command.

• One of the mandatory files is missing or cannot be opened. When opening a shapefile, you must have three files: .dbf, .shp, and .shx.

STV_ShpSource file corruption handling

• If the .shp and .shx files are corrupt, STV_ShpSource returns an error.

• If the .shp and .shx files are valid, but the .dbf file is corrupt, STV_ShpSource ignores the .dbf file and does not create columns for that data.

Examples

=> COPY tl_2010_us_state10 WITH SOURCE
STV_ShpSource(file='/shapefiles/tl_2010_us_state10.shp', SRID=4269) PARSER STV_ShpParser();

 Rows loaded
-------------
          52