GanosBase functions - AnalyticDB - Alibaba Cloud Documentation Center

AnalyticDB for PostgreSQL GanosBase is a spatio-temporal engine. GanosBase provides a series of data types, functions, and stored procedures for AnalyticDB for PostgreSQL to store, index, query, analyze, and compute spatial or spatio-temporal data in a cost-efficient manner.

For more information about GanosBase, see Overview.

Raster SQL references

Feature	Function	Description
Raster creation	ST_CreateRast	Creates a raster object based on Object Storage Service (OSS).
Import and export	ST_ImportFrom	Imports an OSS object to the database.
Import and export	ST_ExportTo	Exports a raster object as an OSS object.
Pyramid operations	ST_BuildPyramid	Creates a pyramid for a raster object.
	ST_DeletePyramid	Deletes a pyramid for a raster object.
	ST_BestPyramidLevel	Returns the optimal pyramid level based on the world space, width, and height of a viewport.
Coordinate system conversion	ST_Rast2WorldCoord	Returns the world coordinates of a cell by using an affine transformation formula based on the pixel coordinates and pyramid level of the cell.
Coordinate system conversion	ST_World2RastCoord	Returns the pixel coordinates of a cell by using the inverse of an affine transformation based on the world coordinates and pyramid level of the cell.
Pixel value operations	ST_ClipDimension	Returns the raster coordinates of a clipped area.
	ST_Clip	Clips a raster object.
	ST_ClipToRast	Clips a raster object by using a specific geometry object and returns the clipping result as a new raster object.
	ST_AddZ	Specifies the Z coordinate of a geometry based on the band of the raster that is converted from the geometry.
	ST_MosaicFrom	Performs a mosaic operation to combine multiple raster objects into a new raster object.
