Geospatial functions manipulate complex two-dimensional spatial objects and store them in a database according to the Open Geospatial Consortium (OGC) standards.
Geospatial functions manipulate complex two-dimensional spatial objects and store them in a database according to the Open Geospatial Consortium (OGC) standards.
Function naming conventions
The geospatial functions use the following naming conventions:
Most ST_function-name functions are compliant with the latest OGC standard OGC SFA-SQL version 1.2.1 (reference. number is OGC 06-104r4, date: 2010-08-04). Currently, some ST_function-name functions may not support all data types. Each function page contains details about the supported data types.
Note
Some functions, such as ST_GeomFromText, are based on previous versions of the standard.
The STV_function-name functions are unique to Vertica and not compliant with OGC standards. Each function page explains its functionality in detail.
Verifying spatial objects validity
Many spatial functions do not validate their parameters. If you pass an invalid spatial object to an ST_ or STV_ function, the function might return an error or produce incorrect results.
To avoid this issue, Vertica recommends that you first run ST_IsValid on all spatial objects to validate the parameters. If your object is not valid, run STV_IsValidReason to get information about the location of the invalidity.
1 - ST_Area
Calculates the area of a spatial object.
Calculates the area of a spatial object.
The units are:
GEOMETRY objects: spatial reference system identifier (SRID) units
Creates the Well-Known Binary (WKB) representation of a spatial object.
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.
Spatial object for which you want the WKB, type GEOMETRY or GEOGRAPHY
Returns
LONG VARBINARY
Supported data types
Data Type
GEOMETRY
GEOGRAPHY (Perfect Sphere)
GEOGRAPHY (WGS84)
Point
Yes
Yes
Yes
Multipoint
Yes
Yes
Yes
Linestring
Yes
Yes
Yes
Multilinestring
Yes
Yes
Yes
Polygon
Yes
Yes
Yes
Multipolygon
Yes
Yes
Yes
GeometryCollection
Yes
No
No
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:
Spatial object for which you want the WKT string, type GEOMETRY or GEOGRAPHY
Returns
LONG VARCHAR
Supported data types
Data Type
GEOMETRY
GEOGRAPHY (Perfect Sphere)
GEOGRAPHY (WGS84)
Point
Yes
Yes
Yes
Multipoint
Yes
Yes
Yes
Linestring
Yes
Yes
Yes
Multilinestring
Yes
Yes
Yes
Polygon
Yes
Yes
Yes
Multipolygon
Yes
Yes
Yes
GeometryCollection
Yes
No
No
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:
Creates a GEOMETRY object greater than or equal to a specified distance from the boundary of a spatial object.
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.
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
Data Type
GEOMETRY
Point
Yes
Multipoint
Yes
Linestring
Yes
Multilinestring
Yes
Polygon
Yes
Multipolygon
Yes
GeometryCollection
Yes
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.
Calculates the geometric center—the centroid—of a spatial object.
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.
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.
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.
Calculates the shortest distance between two spatial objects.
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.
Determines if two spatial objects are spatially equivalent.
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:
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.
Converts a Well-Known Text (WKT) string into its corresponding GEOGRAPHY object.
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.
Converts a Well-Known Binary (WKB) value into its corresponding GEOGRAPHY object.
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.
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
Data Type
GEOMETRY
GEOGRAPHY (Perfect Sphere)
GEOGRAPHY (WGS84)
Point
Yes
Yes
Yes
Multipoint
Yes
Yes
Yes
Linestring
Yes
Yes
Yes
Multilinestring
Yes
Yes
Yes
Polygon
Yes
Yes
Yes
Multipolygon
Yes
Yes
Yes
GeometryCollection
Yes
No
No
Examples
The following examples show how to use ST_PointFromGeoHash.
Generate a full precision GeoHash for the specified geometry:
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)
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.
Converts the geometry portion of a GeoJSON record in the standard format into a GEOMETRY object.
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.
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:
Create a table named polygons that stores GEOMETRY spatial types:
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] ] ] }
>> \.
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.
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 232-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.
Converts the Well-Known Binary (WKB) value to its corresponding GEOMETRY object.
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.
Determines if two GEOMETRY or GEOGRAPHY objects intersect or touch at a single point.
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.
Determines if a spatial object is well formed or valid.
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.
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.
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.
Note
ST_Length does not calculate the length of WKTs or WKBs. To calculate the lengths of those objects, use the Vertica LENGTH SQL function with ST_AsBinary or ST_AsText.
Determines if a GEOMETRY object shares space with another GEOMETRY object, but is not completely contained within that object.
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.
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 point of a spatial object.
Finds the nth 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.
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:
Identifies the spatial reference system identifier (SRID) stored with a spatial object.
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 232-1 are valid.
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.
Calculates the union of all points in two spatial objects.
This result is represented mathematically by: g1 È g2
If spatial object g1 is completely inside of spatial object g2, then ST_Within returns true.
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(g1,``g2) is functionally equivalent to ST_Contains(g2,``g1).
GEOGRAPHY Polygons with a vertex or border on the International Date Line (IDL) or the North or South pole are not supported.
Returns the maximum x-coordinate of the minimum bounding rectangle of the GEOMETRY or GEOGRAPHY object.
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.
Returns the minimum x-coordinate of the minimum bounding rectangle of the GEOMETRY or GEOGRAPHY object.
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.
Returns the maximum y-coordinate of the minimum bounding rectangle of the GEOMETRY or GEOGRAPHY object.
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.
Returns the minimum y-coordinate of the minimum bounding rectangle of the GEOMETRY or GEOGRAPHY object.
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.
Creates a spatial index on a set of polygons to speed up spatial intersection with a set of points.
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.
Important
You cannot access spatial indexes on newly added nodes without rebalancing your cluster. For more information, see REBALANCE_CLUSTER.
Indexes are not connected to any specific table. Subsequent DML commands on the underlying table or tables of the input data source do not modify the index.
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, specifies 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
Data Type
GEOMETRY
GEOGRAPHY (WGS84)
Point
No
No
Multipoint
No
No
Linestring
No
No
Multilinestring
No
No
Polygon
Yes
Yes
Multipolygon
Yes
No
GeometryCollection
No
No
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.
=> 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)
Retrieves information about an index that contains a set of polygons.
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.
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 11.1.x or earlier.
Examples
The following examples show how to use STV_Describe_Index.
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
Data Type
GEOMETRY
GEOGRAPHY (Perfect Sphere)
Point
Yes
Yes
Multipoint
Yes
Yes
Linestring
Yes
Yes
Multilinestring
Yes
Yes
Polygon
Yes
Yes
Multipolygon
Yes
Yes
GeometryCollection
Yes
No
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:
Exports GEOGRAPHY or GEOMETRY data from a database table or a subquery to a shapefile.
Exports GEOGRAPHY or GEOMETRY data from a database table or a subquery to a shapefile. Output is written to the directory specified using STV_SetExportShapefileDirectory.
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.
If you want to save the shapefile to a sub-directory you can do so by concatenating the sub-directory to shapefile-name—for example, visualizations/city-data.shp.
You can also export a shapefile to a mounted S3 directory where you have read and write permissions. Use the syntax bucketname/path/filename.
overwrite
Boolean, specifies 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:
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.
Alters the order of the vertices of a spatial object to follow the left-hand-rule.
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
Data Type
GEOMETRY
GEOGRAPHY (Perfect Sphere)
GEOGRAPHY (WGS84)
Point
No
No
No
Multipoint
No
No
No
Linestring
No
No
No
Multilinestring
No
No
No
Polygon
No
Yes
Yes
Multipolygon
No
Yes
Yes
GeometryCollection
No
No
No
Examples
The following example shows how you can use STV_ForceLHR.
Re-orient a geography polygon to left-hand orientation:
Spatial object that you want to cast into a GEOGRAPHY object, type GEOMETRY
Returns
GEOGRAPHY
Supported data types
Data Type
GEOMETRY
Point
Yes
Multipoint
Yes
Linestring
Yes
Multilinestring
Yes
Polygon
Yes
Multipolygon
Yes
GeometryCollection
No
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.
Returns a GEOGRAPHY point based on the input values.
This is the optimal way to convert raw coordinates to GEOGRAPHY points.
=> 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)
Spatial object that you want to cast into a GEOMETRY object, type GEOGRAPHY
Returns
GEOMETRY
Supported data types
Data Type
GEOGRAPHY (Perfect Sphere)
GEOGRAPHY (WGS84)
Point
Yes
Yes
Multipoint
Yes
Yes
Linestring
Yes
Yes
Multilinestring
Yes
Yes
Polygon
Yes
Yes
Multipolygon
Yes
Yes
GeometryCollection
No
No
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.
Returns a GEOMETRY point, based on the input values.
This approach is the most-optimal way to convert raw coordinates to GEOMETRY points.
=> 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)
Spatially intersects a point or points with a set of polygons.
Spatially intersects a point or points with a set of polygons. The STV_Intersect scalar function returns the identifier associated with an intersecting polygon.
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)
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.
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)
Determines if a spatial object is well formed or valid.
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.
Note
If you pass a valid polygon to STV_IsValidReason, it returns NULL.
Geospatial object to test for validity, value of type GEOMETRY or GEOGRAPHY (WGS84).
Returns
LONG VARCHAR
Supported data types
Data Type
GEOMETRY
GEOGRAPHY (Perfect Sphere)
GEOGRAPHY (WGS84)
Point
Yes
No
No
Multipoint
Yes
No
No
Linestring
Yes
No
No
Multilinestring
Yes
No
No
Polygon
Yes
No
Yes
Multipolygon
Yes
No
No
GeometryCollection
Yes
No
No
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)
Retrieves the vertices of a linestring or multilinestring.
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.
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
Data Type
GEOMETRY
GEOGRAPHY (Perfect Sphere)
GEOGRAPHY (WGS84)
Point
No
No
No
Multipoint
No
No
No
Linestring
Yes
Yes
Yes
Multilinestring
Yes
Yes
Yes
Polygon
No
No
No
Multipolygon
No
No
No
GeometryCollection
No
No
No
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)
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.
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.
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
Data Type
GEOMETRY
GEOGRAPHY (Perfect Sphere)
Point
Yes
Yes
Multipoint
Yes
Yes
Linestring
Yes
Yes
Multilinestring
Yes
Yes
Polygon
Yes
Yes
Multipolygon
Yes
Yes
GeometryCollection
Yes
No
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.
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.
STV_PolygonPoint( g )
OVER( [PARTITION NODES] ) AS
Arguments
g
Polygon, value of type GEOMETRY or GEOGRAPHY
Returns
GEOMETRY or GEOGRAPHY
Supported data types
Data Type
GEOMETRY
GEOGRAPHY (Perfect Sphere)
GEOGRAPHY (WGS84)
Point
No
No
No
Multipoint
No
No
No
Linestring
No
No
No
Multilinestring
No
No
No
Polygon
Yes
Yes
Yes
Multipolygon
Yes
Yes
Yes
GeometryCollection
No
No
No
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)
Appends newly added or updated polygons and removes deleted polygons from an existing spatial 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
Data Type
GEOMETRY
GEOGRAPHY (WGS84)
Point
No
No
Multipoint
No
No
Linestring
No
No
Multilinestring
No
No
Polygon
Yes
Yes
Multipolygon
Yes
No
GeometryCollection
No
No
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.
=> 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)
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.
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
Data Type
GEOMETRY
GEOGRAPHY (Perfect Sphere)
GEOGRAPHY (WGS84)
Point
No
No
No
Multipoint
No
No
No
Linestring
No
No
No
Multilinestring
No
No
No
Polygon
No
Yes
Yes
Multipolygon
No
Yes
Yes
GeometryCollection
No
No
No
Examples
The following examples show how you can use STV_Reverse.
STV_SetExportShapefileDirectory( USING PARAMETERS path='shapefile_path' )
Arguments
path = ' shapefile_path '
The path where you want the shapefile exported. For example, '/home/user/temp'. You can also export to a mounted S3 directory where you have read and write permissions using the convention '<bucketname>/path'.
Returns a CREATE TABLE statement with the columns and types of the attributes found in the specified shapefile.
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 gid, which is an auto-increment 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.
Fully qualified path of the .dbf, .shp, or .shx file (file extension optional).
You can also create a table using a shapefile stored on a mounted S3 directory where you have read and write permissions. Use the following syntax:
bucketname/path/filename
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.
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.
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.
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.
file = 'pathname'
Specifies the fully qualified path of a .dbf, .shp, or .shx file.
You can also load from a shapefile that is stored on a mounted S3 directory where you have read and write permissions. In this case, use the following the syntax:
bucketname/path/filename
SRID=spatial-reference-identifier
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.