DEM operations	ST_Slope	Returns the slope of an elevation raster band in degrees.
Attribute query and update	ST_Name	Returns the name of a raster object. If no name is specified, the function returns null.
	ST_SetName	Sets the name of a raster object.
	ST_MetaData	Returns the metadata of a raster object in the JSON format.
	ST_Width	Returns the width of a raster object. For information about how to return the width of a chunk, see ST_ChuckWidth.
	ST_Height	Returns the height of a raster object.
	ST_NumBands	Returns the number of bands in a raster object.
	ST_Value	Returns the value of a cell based on the specified band, row number, and column number.
	ST_RasterID	Returns the UUID of a raster object.
	ST_CellDepth	Returns the pixel depth of a raster object. The pixel depth can be 0, 1, 2, 4, 8, 16, 32, or 64, where 0 indicates that the pixel depth is unknown.
	ST_CellType	Returns the pixel type of a raster object. The pixel type can be 8BSI, 8BUI, 16BSI, 16BUI, 32BSI, 32BUI, 32BF, or 64BF.
	ST_InterleavingType	Returns the interleaving type of a raster object. The interleaving type can be BSQ, BIL, or BIP.
	ST_TopPyramidLevel	Returns the highest pyramid level of a raster object.
	ST_Extent	Returns the coordinate range of a raster object in the `'((maxX,maxY),(minX,minY))'` format.
	ST_ConvexHull	Returns the convex hull geometry of a raster object.
	ST_Envelope	Returns the bounding box of a raster object.
	ST_Srid	Returns the spatial reference system identifier (SRID) of a raster object. The SRID and its definition are stored in the spatial_ref_sys table.
	ST_SetSrid	Sets the SRID of a raster object. The SRID and its definition are stored in the spatial_ref_sys table.
	ST_ScaleX	Returns the pixel width of a raster object on the X scale in the spatial reference system.
	ST_ScaleY	Returns the pixel width of a raster object on the Y scale in the spatial reference system.
	ST_SetScale	Sets the pixel widths of a raster object on the X and Y scales in the spatial reference system.
	ST_SkewX	Returns the skew of a raster object on the X scale in the spatial reference system.
	ST_SkewY	Returns the skew of a raster object on the Y scale in the spatial reference system.
	ST_SetSkew	Sets the skews of a raster object on the X and Y scales in the spatial reference system.
	ST_UpperLeftX	Returns the upper-left X coordinate of a raster object in the spatial reference system.
	ST_UpperLeftY	Returns the upper-left Y coordinate of a raster object in the spatial reference system.
	ST_SetUpperLeft	Sets the upper-left X and Y coordinates of a raster object in the spatial reference system.
	ST_PixelWidth	Returns the pixel width of a raster object in the spatial reference system.
	ST_PixelHeight	Returns the pixel height of a raster object in the spatial reference system.
	ST_Georeference	Returns the geographic reference information of a raster object. Affine parameters in the text format of "A,B,C,D,E,F" are returned.
	ST_IsGeoreferenced	Specifies whether a raster object is geographically referenced. The return value is t or f in the Boolean format.
	ST_UnGeoreference	Deletes the geographic reference information of a raster object.
	ST_SetGeoreference	Sets the geographic reference information for a raster object.
	ST_NoData	Returns the predefined NoData value for a band of a raster object.
	ST_SetNoData	Sets the NoData value for a specific band of a raster object.
	ST_ColorTable	Returns the color table of a band of a raster object in the JSON format.
	ST_SetColorTable	Sets the color table in the JSON format for a specific band of a raster object.
	ST_Statistics	Returns the statistics for a band of a raster object in the JSON format.
	ST_SetStatistics	Sets the statistics for a specific band of a raster object.
	ST_SummaryStats	Returns the statistics for a specific band set of a raster object.
	ST_ColorInterp	Returns the color interpretation type of a band of a raster object.
	ST_SetColorInterp	Sets the color interpretation type for a specific band of a raster object.
	ST_Histogram	Returns the histogram of a specific band of a raster object in the text format. If the band does not have a histogram, the function returns null.
	ST_SetHistogram	Sets the histogram in the JSON format for a specific band of a raster object.
	ST_BuildHistogram	Returns the histogram of a specific band set of a raster object.
	ST_StatsQuantile	Returns the quantiles of a raster object.
	ST_Quantile	Returns the pixel values of the quantiles for a raster object.
	ST_MD5Sum	Returns the MD5 hash string of a raster object.
	ST_SetMD5Sum	Sets the MD5 hash string for a raster object.
	ST_XMin	Returns the minimum X-coordinate value of a raster object.
	ST_YMin	Returns the minimum Y-coordinate value of a raster object.
	ST_XMax	Returns the maximum X-coordinate value of a raster object.
	ST_YMax	Returns the maximum Y-coordinate value of a raster object.
	ST_ChunkHeight	Returns the chunk height of a raster object.
	ST_ChunkWidth	Returns the chunk width of a raster object.
	ST_ChunkBands	Returns the number of bands for a chunk of a raster object.
	ST_MetaItems	Returns the customized metadata items of a raster object.
	ST_SetMetaData	Sets a metadata item for a raster object or a band.
	ST_BeginDateTime	Returns the start time of a raster object.
	ST_EndDateTime	Returns the end time of a raster object.
	ST_SetBeginDateTime	Sets the start time of a raster object.
	ST_SetEndDateTime	Sets the end time of a raster object.
	ST_DateTime	Returns the time information of a raster object or band.
	ST_SetDateTime	Sets the start and end time of a raster object or the time information of a band.
Operators	Equal to operator (=)	Determines whether the UUID of the first raster object is equal to that of the second.
	Greater than operator (>)	Determines whether the UUID of the first raster object is greater than that of the second.
	Less than operator (<)	Determines whether the UUID of the first raster object is less than that of the second.
	Greater than or equal to operator (>=)	Determines whether the UUID of the first raster object is greater than or equal to that of the second.
	Less than or equal to operator (<=)	Determines whether the UUID of the first raster object is less than or equal to that of the second.
Spatial relationship identification	ST_Intersects	Identifies the spatial relationship between two raster objects or between a raster object and a geometric object to determine whether the first object intersects with the second.
	ST_Contains	Identifies the spatial relationship between two raster objects or between a raster object and a geometric object to determine whether the first object completely contains the second.
	ST_ContainsProperly	Identifies the spatial relationship between two raster objects or between a raster object and a geometric object to determine whether the first object properly contains the second.
	ST_Covers	Identifies the spatial relationship between two raster objects or between a raster object and a geometric object to determine whether the first object covers the second.
	ST_CoveredBy	Identifies the spatial relationship between two raster objects or between a raster object and a geometric object to determine whether the first object is covered by the second.
	ST_Disjoint	Identifies the spatial relationship between two raster objects or between a raster object and a geometric object to determine whether the first object does not touch or intersect with the second.
	ST_overlaps	Identifies the spatial relationship between two raster objects or between a raster object and a geometric object to determine whether the first object overlaps with the second.
	ST_Touches	Identifies the spatial relationship between two raster objects or between a raster object and a geometric object to determine whether the first object touches the second.
	ST_Within	Identifies the spatial relationship between two raster objects or between a raster object and a geometric object to determine whether the first object is completely enclosed within the second.
Auxiliary functions	ST_CheckGPU	Checks whether a GPU is available in the current environment.
	ST_AKId	Returns the AccessKey ID that is used to access a raster object stored in OSS. This function can be used to modify multiple AccessKey IDs at a time.
	ST_SetAccessKey	Sets the AccessKey pair that is used to access a raster object stored in OSS.
	ST_SetAKId	Sets the AccessKey ID that is used to access a raster object stored in OSS.
	ST_SetAKSecret	Sets the AccessKey secret that is used to access a raster object stored in OSS.
Variables	ganos.raster.calculate_md5	Specifies whether the MD5 hash string is calculated and saved in the metadata when a raster object is imported.
	ganos.raster.md5sum_chunk_size	Sets the size of data that can be cached for each read operation when the MD5 hash string is calculated. Unit: MB.
	ganos.raster.mosaic_must_same_nodata	Specifies whether the values of NoData in a data source must be the same during the mosaic operation. The values of NoData are not changed during the mosaic operation. If you set this variable to false, the semantics of pixels after the mosaic operation may be changed.

SpatialRef SQL references

Function	Description
ST_SrEqual	Determines whether two spatial reference systems are the same.
ST_SrReg	Registers a spatial reference system.
ST_SrFromEsriWkt	Converts a string from the Esri well-known text (WKT) format to the Open Geospatial Consortium (OGC) WKT format.

Trajectory SQL references

Feature	Function	Description
Geometry constructors	ST_makeTrajectory	Constructs a trajectory object.
Geometry constructors	ST_append	Appends trajectory points or a sub-trajectory to a trajectory object.
Editing and processing functions	ST_Compress	Compresses a trajectory object based on the offset threshold of the Euclidean distance.
	ST_CompressSED	Compresses a trajectory object based on the offset threshold of the synchronous Euclidean distance (SED).
	ST_attrDeduplicate	Removes trajectory points that have duplicate values for an attribute field from a trajectory object and returns the deduplicated trajectory object. The start and end trajectory points of the trajectory object must be retained.
	ST_sort	Sorts the timeline of a trajectory object in ascending order and returns the trajectory object after sorting.
	ST_deviation	Returns the deviation of the processed trajectory object from the original trajectory object.
Attribute metadata	ST_attrDefinition	Returns the definitions of attribute fields in a trajectory object.
	ST_attrSize	Returns the number of attribute fields in a trajectory object.
	ST_attrName	Returns the name of an attribute field or the names of all attribute fields in a trajectory object.
	ST_attrType	Returns the data type of an attribute field in a trajectory object.
	ST_attrLength	Returns the defined length of an attribute field in a trajectory object.
	ST_attrNullable	Determines whether an attribute field can be null in a trajectory object.
Event functions	ST_addEvent	Adds an event to a trajectory object.
	ST_eventTimes	Returns the time of all events in a trajectory object.
	ST_eventTime	Returns the time of an event with a specific index in a trajectory object.
	ST_eventTypes	Returns the type IDs of all events in a trajectory object.
	ST_eventType	Returns the type ID of an event with a specific index in a trajectory object.
Attribute functions	ST_startTime	Returns the start time of a trajectory object.
	ST_endTime	Returns the end time of a trajectory object.
	ST_trajectorySpatial	Returns the spatial geometry object of a trajectory object.
	ST_trajectoryTemporal	Returns the timeline of a trajectory object.
	ST_trajAttrs	Returns the attribute fields of a trajectory object.
	ST_attrIntMax	Returns the maximum value of an INTEGER-type attribute field in a trajectory object.
	ST_attrIntMin	Returns the minimum value of an INTEGER-type attribute field in a trajectory object.
	ST_attrIntAverage	Returns the average value of an INTEGER-type attribute field in a trajectory object.
	ST_attrFloatMax	Returns the maximum value of a FLOAT-type attribute field in a trajectory object.
	ST_attrFloatMin	Returns the minimum value of a FLOAT-type attribute field in a trajectory object.
	ST_attrFloatAverage	Returns the average value of a FLOAT-type attribute field in a trajectory object.
	ST_leafType	Returns the leaf type of a trajectory object.
	ST_leafCount	Returns the number of leaves (trajectory points) in a trajectory object.
	ST_duration	Returns the duration of a trajectory object.
	ST_timeAtPoint	Returns a set of time points when a trajectory object passes through a spatial point.
	ST_pointAtTime	Returns the spatial geometry object of a trajectory object at a specific time point.
	ST_velocityAtTime	Returns the value of the velocity attribute field in a trajectory object at a specific time point.
	ST_accelerationAtTime	Returns the value of the acceleration attribute field in a trajectory object at a specific time point.
	ST_timeToDistance	Returns a line chart in which the time is on the x-axis and the Euclidean distance is on the y-axis.
	ST_timeAtDistance	Returns the time point at which a trajectory object moves from the start point for a specific distance.
	ST_cumulativeDistanceAtTime	Returns the cumulative distance that a trajectory object covers after it moves from the start point until a specific time point arrives.
	ST_timeAtCumulativeDistance	Returns the time point at which a trajectory object moves from the start point for a specific cumulative distance.
	ST_subTrajectory	Returns the sub-trajectory of a trajectory object within a specific time range.
	ST_subTrajectorySpatial	Returns the spatial geometry object of a trajectory object within a specific time range.
	ST_samplingInterval	Returns the sampling interval.
	ST_trajAttrsAsText	Returns an array of values for a TEXT-type attribute field in a trajectory object.
	ST_trajAttrsAsInteger	Returns an array of values for an INTEGER-type attribute field in a trajectory object.
	ST_trajAttrsAsDouble	Returns an array of values for a DOUBLE-type attribute field in a trajectory object.
	ST_trajAttrsAsBool	Returns an array of values for a BOOLEAN-type attribute field in a trajectory object.
	ST_trajAttrsAsTimestamp	Returns an array of values for a TIMESTAMP-type attribute field in a trajectory object.
	ST_attrIntFilter	Returns an array of values for an INTEGER-type attribute field in a trajectory object based on a specific fixed value or value range.
	ST_attrFloatFilter	Returns an array of values for a FLOAT-type attribute field in a trajectory object based on a specific fixed value or value range.
	ST_attrTimestampFilter	Returns an array of values for a TIMESTAMP-type attribute field in a trajectory object based on a specific fixed value or value range.
	ST_attrNullFilter	Filters trajectory points whose values are null for a specific attribute field in a trajectory object and returns a new trajectory object that consists of these trajectory points.
	ST_attrNotNullFilter	Filters trajectory points whose values are not null for a specific attribute field in a trajectory object and returns a new trajectory object that consists of these trajectory points.
	ST_trajAttrsMeanMax	Returns the maximum average value of an attribute field within each time range by using the MEAN-MAX algorithm.
Functions to process bounding boxes	ST_MakeBox	Returns the bounding box of an object within a specific time range.
	ST_MakeBox{Z\|T\|2D\|2DT\|3D\|3DT}	Builds a bounding box based on specified parameter settings.
	ST_BoxndfToGeom	Converts a bounding box into a geometry.
	ST_Has{xy\|z\|t}	Determines whether a bounding box has one or more specified dimensions.
	ST_{X\|Y\|Z\|T}Min	Returns the minimum value of a bounding box in a specific dimension.
	ST_{X\|Y\|Z\|T}Max	Returns the maximum value of a bounding box in a specific dimension.
	ST_ExpandSpatial	Expands a bounding box to a specific spatial scope.
Operators to process bounding boxes	INTERSECT operators	Determines whether the bounding box of the left operand-specified object intersects with that of the right operand-specified object in a specific dimension.
	INCLUDE operators	Determines whether the bounding box of the left operand-specified object includes that of the right operand-specified object in a specific dimension.
	INCLUDED operators	Determines whether the bounding box of the left operand-specified object is included in that of the right operand-specified object in a specific dimension.
Spatial relationship identification	ST_intersects	Determines whether a trajectory object and a geometry object intersect in space within a specific time range.
	ST_equals	Determines whether a trajectory object and a geometry object are spatially equal within a specific time range.
	ST_distanceWithin	Determines whether the distance between a trajectory object within a specific time range and a geometry object is within a specific distance.
Spatial processing	ST_intersection	Returns a new trajectory object that indicates the intersection of a trajectory object within a specific time range and a specific geometry object.
Spatial processing	ST_difference	Returns a new trajectory object that indicates the difference between a trajectory object within a specific time range and a specific geometry object.
Spatial statistics	ST_nearestApproachPoint	Returns the spatial point in a trajectory object that is nearest to a specific geometry object within a specific time range.
Spatial statistics	ST_nearestApproachDistance	Returns the nearest distance between a trajectory object and a specific geometry object within a specific time range.
Spatio-temporal relationship identification	ST_intersects	Determines whether trajectory objects 1 and 2 intersect in space within a specific time range.
	ST_equals	Determines whether trajectory objects 1 and 2 are spatially equal within a specific time range.
	ST_distanceWithin	Determines whether the distance between trajectory objects 1 and 2 within a specific time range is within a specific distance.
	ST_durationWithin	Determines whether the time difference between when trajectory objects 1 and 2 intersect at the same spatial point within a specific time range is within a specific interval.
	ST_{Z\|T\|2D\|2DT\|3D\|3DT}Intersects	Determines whether two objects intersect on a specific axis.
	ST_{2D\|2DT\|3D\|3DT}Intersects_IndexLeft	Determines whether two objects intersect on a specific axis and specifies to use the index on the column that stores the first object.
	ST_{2D\|2DT\|3D\|3DT}DWithin	Determines whether the distance between two objects on a specific axis is less than or equal to a specific threshold.
	ST_{2D\|2DT\|3D\|3DT}DWithin_IndexLeft	Determines whether the distance between two objects on a specific axis is less than or equal to a specific threshold and specifies to use the index on the column that stores the first object.
	ST_{T\|2D\|2DT\|3D\|3DT}Contains	Determines whether the first object includes the second object on a specific axis.
	ST_{T\|2D\|2DT\|3D\|3DT}Within	Determines whether the first object is included in the second object on a specific axis.
Spatio-temporal processing	ST_intersection	Returns a new trajectory object that indicates the intersection of trajectory objects 1 and 2 within a specific time range.
Spatio-temporal statistics	ST_nearestApproachPoint	Returns the spatial point in trajectory object 1 that is nearest to trajectory object 2 within a specific time range.
Spatio-temporal statistics	ST_nearestApproachDistance	Returns the nearest distance between trajectory objects 1 and 2 within a specific time range.
Distance measurement	ST_length	Returns the total length of a trajectory object, in meters.
	ST_euclideanDistance	Returns the Euclidean distance between two trajectory objects at the same time point.
	ST_mdistance	Returns an array of Euclidean distances between two trajectory objects at the same time point.
Similarity analysis	ST_lcsSimilarity	Returns the similarity between two trajectory objects based on the longest common sub-sequence (LCSS) algorithm.
	ST_lcsDistance	Returns the distance and time difference between two trajectory objects based on the LCSS algorithm.
	ST_lcsSubDistance	Returns the distance between an LCSS trajectory object and its sub-trajectory object.
Indexing	GiST Indexing	Creates a GiST index on a column that stores trajectory data.
Indexing	TrajGiST indexing	Creates a TrajGiST index on a column that stores trajectory data.
Variables	ganos.trajectory.attr_string_length	Sets a default length for STRING-type attribute fields.